From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: RES: Priority to a mission critical transaction
Date: Wed, 29 Nov 2006 02:55:48 -0800
Message-ID: <456D6734.7040908@cheapcomplexdevices.com> (raw)
In-Reply-To: <456CE21A.4010004@paradise.net.nz>
References: <20061127005143.GJ39519@nasby.net>
<PEEPKDFEHHEMKBBFPOOKMEAIDKAA.carlos.reimer@opendb.com.br>
<20061128192036.GB7489@andi-lap.la.revver.com>
<456C9CA0.3040604@cheapcomplexdevices.com>
<456CE21A.4010004@paradise.net.nz>
Mark Kirkwood wrote:
> Ron Mayer wrote:
>> Short summary:
>> * Papers studying priority inversion issues with
>> databases including PosgreSQL and realistic workloads
>> conclude setpriority() helps even in the presence of
>> priority inversion issues for TCP-C and TCP-W like
>> workloads.
>> * Avoiding priority inversion with priority inheritance
>> will further help some workloads (TCP-C) more than
>> others (TCP-W) but even without such schedulers
>> priority inversion does not cause as much harm
>> as the benefit you get from indirectly scheduling
>> I/O through setpriority() in any paper I've seen.
>>
>> Andreas Kostyrka wrote:
>>> * Carlos H. Reimer <carlos.reimer@opendb.com.br> [061128 20:02]:
>>>> Will the setpriority() system call affect i/o queue too?
>>> Nope, and in fact the article shows the way not to do it.
>>
>> Actually *YES* setpriority() does have an indirect effect
>> on the I/O queue.
>>
>
> While I was at Greenplum a related point was made to me:
>
> For a TPC-H/BI type workload on a well configured box the IO subsystem
> can be fast enough so that CPU is the bottleneck for much of the time -
> so being able to use setpriority() as a resource controller makes sense.
Perhaps - but section 4 of the paper in question (pages 3 through 6
of the 12 pages at http://www.cs.cmu.edu/~bianca/icde04.pdf) go
through great lengths to identify the bottlenecks for each workload
and each RDBMS. Indeed for the TCP-W on PostgreSQL and DB2, CPU
was a bottleneck but no so for TCP-C - which had primarily I/O
contention on PostgreSQL and lock contention on DB2.
http://www.cs.cmu.edu/~bianca/icde04.pdf
"for TPC-C ... The main result shown in Figure 1 is that locks
are the bottleneck resource for both Shore and DB2 (rows 1 and
2), while I/O tends to be the bottleneck resource for PostgreSQL
(row 3). We now discuss these in more detail.
...
Thus, CPU is the bottleneck resource for TPC-W 1."
> Also, with such a workload being mainly SELECT type queries, the dangers
> connected with priority inversion are considerably reduced.
And indeed the TCP-W benchmark did not show further improvement
for high priority transactions with Priority Inheritance enabled
in the scheduler (which mitigates the priority inversion problem) -
but the TCP-C benchmark did show further improvement -- which agrees
with Mark's observation. However even with priority inversion
problems; the indirect benefits of setpriority() on I/O scheduling
outweighed the penalties of priority inversion in each of their
test cases.
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: rm_pg@cheapcomplexdevices.com, markir@paradise.net.nz
Subject: Re: RES: Priority to a mission critical transaction
In-Reply-To: <456D6734.7040908@cheapcomplexdevices.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