Re: Expose checkpoint start/finish times into SQL.

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.
>
> Since checkpoints have a fairly predictable duration in 8.3, as long as
> you catch the start or end of them you can make a resonable guess where
> the other side was.  The case you're trying to avoid here, the system
> going a long time without checkpointing, can be implemented by looking for
> a begin or end regularly, you don't need to track both.  As long as
> there's a checkpoint finish "pulse" in buffers_checkpoint showing up
> regularly you're fine.  The only situation I can think of where this might
> be problematic is where the system has been idle enough to not have any
> buffers to write at checkpoint time, but I recall a code path there where
> checkpoints stop altogether unless there's been activity so even tracking
> the time may not change that.

If we want to expose more of the internals of Postgres via functions,
fine, but a one-off function just to cover something that happened to
one user just doesn't make sense.  We need a plan on exactly what we
want to expose in a coherent way, and how.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +