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@[EMAIL PROTECTED]
 http://www.gregsmith.com
Baltimore, MD

-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance