Re: Details about pg_stat_bgwriter

On Tue, Jun 8, 2010 at 11:14 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> Greg Smith wrote:
>>
>> You don't much with a single snapshot of pg_stat_bgwriter data.  Try
>> saving this instead:
>> select *,now() from pg_stat_bgwriter;
>> And then take another snapshot at least a few hours later, preferably the
>> next day.  With two snapshots and timestamps on them, then it's possible to
>> make some sense of the numbers.
>
> I probably should have explained the next part.  I've now shared what I do
> with this information at
> http://www.pgcon.org/2010/schedule/events/218.en.html
>
> Basically, if you put the data from the two snapshots into one of the
> Statistics Spreadsheet versions, you'll get several derived numbers that pop
> out:
>
> -Average checkpoint frequency
> -Average size of each checkpoint
> -Average rate at which new buffers are allocated
> -Average rate of writes out of the buffer cache
> -Percentage of writes done by checkpoints, the background writer LRU
> cleaner, and client backends

I think you get all or most of that if you just log checkpoints.