Bug #37576 | MySQL6.0 performance regression compared with MySQL5.1 using Sysbench read test | ||
---|---|---|---|
Submitted: | 23 Jun 2008 6:29 | Modified: | 6 Dec 2011 1:00 |
Reporter: | Xuekun Hu | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S5 (Performance) |
Version: | 6.0 | OS: | Linux (SLES10SP1 (2.6.16.46-0.12-smp)) |
Assigned to: | Olav Sandstå | CPU Architecture: | Any |
Tags: | DsMRR, mrr, MySQL6.0, optimizer_switch |
[23 Jun 2008 6:29]
Xuekun Hu
[23 Jun 2008 10:27]
Susanne Ebrecht
Verified as described.
[12 Jun 2010 14:18]
Olav Sandstå
Here are some initial performance numbers when running sysbench as described above for several different MySQL versions. MySQL was checked out from the corresponding bzr trees and compiled using ./BUILD/compile-pentium. The table gives the tps as reported by sysbench: 5.1-bugteam (5.1.48) 1088 trunk-bugfixing (5.5.5) 1108 +2% next-mr-bugfixing (5.6.99) 1108 +2% opt-backporting with MRR 967 -11% opt-backporting with MRR disabled 1036 -5% 6.0-bugfixing with MRR 744 -31% 6.0-bugfixing with MRR disabled 781 -28% The test was run using a dual CPU AMD Opteron 2.6 GHz server running Linux. Next step is to find out why we see the performance drops in the two bzr trees that have MRR (mysql-next-mr-opt-backporting and mysql-6.0-codebase-bugfixing) and why disabling MRR improves the performance. Note that the performance difference also might be influence by changes in how MySQL was compiled and the test were run.
[17 Jun 2010 9:51]
Olav Sandstå
The majority of the queries run by sysbench consists of the following five queries: 1. SELECT c FROM sbtest WHERE id = ? 2. SELECT c FROM sbtest WHERE id BETWEEN ? AND ? 3. SELECT c FROM sbtest WHERE id BETWEEN ? AND ? ORDER BY c 4. SELECT SUM(k) FROM sbtest WHERE id BETWEEN ? AND ? 5. SELECT DISTINCT c FROM sbtest WHERE id BETWEEN ? AND ? ORDER BY c (for the queries with BETWEEN the BETWEEN interval is 100 records) I have made a simple test client and run each of these queries separately. The following table gives the time to run each individual query (in micro-seconds) against mysql-next-mr-bugfixing and mysql-next-mr-opt-backporting with MRR enabled and disabled: next-mr opt-backporting opt-backporting with MRR without MRR 1. 70.4 72.6 ( +3%) 73.4 ( +4%) 2. 239.3 286.2 (+20%) 261.5 ( +9%) 3. 354.7 408.0 (+15%) 375.6 ( +6%) 4. 198.7 259.4 (+31%) 234.8 (+18%) 5. 308.8 358.7 (+16%) 343.4 (+10%) The "response time" in this table includes the time spent both in the test client and the server. The time is measured by running each query 3 million times back-to-back. The percentage numbers are the increase in time compared to mysql-next-mr-bugfixing (and since these also includes time spent in the client these numbers are lower than the actual performance increase in the server).
[6 Dec 2010 12:41]
Olav Sandstå
Sysbench with a small database that are mostly stored in memory does not benefit from using MRR (as shown by the initial bug report and some of the more detailed test reported here). MRR will introduce extra CPU overhead due to first filling the sort buffer, then sorting the keys and then retrieving the records. With most of the data in memory MRR will not save IO. Until now the default configuration for MRR has been to always use it. One of the changes of WL#5692 is to make MRR "cost based" by default. With this change cost estimates for MRR will determine that using MRR is not beneficial and thus MRR not be used when running sysbench. This should remove the majority of the overhead introduced by using MRR. The plan is to be able to close this bug report when WL#5692 is completed. Keeping it open also to be able to add some performance numbers after WL#5602 has been pushed to the source trees.
[23 Jun 2011 7:26]
Olav Sandstå
The use of MRR is now based on cost estimates in 5.6. Based on these cost estimates MRR will no longer be used for simple queries like those produced by sysbench. Closing this bug.
[6 Dec 2011 1:00]
Paul DuBois
Noted in 5.6.2 changelog. Multi-read range access is now based on cost estimates and no longer used for simple queries for which it is not beneficial.