From: Greg Smith <gsmith@gregsmith.com>
To: pgsql-performance@postgresql.org
Subject: Re: shared_buffers performance
Date: Mon, 14 Apr 2008 16:08:48 -0400 (EDT)
Message-ID: <Pine.GSO.4.64.0804141546230.189@westnet.com> (raw)
In-Reply-To: <21220.1208201514@sss.pgh.pa.us>
References: <48032021.90704@gmail.com>
<87r6d8hfgw.fsf@oxford.xeocode.com>
<21220.1208201514@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gsmith@gregsmith.com
Subject: Re: shared_buffers performance
In-Reply-To: <Pine.GSO.4.64.0804141546230.189@westnet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox