Re: should we enable log_checkpoints out of the box?

On Tue, Nov 2, 2021 at 3:05 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> I think that 10 minutes is fine. But if it has to be 30 minutes, then
> that's also probably fine.

+1.

> I think that the case for enabling autovacuum logging is particularly
> good. The really big problems with autovacuum often involve
> anti-wraparound VACUUM, where you really want to have every possible
> opportunity to learn why VACUUM took much longer than expected.

+1.

> Going from doing index vacuuming in one single pass to requiring more
> than one pass can very significantly delay things, quite suddenly.
> Even when you have 95% of the maintenance_work_mem required to process
> indexes in a single pass, it might not be that much better than 50% or
> less. It's way too unpredictable in cases where users actually run
> into real problems -- cases where it actually matters.

Yeah. I have only very rarely run into cases where people actually end
up needing multiple passes, but it's always something I need to rule
out as part of the troubleshooting process, and it's hard to do that
without the log_autovacuum_min_duration output. It's pretty common for
me to see cases where, for example, the I/O performed by autovacuum
read a bunch of data off disk, which shoved a bunch of hot data out of
cache, and then performance tanked really hard. Or where the vacuum
cost limit is ridiculously low relative to the table size and the
vacuums take an unreasonably long time. In those kinds of cases what
you really need to know is that there was a vacuum on a certain table,
and how long it took, and when that happened.

--
Robert Haas
EDB: http://www.enterprisedb.com