Re: Checksums by default?

On 1/21/17 10:02 AM, Tom Lane wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Is it time to enable checksums by default, and give initdb a switch to turn
>> it off instead?
> Have we seen *even one* report of checksums catching problems in a useful
> way?

I've experienced multiple corruption events that were ultimately tracked
down to storage problems. These first manifested as corruption to PG
page structures, and I have no way to know how much user data might have
been corrupted.

Obviously I can't prove that checksums would have caught all of those
errors, but there's one massive benefit they would have given us: we'd
know that Postgres was not the source of the corruption. That would have
eliminated a lot of guesswork.

So that we can stop guessing about performance, I did a simple benchmark
on my laptop. Stock config except for synchronous_commit=off and
checkpoint_timeout=1min, with the idea being that we want to test
flushing buffers. Both databases initialized with scale=50, or 800MB.
shared_buffers was 128MB.

After a couple runs in each database to create dead tuples, runs were
performed with pgbench -rT 300 & sleep 2 && PGPORT=5444 pgbench -rT 300

No checksums checksums
818 tps 758 tps
821 tps 877 tps
879 tps 799 tps
739 tps 808 tps
867 tps 845 tps
854 tps 831 tps

Looking at per-statement latency, the variation is always in the update
to branches. I'll try to get some sequential runs tonight.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)