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

On 01/19/2012 10:52 AM, Robert Haas wrote:
> 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?

Right--they get updated in the same atomic bump that moves up things
like buffers_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.

Priority #1 here is to reduce (but, admittedly, not always eliminate)
the need for log file parsing of this particular area, so including all
the major bits from the existing log message that can be published this
way would include the write phase time. You mentioned one reason why
the write phase time might be interesting; there could be others. One
of the things expected here is that Munin will expand its graphing of
values from pg_stat_bgwriter to include all these fields. Most of the
time the graph of time spent in the write phase will be boring and
useless. Making it easy for a look at a graph to spot those rare times
when it isn't is one motivation for including it.

As for why to include the number of files being sync'd, one reason is
again simply wanting to include everything that can easily be
published. A second is that it helps support ideas like my "Checkpoint
sync pause" one; that's untunable in any reasonable way without some
easy way of monitoring the number of files typically sync'd. Sometimes
when I'm investigating checkpoint spikes during sync, I wonder whether
they were because more files than usual were synced, or if it's instead
just because of more churn on a smaller number. Making this easy to
graph pulls that data out to where I can compare it with disk I/O
trends. And there's precedent now proving that an always incrementing
number in pg_stat_bgwriter can be turned into such a graph easily by
monitoring tools.

> 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.

This isn't ideal yet. I mentioned how some future "performance event
logging history collector" was really needed as a place to push longest
sync times into, and we don't have it yet. This is the best thing to
instrument that I'm sure is useful, and that I can stick onto with the
existing infrastructure.

The idea is that this change makes it possible to trigger a "sync times
are too long" alert out of a tool that's based solely on database
queries. When that goes off, yes you're possibly back to the logs again
for more details about the longest individual sync time. But the rest
of the time, what's hopefully the normal state of things, you can ignore
the logs and just track the pg_stat_bgwriter numbers.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com