Bug #52588 InnoDB: performance regression in sysbench OLTP_RW test
Submitted: 5 Apr 2010 2:45 Modified: 7 Jul 2010 22:22
Reporter: Alexey Stroganov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S2 (Serious)
Version:5.5.4/iplugin1.1 OS:Any
Assigned to: Sunny Bains CPU Architecture:Any

[5 Apr 2010 2:45] Alexey Stroganov 
Description:
While running variations of sysbench OLTP_RW test using special distribution for 5.5.3 and 5.5.4 the notable regression was observed.

In the test we populate table with 100k rows(using either special or uniform
distributions) and then access either whole dataset(100k) or some smaller
fraction(10k) with 100 threads using uniform or special distributions.

------------------------------------------------------------------
data         |                         |                         |
distribution |  oltp-dist-type=special |  oltp-dist-type=uniform |
------------------------------------------------------------------
query        |      |         |        |      |         |        |
distribution | rows |     553 |    554 | rows |     553 |    554 |
---------------------------------------|-------------------------|
uniform      |      |         |        |      |         |        |
             | 100k | 1017.44 | 861.91 | 100k | 1012.24 | 862.28 |
             | 10k  | 1023.41 | 821.70 | 10k  | 1022.40 | 834.93 |
------------------------------------------------------------------
special      |      |         |        |      |         |        |
             | 100k | 1000.17 | 821.84 | 100k | 1000.34 | 813.50 |
             | 10k  |  907.50 |  31.78 | 10k  |  903.45 |  48.90 |
------------------------------------------------------------------

How to repeat:
start 5.5.3/5.5.4 servers with defaults options 

populate db with data using special distribution
-------------------------------------------------
sysbench --test=oltp --mysql-host=127.0.0.1  --mysql-user=root --mysql-db=sbtest --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-port=10000 prepare 

populate db with data using uniform distribution
-------------------------------------------------
sysbench --test=oltp --mysql-host=127.0.0.1  --mysql-user=root --mysql-db=sbtest --mysql-table-engine=innodb --oltp-table-size=100000 --mysql-port=10000 --oltp-dist-type=uniform prepare 

run oltp_rw using special distribution
---------------------------------------
sysbench --forced-shutdown=1 --test=oltp --num-threads=100 --mysql-host=127.0.0.1  --mysql-user=root --mysql-db=sbtest --mysql-table-engine=innodb --max-requests=0 --max-time=60 --oltp-table-size=<10000 or 100000> --mysql-port=10000 run

run oltp_rw using special distribution
---------------------------------------
sysbench --forced-shutdown=1 --test=oltp --num-threads=100 --mysql-host=127.0.0.1  --mysql-user=root --mysql-db=sbtest --mysql-table-engine=innodb --max-requests=0 --max-time=60 --oltp-table-size=<10000 or 100000> --mysql-port=10000 --oltp-dist-type=uniform run