Re: RES: Priority to a mission critical transaction - Mailing list pgsql-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
    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.

This paper: http://www.cs.cmu.edu/~bianca/icde04.pdf
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
PostgreSQL.

"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
 CPU-Prio-Inherit."

> See http://en.wikipedia.org/wiki/Priority_inversion

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:
 http://www.cs.cmu.edu/~bianca/icde04.pdf

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.