Re: Publish checkpoint timing and sync files summary data to pg_stat_bgwriter

On Mon, Jan 16, 2012 at 1:28 AM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:
> One of the most useful bits of feedback on how well checkpoint I/O is going
> is the amount of time taken to sync files to disk.  Right now the only way
> to get that is to parse the logs.  The attached patch publishes the most
> useful three bits of data you could only get from log_checkpoints before out
> to pg_stat_bgwriter.

It's not quite clear from your email, but I gather that the way that
this is intended to work is that these values increment every time we
checkpoint?

Also, forgive for asking this possibly-stupid question, but of what
use is this information? I can't imagine why I'd care about a running
total of the number of files fsync'd to disk. I also can't really
imagine why I'd care about the length of the write phase, which surely
will almost always be a function of checkpoint_completion_target and
checkpoint_timeout unless I manage to overrun the number of
checkpoint_segments I've allocated. The only number that really seems
useful to me is the time spent syncing. I have a clear idea what to
look for there: smaller numbers are better than bigger ones. For the
rest I'm mystified.

And, it doesn't seem like it's necessarily going to safe me a whole
lot either, because if it turns out that my sync phases are long, the
first question out of my mouth is going to be "what percentage of my
total sync time is accounted for by the longest sync?". And so right
there I'm back to the logs. It's not clear how such information could
be usefully exposed in pg_stat_bgwriter either, since you probably
want to know only the last few values, not a total over all time.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company