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: Andreas Kostyrka <andreas(at)kostyrka(dot)org>, jim(at)nasby(dot)net, Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
Subject: Re: RES: Priority to a mission critical transaction
Date: 2006-11-28 20:31:28
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-advocacypgsql-performance
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
  * 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.

This paper:
studies setpriority() with non-trivial (TCP-W and TCP-C)
workloads on a variety of databases and shows that
that setpriority() is *extremely* effective for

"For TPC-C on MVCC DBMS, and in particular PostgreSQL,
 CPU scheduling is most effective, due to its ability
 to indirectly schedule the I/O bottleneck.

 For TPC-C running on PostgreSQL,
 the simplest CPU scheduling policy (CPU-Prio) provides
 a factor of 2 improvement for high-priority transactions,
 while adding priority  inheritance (CPU-Prio-Inherit)
 provides a factor of 6 improvement while hardly
 penalizing low-priority transactions. Preemption
 (P-CPU) provides no appreciable benefit over

> See

Priority Inversion is a well studied problem; and depends
on both the workload and the database.  In particular,
TPC-W workloads have been studied on a variety of databases
including PostgreSQL.  Again, from:

They observe that avoiding priority inversion
issues by enabling priority inheritance with PostgreSQL
has a negligible effect on TCP-W like workloads, but
a significant improvement on TCP-C like workloads.

  "Recall from Section 5.3 that CPU scheduling (CPUPrio)
   is more effective than NP-LQ for TPC-W. Thus Figure 8
   compares the policies CPU-Prio-Inherit to CPU-Prio for
   the TPC-W workload on PostgreSQL.

   We find that there is no improvement for CPU-Prio-
   Inherit over CPU-Prio. This is to be expected given
   the low data contention found in the TPC-W workload; priority
   inversions can only occur during data contention. Results
   for low-priority transactions are not shown, but as in
   Figure 4, low-priority transactions are only negligibly
   penalized on average."

Yes, theoretically priority inversion can have pathologically
bad effects (not unlike qsort), it affects some workloads more
than others.

But in particular, their paper concludes that
PostgreSQL with TCP-C and TCP-W like workloads
gain significant benefits and no drawbacks from
indirectly tuning I/O scheduling with setpriority().

If anyone has references to papers or studies that suggest that
priority inversion actually is a problem with RDBMS's - and
PostgreSQL on Linux in particular, I'd be very interested.

Otherwise it seems to me existing research points to
significant benefits with only theoretical drawbacks
in pathological cases.

In response to


pgsql-performance by date

Next:From: Bruce MomjianDate: 2006-11-28 23:44:45
Subject: Re: RES: Priority to a mission critical transaction
Previous:From: Josh BerkusDate: 2006-11-28 19:45:44
Subject: Re: RES: Priority to a mission critical transaction

pgsql-advocacy by date

Next:From: Mike EllsworthDate: 2006-11-28 20:35:22
Subject: Re: Collateral for LISA 06
Previous:From: Mike EllsworthDate: 2006-11-28 20:23:15
Subject: Re: Collateral for LISA 06

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