Skip site navigation (1) Skip section navigation (2)

Re: RES: Priority to a mission critical transaction

From: Andreas Kostyrka <andreas(at)kostyrka(dot)org>
To: "Carlos H(dot) Reimer" <carlos(dot)reimer(at)opendb(dot)com(dot)br>
Cc: "Jim C(dot) Nasby" <jim(at)nasby(dot)net>,Brad Nicholson <bnichols(at)ca(dot)afilias(dot)info>,pgsql-performance(at)postgresql(dot)org
Subject: Re: RES: Priority to a mission critical transaction
Date: 2006-11-28 19:20:36
Message-ID: 20061128192036.GB7489@andi-lap.la.revver.com (view raw or flat)
Thread:
Lists: pgsql-advocacypgsql-performance
* Carlos H. Reimer <carlos(dot)reimer(at)opendb(dot)com(dot)br> [061128 20:02]:
> Hi,
> 
> There is an article about "Lowering the priority of a PostgreSQL query"
> (http://weblog.bignerdranch.com/?p=11) that explains how to use the
> setpriority() to lower PostgreSQL processes.
> 
> I?m wondering how much effective it would be for i/o bound systems.
> 
> Will the setpriority() system call affect i/o queue too?

Nope, and in fact the article shows the way not to do it.

See http://en.wikipedia.org/wiki/Priority_inversion

Basically, lowering the priority of one backend in PostgreSQL can lead
to reduced performance of all, especially also the backends with
higher priorities.

(Think of priority inversion as a timed soft deadlock. It will
eventually resolve, because it's not a real deadlock, but it might
mean halting important stuff for quite some time.)

Taking the example above, consider the following processes and nice
values:

19x backends As nice = 0
 1x backend B nice = 10 (doing maintenance work)
 1x updatedb nice = 5 (running as a cronjob at night)
 
 Now, it possible (the probability depends upon your specific
situation), where backend B grabs some internal lock that is needed,
and then it gets preempted by higher priority stuff. Well, the A
backends need that lock too, so they cannot run; instead we wait till
updatedb (which updates the locate search db, and goes through the
whole filesystem of the server) is finished.

Luckily most if not all of these processes are disc io bound, so they
get interrupted any way, and low priority processes don't starve.
Well, replace updatedb with something hogging the CPU, and rethink the
situation.

Andreas

In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2006-11-28 19:45:44
Subject: Re: RES: Priority to a mission critical transaction
Previous:From: Tom LaneDate: 2006-11-28 19:07:05
Subject: Re: RES: Priority to a mission critical transaction

pgsql-advocacy by date

Next:From: Brad NicholsonDate: 2006-11-28 19:22:52
Subject: Re: Integrating Replication into Core
Previous:From: Tom LaneDate: 2006-11-28 19:07:05
Subject: Re: RES: Priority to a mission critical transaction

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group