Обсуждение: pg_rewind and WAL size...

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ć

On Tue, Nov 22, 2016 at 9:40 PM,  <marin@kset.org> wrote:
> Am I doing something wrong? Would tweaking some checkpoint parameters help
> reduce the WAL volume?

The answer to this question is likely yes. wal_log_hints enforces a
FPW for the first modification of a page even if that's for some hint
bits. This generates traffic for OLTP types of workloads where many
pages are dirtied and flushed at checkpoint.
--
Michael