From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Subject: Re: lowering priority automatically at connection
Date: Tue, 06 Jun 2006 16:27:16 -0700
Organization: Hub.Org Networking Services
Lines: 47
Message-ID: <44860F54.7080401@cheapcomplexdevices.com>
References: <1148573784.6017.26.camel@dell.home.lan>
	<13758.1148574403@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
To: Tom Lane <tgl@sss.pgh.pa.us>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
In-Reply-To: <13758.1148574403@sss.pgh.pa.us>
To: pgsql-performance@postgresql.org

Tom Lane wrote:
> That guy doesn't actually have the foggiest idea what he's doing.
> The reason there is no built-in capability to do that is that it *does
> not work well*.  Search the list archives for "priority inversion" to
> find out why not.

I agree that that particular author seems clueless, but better
researched papers do show benefits as well:

The CMU paper
"Priority Mechanisms for OLTP and Transactional Web Applications" [1]
studied both TPC-C and TPC-W workloads on postgresql (as well as DB2).
For PostgreSQL they found that without priority inheritance they
had factor-of-2 benefits for high-priority transactions;
and with priority inheritance they had factor-of-6 benefits
for high priority transactions -- both with negligible harm
to the low priority transactions.

Unless there's something wrong with that paper (and at first glance
it looks like their methodologies apply at least to many workloads)
it seems that "it *does not work well*" is a bit of a generalization;
and that databases with TPC-C and TPC-W like workloads may indeed
be cases where this feature would be useful.

[1] http://www.cs.cmu.edu/~harchol/Papers/actual-icde-submission.pdf
"
  ...This paper analyzes and proposes prioritization for
  transactional workloads in conventional DBMS...This paper
  provides a detailed resource utilization breakdown for
  OLTP workloads executing on a range of database platforms
  including IBM DB2[14], Shore[16], and PostgreSQL[17]....
   ...
  For DBMS using MVCC (with TPC-C or TPC-W workloads) and
  for TPC-W workloads (with any concurrency control mechanism),
  we find that lock scheduling is largely ineffective (even
  preemptive lock scheduling) and CPU scheduling is highly
  effective. For example, we find that for PostgreSQL
  running under TPC-C, the simplest CPU scheduling
  algorithm CPU-Prio provides a factor of 2 improvement
  for the high-priority transactions, and adding priority
  inheritance (CPU-Prio-Inherit) brings this up to a factor
  of near 6 improvement under high loads, while hardly
  penalizing low-priority transactions.
"

    Or am I missing something?
    Ron