Hi,
I've been running a bunch of benchmarks to measure the overhead of data
checksums. I'm not sure if we're still considering this change for 10,
but let me post the results anyway.
The benchmark was fairly simple - run a few simple workloads, measure
tps, amount of WAL generated etc. The scripts and results (including sar
data etc) is available here:
https://bitbucket.org/tvondra/checksum-bench
I encourage others to validate the results, see if I made some stupid
mistakes and perhaps do additional analysis of the data. There's quite a
bit of data, including pg_stat_* snapshots collected every second, etc.
The workloads benchmarked were
* regular pgbench
* 'skewed' pgbench (simulating workloads that frequently access only
small subset of data)
* 'skewed-n' (same as before, but skipping updates on tiny tables, to
prevent concurrency issues, just like "pgbench -N")
* pgbench with 90% reads, 10% writes
All of this on three different scales:
* 100 - fits into shared buffers
* 1000 - fits into RAM
* 10000 - exceeds RAM
This was done on a machine with 2 x 8/16 cores, with 32 clients, each
run taking 2h. The machine has PCIe SSD drive (Intel 750), so fairly
powerful both for reads and writes.
The TPS results look like this (charts and spreadsheet attached):
test scale checksums no-checksums ratio
-----------------------------------------------------------------
pgbench 100 20888 21166 98.69%
1000 15578 16036 97.14%
10000 5205 5352 97.25%
test scale checksums no-checksums ratio
-----------------------------------------------------------------
read-write 100 151228 152330 99.28%
1000 95396 96755 98.60%
10000 13067 14514 90.03%
test scale checksums no-checksums ratio
------------------------------------------------------------------
skewed 100 20957 20805 100.73%
1000 20865 20744 100.58%
10000 8855 9482 93.39%
test scale checksums no-checksums ratio
-----------------------------------------------------------------
skewed-n 100 29226 28104 103.99%
1000 28011 27612 101.45%
10000 9363 10109 92.62%
So it seems with smaller scales, the overhead is fairly low (~2%, so
within noise). On the large data the throughput drops by ~10% with some
of the workloads.
The amount of WAL generated (over the 2h run with checkpoints triggered
every 30 minutes) looks like this:
test scale checksums no-checksums ratio
-------------------------------------------------------------
pgbench 100 66.11 66.48 99.45%
1000 119.77 119.55 100.19%
10000 375.05 350.63 106.97%
test scale checksums no-checksums ratio
---------------------------------------------------------------
read-write 100 26.06 26.26 99.26%
1000 70.37 70.01 100.51%
10000 147.54 154.78 95.33%
test scale checksums no-checksums ratio
-----------------------------------------------------------------
skewed 100 65.64 64.88 101.18%
1000 117.20 114.79 102.10%
10000 370.15 346.39 106.86%
test scale checksums no-checksums ratio
----------------------------------------------------------------
skewed-n 100 61.00 56.53 107.92%
1000 106.85 102.48 104.26%
10000 372.03 348.29 106.82%
Those numbers should be probably corrected a bit to compensate for the
tps difference (if you to ~10% more TPS, you expect ~10% more WAL), but
I think it's clear the WAL overhead is roughly +10% or so.
I'm not suggesting this means we should or should not enable data
checksums by default, but hopefully it provides some actual data
measuring the impact. If needed, I can do more tests with other
workloads, and it should be difficult to use the scripts on other
systems if you want to run some benchmarks yourself.
regards
regards
Tomas Vondra
