From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Subject: Re: Background vacuum
Date: Thu, 10 May 2007 17:10:56 -0700
Organization: Hub.Org Networking Services
Lines: 56
Message-ID: <4643B490.2040704@cheapcomplexdevices.com>
References: <4642A1B3.6030009@swissembedded.com>
	<4642A811.9010703@drivefaster.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
To: Dan Harris <fbsd@drivefaster.net>,
	Ron Mayer <rm_pg@cheapcomplexdevices.com>
User-Agent: Icedove 1.5.0.10 (X11/20070329)
In-Reply-To: <4642A811.9010703@drivefaster.net>
To: pgsql-performance@postgresql.org

Dan Harris wrote:
> Daniel Haensse wrote:
>> Has anybody a nice
>> solution to change process priority? A shell script, maybe even for java?

One way is to write astored procedure that sets it's own priority.
An example is here:
http://weblog.bignerdranch.com/?p=11


> While this may technically work, I think it lacks a key point.  'nice' (
> at least the versions I'm familiar with ) do not adjust I/O priority. 
> VACUUM is bogging things down because of the extra strain on I/O.  CPU
> usage shouldn't really be much of a factor.

Actually, CPU priorities _are_ an effective way of indirectly scheduling
I/O priorities.

This paper studied both CPU and lock priorities on a variety
of databases including PostgreSQL.

http://www.cs.cmu.edu/~bianca/icde04.pdf

" By contrast, for PostgreSQL, lock scheduling is not as
  effective as CPU scheduling (see Figure 4(c)).
  ...
  The effectiveness of CPU-Prio for TPC-C on
  PostgreSQL is surprising, given that I/O (I/O-related
  lightweight locks) is its bottleneck. Due to CPU prioritization,
  high-priority transactions are able to request I/O resources
  before low-priority transactions can. As a result,
  high-priority transactions wait fewer times (52% fewer) for
  I/O, and when they do wait, they wait behind fewer transactions
  (43% fewer). The fact that simple CPU prioritization
  is able to improve performance so significantly suggests that
  more complicated I/O scheduling is not always necessary.
  ...
  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
  "

> Instead, I would recommend looking at vacuum_cost_delay and the related
> settings to make vacuum lower priority than the queries you care about. 
> This should be a cleaner solution for you.

Yeah, that's still true.