Tuning shared_buffers

It is always at the back of a DBA’s mind “have i tuned Postgres shared_buffers properly?” And we start looking at all the blogs about how to set it and a hit and trial method or increasing it and then decreasing it to find the optimal value. Although it is true to probably set it a maximum of 25% of your total memory and do the math around the database size but there is one such tool that can give you an idea if it is time to increase the shared_buffers or have we just made it too big that performance is falling down.

pg_buffercache contrib module will help make a decision for you. How? lets see:

Assuming you have the source installer in place just navigate to the contrib directory in source here you would find a module called pg_buffercache.

All you would need to do to install it is

$ make

$make install

To register the functions with your database

$ psql -d <database> -f pg_buffercache.sql

Its now time to check if we need to increase the shared_buffers. Remember to run the below SQL at high peak load times a couple of times to get a more realistic data.

The query below will show usage count data:

select usagecount, count(*), isdirty from pg_buffercache
group by isdirty, usagecount order by isdirty, usagecount;
usagecount | count  | isdirty
------------+--------+---------
0 | 167072 | f
1 | 125923 | f
2 |  22361 | f
3 |  14248 | f
4 |  13284 | f
5 | 156717 | f
1 |  11605 | t
2 |   2528 | t
3 |   1996 | t
4 |   1239 | t
5 |   7315 | t

If there is a large number of buffers with high usage count of 4 or 5 your buffers are working well.

This query shows objects (tables and indexes) in cache:
SELECT
 c.relname, count(*) AS buffers,usagecount
FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname,usagecount
ORDER BY c.relname,usagecount

This shows how much of  relations are in cache:
SELECT
 c.relname,
 pg_size_pretty(count(*) * 8192) as buffered,
 round(100.0 * count(*) /
 (SELECT setting FROM pg_settings WHERE name='shared_buffers')::integer,1)
 AS buffers_percent,
 round(100.0 * count(*) * 8192 / pg_table_size(c.oid),1)
 AS percent_of_relation
FROM pg_class c
 INNER JOIN pg_buffercache b
 ON b.relfilenode = c.relfilenode
 INNER JOIN pg_database d
 ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.oid,c.relname
ORDER BY 3 DESC
LIMIT 20
Advertisements

, , ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: