Re: [HACKERS] 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers