pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Query Priority 4+ messages / 4 participants [nested] [flat]
* Query Priority @ 2003-05-02 12:55 Peter Childs <blue.dragon@blueyonder.co.uk> 0 siblings, 2 replies; 4+ messages in thread From: Peter Childs @ 2003-05-02 12:55 UTC (permalink / raw) To: pgsql-performance 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query Priority @ 2003-05-02 14:05 Andrew Sullivan <andrew@libertyrms.info> parent: Peter Childs <blue.dragon@blueyonder.co.uk> 1 sibling, 0 replies; 4+ messages in thread From: Andrew Sullivan @ 2003-05-02 14:05 UTC (permalink / raw) To: pgsql-performance 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query Priority @ 2003-05-02 14:23 Tom Lane <tgl@sss.pgh.pa.us> parent: Peter Childs <blue.dragon@blueyonder.co.uk> 1 sibling, 1 reply; 4+ messages in thread From: Tom Lane @ 2003-05-02 14:23 UTC (permalink / raw) To: Peter Childs <blue.dragon@blueyonder.co.uk>; +Cc: pgsql-performance 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 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Query Priority @ 2003-05-03 03:10 Ron Mayer <ron@intervideo.com> parent: Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 4+ messages in thread From: Ron Mayer @ 2003-05-03 03:10 UTC (permalink / raw) To: Peter Childs <blue.dragon@blueyonder.co.uk>; +Cc: pgsql-performance 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... ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-05-03 03:10 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-05-02 12:55 Query Priority Peter Childs <blue.dragon@blueyonder.co.uk> 2003-05-02 14:05 ` Andrew Sullivan <andrew@libertyrms.info> 2003-05-02 14:23 ` Tom Lane <tgl@sss.pgh.pa.us> 2003-05-03 03:10 ` Ron Mayer <ron@intervideo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox