Re: RES: Priority to a mission critical transaction

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(dot)reimer(at)opendb(dot)com(dot)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.