> 21 янв. 2017 г., в 18:18, Petr Jelinek <petr.jelinek@2ndquadrant.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
allthe 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
severaltimes 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
performancedifference for them comparing with non-checksumed clusters. And we have already had one case when we caught
datacorruption with checksums.
[1] https://yadi.sk/i/VAiWjv6t3AQCs2?lang=en
--
May the force be with you…
https://simply.name