Thread: "nice"/low priority Query

From:
Tobias Brox
Date:

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

From:
Tom Lane
Date:

Tobias Brox <> writes:
> 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.

And it won't help you anyway, because renice only affects CPU priority
not I/O scheduling ... which, by your description, is the real problem.

I think the only thing that's likely to help much is trying to arrange
that the "simple" queries only need to touch pages that are already in
memory.  Some playing around with shared_buffer sizing might help.
Also, if you're not on PG 8.0.*, an update might help.

            regards, tom lane

From:
Richard Huxton
Date:

Tom Lane wrote:
> Tobias Brox <> writes:
>
>>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.
>
> And it won't help you anyway, because renice only affects CPU priority
> not I/O scheduling ... which, by your description, is the real problem.
>
> I think the only thing that's likely to help much is trying to arrange
> that the "simple" queries only need to touch pages that are already in
> memory.  Some playing around with shared_buffer sizing might help.
> Also, if you're not on PG 8.0.*, an update might help.

Would it be useful to be able to re-use the vacuum_cost_xxx settings in
8.0 for this sort of thing? I'm thinking a long-running report query
isn't that different from a vacuum.

--
   Richard Huxton
   Archonet Ltd

From:
"Jim C. Nasby"
Date:

On Tue, Aug 02, 2005 at 12:19:30PM -0400, Tom Lane wrote:
> Tobias Brox <> writes:
> > 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.
>
> And it won't help you anyway, because renice only affects CPU priority
> not I/O scheduling ... which, by your description, is the real problem.

Actually, from what I've read 4.2BSD actually took priority into account
when scheduling I/O. I don't know if this behavior is still present in
FreeBSD or the like, though. So depending on the OS, priority could play
a role in determining I/O scheduling.
--
Jim C. Nasby, Database Consultant               
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

From:
Tobias Brox
Date:

[Tobias Brox - Tue at 06:04:34PM +0200]
> (...) 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.

I just came to think about some more "dirty" tricks I can do.  I have turned
on stats collection in the configuration; now, if I do:

  select count(*) from pg_stat_activity where not current_query like '<IDLE>%';

or, eventually:

  select count(*) from pg_stat_activity
  where not current_query like '<IDLE>%' and query_start+'1 second'<now();

it will give a hint about how busy the database server is, thus I can
eventually let the application sleep and retry if there are any other heavy
queries in progress.

--
Tobias Brox, +47-91700050
Nordicbet, IT dept

From:
Richard Huxton
Date:

Tobias Brox wrote:
> [Tobias Brox - Tue at 06:04:34PM +0200]
>
>>(...) 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.
>
>
> I just came to think about some more "dirty" tricks I can do.  I have turned
> on stats collection in the configuration; now, if I do:
>
>   select count(*) from pg_stat_activity where not current_query like '<IDLE>%';
>
> or, eventually:
>
>   select count(*) from pg_stat_activity
>   where not current_query like '<IDLE>%' and query_start+'1 second'<now();
>
> it will give a hint about how busy the database server is, thus I can
> eventually let the application sleep and retry if there are any other heavy
> queries in progress.

Or - create a table with an estimated_cost column, when you start a new
"heavy" query, insert that query's cost, then sleep
SUM(estimated_cost)/100 secs or something. When the query ends, delete
the cost-row.

Hmm - actually rather than dividing by 100, perhaps make it a tunable value.
--
   Richard Huxton
   Archonet Ltd

From:
Neil Conway
Date:

Jim C. Nasby wrote:
> Actually, from what I've read 4.2BSD actually took priority into account
> when scheduling I/O.

FWIW, you can set I/O priority in recent versions of the Linux kernel
using ionice, which is part of RML's schedutils package (which was
recently merged into util-linux).

-Neil