Re: Expose checkpoint start/finish times into SQL.

On Thu, 3 Apr 2008, Joshua D. Drake wrote:

> For knowing how long checkpoints are taking. If they are taking too
> long you may need to adjust your bgwriter settings, and it is a
> serious drag to parse postgresql logs for this info.

There's some disconnect here between what I think you want here and what
Theo's patch does.

If I were running an 8.2 system, there's a whole lot of diagnostic
information I'd like to have handy in order to optimize checkpoints, as
you describe. But this patch doesn't help 8.2 users, because the whole
pg_stat_bgwriter structure is 8.3 only--it provides much of what is needed
in this area.

In 8.3, there is very little background writer tuning to do. What tuning
you can do is easy to figure out from what's in pg_stat_bgwriter. The new
checkpoint_completion_target feature works better than tuning the 8.2
background writer to reduce checkpoint spikes ever did anyway, and that's
the main thing that drives how long checkpoints take to process now.

So in the only situation where this patch can be applied, 8.3, I don't
think the scenario you describe is likely to pop up. And the combination
of two 8.3 features (log_checkpoint and the CSV logs, it's no coincidence
I worked on both) should allow a good enough way to hack around this area
when it does. Just make the logs rotate fairly regularly, and every time
they do import the last CSV section that's now finished into a table so
you can compute statistics about how the checkpoints are doing there. I
don't see why that sort of stuff should go into core when it's now easy to
do outside of it. I have a whole stack of scripts in that area I plan to
release over the summer.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD