Re: Checksums by default?

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