Re: tuning bgwriter in 8.4.2

[Ben Chobot <bench@xxxxxxxxxxxxxxx>]

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?