Re: Avoiding adjacent checkpoint records

On 7 June 2012 18:03, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Jun 7, 2012 at 12:52 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> Clearly, delaying checkpoint indefinitely would be a high risk choice.
>> But they won't be delayed indefinitely, since changes cause WAL
>> records to be written and that would soon cause another checkpoint.
>
> But that's exactly the problem - it might not be soon at all.  We have
> customers who process about one write transaction per day.  The
> current state of play in 9.2 is that we'll checkpoint when we get to
> the next WAL segment.  But at one write transaction per day, that
> could take weeks or months.  Someone invented a setting called
> 'checkpoint_timeout' for a very good reason - people don't want huge
> amounts of time to pass between checkpoints.  That setting is
> currently capped at one hour; the proposition that someone who sets it
> to 5 minutes on a low-write-volume system is OK with the effective
> value being 5 months does not seem likely to me.

We discussed that in the original thread.

The purpose of a checkpoint is to reduce recovery time. Nobody
actually wants a checkpoint just of itself and why would we care how
many months that takes? I grant that it takes a little while to come
to terms with that thought, but that doesn't make the patch wrong.

The only risk of data loss is in the case where someone deletes their
pg_xlog and who didn't take a backup in all that time, which is hardly
recommended behaviour. We're at exactly the same risk of data loss if
someone deletes their pg_clog. Too frequent checkpoints actually makes
the data loss risk from deleted pg_clog greater, so the balance of
data loss risk doesn't seem to have altered.

> Your suggestion of just checkpointing CLOG seems like it would
> mitigate the worst of the damage, but I think I agree with Tom that
> just reverting the whole patch would be a better solution, if we can't
> figure out anything cleaner.  It's better to have a few unnecessary
> checkpoints than to risk losing somebody's data, especially since the
> unnecessary checkpoints only happen with wal_level=hot_standby, but
> the data loss risk exists for everyone.

We're not at risk of losing anyone's data. There is no aspect of the
normal running system that is at a higher risk of data loss.

I don't think its fair comment to claim the patch has issues because
you've found a guy with very, very, very low write rates, no backup
strategy and a propensity to delete essential parts of their database
who will be adversely affected.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services