Bug #73168 | locking/unlocking overhead for THD::LOCK_thd_data | ||
---|---|---|---|
Submitted: | 2 Jul 2014 4:14 | Modified: | 2 Jul 2014 11:15 |
Reporter: | liu hickey (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S5 (Performance) |
Version: | 5.6/5.7 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | LOCK_thd_data, set_query |
[2 Jul 2014 4:14]
liu hickey
[2 Jul 2014 11:15]
MySQL Verification Team
Hello Liu, Thank you for the report. I observed this while using sysbench // 5.6.20 with defaults settings // mysqld with --performance_schema_instrument='wait/synch/mutex/%=on' ** set up proper consumers // Sysbench [root@cluster-repo mysql-advanced-5.6.20]# sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=dbtest --mysql-user=root --mysql-socket=/tmp/72720M/72720M.sock prepare sysbench 0.4.12: multi-threaded system evaluation benchmark No DB drivers specified, using mysql Creating table 'sbtest'... Creating 1000000 records in table 'sbtest'... [root@cluster-repo mysql-advanced-5.6.20]# sysbench --test=oltp --oltp-test-mode=complex --oltp-read-only=off --oltp-table-size=1000000 --num-threads=900 --max-time=360 --max-requests=0 --mysql-db=dbtest --mysql-user=root --mysql-socket=/tmp/72720M/72720M.sock run sysbench 0.4.12: multi-threaded system evaluation benchmark No DB drivers specified, using mysql Running the test with following options: Number of threads: 900 Doing OLTP test. Running mixed OLTP test Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases) Using "BEGIN" for starting transactions Using auto_inc on the id column Threads started! // mysql> select version(); +---------------------------------------+ | version() | +---------------------------------------+ | 5.6.20-enterprise-commercial-advanced | +---------------------------------------+ 1 row in set (0.00 sec) mysql> use performance_schema;select e.EVENT_NAME, e.SOURCE, e.OPERATION, count(*) as cnt from events_waits_current e join threads t using(thread_id) where type='foreground' and processlist_id not in (1, @@pseudo_thread_id) group by e.EVENT_NAME, e.SOURCE, e.OPERATION order by cnt desc; Database changed +-----------------------------------------+-------------------+-----------+-----+ | EVENT_NAME | SOURCE | OPERATION | cnt | +-----------------------------------------+-------------------+-----------+-----+ | wait/synch/mutex/sql/THD::LOCK_thd_data | sql_class.cc:4467 | lock | 250 | | idle | mysqld.cc:935 | idle | 89 | | wait/synch/mutex/sql/LOCK_open | sql_base.cc:2936 | lock | 36 | | wait/synch/mutex/sql/THD::LOCK_thd_data | sql_class.h:3983 | lock | 4 | | wait/synch/mutex/innodb/dict_sys_mutex | dict0dict.cc:1026 | lock | 3 | | wait/io/table/sql/handler | handler.cc:2788 | fetch | 1 | +-----------------------------------------+-------------------+-----------+-----+ 6 rows in set (0.08 sec) Thanks, Umesh
[25 Feb 2015 10:51]
MySQL Verification Team
Hello Liu, Could you please provide more details about real performance impact? Do you have any test case/ real time stats which confirms the performance impact etc? Thanks, Umesh