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

Reply via email to