From: | marin(at)kset(dot)org |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | pg_rewind and WAL size... |
Date: | 2016-11-22 12:40:13 |
Message-ID: | a66e45e5d649668bd18f40770eafdedf@kset.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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ć
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2016-11-22 12:57:15 | Re: pg_rewind and WAL size... |
Previous Message | Subhankar Chattopadhyay | 2016-11-22 11:41:32 | Re: pg_basebackup on slave running for a long time |