Hi,
I've repeated those benchmarks on a much smaller/older machine, with
only minimal changes (mostly related to RAM and cores available). I've
expected to see more significant differences, assuming that newer CPUs
will handle the checksumming better, but to my surprise the impact of
enabling checksums on this machine is ~2%.
As usual, full results and statistics are available for review here:
https://bitbucket.org/tvondra/checksum-bench-i5
Looking at average TPS (measured over 2 hours, with a checkpoints every
30 minutes), I see this:
test scale checksums no-checksums
-----------------------------------------------------------
pgbench 50 7444 7518 99.02%
300 6863 6936 98.95%
1000 4195 4295 97.67%
read-write 50 48858 48832 100.05%
300 41999 42302 99.28%
1000 16539 16666 99.24%
skewed 50 7485 7480 100.07%
300 7245 7280 99.52%
1000 5950 6050 98.35%
skewed-n 50 10234 10226 100.08%
300 9618 9649 99.68%
1000 7371 7393 99.70%
And the amount of WAL produced looks like this:
test scale checksums no-checksums
-----------------------------------------------------------------
pgbench 50 24.89 24.67 100.89%
300 37.94 37.54 101.07%
1000 65.91 64.88 101.58%
read-write 50 10.00 9.98 100.11%
300 23.28 23.35 99.66%
1000 54.20 53.20 101.89%
skewed 50 24.35 24.01 101.43%
300 35.12 34.51 101.77%
1000 52.14 51.15 101.93%
skewed-n 50 21.71 21.13 102.73%
300 32.23 31.54 102.18%
1000 53.24 51.94 102.50%
Again, this is hardly a proof of non-existence of a workload where data
checksums have much worse impact, but I've expected to see a much more
significant impact on those workloads.
Incidentally, I've been dealing with a checksum failure reported by a
customer last week, and based on the experience I tend to agree that we
don't have the tools needed to deal with checksum failures. I think such
tooling should be a 'must have' for enabling checksums by default.
In this particular case the checksum failure is particularly annoying
because it happens during recovery (on a standby, after a restart),
during startup, so FATAL means shutdown.
I've managed to inspect the page in different way (dd and pageinspect
from another instance), and it looks fine - no obvious data corruption,
the only thing that seems borked is the checksum itself, and only three
consecutive bits are flipped in the checksum. So this doesn't seem like
a "stale checksum" - hardware issue is a possibility (the machine has
ECC RAM though), but it might just as easily be a bug in PostgreSQL,
when something scribbles over the checksum due to a buffer overflow,
just before we write the buffer to the OS. So 'false failures' are not
entirely impossible thing.
And no, backups may not be a suitable solution - the failure happens on
a standby, and the page (luckily) is not corrupted on the master. Which
means that perhaps the standby got corrupted by a WAL, which would
affect the backups too. I can't verify this, though, because the WAL got
removed from the archive, already. But it's a possibility.
So I think we're not ready to enable checksums by default for everyone,
not until we can provide tools to deal with failures like this (I don't
think users will be amused if we tell them to use 'dd' and inspect the
pages in a hex editor).
ISTM the way forward is to keep the current default (disabled), but to
allow enabling checksums on the fly. That will mostly fix the issue for
people who actually want checksums but don't realize they need to enable
them at initdb time (and starting from scratch is not an option for
them), are running on good hardware and are capable of dealing with
checksum errors if needed, even without more built-in tooling.
Being able to disable checksums on the fly is nice, but it only really
solves the issue of extra overhead - it does really help with the
failures (particularly when you can't even start the database, because
of a checksum failure in the startup phase).
So, shall we discuss what tooling would be useful / desirable?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgbench checksums 50 7444 26728913488
pgbench checksums 300 6863 40739080776
pgbench checksums 1000 4195 70768241600
pgbench no-checksums 50 7518 26494392120
pgbench no-checksums 300 6936 40309039464
pgbench no-checksums 1000 4295 69665291328
skewed checksums 50 7485 26149380024
skewed checksums 300 7245 37713207584
skewed checksums 1000 5950 55981298696
skewed no-checksums 50 7480 25781552152
skewed no-checksums 300 7280 37057932000
skewed no-checksums 1000 6050 54922629488
skewed-n checksums 50 10234 23305813920
skewed-n checksums 300 9618 34603628592
skewed-n checksums 1000 7371 57166816104
skewed-n no-checksums 50 10226 22685943632
skewed-n no-checksums 300 9649 33866305328
skewed-n no-checksums 1000 7393 55771911928
read-write checksums 50 48858 10732135640
read-write checksums 300 41999 24992342144
read-write checksums 1000 16539 58197245952
read-write no-checksums 50 48832 10720848632
read-write no-checksums 300 42302 25076924416
read-write no-checksums 1000 16666 57119901144
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
