Saturday, February 19, 2011

Sizing shared_buffer of Postgresql

0 comments
Database: Postgresql 9.0.1.
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.

Leave a Reply

Labels