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

Heya,

(changing the subject as we're moving the goalposts)

Am Samstag, den 02.01.2021, 10:47 -0500 schrieb Stephen Frost:
> * Michael Paquier (michael(at)paquier(dot)xyz) wrote:
> > On Fri, Jan 01, 2021 at 08:34:34PM +0100, Michael Banck wrote:
> > > I think enough people use data checksums these days that it warrants to
> > > be moved into the "normal part", like in the attached.
> >
> > +1. Let's see first what others think about this change.
>
> I agree with this, but I'd also like to propose, again, as has been
> discussed a few times, making it the default too.

One thing my colleagues have complained is the seemingly excessive
amount of WAL generation when checksums are enabled (compared to the
default where data_checksums and wal_log_hints is both off) due to
additional FPIs.

So I made some quick benchmarks based on pgbench -i (i.e. just
initializing the data, not actually running queries) and seeing how much
WAL is produced during a VACUUM with a forced CHECKPOINT beforehand.

This creates a new instance, turns archiving on and then first does the
data-load with scale-factor 100 in pgbench (initialization steps "dtg"),
followed by a CHECKPOINT and then the VACUUM/PK generation steps
(initialization steps "vp"), followed by a final CHECKPOINT. It looks
like this where $CHECKSUM is either empty or '-k':

pg_ctl -D data1 stop; rm -rf data1/ data1_archive/*;
initdb $CHECKSUM -D
data1; cp postgresql.conf data1;
pg_ctl -D data1 -l data1_logfile start;
pgbench -s 100 -i -p 65432 -I dtg; echo CHECKPOINT | psql -p 65432;
pgbench -s 100 -i -p 65432 -I vp; echo CHECKPOINT | psql -p 65432;
du -s
-h data1/pg_wal data1/base data1_archive/

All runs were repeated twice. These are the $PGDATA/{pg_wal,base} sizes
and the archive, as well as the timing for the second pgbench
initialization step:

data_checksums=off, wal_compression=off

1,1G data1/pg_wal
1,5G data1/base
1,3G data1_archive/

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=on, wal_compression=off

1,5G data1/pg_wal
1,5G data1/base
2,5G data1_archive/

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).

So data_checksums (and/or wal_log_hints, I ommitted those numbers as
they are basically identical to the data_checksums=on case) makes (i)
Vacuum run 20x and primary keys 2x longer and also increases the
generated WAL by 40% for pg_wal and roughly doubles the WAL in the
archive.

I then re-ran the tests with wal_compression=on in order to see how much
that helps:

data_checksums=off, wal_compression=on

1,1G data1/pg_wal
1,5G data1/base
1,2G data1_archive/

done in 26.60 s (vacuum 3.30 s, primary keys 23.30 s).
done in 19.54 s (vacuum 3.11 s, primary keys 16.43 s).
done in 19.50 s (vacuum 3.46 s, primary keys 16.04 s).

data_checksums=on, wal_compression=on

1,1G data1/pg_wal
1,5G data1/base
1,3G data1_archive/

done in 60.24 s (vacuum 42.52 s, primary keys 17.72 s).
done in 62.07 s (vacuum 45.64 s, primary keys 16.43 s).
done in 56.20 s (vacuum 40.96 s, primary keys 15.24 s).

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.

So maybe we should switch on wal_compression if we enable data checksums
by default.

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