Re: Redesigning checkpoint_segments

On Thu, Jun 6, 2013 at 10:43 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> The general complaint the last time I suggested a change in this area, to
> make checkpoint_segments larger for the average user, was that some people
> had seen workloads where that was counterproductive.  Pretty sure Kevin
> Grittner said he'd seen that happen.  That's how I remember this general
> idea dying the last time, and I still don't have enough data to refute that
> doesn't happen.

My guess is that, with Heikki's patch, a lot of the value of keeping
checkpoint_segments low should go away - because if there wasn't much
activity, checkpoint_segments will in effect remain low, even the
configured value is not so low.  And if activity is high, well then
larger checkpoint_segments will be better anyway.

(As to why smaller checkpoint_segments can help, here's my guess: if
checkpoint_segments is relatively small, then when we recycle a
segment we're likely to find its data already in cache.  That's a lot
better than reading it back in from disk just to overwrite the data.)

> As far as the UI, if it's a soft limit I'd suggest wal_size_target for the
> name.  What I would like to see is a single number here in memory units that
> replaces both checkpoint_segments and wal_keep_segments.  If you're willing
> to use a large chunk of disk space to handle either one of activity spikes
> or the class of replication issues wal_keep_segments targets, I don't see
> why you'd want to ban using that space for the other one too.

This isn't really making sense to me.  I don't think we should assume
that someone who wants to keep WAL around for replication also wants
to wait longer between checkpoints.  Those are two quite different
things.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company