Re: Checksums by default?

> 21 янв. 2017 г., в 18:18, Petr Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com> написал(а):
>
> On 21/01/17 11:39, Magnus Hagander wrote:
>> Is it time to enable checksums by default, and give initdb a switch to
>> turn it off instead?

+1

>
> I'd like to see benchmark first, both in terms of CPU and in terms of
> produced WAL (=network traffic) given that it turns on logging of hint bits.

Here are the results of my testing for 9.4 in December 2014. The benchmark was done on a production like use case when all the data fits in memory (~ 20 GB) and doesn’t fit into shared_buffers (it was intentionally small - 128 MB on stress stend), so that shared_blk_read / shared_blk_hit = 1/4. The workload was a typical OLTP but with mostly write queries (80% writes, 20% reads).

Here are the number of WALs written during the test:
Defaults 263
wal_log_hints 410
checksums 367

I couldn’t find the answer why WAL write amplification is even worse for wal_log_hints than for checksums but I checked several times and it always reproduced.

As for CPU I couldn’t see the overhead [1]. And perf top showed me less then 2% in calculating CRC.

For all new DBs we now enable checksums at initdb and several dozens of our shards use checksums now. I don’t see any performance difference for them comparing with non-checksumed clusters. And we have already had one case when we caught data corruption with checksums.

[1] https://yadi.sk/i/VAiWjv6t3AQCs2?lang=en

--
May the force be with you…
https://simply.name