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:
None 
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
Description:
MySQL6.0 is ~18% lower than MySQL5.1 with MyISAM table, using Sysbench read test on DP, Xeon Dual Core platform. 

Configuration:
   MySQL5.1.22, MySQL6.0 (Changeset@1.2613)
   OS: SLES10SP1 (2.6.16.46-0.12-smp, x64)

How to repeat:
Sysbench prepare:
    sysbench prepare --test=oltp --mysql-table-engine=myisam --oltp-table-size=10000000 --myisam-max-rows=10000000 --mysql-socket=/tmp/mysql.sock

Sysbench run:
    sysbench run --num-threads=4 --max-time=100 --max-requests=0 --mysql-table-engine=myisam --test=oltp --oltp-read-only=on --mysql-user=mysql --oltp-table-size=10000000 --mysql-socket=/tmp/mysql.sock run

Results on MySQL5.1.22: 2962 tps

Results on MySQL6.0: 2498 tps

Suggested fix:
The problem is in the new DsMRR code. After forcing to disable DsMRR on MySQL6.0, the performance is back to similar with MySQL5.1.
[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.