Re: Expose checkpoint start/finish times into SQL.

On Friday 04 April 2008 00:09, Greg Smith wrote:
> On Thu, 3 Apr 2008, Robert Treat wrote:
> > You can plug a single item graphed over time into things like rrdtool to
> > get good trending information. And it's often easier to do this using
> > sql interfaces to get the data than pulling it out of log files (almost
> > like the db was designed for that :-)
>
> The pg_stat_bgwriter value for buffers_checkpoint was intentionally
> implemented in 8.3 such that it jumps in one big lump when the checkpoint
> is done. While it's not the ideal interface for what you're looking for,
> the reason for that is to made it possible to build a "when was the last
> checkpoint finished?" interface via some remote monitoring tool just by
> determining the last time that the value jumped upwards. You can easily
> see them just by graphing that value, it shouldn't be too hard to teach
> something with rrdtool guts to find them.
>

the advantage of using a timestamp is that you get the incrementing counter
for free which is certainly helpful in third party tools like phppgadmin that
don't instrument tracking methods; you can look at the system and it's
settings and have a pretty good idea if something is awry.

>
> Ultimately a lot of the other questions you might ask (i.e. "how many
> buffers have been written per hour by checkpoints?") require processing
> the numbers in this way anyway, and I thought this implementation was good
> enough to monitor the situation you're trying to avoid--presuming you're
> using some sort of moderately powerful remote monitoring tool. Theo's
> patch would make it easier to answer with a simple command which has some
> value; a little SQL in a cron job would be good enough to trigger an alert
> rather than needing a real monitoring probe.
>

Yes, the idea of making a basic nagios/munin check that is readily consumable
by the general public is certainly a bonus in my eyes.

I have to add, given that we already provide the time of last checkpoint
information via pg_controldata, I don't understand why people are against
making that information accesible to remote clients.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL