Bug #55165 performance regression in sysbench POINT_SELECT test
Submitted: 11 Jul 2010 12:11 Modified: 18 Feb 2017 11:02
Reporter: Alexey Stroganov Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.5.5 OS:Any
Assigned to: CPU Architecture:Any

[11 Jul 2010 12:11] Alexey Stroganov 
Description:
While running sysbench tests for 5.5.5 release we've observed drop in POINT_SELECT test.

Tests were run on two boxes: with 8(SUSE10/RHEL52) and 24(Fedora10) cores.

In all cases we observe most notable drop for MyISAM - 5-10%. InnoDB engine is very slightly affected.

Another interesting observation about scalability: for both engines for threads 64-256 throughput is significantly better on box with 8 cores.

8 cores:
========
                    SUSE 10                RHEL52
#              5.5.4     5.5.5       5.5.4      5.5.5
# Thread      MYISAM    MYISAM      MYISAM     MYISAM
          4 46272.56  45856.11    39772.79   38915.87
         16 87740.53  85343.50    76977.96   73884.17
         64 83644.22  81509.31    76645.71   73330.75
        128 91204.01  80677.23    79091.37   72744.72
        256 83919.60  78113.56    76554.30   68709.80

                   SUSE 10                  RHEL52
#             5.5.4      5.5.5       5.5.4      5.5.5
# Thread      INNODB    INNODB      INNODB     INNODB
          4 45612.41  44181.04    37873.98   38718.35
         16 93676.89  91398.59    79474.89   77829.43
         64 89453.89  87027.06    77205.62   75157.31
        128 92726.32  89981.52    78881.81   77203.74
        256 89609.88  86358.62    77977.69   76592.71

24 cores:
=========

#              554        555           554         555
# Thread      MYISAM     MYISAM       INNODB     INNODB
          4 27989.37   26331.12     30809.56   29727.26
         16 79168.94   66985.89     84295.17   81184.00
         64 64659.39   59059.19     51893.51   52381.30
        128 63272.82   58184.60     50829.75   50514.62
        256 62476.88   59948.34     45903.46   45149.31

POINT_SELECT query:
-------------------
SELECT c FROM sbtest WHERE id=1;

EXPLAIN(similar for both servers 554/555)
-----------------------------------------
explain SELECT c FROM sbtest WHERE id=1;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | sbtest | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+

PSE information for POINT_SELECT/MyISAM/256 threads on 24 core box:

5.5.4
=====
------------------------------------------------------+------------+-------------------+----------------+----------------+---
_NAME                                                 | COUNT_STAR | SUM_TIMER_WAIT    | MIN_TIMER_WAIT | AVG_TIMER_WAIT | MA
------------------------------------------------------+------------+-------------------+----------------+----------------+---
synch/mutex/mysys/KEY_CACHE::cache_lock               |   75428827 | 26336850270751120 |          41360 |      349161604 |
synch/rwlock/sql/MDL_lock::rwlock                     |   25088586 |  7663263258100960 |          48880 |      305448192 |
synch/mutex/sql/LOCK_open                             |   25090676 |  7293817918381840 |          41360 |      290698342 |
synch/mutex/myisam/MYISAM_SHARE::intern_lock          |   25083737 |  6418910632170240 |          41360 |      255899295 |
synch/mutex/mysys/THR_LOCK::mutex                     |   25083494 |  5584870960918960 |          41360 |      222651236 |
synch/mutex/sql/MDL_map::mutex                        |   12546869 |  1830502434239120 |          41360 |      145893165 |
synch/rwlock/myisam/MYISAM_SHARE::key_root_lock       |   12398587 |   562966028745200 |          48880 |       45405660 |
synch/cond/sql/COND_thread_cache                      |          8 |   299745503232320 |       70022480 | 37468187904040 | 28
synch/rwlock/sql/LOCK_grant                           |   12280903 |   234262430195680 |          48880 |       19075342 |
io/file/myisam/dfile                                  |   12474480 |   106794617315040 |        1684480 |        8561047 |
synch/mutex/mysys/THR_LOCK_myisam                     |        256 |    20010195006240 |         172960 |    78164824243 |
synch/mutex/sql/THD::LOCK_thd_data                    |   72064316 |    11828841796880 |          41360 |         164142 |
synch/cond/mysys/my_thread_var::suspend               |        129 |      571338670240 |       54467360 |     4428981939 |

5.5.5
======
------------------------------------------------------+------------+-------------------+----------------+----------------+---
synch/mutex/mysys/KEY_CACHE::cache_lock               |   61619031 | 28878838319625000 |          41250 |      468667517 |
synch/rwlock/sql/MDL_lock::rwlock                     |   20496091 |  6821593391227500 |          48750 |      332824117 |
synch/mutex/sql/LOCK_open                             |   20495842 |  6317483114463750 |          41250 |      308232426 |
synch/mutex/myisam/MYISAM_SHARE::intern_lock          |   20491123 |  5278663186286250 |          45000 |      257607315 |
synch/mutex/mysys/THR_LOCK::mutex                     |   20490938 |  4153848832590000 |          45000 |      202716382 |
synch/mutex/sql/MDL_map::mutex                        |   10250669 |  1523962589066250 |          48750 |      148669573 |
synch/rwlock/myisam/MYISAM_SHARE::key_root_lock       |   10102809 |   568764367942500 |          52500 |       56297646 |
synch/rwlock/sql/LOCK_grant                           |   10080662 |   202360561188750 |          52500 |       20074134 |
synch/cond/sql/COND_thread_cache                      |          9 |   135479833507500 |       66840000 | 15053314834166 |
io/file/myisam/dfile                                  |   10187449 |    97136017961250 |        1635000 |        9534871 |
synch/mutex/sql/THD::LOCK_thd_data                    |   59440541 |    11762712566250 |          41250 |         197890 |
synch/mutex/mysys/THR_LOCK_myisam                     |        256 |    11212889718750 |         123750 |    43800350463 |
synch/cond/mysys/my_thread_var::suspend               |         78 |      347128972500 |      182527500 |     4450371442 |

How to repeat:
- start server with following settings for myisam:
table_cache=2048
thread_cache=16
key_buffer_size=1300M

- load or restore sysbench database with 1M rows

sysbench --test=oltp --oltp-table-size=1000000 \
--oltp-dist-type=uniform --oltp-table-name=sbtest \
--init-rng=off --mysql-host=127.0.0.1 --mysql-user=root \
--mysql-port=3306 --mysql-db=sbtest --mysql-table-engine=MYISAM \
prepare

- run sysbench POINT_SELECT test

sysbench  --num-threads=<threads> \
--test=oltp --oltp-table-size=1000000 \
--oltp-dist-type=uniform --oltp-table-name=sbtest \
--init-rng=off --validate=on --forced-shutdown=1 \
--max-requests=0 --max-time=300 \
--mysql-host=127.0.0.1 --mysql-user=root --mysql-port=3306 \
--mysql-db=sbtest --mysql-table-engine=MYISAM \
--oltp-point-selects=1 --oltp-simple-ranges=0 \
--oltp-sum-ranges=0 --oltp-order-ranges=0 \
--oltp-distinct-ranges=0 --oltp-skip-trx=on --oltp-read-only=on run
[11 Jul 2010 12:23] Alexey Stroganov 
Most notable changes in PSE profile information between 5.5.4 and 5.5.5:
- In 5.5.5 number of calls for hottest mutexes - reduced
                             
                                    5.5.4         5.5.5                  
KEY_CACHE::cache_lock            75428827   |   61619031 |  
MDL_lock::rwlock                 25088586   |   20496091 |  

- however avg time for every call is increased
KEY_CACHE::cache_lock            349161604  |  468667517
MDL_lock::rwlock                 305448192  |  332824117 

- Sum_timer_wait - total time spent on these mutexes
KEY_CACHE::cache_lock    26336850270751120  |  28878838319625000 
MDL_lock::rwlock          7663263258100960  |   6821593391227500
[18 Jan 2017 11:02] MySQL Verification Team 
It's this bug still valid having a verifer and open status?. Thanks.
[19 Feb 2017 1:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".