pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / 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