Re: should we enable log_checkpoints out of the box?

Hi,

On 2021-10-31 15:43:57 -0400, Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2021-10-31 10:59:19 -0400, Tom Lane wrote:
> >> No DBA would be likely to consider it as anything but log spam.
>
> > I don't agree at all. No postgres instance should be run without
> > log_checkpoints enabled. Performance is poor if checkpoints are
> > triggered by anything but time, and that can only be diagnosed if
> > log_checkpoints is on.
>
> This is complete nonsense.

Shrug. It's based on many years of doing or being around people doing
postgres support escalation shifts. And it's not like log_checkpoints
incurs meaningful overhead or causes that much log volume.

> If we think that's a generic problem, we should be fixing the problem
> (ie, making the checkpointer smarter);

We've made it less bad (checkpoint_segments -> max_wal_size, sorting IO
for checkpoints, forcing the OS to flush writes earlier). But it's still
a significant issue. It's not that easy to make it better.

The largest issues are the spikes in data write and WAL volumes. Of
course, tuning max_wal_size up helps, but that has its own set of issues
(we e.g. still PANIC on WAL ENOSPC).

One motivation for working on AIO/DIO is precisely to make checkpoints
less painful, FWIW. We are going to have to do something to reduce the
impact of FPWs at some point, but it's hard.

Greetings,

Andres Freund