To: Greg Smith <gsmith@gregsmith.com>
Cc: "ml\@bortal.de" <ml@bortal.de>,  pgsql-performance@postgresql.org
Subject: Re: Postgres benchmarking with pgbench
In-Reply-To: <alpine.GSO.2.01.0903161227130.13431@westnet.com> (Greg Smith's
	message of "Mon\, 16 Mar 2009 12\:39\:43 -0400 \(EDT\)")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: <49BE2E84.2040506@bortal.de>
	<alpine.GSO.2.01.0903161052430.9846@westnet.com>
	<87bps132t1.fsf@oxford.xeocode.com>
	<alpine.GSO.2.01.0903161227130.13431@westnet.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Tue, 17 Mar 2009 00:17:18 +0000
Message-ID: <87ljr50zqp.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Greg Smith <gsmith@gregsmith.com> writes:

> On Mon, 16 Mar 2009, Gregory Stark wrote:
>
>> Why would checkpoints force out any data? It would dirty those pages and then
>> sync the files marking them clean, but they should still live on in the
>> filesystem cache.
>
> The bulk of the buffer churn in pgbench is from the statement that updates a
> row in the accounts table.  That constantly generates updated data block and
> index block pages.  If you can keep those changes in RAM for a while before
> forcing them to disk, you can get a lot of benefit from write coalescing that
> goes away if constant checkpoints push things out with a fsync behind them.
>
> Not taking advantage of that effectively reduces the size of the OS cache,
> because you end up with a lot of space holding pending writes that wouldn't
> need to happen at all yet were the checkpoints spaced out better.

Ok, so it's purely a question of write i/o, not reduced cache effectiveness.
I think I could see that. I would be curious to see these results with a
larger checkpoint_segments setting.

Looking further at the graphs I think they're broken but not in the way I had
guessed. It looks like they're *overstating* the point at which the drop
occurs. Looking at the numbers it's clear that under 1GB performs well but at
1.5GBP it's already dropping to the disk-resident speed.

I think pgbench is just not that great a model for real-world usage . a) most
real world workloads are limited by read traffic, not write traffic, and
certainly not random update write traffic; and b) most real-world work loads
follow a less uniform distribution so keeping busy records and index regions
in memory is more effective.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!