Re: Query Priority

Peter Childs <blue(dot)dragon(at)blueyonder(dot)co(dot)uk> writes:
>Is there some way to give some postgres backends higher priority.

>I find my self with a database thats slowed to a craw because of a
>slow batch program it not letting the gui clients the speed they require
>to be usable.

Well, if your backend-process is CPU bound (large in memory sorts & joins)...

The Linux (and SunOS and Ultrix and most certainly others) scheduler,
has logic to make "interactive tasks" more responsive automatically provides
whatever the benefit you might expect by automatically adjusting the
priority of such processes.

If you have one backend hogging the CPU, it _will_ use its entire time
slice, and not get a "bonus" to it's priority. In contrast, backends
that do simple fast queries will _not_ use their whole time slice, so
they look like "interactive processes" to the kernel and do get a bonus
to their priority.

I think that this means that a reporting system where some people are
doing big sorts and others are doing little fast queries, the little
fast ones actually do run at a higher priority (assuming they don't
have to block waiting for each other).

These slides:
http://www.inf.fu-berlin.de/lehre/SS01/OS/Lectures/Lecture08.pdf
explain how this works.

On the other hand, if your process is IO bound (large table seq_scan)...

I don't think setting the scheduler priority will help your application
much anyway.

I strongly suspect which I/O scheduler you're using would have a bigger
effect than a priority setting. There are a few to choose from.
http://lwn.net/Articles/23411/
http://www.cs.rice.edu/~ssiyer/r/antsched/shines.html
I haven't tried any except the default.

If the batch process you're worrying about is I/O bound and doing lots of writes...
... you might want to play with one of the newer I/O schedulers in
the newer kernel...

It looks like the 2.4 I/O scheduler has a bad behavior where a process
doing lots of writes starves other processes that are trying to do
reads...
http://www.ussg.iu.edu/hypermail/linux/kernel/0302.2/1370.html
all the newer schedulers seem to improve this.

PS: Did anyone try any of the newer I/O schedulers? I have a reporting
system that gets pretty unresponsive while large loads of data are
occurring, and was curious if those patches would be the answer...