Re: Expose checkpoint start/finish times into SQL.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 03 Apr 2008 20:29:18 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Joshua D. Drake" <jd(at)commandprompt(dot)com> writes:
> > Heikki Linnakangas <heikki(at)enterprisedb(dot)com> wrote:
> >> Why is that useful?
>
> > 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.
>
> 1. To do anything useful along those lines, you would need to look at
> a lot of checkpoints over time, which is what log_checkpoints is good
> for. This patch only tells you about the latest, which isn't very
> useful for making any good decisions about parameters.

I would agree with this. We would need a history of checkpoints that
didn't reset until we told it to.

>
> 2. If I read the patch correctly, half of the time what you'd be
> seeing is the start time of the currently-active checkpoint and the
> completion time of the prior checkpoint. I don't know what those
> numbers are good for at all.

IMO we should see start checkpoint, end checkpoint. As part of a single
record. Other possibly interesting info would be how many logs were
processed.

>
> 3. As of PG 8.3, the bgwriter tries very hard to make the elapsed time
> of a checkpoint be just about checkpoint_timeout *
> checkpoint_completion_target, regardless of load factors. So unless
> your settings are completely broken, measuring the actual time isn't
> going to tell you much.
>

You assume that people don't have broken settings. :)

> In short: Heikki's question is on point.

I didn't say it wasn't. I was just stating why a patch that does what
was described is useful. I believe with changes it would still be
useful. Having to go to log for a lot of this stuff is painful.

Joshua D. Drake

- --
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH9YETATb/zqfZUUQRAmiTAJ0Shc4rSIKRG5nabAv9RwW1MVi/BQCfUIiK
Nb8qyBonAlNl/Agp/wCyvTU=
=H8TF
-----END PGP SIGNATURE-----