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