The short question:
Is there any ways to give postgresql a hint that a
particular SQL call should be run at lower priority? Since every db
connection has a pid, I can manually run "renice" to scheduele it by the OS
- but of course I can't do it manually all the time.
The long story:
We have a constantly growing database, and hence also a constantly growing
load on the database server. A hardware upgrade has for different reasons
been postponed, and it's still beeing postponed.
We were hitting the first capacity problems in June, though so far I've
managed to keep the situation in check by tuning the configuration, adding
indices, optimizing queries, doing cacheing in the application, and at one
point in the code I'm even asking the database for "explain plan", grepping
out the estimated cost number, and referring the user to take contact with
the IT-dept if he really needs the report. But I digress.
Still there are lots of CPU power available - normally the server runs with
50-80% of the CPUs idle, it's just the spikes that kills us.
We basically have two kind of queries that are significant - an ever-ongoing
"critical" rush of simple queries, both reading and writing to the database,
plus some few heavy "non-critical" read-only queries that may cause
significant iowait. The problem comes when we are so unlucky that two or
three heavy queries are run simultaneously; we get congestion problems -
instead of the applications just running a bit slower, they run _much_
slower.
Ideally, if it was trivial to give priorities, it should be possible to keep
the CPUs running at 100% for hours without causing critical problems...?
--
Tobias Brox, +47-91700050
Tromso, Norway
