From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Subject: Re: RES: Priority to a mission critical transaction
Date: Wed, 29 Nov 2006 02:55:48 -0800
Organization: Hub.Org Networking Services
Lines: 56
Message-ID: <456D6734.7040908@cheapcomplexdevices.com>
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>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
To: Mark Kirkwood <markir@paradise.net.nz>
User-Agent: Icedove 1.5.0.7 (X11/20061013)
In-Reply-To: <456CE21A.4010004@paradise.net.nz>
To: pgsql-performance@postgresql.org

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.