SEO

On Thursday 03 April 2008 19:08, 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.
>

Exposing everything into the log files isn't always sufficient (says the guy
who maintains a remote admin tool)

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

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

Robert Treat wrote:
> On Thursday 03 April 2008 19:08, 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.
>>
>>
>
> 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) ;-)

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 wrote:
>
>>
>>
>> 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. :-)
>
>

Well, not really. I believe -hackers has a much larger readership than
-patches, so even for small features we generally want them discussed
there.

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

On Thursday 03 April 2008 21:14, Joshua D. Drake wrote:
> On Thu, 03 Apr 2008 20:29:18 -0400
>
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "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.
>
> I would agree with this. We would need a history of checkpoints that
> didn't reset until we told it to.
>

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 :-)

> > 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.
>

Knowing when the last checkpoint occured is certainly useful for monitoring
purposes (wrt pitr and as a general item for cuasing alerts if checkpoints
stop occuring frequently enough, or if they start taking too long.)

>
> > 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.
>

How does one measure when the bgwriter is failing at this effort?

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

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

Robert Treat <xzilla@users.sourceforge.net> writes:
>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

> How does one measure when the bgwriter is failing at this effort?

Well, not with *this* patch. At least not without adding a lot of
infrastructure on top of it, and I'm failing to see why you'd build
such infrastructure in order to track just two numbers that are of
uncertain value.

JD seems to be on record that the existing logging mechanism sucks
and he needs something else. That's fine, but I think it means that
we need to improve logging in general, not invent a single-purpose
mechanism for logging checkpoint times.

Theo claimed he had a reason for wanting to know the latest checkpoint
time, *without* any intention of time-extended tracking of that; but
he didn't say what it was. If there is a credible reason for that
then it might justify a patch of this nature, but I don't see that
the reasons that have been stated so far in the thread hold any water.

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 10:33 PM, Tom Lane wrote:
> Theo claimed he had a reason for wanting to know the latest checkpoint
> time, *without* any intention of time-extended tracking of that; but
> he didn't say what it was. If there is a credible reason for that
> then it might justify a patch of this nature, but I don't see that
> the reasons that have been stated so far in the thread hold any water.


We had a recent event where the system stopped checkpointing, which
eventually lead to Robert Treat's therapy session at PostgreSQL
Conference East. If you have been good to be able to remotely (via
SQL) monitor the system to ensure that the last checkpoint start time
is within a reasonable window from current_timestamp.

My patch was clearly single purposed and never intended to be a SQL
accessible log of checkpoint times.

So, in reality, the "finish time" of the checkpoint is really not of
any importance to me either.

--
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 <jesus@omniti.com> writes:
> On Apr 3, 2008, at 10:33 PM, Tom Lane wrote:
>> Theo claimed he had a reason for wanting to know the latest checkpoint
>> time, *without* any intention of time-extended tracking of that; but
>> he didn't say what it was.

> We had a recent event where the system stopped checkpointing, which
> eventually lead to Robert Treat's therapy session at PostgreSQL
> Conference East.

Therapy is great ;-) ... but "the system stopped checkpointing" does not
strike me as a routine occurrence that we should be making provisions
for DBAs to watch for. What, pray tell, is the DBA supposed to do when
and if he notices that? Not to mention that since checkpointing is
ingrained in the bgwriter, you've got worse problems than that if it's
not working.

I'd much rather be spending our time and effort on understanding what
broke for you, and fixing the code so it doesn't happen again. An ounce
of prevention is worth a pound of cure, and all that...

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 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.

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.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

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. +

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

On Thu, 3 Apr 2008, Tom Lane wrote:

> "the system stopped checkpointing" does not strike me as a routine
> occurrence that we should be making provisions for DBAs to watch for.
> What, pray tell, is the DBA supposed to do when and if he notices that?

Schedule downtime rather than wait for it to happen unpredictably when the
inevitable crash happens.

> I'd much rather be spending our time and effort on understanding what
> broke for you, and fixing the code so it doesn't happen again.

(Here I start laughing all over again as I recall Robert's talk, which we
really need to get you in particular the video of) Their situation had
possible causes that included a bit flipping in bad memory, which is
pretty hard to code around (unless you're a Core Wars veteran). I'm
familiar with that part of the checkpoint code path, and everything I was
able to think of when hearing the outline of events was already considered
and rejected as not being a likely cause. This patch comes out of
pragmatic acceptance that, sometimes, stuff will happen you can't easily
explain, but that doesn't mean it's not worth keeping an eye on it anyway
so it doesn't sneak up on you again.

Anyway, I think this whole thing would be better handled by a larger
internals view that this whole codebase could use a dose of anyway. What
I really want is an interface like this:

psql> select pg_internals('last_checkpoint_time');

and then start sprinkling exports of those probe points in some popular
places people would like to look at.

I will apologize now for suggesting this, followed by not having enough
time to code it in the near future.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

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