SEO

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

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

On Friday 04 April 2008 01:59, Tom Lane wrote:
> 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.
>

So, thinking about this for a few minutes, here are some of the things that
knowing the last checkpoint time might help a DBA determine.

1) Alert if checkpointing stops occuring within a reasonable time frame (note
there are failure cases and normal use cases where this might occur) (also
note I'll agree, this isn't common, but the results are pretty disatrous if
it does happen)

2) Can be graphed over time (using rrdtool and others) for trending checkpoint
activity

3) Ease monitoring of checkpoints across pitr setups

4) Help determine if your checkpoints are being timeout driven or segment
driven, or if you need to look at those settings

5) Determine the number of log files that will need to be replayed in the
event of a crash

6) Helps give an indication on if you should enter a manual checkpoint before
issuing a pg_start_backup call

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

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

This patch makes that a 1 line sql call, which can be plugged into any number
of monitoring systems.

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

Alvaro Herrera <alvherre@commandprompt.com> writes:
> 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'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.

Any Nagios-style monitoring would have to watch checkpoint end (and
we'd better define that field as only updating at *successful*
checkpoint end). Consider the case where some dirty buffer has a
persistent write failure condition.

I'm almost inclined to say that the patch shouldn't expose checkpoint
start at all, just to make sure people won't get this wrong. We've
pretty thoroughly trashed the notion that looking at the interval is
helpful anyway.

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, 2008-04-04 at 02:21 -0400, Greg Smith wrote:

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

A few additional comments here:

If you set checkpoint_segments very, very high you can avoid a
checkpoint via checkpoint_timeout for up to 60 minutes. If you did this
for performance reasons presumably you've got lots of WAL files and
might end up with 1000s of them in that time period.

If you set it too high, you hit the disk limits first and can then crash
the server if the pg_xlog directory's physical limits are unluckily low
enough.

Starvation of the checkpoint start lock has been witnessed previously,
so if you're running 8.2 or previous that could be a possible
explanation here. What can happen is that a checkpoint is triggered yet
the bgwriter needs to wait to get access to the CheckpointStartLock. I
witnessed a starvation of 3 minutes once during testing a server running
at max velocity with 200 users, in 2006. I assumed that was an outlier,
but its possible for that to be longer. I wouldn't believe too much
longer, though. That was patched in 8.3 as a result.

Anyway, either of those factors, or their combination, plus a small
pg_xlog disk would be sufficient to explain the crash and wal file build
up.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com


--
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:
> 1) Alert if checkpointing stops occuring within a reasonable time frame (note
> there are failure cases and normal use cases where this might occur) (also
> note I'll agree, this isn't common, but the results are pretty disatrous if
> it does happen)

What are the normal use cases where this would occur? I can't think of any.

Wrt. failure cases, there's a million things that can go wrong in a
system, and only few of them will give the symptom of "checkpoints
stopped happening", so I'm not excited about adding a facility to
monitor just that.

More hooks for monitoring purposes in general would be nice, and I would
like to see them exposed as SQL functions, but I'd like to see a much
bigger proposal for that.

> 2) Can be graphed over time (using rrdtool and others) for trending checkpoint
> activity

Hmm. You'd need the historical data to do that properly. In particular,
if two checkpoints happen between the polling interval, you'd miss that.
log_checkpoints=on, in CSV output, seems like a better approach for that.

> 3) Ease monitoring of checkpoints across pitr setups

How is that different from monitoring in a non-pitr setup?

> 4) Help determine if your checkpoints are being timeout driven or segment
> driven, or if you need to look at those settings

Seems like the log_checkpoints output is more useful for that, as it
directly tells you what triggered the checkpoint.

> 5) Determine the number of log files that will need to be replayed in the
> event of a crash

If you have a requirement for that, just set checkpoint_segments
accordingly. And again, you can get the information in the logs by
log_checkpoints=on already.

> 6) Helps give an indication on if you should enter a manual checkpoint before
> issuing a pg_start_backup call

If you want the backup to begin immediately, just do a manual checkpoint
unconditionally. It'll finish quickly if there hasn't been much activity
since the last one. We talked about adding a new variant of
pg_start_backup() that does that automatically (or rather, just hurries
the current checkpoint) in the 8.3 cycle, but didn't do it in the end.
Perhaps we should add that, after all?

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

On Sat, 05 Apr 2008 16:37:15 +0100
Heikki Linnakangas <heikki@enterprisedb.com> wrote:


May I just say that every person that is currently talking on this
thread is offtopic? Move it to -hackers please.

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



--
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 wrote:
> Robert Treat wrote:

>> 2) Can be graphed over time (using rrdtool and others) for trending
>> checkpoint activity
>
> Hmm. You'd need the historical data to do that properly. In particular,
> if two checkpoints happen between the polling interval, you'd miss that.

Yes, Munin and other tools collect the values over time to create the
graph. It's not like "top" saves historical system load data either,
yet the collector is able to present you with a graph.

> log_checkpoints=on, in CSV output, seems like a better approach for that.

It's not, because the tool would have a much harder time trolling the
logs. CSV does not really make it much easier -- the DBA is still
tasked with importing the thing after the current file is complete; and
you don't have an up-to-date picture either.

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