Re: proposal: CREATE DATABASE vs. (partial) CHECKPOINT - Mailing list pgsql-hackers

On Wed, Oct 29, 2014 at 2:36 PM, Tomas Vondra <tv@fuzzy.cz> wrote:
>> I would tend not to worry too much about this case. I'm skeptical
>> that there are a lot of people using large template databases. But
>> if there are, or if some particular one of those people hits this
>> problem, then they can raise checkpoint_segments to avoid it. The
>> reverse problem, which you are encountering, cannot be fixed by
>> adjusting settings.
>
> That however solves "only" the checkpoint, not the double amount of I/O
> due to writing both the files and WAL, no? But maybe that's OK.

I mean, it's not unimaginable that it's going to hurt somebody, but
the current situation is pretty bad too.  You don't have to be the
world's foremost PostgreSQL performance expert to know that extra
checkpoints are really bad for performance.  Write volume is of course
also a problem, but I bet there are a lot more people using small
template databases (where the write volume isn't really an issue,
because as Heikki points out the checkpoint wastes half a segment
anyway, but the checkpoint may very well be a issue) than large ones
(where either could be an issue).

> Also, all this is concern only with 'wal_level != minimal', but ISTM 'on
> wal_level=minimal it's fast' is a rather poor argument.

I think the argument here is more that there's no such thing as a free
lunch.  If someone wants to come up with a way to make this work
without WAL-logging every block -AND- without triggering a checkpoint,
great.  If that works well, perhaps we can apply it to other cases
like ALTER TABLE .. SET TABLESPACE which are currently quite painful
and which seem to me to have more or less the same problem.  But I
don't really know why we should expect that such a solution exists at
all or is easy to engineer correctly.  All we're doing here is
applying the same solution that's been found to be robust in other
situations to some old, crufty code that isn't.

>> (This reminds me, yet again, that it would be really nice to something
>> smarter than checkpoint_segments.  If there is little WAL activity
>> between one checkpoint and the next, we should reduce the number of
>> segments we're keeping around to free up disk space and ensure that
>> we're recycling a file new enough that it's likely to still be in
>> cache.  Recycling files long-since evicted from cache is poor.  But
>> then we should also let the number of WAL files ratchet back up if the
>> system again becomes busy.  Isn't this more or less what Heikki's
>> soft-WAL-limit patch did?  Why did we reject that, again?)
>
> What about simply reusing the files in a different way? Instead of
> looping through the files in a round robin manner, couldn't we just use
> the last recently used file, instead of going all the way back to the
> first one? This won't free the disk space, but IMHO that's not a problem
> because noone is going to use that space anyway (as it would be a risk
> once all the segments will be used again).

Hmm, interesting idea.  I have no idea whether that would work out to
a win or not.

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