Date: Thu, 17 May 2007 22:50:48 -0400 (EDT)
From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-performance@postgresql.org
Subject: Re: Background vacuum
In-Reply-To: <464D04F8.7020306@cheapcomplexdevices.com>
Message-ID: <Pine.GSO.4.64.0705172246320.20470@westnet.com>
References: <4642A1B3.6030009@swissembedded.com>
	<4642A811.9010703@drivefaster.net>
	<4643B490.2040704@cheapcomplexdevices.com>
	<20070517135530.GI6907@phlogiston.dyndns.org>
	<464D04F8.7020306@cheapcomplexdevices.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

Ah, glad this came up again 'cause a problem here caused my original reply 
to bounce.

On Thu, 10 May 2007, Ron Mayer wrote:

> 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

I spent a fair amount of time analyzing that paper recently, and found it 
hard to draw any strong current conclusions from it.  Locking and related 
scalability issues are much better now than in the PG 7.3 they tested. 
For example, from the paper:

"We find almost all lightweight locking in PostgreSQL fucntions to 
serialize the I/O buffer pool and WAL activity...as a result, we attribute 
all the lightweight lock waiting time for the above-listed locks to I/O."

Well, sure, if you classify those as I/O waits it's no surprise you can 
darn near directly control them via CPU scheduling; I question the current 
relevancy of this historical observation about the old code.  I think it's 
much easier to get into an honest I/O bound situation now with a TPC-C 
like workload (they kind of cheated on that part too which is a whole 
'nother discussion), especially with the even faster speeds of modern 
processors, and then you're in a situation where CPU scheduling is not so 
effective for indirectly controlling I/O prioritization.

Count me on the side that agrees adjusting the vacuuming parameters is the 
more straightforward way to cope with this problem.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD