Re: Enabling Checksums - Mailing list pgsql-hackers

The 16-bit checksum feature seems functional, with two sources of
overhead.  There's some CPU time burned to compute checksums when pages
enter the system.  And there's extra overhead for WAL logging hint bits.
  I'll quantify both of those better in another message.

For completeness sake I've attached the latest versions of the patches I
tested (same set as my last message) along with the testing programs and
source changes that have been useful for my review.  I have a test case
now demonstrating a tricky issue my gut told me was possible in page
header handling, and that's what I talk about most here.

= Handling bit errors in page headers =

The thing I've been stuck on is trying to find a case where turning
checksums on results in data that could be read becoming completely
unavailable, after a single bit of corruption.  That seemed to me the
biggest risk of this feature.  If checksumming can result in lost data,
where before that data would be available just with some potential for
error in it, that's kind of bad.  I've created a program that does just
that, with a repeatable shell script test case (check-check.sh)

This builds on the example I gave before, where I can corrupt a single
bit of data in pgbench_accounts (lowest bit in byte 14 in the page) and
then reads that page without problems:

$ psql -c "select sum(abalance) from pgbench_accounts"
  sum
-----
    0

Corrupting the same bit on a checksums enabled build catches the problem:

WARNING:  page verification failed, calculated checksum 5900 but
expected 9227
ERROR:  invalid page header in block 0 of relation base/16384/16397

This is good, because it's exactly the sort of quiet corruption that the
feature is supposed to find.  But clearly it's *possible* to still read
all of the data in this page, because the build without checksums does
just that.  All of these fail now:

$ psql -c "select sum(abalance) from pgbench_accounts"
WARNING:  page verification failed, calculated checksum 5900 but
expected 9227
ERROR:  invalid page header in block 0 of relation base/16384/16397

$ psql -c "select * from pgbench_accounts"
WARNING:  page verification failed, calculated checksum 5900 but
expected 9227
ERROR:  invalid page header in block 0 of relation base/16384/16397

And you get this sort of mess out of pg_dump:

COPY pgbench_accounts (aid, bid, abalance, filler) FROM stdin;
pg_dump: WARNING:  page verification failed, calculated checksum 5900
but expected 9227
\.


pg_dump: Dumping the contents of table "pgbench_accounts" failed:
PQgetResult() failed.
pg_dump: Error message from server: ERROR:  invalid page header in block
0 of relation base/16384/16397
pg_dump: The command was: COPY public.pgbench_accounts (aid, bid,
abalance, filler) TO stdout;

I think an implicit goal of this feature was to soldier on when possible
to do so.  The case where something in the page header is corrupted
seems the weakest part of that idea.  I would still be happy to enable
this feature on a lot of servers, because stopping in the case of subtle
header corruption just means going to another known good copy of the
data; probably a standby server.

I could see some people getting surprised by this change though.  I'm
not sure if it's possible to consider a checksum failure in a page
header something that is WARNed about, rather than always treating it as
a failure and the data is unavailable (without page inspection tools at
least).  That seems like the main thing that might be improved in this
feature right now.

= Testing issues =

It is surprisingly hard to get a repeatable test program that corrupts a
bit on a data page.  If you already have a copy of the page in memory
and you corrupt the copy on disk, the corrupted copy won't be noticed.
And if you happen to trigger a write of that page, the corruption will
quietly be fixed.  This is all good, but it's something to be aware of
when writing test code.

The other thing to watch out for is that you're not hitting an
Index-Only Scan anywhere, because then you're bypassing the database
page you corrupted.

What I've done is come up with a repeatable test case that shows the
checksum patch finding a single bit of corruption that is missed by a
regular server.  The program is named check-check.sh, and a full output
run is attached as check-check.log

I also added a developer only debugging test patch as
show_block_verifications.patch  This makes every block read spew a
message about what relation it's touching, and proves the checksum
mechanism is being hit each time.  The main reason I needed that is to
make sure the pages I expected to be read were actually the ones being
read.  When I accidentally was hitting index-only scans for example, I
could tell that because it was touching something from
pgbench_accounts_pkey instead the pgbench_account table data I was
corrupting.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com