Hi,
I did a series of test to see the WAL size impact of enabling data
checksums/wal_log_hints on our databases (so that we can use pg_rewind
to fix split brain situations). Having a set of servers available the
last few days I did a few tests. Here are the results:
No data checksums and wal_log_hints=off :
createdb benchdisk
pgbench -i -s 10000 benchdisk
-> This creates a WAL archive of 121GB
pgbench -c 32 -j 16 -t 100000 benchdisk
-> The WAL archive is now 167GB. Increase of 167 - 121 = 46GB
pgbench -c 32 -j 16 -t 100000 -N benchdisk
-> The WAL archive is now 209GB. Increase of 209 - 167 = 42GB
Data checksums or wal_log_hints=on :
createdb benchdisk
pgbench -i -s 10000 benchdisk
-> This creates a WAL archive of 245GB
pgbench -c 32 -j 16 -t 100000 benchdisk
-> The WAL archive is now 292GB. Increase of 292 - 245 = 47GB
pgbench -c 32 -j 16 -t 100000 -N benchdisk
-> The WAL archive is now 334GB. Increase of 334 - 292 = 42GB
The tests run on two identical servers on a freshly initialized data
folder.
During the testing for read-write and simple write test I expected some
additional WAL volume (a couple of percentages is tolerable), but the
100% increase when creating the test database is very disturbing. I
assume a dump/restore would work the same way.
Am I doing something wrong? Would tweaking some checkpoint parameters
help reduce the WAL volume?
I was planing to turn data checksums on (for data integrity) for a
larger migration but with this numbers I would have to turn it off, and
use the wal_log_hints after the loading.
Regards,
Mladen Marinović
