Re: shared_buffers performance

On Mon, 14 Apr 2008, Tom Lane wrote:

> 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.

With "pgbench -S", the main hot pages that get elevated usage counts and
cling persistantly to shared buffers are those holding data from the
primary key on the accounts table.

Here's an example of what the buffer cache actually has after running
"pgbench -S -c 8 -t 10000 pgbench" on a system with shared_buffers=256MB
and a total of 2GB of RAM.  Database scale is 100, so there's
approximately 1.5GB worth of database, mainly a 1.3GB accounts table and
171MB of primary key on accounts:

relname       |buffered| buffers % | % of rel
accounts      | 306 MB | 65.3      | 24.7
accounts pkey | 160 MB | 34.1      | 93.2

relname       | buffers | usage
accounts      | 10223   | 0
accounts      | 25910   | 1
accounts      | 2825    | 2
accounts      | 214     | 3
accounts      | 14      | 4
accounts pkey | 2173    | 0
accounts pkey | 5392    | 1
accounts pkey | 5086    | 2
accounts pkey | 3747    | 3
accounts pkey | 2296    | 4
accounts pkey | 1756    | 5

This example and the queries to produce that summary are all from the
"Inside the PostgreSQL Buffer Cache" talk on my web page.

For this simple workload, if you can fit the main primary key in shared
buffers that helps, but making that too large takes away memory that could
be more usefully given to the OS to manage.  The fact that you can start
to suffer from double-buffering (where the data is in the OS filesystem
cache and shared_buffers) when making shared_buffers too large on a
benchmark workload is interesting.  But I'd suggest considering the real
application, rather than drawing a conclusion about shared_buffers sizing
based just on that phenomenon.

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