To: Gregory Stark <stark@enterprisedb.com>
cc: "Gaetano Mendola" <mendola@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: shared_buffers performance 
In-reply-to: <87r6d8hfgw.fsf@oxford.xeocode.com> 
References: <48032021.90704@gmail.com> <87r6d8hfgw.fsf@oxford.xeocode.com>
Comments: In-reply-to Gregory Stark <stark@enterprisedb.com>
	message dated "Mon, 14 Apr 2008 11:56:47 +0100"
Date: Mon, 14 Apr 2008 15:31:54 -0400
Message-ID: <21220.1208201514@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Gregory Stark <stark@enterprisedb.com> writes:
> The transition domain where performance drops dramatically as the database
> starts to not fit in shared buffers but does still fit in filesystem cache.

It looks to me like the knee comes where the DB no longer fits in
filesystem cache.  What's interesting is that there seems to be no
synergy at all between shared_buffers and the filesystem cache.
Ideally, very hot pages would stay in shared buffers and drop out of the
kernel cache, allowing you to use a database approximating all-of-RAM
before you hit the performance wall.  It's clear that in this example
that's not happening, or at least that only a small part of shared
buffers isn't getting duplicated in filesystem cache.

Of course, that's because pgbench reads a randomly-chosen row of
"accounts" in each transaction, so that there's exactly zero locality
of access.  A more realistic workload would probably have a Zipfian
distribution of account number touches, and might look a little better
on this type of test.

			regards, tom lane