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.0903161052430.9846@westnet.com> (Greg Smith's
	message of "Mon\, 16 Mar 2009 11\:04\:23 -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>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Mon, 16 Mar 2009 15:28:10 +0000
Message-ID: <87bps132t1.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, ml@bortal.de wrote:
>
>> Any idea why my performance colapses at 2GB Database size?

I don't understand how you get that graph from the data above. The data above
seems to show your test databases at 1.4GB and 2.9GB. There are no 1GB and 2GB
data points like the graphs show.

Presumably the data point at 2G on the graph should really be at 2.9GB? In
which case I don't find it surprising at all that performance would start to
shift from RAM-resident before that to disk-resident above that. You have 1GB
set aside for shared buffers leaving about 3GB for filesystem cache.

You could try setting shared buffers smaller, perhaps 512kB or larger, perhaps
3.5GB. To minimize the overlap. I would tend to avoid the latter though.

One thing to realize is that pgbench performs a completely flat distribution
of data accesses. So every piece of data is equally likely to be used. In real
life work-loads there are usually some busier and some less busy sections of
the database and the cache tends to keep the hotter data resident even as the
data set grows.

> In your case, you've got shared_buffers=1GB, but the rest of the RAM is the
> server isn't so useful to you because you've got checkpoint_segments set to the
> default of 3.  That means your system is continuously doing small checkpoints
> (check your database log files, you'll see what I meant), which keeps things
> from ever really using much RAM before everything has to get forced to disk.

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.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!