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          http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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