Re: RES: Priority to a mission critical transaction

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.

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.