On Feb 17, 2010, at 6:38 PM, Greg Smith wrote:
Ben Chobot wrote:
Is there a way to tell if I really am just keeping the same few pages dirty throughout every checkpoint? I wouldn't have expected that, but given our application I suppose it is possible.
You can install pg_buffercache and look at what's in the cache to check your theory. I have some sample queries that show neat things at
http://www.westnet.com/~gsmith/content/postgresql/bufcache.sh
This appears to be fairly typical:
# select count(*),isdirty,usagecount from pg_buffercache group by isdirty,usagecount order by usagecount desc,isdirty;
count | isdirty | usagecount
--------+---------+------------
670629 | f | 5
75766 | t | 5
237311 | f | 4
5372 | t | 4
74682 | f | 3
31 | t | 3
73786 | f | 2
18 | t | 2
104112 | f | 1
62 | t | 1
68951 | f | 0
(11 rows)
Is it reading it correctly to say that the bgwriter probably wouldn't help much, because a majority of the dirty pages appear to be popular?