Checksum performance again
InnoDB computes a checksum immediately prior to writing a page to disk and stores that on the page. It validates the checksum immediately after reading the page back from disk. I love this feature but I don't love the implementation of the checksum function and neither does Domas.
This is a big deal on servers that can do 10,000 IOPS.
I added code for different checksums to my sysbench branch. The default for sysbench is crc32. The --file-checksum option supports that and innodb_byte, innodb_word and none. The innodb_byte function matches what is used by InnoDB. The innodb_word function is similar to innodb_byte but computes the checksum one word at a time. Switching to that in production would require a dump/reload unless you try to be clever.
I lost the sysbench command line but salvaged this from sysbench output:
- ran for 180 seconds
- used 1 64GB file
- used the rndrd mode to do random reads
- file opened with O_DIRECT
- test repeated for 1, 2, 4, 8, 16, 32, 64 and 128 threads
- used 4kb, 8kb and 16kb reads
- test run using these page checksum algorithms: none, crc32, innodb_byte, innodb_word
- I think I used XFS
The server has 8 cores and can do a lot of IOPS as measured by sysbench fileio
- 59000 4kb reads/second with 64 concurrent threads
- 49000 8kb reads/second with 64 concurrent threads
- 35000 16kb reads/second with 64 concurrent threads
The table below reports the %CPU time in user and system mode. System time is high for all tests because a lot of IO is being done (35,000 to 59,000 reads/second). With 16kb pages almost twice as much CPU time is used by innodb_byte. The difference at 4kb pages is much less signficant because the system time is high. It would be significant without that.
checksum 4kb reads 8kb reads 16kb reads
none 2.2user + 32sys 1.8user + 27sys 1.2user + 20sys
innodb_byte 11.6user + 32sys 17.5user + 27sys 24.1user + 20sys
innodb_word 3.5user + 32sys 4.3user + 27sys 4.8user + 20sys
I added feature request 49852 for this.