From: Andreas Kostyrka <andreas@kostyrka.org>
To: Carlos H. Reimer <carlos.reimer@opendb.com.br>
Cc: Jim C. Nasby <jim@nasby.net>
Cc: Brad Nicholson <bnichols@ca.afilias.info>
Cc: pgsql-performance@postgresql.org
Subject: Re: RES: Priority to a mission critical transaction
Date: Tue, 28 Nov 2006 20:20:36 +0100
Message-ID: <20061128192036.GB7489@andi-lap.la.revver.com> (raw)
In-Reply-To: <PEEPKDFEHHEMKBBFPOOKMEAIDKAA.carlos.reimer@opendb.com.br>
References: <20061127005143.GJ39519@nasby.net>
<PEEPKDFEHHEMKBBFPOOKMEAIDKAA.carlos.reimer@opendb.com.br>
* Carlos H. Reimer <carlos.reimer@opendb.com.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
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@kostyrka.org, carlos.reimer@opendb.com.br, jim@nasby.net, bnichols@ca.afilias.info
Subject: Re: RES: Priority to a mission critical transaction
In-Reply-To: <20061128192036.GB7489@andi-lap.la.revver.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