From: Tobias Brox <tobias@nordicbet.com>
To: pgsql-performance@postgresql.org
Subject: "nice"/low priority Query
Date: Tue, 2 Aug 2005 18:04:34 +0200
Message-ID: <20050802160434.GD9278@tobias.lan> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tobias@nordicbet.com
Subject: Re: "nice"/low priority Query
In-Reply-To: <20050802160434.GD9278@tobias.lan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox