Bug #64170 why does buf_page_get_gen confirm the page checksum for compressed pages
Submitted: 30 Jan 2012 18:46 Modified: 2 Aug 2012 19:51
Reporter: Mark Callaghan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S5 (Performance)
Version: OS:Any
Assigned to: Marko Mäkelä
Tags: checksum, compressed, innodb

[30 Jan 2012 18:46] Mark Callaghan
Description:
Why does buf_page_get_gen call buf_zip_decompress(..., srv_use_checksums)?
The page checksum for a compressed page is confirmed when the page is read from disk via:
   buf_page_read_low
     buf_page_io_complete
       buf_page_is_corrupted
         page_zip_calc_checksum
The checksum confirmation done on the buf_page_get_gen seems to be an extra check and not always a good use of CPU.

How to repeat:
Look for calls to page_zip_calc_checksum

Suggested fix:
Disable or make optional via yet another my.cnf parameter checksumming to be done via buf_page_get_gen
[30 Jan 2012 19:16] Mark Callaghan
It looks like there might an extra confirmation of the checksum on the write side too in buf_flush_block_write_low when the checksum was set in buf_flush_init_for_writing and buf_LRU_free_block

        case BUF_BLOCK_ZIP_DIRTY:
                frame = bpage->zip.data;
                if (UNIV_LIKELY(srv_use_checksums)) {
                        ut_a(mach_read_from_4(frame + FIL_PAGE_SPACE_OR_CHKSUM)
                             == page_zip_calc_checksum(frame, zip_size));
                }
[6 Mar 2012 15:16] Mark Callaghan
After removing other bottlenecks and getting a server into the state where very few pages are on the unzip_LRU, I get 5% more QPS by removing the extra checksum calls.
[9 Mar 2012 20:36] Mark Callaghan
Performance results are at https://www.facebook.com/note.php?note_id=10150580052310933
[19 Jun 2012 5:40] Shane Bester
Related: Bug 13397030 - DO NOT CALC UNNECESSARY CHECKSUMS WHEN VALIDATING A PAGE
That was fixed in 5.6.4.
[2 Aug 2012 19:51] John Russell
Added to changelog for 5.6.7, 5.7.0: 

This fix removes redundant checksum validation on InnoDB pages. The
checksum was being verified both when a compressed page was read from
disk and when it was uncompressed. Now the verification is only
performed when the page is read from disk.