pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron Mayer <ron@intervideo.com>
To: Peter Childs <blue.dragon@blueyonder.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query Priority
Date: Fri, 2 May 2003 20:10:02 -0700
Message-ID: <POEDIPIPKGJJLDNIEMBEGEBKCLAA.ron@intervideo.com> (raw)
In-Reply-To: <10241.1051885390@sss.pgh.pa.us>

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...




view thread (4+ messages)

Message-ID: <POEDIPIPKGJJLDNIEMBEGEBKCLAA.ron@intervideo.com>
Permalink:  ../POEDIPIPKGJJLDNIEMBEGEBKCLAA.ron@intervideo.com/
Also on:    postgresql.org/message-id/POEDIPIPKGJJLDNIEMBEGEBKCLAA.ron@intervideo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: ron@intervideo.com, blue.dragon@blueyonder.co.uk
  Subject: Re: Query Priority
  In-Reply-To: <POEDIPIPKGJJLDNIEMBEGEBKCLAA.ron@intervideo.com>
  References: <10241.1051885390@sss.pgh.pa.us> <POEDIPIPKGJJLDNIEMBEGEBKCLAA.ron@intervideo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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