Thread: low priority postmaster threads?

Is there any way in psql to connect to a database and reduce the run
priority of the child thread it kicks off ?
i.e. equivalent of 'nice' on the thread?

From first looks at the code, it seems to fork off the process and there is
a pid that can be niced.
If an extra run level parameter is passed in to the PQExec interface
(defaulted for compatibility with older code), would it work?

This assumes that there isn't already a mechanism to reduce the priority of
specific queries.

What I am looking for is a postgres system that runs 100 users or so at
'full speed', and major day long queries at a 'when idle' priority.


Cheers,
Chris

Chris Storah <cstorah@emis-support.demon.co.uk> writes:
> Is there any way in psql to connect to a database and reduce the run
> priority of the child thread it kicks off ?
> i.e. equivalent of 'nice' on the thread?

Not at the moment, though it'd be a fairly trivial hack on postgres.c
to add a "-nice n" backend switch, which you could then specify at
connection time via PGOPTIONS.

> What I am looking for is a postgres system that runs 100 users or so at
> 'full speed', and major day long queries at a 'when idle' priority.

The trouble here is that CPU nice doesn't (on most platforms) change the
behavior of the I/O scheduler, so this would only be of use to the
extent that your queries are CPU bound and not I/O bound.
        regards, tom lane

I wrote:
> Chris Storah <cstorah@emis-support.demon.co.uk> writes:
>> What I am looking for is a postgres system that runs 100 users or so at
>> 'full speed', and major day long queries at a 'when idle' priority.

> The trouble here is that CPU nice doesn't (on most platforms) change the
> behavior of the I/O scheduler, so this would only be of use to the
> extent that your queries are CPU bound and not I/O bound.

Now that I think twice, there's an even more severe problem with trying
to nice() down an individual backend, namely priority inversion.

What happens when the low-priority process holds some lock or other,
and then a higher-priority process comes along and wants the lock?
The high-priority process has to wait, that's what.  But there's no
mechanism to raise the priority of the lower-priority lock holder, which
means that the high-priority process is now effectively lowered to the
lower priority; it may have to wait quite a long time, if there are
other high-priority processes sucking CPU away from the low-priority
guy.

In short, forget about nice'ing an individual backend; you probably
won't like the results.  Sorry.
        regards, tom lane

Tom Lane Wrote:
> The trouble here is that CPU nice doesn't (on most platforms) change the
> behavior of the I/O scheduler, so this would only be of use to the
> extent that your queries are CPU bound and not I/O bound.

Assuming there is a major processor hit, and the backend has a UW-SCSI RAID
box with enough I/O capability...


>What happens when the low-priority process holds some lock or other,
>and then a higher-priority process comes along and wants the lock?

If the query was a select only, would the locking problem still apply?
(The long queries in this case are in the form of 'select * from [all tables
joined together] where x')

I will make a couple of changes and test it to see if there are any
performance gains in particular cases.
The other option is to add another processor :)

Chris