Date: Mon, 18 Feb 2008 16:24:30 -0500 (EST)
From: Greg Smith <gsmith@gregsmith.com>
To: Peter Schuller <peter.schuller@infidyne.com>
cc: pgsql-performance@postgresql.org
Subject: Re: shared_buffers in 8.3 w/ lots of RAM on dedicated PG
 machine
In-Reply-To: <200802180839.46557.peter.schuller@infidyne.com>
Message-ID: <Pine.GSO.4.64.0802181606160.29622@westnet.com>
References: <20080215123528.GA16532@hyperion.scode.org>
	<20080215135846.GA17327@hyperion.scode.org>
	<Pine.GSO.4.64.0802150916380.28161@westnet.com>
	<200802180839.46557.peter.schuller@infidyne.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

On Mon, 18 Feb 2008, Peter Schuller wrote:

> Am I interpreting that correctly in that dirty buffers need to be 
> flushed to disk at checkpoints? That makes perfect sense - but why would 
> that not be the case with OS buffers?

All the dirty buffers in the cache are written out as part of the 
checkpoint process--all at once in earlier versions, spread out based on 
checkpoint_completion_target in 8.3.  In the worst case you could 
theoretically have to write the entire shared_buffer cache out, however 
big it is, if you managed to get it all dirty just before the checkpoint.

Ultimately everything written to the database (again, with the exception 
of non-standard direct I/O setups) passes through the OS buffers, so in 
that respect the OS buffers will also be flushed when the checkpoint does 
its cleansing fsync.

But dirty buffers for less popular pages do get written before the 
checkpoint occurs.  As there is a need to allocate new pages for the 
database to work with, it evicts pages in order to find space, and if the 
page given the boot is dirty it gets written to the OS buffer cache. 
Those writes trickle out to disk in advance of the checkpoint itself.  If 
you've pushed the majority of memory into the PostgreSQL cache, that won't 
happen as much (more shared_buffers=>less evictions+less OS cache) and 
there's a potential for longer, more intensive checkpoints.

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