Database: Postgresql 9.0.1.
pg_buffercache is required to execute the following query and sizing shared_buffer based on the result.
Inspecting the result, I found that more than 50% of buffercache blocks were accumulated with a high usage count that is 4,5.
This was a strong evidence that I needed to increase the value for shared_buffer. As I had a dedicated database server with 32 GB RAM, I increased the value from 2 Gb to 4GB.
pg_buffercache is required to execute the following query and sizing shared_buffer based on the result.
SELECT
usagecount,count(*),isdirty,
round((count(*)/max(total_cache.cnt)::float*100)::numeric,2) as percent_of_total_cache
FROM pg_buffercache,
( select count(*) as cnt from pg_buffercache) as total_cache
GROUP BY isdirty,usagecount
ORDER BY isdirty,usagecount;
usagecount | count | isdirty | percent_of_total_cache
------------+--------+---------+------------------------
0 | 44204 | f | 16.86
1 | 39288 | f | 14.99
2 | 18917 | f | 7.22
3 | 10702 | f | 4.08
4 | 39549 | f | 15.09
5 | 109484 | f | 41.76
(6 rows)
usagecount | count | isdirty | percent_of_total_cache
------------+--------+---------+------------------------
0 | 44204 | f | 16.86
1 | 39288 | f | 14.99
2 | 18917 | f | 7.22
3 | 10702 | f | 4.08
4 | 39546 | f | 15.09
5 | 109435 | f | 41.75
5 | 52 | t | 0.02
(7 rows)
usagecount | count | isdirty | percent_of_total_cache
------------+--------+---------+------------------------
0 | 44204 | f | 16.86
1 | 39288 | f | 14.99
2 | 18917 | f | 7.22
3 | 10702 | f | 4.08
4 | 39546 | f | 15.09
5 | 109487 | f | 41.77
(6 rows)
Inspecting the result, I found that more than 50% of buffercache blocks were accumulated with a high usage count that is 4,5.
This was a strong evidence that I needed to increase the value for shared_buffer. As I had a dedicated database server with 32 GB RAM, I increased the value from 2 Gb to 4GB.
