Re: RES: Priority to a mission critical transaction - Mailing list pgsql-performance

From Ron Mayer
Subject Re: RES: Priority to a mission critical transaction
Date
Msg-id 456D6734.7040908@cheapcomplexdevices.com
Whole thread Raw
In response to Re: RES: Priority to a mission critical transaction  (Mark Kirkwood <markir@paradise.net.nz>)
List pgsql-performance
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.

pgsql-performance by date:

Previous
From: Mark Kirkwood
Date:
Subject: Re: RES: Priority to a mission critical transaction
Next
From: Alessandro Baretta
Date:
Subject: NAMEDATALEN and performance