Thread: Query Priority
Is there some way to give some postgres backends higher priority.
Hence on a very busy server "important" queries get done faster than less
priority that unimportant queries.
I don't think this would be too difficult to do as certainly on
Linux the process could just be reniced and the os left to figure it out.
of course any query that is holding up another query with locks needs to
get done quickly.
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.
Peter Childs
On Fri, May 02, 2003 at 01:55:01PM +0100, Peter Childs wrote:
> Is there some way to give some postgres backends higher priority.
> Hence on a very busy server "important" queries get done faster than less
> priority that unimportant queries.
No.
> I don't think this would be too difficult to do as certainly on
> Linux the process could just be reniced and the os left to figure it out.
> of course any query that is holding up another query with locks needs to
> get done quickly.
It's the latter condition that causes the problem for the nice-ing
(among other things -- there's plenty of discussion about this in the
archives. Tom Lane gave a quite long explanation one time, but I
can't find it right now.)
> 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.
Sounds like what you really need is a replica database which you can
use for batch reports, &c. You could do this with a small-ish box,
because you'd only have one client.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Peter Childs <blue.dragon@blueyonder.co.uk> writes:
> Is there some way to give some postgres backends higher priority.
No.
> I don't think this would be too difficult to do as certainly on
> Linux the process could just be reniced and the os left to figure it out.
Read about "priority inversion" in any handy CS textbook ... renicing a
process won't buy you much if the locking mechanisms don't cooperate.
regards, tom lane
Peter Childs <blue.dragon@blueyonder.co.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...