Re: Redesigning checkpoint_segments

On Thu, Jun 6, 2013 at 10:43 PM, Greg Smith <greg(at)2ndquadrant(dot)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