SEO

First whack at exposing the start and finish checkpoint times into SQL.

--
Theo Schlossnagle
Esoteric Curio -- http://lethargy.org/
OmniTI Computer Consulting, Inc. -- http://omniti.com/






--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Theo Schlossnagle wrote:
> First whack at exposing the start and finish checkpoint times into SQL.

I suggest using GetCurrentTimestamp() directly instead of time_t and
converting.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Theo Schlossnagle wrote:
> First whack at exposing the start and finish checkpoint times into SQL.

Why is that useful?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Heikki Linnakangas <heikki@enterprisedb.com> writes:
> Theo Schlossnagle wrote:
>> First whack at exposing the start and finish checkpoint times into SQL.

> Why is that useful?

Does this implementation even work? It looks to me like the
globalStats.last_checkpoint_start/done fields will go back to zero the
very next time the bgwriter sends a stats message. I'm not sure what
a sane behavior would be, but it seems unlikely that that's it.

regards, tom lane

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Joshua D. Drake wrote:
>> Theo Schlossnagle wrote:
>>
>>> First whack at exposing the start and finish checkpoint times into
>>> SQL.
>>>
>> 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.
>
>
>

Even if this were true, surely the answer is to improve the logging.

Has this feature been discussed on -hackers? I don't recall it (and my
memory has plenty of holes in it), but I'm sure that after attending my
talk last Sunday Theo hasn't sent in a patch for an undiscussed feature ;-)

cheers

andrew

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Heikki Linnakangas <heikki@enterprisedb.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.

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.

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.

In short: Heikki's question is on point.

regards, tom lane

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

"Joshua D. Drake" <jd@commandprompt.com> writes:
> I would agree with this. We would need a history of checkpoints that
> didn't reset until we told it to.

Indeed, but the submitted patch has nought whatsoever to do with that.
It exposes some instantaneous state.

You could perhaps *build* a log facility on top of that, at the SQL
level; but I don't see the point, and I definitely disagree that it
would be "easier than trolling the logs".

regards, tom lane

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

On Apr 3, 2008, at 7:08 PM, Andrew Dunstan wrote:

>
>
> Joshua D. Drake wrote:
>>> Theo Schlossnagle wrote:
>>>
>>>> First whack at exposing the start and finish checkpoint times into
>>>> SQL.
>>>>
>>> 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.
>>
>>
>>
>
> Even if this were true, surely the answer is to improve the logging.
>
> Has this feature been discussed on -hackers? I don't recall it (and
> my memory has plenty of holes in it), but I'm sure that after
> attending my talk last Sunday Theo hasn't sent in a patch for an
> undiscussed feature ;-)


Andrew: I don't think this feature has been discussed on hackers. The
patch took about 15 minutes to author, so it sounds like the most
concise way to start a conversation. Seems silly to start the
conversation on hackers with a patch. :-)

Alvaro: Thanks, I flip that to GetCurrentTimestamp()

Heikki: It it useful for knowing when the last checkpoint occurred.
Like Robert, we have situations where reading the log file is a PITA
-- so this provides that information. I originally planned on only
adding the start time, but figured adding the end would make sense too.

Tom: It worked for me in my testing, though I did not extensively
tested. I didn't see anywhere the stats are zero'd out, so I believe
the timestamp is zero at start and then only ever set to the starttime
during a checkpoint invocation. I admittedly don't have a thorough
understanding of that code -- but that segment (before my patch)
looked pretty concise.

--
Theo Schlossnagle
Esoteric Curio -- http://lethargy.org/
OmniTI Computer Consulting, Inc. -- http://omniti.com/


--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Joshua D. Drake wrote:
>>> Exposing everything into the log files isn't always sufficient
>>> (says the guy who maintains a remote admin tool)
>>>
>>>
>> It should be now that you can have machine readable logs (says the
>> guy who literally spent weeks making that happen) ;-)
>>
>
> And how does the person get access to those? And what script do I need
> to write to make it happen? Don't get me wrong, the feature you worked
> entirely too hard on to get working is valuable but... being able to
> say, "SELECT * FROM give_me_my_db_info;" is much more useful in this
> context.
>

How to load the CSV logs is very clearly documented. It's really *very*
easy, so easy it's mostly C&P. See
http://www.postgresql.org/docs/curre...LOGGING-CSVLOG

If you are trying to tell me that that's too hard for a DBA, then I have
to say you need better DBAs.

> In short, I should never have to go to log for this class of
> information. It should be available in the database.
>
>

What you haven't explained is why this information needs to be kept in
the db on a historical basis, as opposed to all the other possible
diagnostics where history might be useful (and, as Tom has pointed out,
this patch doesn keep it historically any way).

I think there is quite possibly a good case for keeping some diagnostics
in a table or tables, on a rolling basis, maybe. But then that's a
facility that needs to be properly designed.

cheers

andrew



--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

Theo Schlossnagle <jesus@omniti.com> writes:
> Heikki: It it useful for knowing when the last checkpoint occurred.

I guess I'm wondering why that's important. In the current bgwriter
design, the system spends half its time checkpointing (or in general
checkpoint_completion_target % of the time). So this seems fairly close
to wanting to know when the bgwriter last wrote a dirty buffer --- yeah,
I can imagine scenarios for wanting to know that, but they probably
require a whole pile of other knowledge as well.

JD seems to have gone off into the weeds imagining that this patch would
provide tracking of the last N checkpoints; which might start to
approach the level of an interesting feature, except that it's still not
clear *why* those numbers are interesting, given the bgwriter's
propensity to try to hold the checkpoint duration constant.

regards, tom lane

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches