| Bug #43161 | buf_calc_page_new_checksum and ut_2_power_up use a lot of CPU on busy IO loads | ||
|---|---|---|---|
| Submitted: | 24 Feb 2009 21:58 | Modified: | 28 Jun 2010 23:53 |
| Reporter: | Mark Callaghan | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
| Version: | 5.0, 5.1 bzr | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | buf_calc_page_new_checksum, checksum, io | ||
[3 Jul 2009 7:26]
Sveta Smirnova
Thank you for the report. I can not repeat described behavior. Please indicate accurate version of MySQL server you use and how you did tests. I used sysbench read-only test.
[3 Jul 2009 9:48]
Domas Mituzas
Do note, that compatibility issues of changing the algorithm can be resolved by changing the use of pre-4.0.14 checksum field - it can be used for new form checksum, as long as the standard checksum field has a magic variable.
[3 Jul 2009 14:50]
Mark Callaghan
An easier way to reproduce this is:
1) get server with good IO capacity (1000+ IOPs)
2) create several large tables that cannot be cached and have no indexes
3) perform this concurrently for all tables
select count(*) from Ti where <impossible condition on non-indexed column>
[6 Jul 2009 7:55]
Sveta Smirnova
Thank you for the feedback. Verified with a slightly different results: $cat ../mysql-5.1/bug43161.ogprof.txt ... 32.61 62389.00 31113.00 buf_calc_page_new_checksum ... 0.18 80574.00 170.00 ut_2_power_up ... $cat mysql-5.0/bug43161.ogprof.txt ... 37.64 77333.00 77333.00 buf_calc_page_new_checksum ... 0.54 156512.00 1100.00 ut_2_power_up Tested with RQG: $ ./runall.pl --basedir=/users%2
[6 Jul 2009 7:57]
Sveta Smirnova
Thank you for the feedback. Verified with a slightly different results: $cat ../mysql-5.1/bug43161.ogprof.txt ... 32.61 62389.00 31113.00 buf_calc_page_new_checksum ... 0.18 80574.00 170.00 ut_2_power_up ... $cat mysql-5.0/bug43161.ogprof.txt ... 37.64 77333.00 77333.00 buf_calc_page_new_checksum ... 0.54 156512.00 1100.00 ut_2_power_up Tested with RQG: $ ./runall.pl --basedir=/users%2
[26 Jun 2010 4:38]
James Day
Also see bug #54814 which appears to duplicate this one.
[28 Jun 2010 23:53]
Adam Dixon
Setting as duplicate, see http://bugs.mysql.com/bug.php?id=54814
[9 Apr 2014 23:19]
James Day
The duplicate bug #54814 has just been closed. As of 5.7.4 the server calls ut_2_power_up once and stores the result in the buf_pool_t structure, eliminating the overhead of repeated calls. In 5.6 and later we implemented a new checksum method that Mark mentioned in the original report and that can use CPU hardware CRC32 checksum support to reduce the cost of CRC checking. If CRC32 compatibility mode is selected in 5.6 and later the server will gradually convert pages to the new checksum as they are modified. There's CPU overhead in reading unchanged pages because the new checksum will be tried before the old one when compatibility mode is used. As of 5.6.17 online alter table is used for OPTIMIZE TABLE and for many servers it may be non-disruptive enough to use that to update a table to use the new checksums quickly. See http://mysqlserverteam.com/mysql-5-6-17-improved-online-optimize-table-for-innodb-and-part... for more on this. All of this is of greatest relevance to people using extremely fast storage like Fusion-io and similar bus-attached flash storage; to a lesser degree also to use of SSDs. James Day, MySQL Senior Principal Support Engineer, Oracle
Description: On a server with a lot of capacity and an IO bound workload, the top sources of CPU time are: 20% -> buf_calc_page_new_checksum (all from ut_fold_binary) 5% -> ut_2_power_up (all from BUF_READ_AHEAD_AREA) How to repeat: Use a read intensive workload with unmodified MySQL or a read/write workload with Google/Percona patched and a server that can do more than 1000 IOPs. Run oprofile. Suggested fix: I don't think that BUF_READ_AHEAD_AREA needs this: #define BUF_READ_AHEAD_AREA \ ut_min(64, ut_2_power_up(buf_pool->curr_size / 32)) Can it use '64' instead? For buf_calc_page_new_checksum there is nothing trivial that can be done. But machine specific code, such as x86 SSE, may help. Or a new checksum algorithm if you are willing to break compatability (I am not).