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


Wed, 21 Feb 2001 15:34:20 -0000
Chris Storah <cstorah@emis-support.demon.co.uk>
low priority postmaster threads?
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

Wed, 21 Feb 2001 16:41:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: low priority postmaster threads? 
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

Wed, 21 Feb 2001 18:10:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: low priority postmaster threads? 