SEO

On Thu, 3 Apr 2008, Joshua D. Drake wrote:

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

There's some disconnect here between what I think you want here and what
Theo's patch does.

If I were running an 8.2 system, there's a whole lot of diagnostic
information I'd like to have handy in order to optimize checkpoints, as
you describe. But this patch doesn't help 8.2 users, because the whole
pg_stat_bgwriter structure is 8.3 only--it provides much of what is needed
in this area.

In 8.3, there is very little background writer tuning to do. What tuning
you can do is easy to figure out from what's in pg_stat_bgwriter. The new
checkpoint_completion_target feature works better than tuning the 8.2
background writer to reduce checkpoint spikes ever did anyway, and that's
the main thing that drives how long checkpoints take to process now.

So in the only situation where this patch can be applied, 8.3, I don't
think the scenario you describe is likely to pop up. And the combination
of two 8.3 features (log_checkpoint and the CSV logs, it's no coincidence
I worked on both) should allow a good enough way to hack around this area
when it does. Just make the logs rotate fairly regularly, and every time
they do import the last CSV section that's now finished into a table so
you can compute statistics about how the checkpoints are doing there. I
don't see why that sort of stuff should go into core when it's now easy to
do outside of it. I have a whole stack of scripts in that area I plan to
release over the summer.

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

On Thu, 3 Apr 2008, Tom Lane wrote:

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

In the cases where the timing on checkpoint writes are timeout driven.
When they're checkpoint_segments driven the time is derived from that
drumbeat instead. And those cases are the more interesting ones, because
those are the ones that will speed up during really busy periods.

I think it's completely relevant to track how often checkpoints are
happening because they do still vary even in 8.3. I'm just not sure if
the current methods available for that really aren't good enough, or if
it's just the case that not everyone is aware of all of them.

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

On Friday 04 April 2008 00:09, 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.
>

the advantage of using a timestamp is that you get the incrementing counter
for free which is certainly helpful in third party tools like phppgadmin that
don't instrument tracking methods; you can look at the system and it's
settings and have a pretty good idea if something is awry.

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

Yes, the idea of making a basic nagios/munin check that is readily consumable
by the general public is certainly a bonus in my eyes.

I have to add, given that we already provide the time of last checkpoint
information via pg_controldata, I don't understand why people are against
making that information accesible to remote clients.

--
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:
> I have to add, given that we already provide the time of last checkpoint
> information via pg_controldata, I don't understand why people are against
> making that information accesible to remote clients.

So, I can expect to see a patch next week that invents a separate SQL
function to expose each field printed by pg_controldata?

You are failing to make the distinction between debug/developer support
(which is what pg_controldata is) and things that are interesting for
DBAs to track. Would you expect someone to install handwritten
monitoring infrastructure to awaken them in the middle of the night when
pg_control_version changes?

I am not saying that there is no use for some sort of facility in this
area. But I think it needs some more-general thinking and design than
has been evidenced so far in this thread.

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

Greg Smith <gsmith@gregsmith.com> writes:
> On Thu, 3 Apr 2008, Tom Lane wrote:
>> 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.

> [ shit happens... ]

Completely fair, but I still don't see how this particular patch would
be a useful addition to the DBA's monitoring-tool arsenal. The scope
seems too narrow.

(And you still didn't tell me what the actual failure case was.)

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 Fri, 4 Apr 2008, Tom Lane wrote:

> (And you still didn't tell me what the actual failure case was.)

Database stops checkpointing. WAL files pile up. In the middle of
backup, system finally dies, and when it starts recovery there's a bad
record in the WAL files--which there are now thousands of to apply, and
the bad one is 4 hours of replay in. Believe it or not, it goes downhill
from there.

It's what kicked off the first step that's the big mystery. The only code
path I thought of that can block checkpoints like this is when the
archive_command isn't working anymore, and that wasn't being used. Given
some of the other corruption found later and the bad memory issues
discovered, a bit flipping in the "do I need to checkpoint now?" code or
data seems just as likely as any other explanation.

--
* 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 <gsmith@gregsmith.com> writes:
> On Fri, 4 Apr 2008, Tom Lane wrote:
>> (And you still didn't tell me what the actual failure case was.)

> Database stops checkpointing. WAL files pile up. In the middle of
> backup, system finally dies, and when it starts recovery there's a bad
> record in the WAL files--which there are now thousands of to apply, and
> the bad one is 4 hours of replay in. Believe it or not, it goes downhill
> from there.

> It's what kicked off the first step that's the big mystery.

Indeed :-(. But given those observations, I'd still have about zero
faith in the usefulness of this patch. If the bgwriter is not able to
complete checkpoints, is it able to tell you the truth about what it's
doing?

The actual advice I'd give to a DBA faced with such a case is to
kill -ABRT the bgwriter and send the stack trace to -hackers.
That's not in the proposed patch though...

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 Fri, 4 Apr 2008, Tom Lane wrote:

> The actual advice I'd give to a DBA faced with such a case is to
> kill -ABRT the bgwriter and send the stack trace to -hackers.

And that's a perfect example of where they're trying to get to. They
didn't notice the problem until after the crash. The server didn't come
back up (busy processing WALs) and that downtime was caught by a
monitoring system. At that point it was too late to collect debugging
information on what was wrong inside the server processes that might have
given a clue what happened.

If they'd have noticed it while the server was up, perhaps because the
"last checkpoint" value hadn't changed in a long time (which seems like it
might be available via stats even if, as you say, the background writer is
out of its mind at that point), they could have done such a kill and
collected some actual useful information here. That's the theory at
least.

--
* 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 <gsmith@gregsmith.com> writes:
> ... If they'd have noticed it while the server was up, perhaps because the
> "last checkpoint" value hadn't changed in a long time (which seems like it
> might be available via stats even if, as you say, the background writer is
> out of its mind at that point), they could have done such a kill and
> collected some actual useful information here. That's the theory at
> least.

Well, mebbe, but that still seems to require a lot of custom monitoring
infrastructure that is not present in this patch, and furthermore that
this patch doesn't especially aid the development of.

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

"Alvaro Herrera" <alvherre@commandprompt.com> writes:

> Tom Lane wrote:
>> Greg Smith <gsmith@gregsmith.com> writes:
>> > ... If they'd have noticed it while the server was up, perhaps because the
>> > "last checkpoint" value hadn't changed in a long time (which seems like it
>> > might be available via stats even if, as you say, the background writer is
>> > out of its mind at that point), they could have done such a kill and
>> > collected some actual useful information here. That's the theory at
>> > least.
>>
>> Well, mebbe, but that still seems to require a lot of custom monitoring
>> infrastructure that is not present in this patch, and furthermore that
>> this patch doesn't especially aid the development of.
>
> These kind of things can be monitored externally very easily, say by
> Nagios, when the values are available via the database. If you have to
> troll the logs, it's quite a bit harder to do it.

I can see Tom's reluctance out of fear that really this is going to be the
first of hundreds of dials which have to be monitored so a single function to
handle that single dial is kind of short sighted. I would think eventually it
should be part of the Postgres SNMP MIB.

But I would say from my experience on the not-really-a-sysadmin side I think
the time of the last checkpoint is probably the *most* important thing to be
monitoring. Effectively it's monitoring how stale your data on disk is
potentially becoming by showing how much recovery will be necessary.

> I'm not sure about the right values to export -- last checkpoint start
> time is the most obvious idea, but I would also suggest exporting last
> checkpoint end, or NULL if the checkpoint is ongoing; and also previous-
> to-last checkpoint start and end.

I'm surprised y'all want the time of the last checkpoint *start* though. It
seems to me that only the last checkpoint *end* is actually interesting. A
checkpoint which has started but not ended yet is not really a checkpoint at
all. It's nothing.

In the future there could be multiple checkpoints which have "started" but not
finished. Or we could add support for lazy checkpoints in which case there
could be an infinite number of "potential" checkpoints which haven't finished.

Worse, the time the last checkpoint actually finished isn't actually
interesting either. What you want to know is what time the record which the
last finished checkpoint *checkpointed up to* was inserted. That is, the time
of the record that the last checkpoint record *points to*.

That represents the "guarantee" that the database is making to the sysadmin
about data integrity. Everything before that time is guaranteed to have
reached data files already. Everything after it may or may not be in the data
files and has to be checked against the WAL logs.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!

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