Skip site navigation (1) Skip section navigation (2)

Re: RES: Priority to a mission critical transaction

From: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
To: Mark Kirkwood <markir(at)paradise(dot)net(dot)nz>
Subject: Re: RES: Priority to a mission critical transaction
Date: 2006-11-29 10:55:48
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-advocacypgsql-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(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 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.
 "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.

In response to

pgsql-performance by date

Next:From: Alessandro BarettaDate: 2006-11-29 11:31:55
Subject: NAMEDATALEN and performance
Previous:From: Mark KirkwoodDate: 2006-11-29 01:27:54
Subject: Re: RES: Priority to a mission critical transaction

pgsql-advocacy by date

Next:From: Simon RiggsDate: 2006-11-29 12:48:41
Subject: Re: Integrating Replication into Core
Previous:From: eleinDate: 2006-11-29 02:05:41
Subject: Re: Importand points about PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group