Re: data_checksums enabled by default (was: Move --data-checksums to common options in initdb --help)

Hi,

On Wed, Jan 06, 2021 at 09:55:08AM -0800, Andres Freund wrote:
> On 2021-01-06 12:02:40 -0500, Stephen Frost wrote:
> > * Andres Freund (andres(at)anarazel(dot)de) wrote:
> > > On 2021-01-04 19:11:43 +0100, Michael Banck wrote:
> > > > This looks much better from the WAL size perspective, there's now almost
> > > > no additional WAL. However, that is because pgbench doesn't do TOAST, so
> > > > in a real-world example it might still be quite larger. Also, the vacuum
> > > > runtime is still 15x longer.
> > >
> > > That's obviously an issue.
> >
> > It'd certainly be nice to figure out a way to improve the VACUUM run but
> > I don't think the impact on the time to run VACUUM is really a good
> > reason to not move forward with changing the default.
>
> Vacuum performance is one of *THE* major complaints about
> postgres. Making it run slower by a lot obviously exascerbates that
> problem significantly. I think it'd be prohibitively expensive if it
> were 1.5x, not to even speak of 15x.

To maybe clarify, the vacuum slowdown is just as large in my (somewhat
contrived as a worst-case scenario) tests when wal_log_hints is on and
not data_checksums, I just ommitted those numbers due to being basically
identical (or maybe a bit worse even):

|data_checksums=off, wal_log_hints=off:
|
|done in 10.24 s (vacuum 3.31 s, primary keys 6.92 s).
|done in 8.81 s (vacuum 2.72 s, primary keys 6.09 s).
|done in 8.35 s (vacuum 2.32 s, primary keys 6.03 s).
|
|data_checksums=off, wal_log_hints=on:
|
|1,5G data1/pg_wal
|1,5G data1/base
|2,5G data1_archive/
|
|done in 87.89 s (vacuum 69.67 s, primary keys 18.23 s).
|done in 73.71 s (vacuum 60.19 s, primary keys 13.52 s).
|done in 75.12 s (vacuum 62.49 s, primary keys 12.62 s).
|
|data_checksums=on, wal_log_hints=off:
|
|done in 67.42 s (vacuum 54.57 s, primary keys 12.85 s).
|done in 65.03 s (vacuum 53.25 s, primary keys 11.78 s).
|done in 77.57 s (vacuum 62.64 s, primary keys 14.94 s).

Of course, wal_log_hints is not the default either and can be turned off
easily. You mostly lose the ability to run pg_rewind I think, are there
other use-cases for it?

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael(dot)banck(at)credativ(dot)de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz