Google bookmark synch

14.17.2 Monitoring InnoDB Mutex Waits Using Performance Schema A mutex is a synchronization mechanism used in the code to enforce that only one thread at a given time can have access to a common resource. When two or more threads executing in the server need to access the same resource, the threads compete against each other. The first thread to obtain a lock on the mutex causes the other threads to wait until the lock is released. For InnoDB mutexes that are instrumented, mutex waits can be monitored using Performance Schema. Wait event data collected in Performance Schema tables can help identify mutexes with the most waits or the greatest total wait time, for example. The following example demonstrates how to enable InnoDB mutex wait instruments, how to enable associated consumers, and how to query wait event data. To view available InnoDB mutex wait instruments, query the Performance Schema setup_instruments table, as shown below. All InnoDB mutex wait instruments are disabled by default. mysql> SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE '%wait/synch/mutex/innodb%';+-------------------------------------------------------+---------+-------+| NAME | ENABLED | TIMED |+-------------------------------------------------------+---------+-------+| wait/synch/mutex/innodb/commit_cond_mutex | NO | NO || wait/synch/mutex/innodb/innobase_share_mutex | NO | NO || wait/synch/mutex/innodb/autoinc_mutex | NO | NO || wait/synch/mutex/innodb/buf_pool_mutex | NO | NO || wait/synch/mutex/innodb/buf_pool_zip_mutex | NO | NO || wait/synch/mutex/innodb/cache_last_read_mutex | NO | NO || wait/synch/mutex/innodb/dict_foreign_err_mutex | NO | NO || wait/synch/mutex/innodb/dict_sys_mutex | NO | NO || wait/synch/mutex/innodb/recalc_pool_mutex | NO | NO || wait/synch/mutex/innodb/file_format_max_mutex | NO | NO || wait/synch/mutex/innodb/fil_system_mutex | NO | NO || wait/synch/mutex/innodb/flush_list_mutex | NO | NO || wait/synch/mutex/innodb/fts_bg_threads_mutex | NO | NO || wait/synch/mutex/innodb/fts_delete_mutex | NO | NO || wait/synch/mutex/innodb/fts_optimize_mutex | NO | NO || wait/synch/mutex/innodb/fts_doc_id_mutex | NO | NO || wait/synch/mutex/innodb/log_flush_order_mutex | NO | NO || wait/synch/mutex/innodb/hash_table_mutex | NO | NO || wait/synch/mutex/innodb/ibuf_bitmap_mutex | NO | NO || wait/synch/mutex/innodb/ibuf_mutex | NO | NO || wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | NO | NO || wait/synch/mutex/innodb/log_sys_mutex | NO | NO || wait/synch/mutex/innodb/page_zip_stat_per_index_mutex | NO | NO || wait/synch/mutex/innodb/purge_sys_pq_mutex | NO | NO || wait/synch/mutex/innodb/recv_sys_mutex | NO | NO || wait/synch/mutex/innodb/recv_writer_mutex | NO | NO || wait/synch/mutex/innodb/redo_rseg_mutex | NO | NO || wait/synch/mutex/innodb/noredo_rseg_mutex | NO | NO || wait/synch/mutex/innodb/rw_lock_list_mutex | NO | NO || wait/synch/mutex/innodb/rw_lock_mutex | NO | NO || wait/synch/mutex/innodb/srv_dict_tmpfile_mutex | NO | NO || wait/synch/mutex/innodb/srv_innodb_monitor_mutex | NO | NO || wait/synch/mutex/innodb/srv_misc_tmpfile_mutex | NO | NO || wait/synch/mutex/innodb/srv_monitor_file_mutex | NO | NO || wait/synch/mutex/innodb/buf_dblwr_mutex | NO | NO || wait/synch/mutex/innodb/trx_undo_mutex | NO | NO || wait/synch/mutex/innodb/trx_pool_mutex | NO | NO || wait/synch/mutex/innodb/trx_pool_manager_mutex | NO | NO || wait/synch/mutex/innodb/srv_sys_mutex | NO | NO || wait/synch/mutex/innodb/lock_mutex | NO | NO || wait/synch/mutex/innodb/lock_wait_mutex | NO | NO || wait/synch/mutex/innodb/trx_mutex | NO | NO || wait/synch/mutex/innodb/srv_threads_mutex | NO | NO || wait/synch/mutex/innodb/rtr_active_mutex | NO | NO || wait/synch/mutex/innodb/rtr_match_mutex | NO | NO || wait/synch/mutex/innodb/rtr_path_mutex | NO | NO || wait/synch/mutex/innodb/rtr_ssn_mutex | NO | NO || wait/synch/mutex/innodb/trx_sys_mutex | NO | NO || wait/synch/mutex/innodb/zip_pad_mutex | NO | NO |+-------------------------------------------------------+---------+-------+49 rows in set (0.02 sec) Some InnoDB mutex instances are created at server startup and are only instrumented if the associated instrument is also enabled at server startup.

Is presented in descending order, by the number of summarized wait events (COUNT_STAR). You can adjust the ORDER BY clause to order the data by total wait time. mysql> SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 SUM_TIMER_WAIT_MS FROM performance_schema.events_waits_summary_global_by_event_name WHERE SUM_TIMER_WAIT > 0 AND EVENT_NAME LIKE 'wait/synch/mutex/innodb/%' ORDER BY COUNT_STAR DESC;+--------------------------------------------------+------------+-------------------+| EVENT_NAME | COUNT_STAR | SUM_TIMER_WAIT_MS |+--------------------------------------------------+------------+-------------------+| wait/synch/mutex/innodb/os_mutex | 78831 | 10.3283 || wait/synch/mutex/innodb/log_sys_mutex | 41488 | 6510.3233 || wait/synch/mutex/innodb/trx_sys_mutex | 29770 | 1107.9687 || wait/synch/mutex/innodb/lock_mutex | 24212 | 104.0724 || wait/synch/mutex/innodb/trx_mutex | 22756 | 1.9421 || wait/synch/mutex/innodb/rseg_mutex | 20333 | 3.6220 || wait/synch/mutex/innodb/dict_sys_mutex | 13422 | 2.2284 || wait/synch/mutex/innodb/mutex_list_mutex | 12694 | 344.1164 || wait/synch/mutex/innodb/fil_system_mutex | 9208 | 0.9542 || wait/synch/mutex/innodb/rw_lock_list_mutex | 8304 | 0.1794 || wait/synch/mutex/innodb/trx_undo_mutex | 6190 | 0.6801 || wait/synch/mutex/innodb/buf_pool_mutex | 2869 | 29.4623 || wait/synch/mutex/innodb/innobase_share_mutex | 2005 | 0.1349 || wait/synch/mutex/innodb/flush_list_mutex | 1274 | 0.1300 || wait/synch/mutex/innodb/file_format_max_mutex | 1016 | 0.0469 || wait/synch/mutex/innodb/purge_sys_bh_mutex | 1004 | 0.0326 || wait/synch/mutex/innodb/buf_dblwr_mutex | 640 | 0.0437 || wait/synch/mutex/innodb/log_flush_order_mutex | 437 | 0.0510 || wait/synch/mutex/innodb/recv_sys_mutex | 394 | 0.0202 || wait/synch/mutex/innodb/srv_sys_mutex | 169 | 0.5259 || wait/synch/mutex/innodb/lock_wait_mutex | 154 | 0.1172 || wait/synch/mutex/innodb/ibuf_mutex | 9 | 0.0027 || wait/synch/mutex/innodb/srv_innodb_monitor_mutex | 2 | 0.0009 || wait/synch/mutex/innodb/ut_list_mutex | 1 | 0.0001 || wait/synch/mutex/innodb/recv_writer_mutex | 1 | 0.0005 |+--------------------------------------------------+------------+-------------------+25 rows in set (0.01 sec)Note The preceding result set includes wait event data produced during the startup process. To exclude this data, you can truncate the events_waits_summary_global_by_event_name table immediately after startup and before running your workload. However, the truncate operation itself may produce a negligible amount wait event data. mysql> TRUNCATE performance_schema.events_waits_summary_global_by_event_name;

| 686322 || 87 | wait/synch/mutex/mysys/THR_LOCK_malloc | 320535 || 88 | wait/synch/mutex/mysys/THR_LOCK_malloc | 339390 || 89 | wait/synch/mutex/mysys/THR_LOCK_malloc | 377100 || 90 | wait/synch/mutex/sql/LOCK_plugin | 614673 || 91 | wait/synch/mutex/sql/LOCK_open | 659925 || 92 | wait/synch/mutex/sql/THD::LOCK_thd_data | 494001 || 93 | wait/synch/mutex/mysys/THR_LOCK_malloc | 222489 || 94 | wait/synch/mutex/mysys/THR_LOCK_malloc | 214947 || 95 | wait/synch/mutex/mysys/LOCK_alarm | 312993 |+----------+-----------------------------------------+------------+ As new events are added to a history table, older events are discarded if the table is full. Summary tables provide aggregated information for all events over time. The tables in this group summarize event data in different ways. To see which instruments have been executed the most times or have taken the most wait time, sort the events_waits_summary_global_by_event_name table on the COUNT_STAR or SUM_TIMER_WAIT column, which correspond to a COUNT(*) or SUM(TIMER_WAIT) value, respectively, calculated over all events: mysql> SELECT EVENT_NAME, COUNT_STAR FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10;+---------------------------------------------------+------------+| EVENT_NAME | COUNT_STAR |+---------------------------------------------------+------------+| wait/synch/mutex/mysys/THR_LOCK_malloc | 6419 || wait/io/file/sql/FRM | 452 || wait/synch/mutex/sql/LOCK_plugin | 337 || wait/synch/mutex/mysys/THR_LOCK_open | 187 || wait/synch/mutex/mysys/LOCK_alarm | 147 || wait/synch/mutex/sql/THD::LOCK_thd_data | 115 || wait/io/file/myisam/kfile | 102 || wait/synch/mutex/sql/LOCK_global_system_variables | 89 || wait/synch/mutex/mysys/THR_LOCK::mutex | 89 || wait/synch/mutex/sql/LOCK_open | 88 |+---------------------------------------------------+------------+mysql> SELECT EVENT_NAME, SUM_TIMER_WAIT FROM performance_schema.events_waits_summary_global_by_event_name ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;+----------------------------------------+----------------+| EVENT_NAME | SUM_TIMER_WAIT |+----------------------------------------+----------------+| wait/io/file/sql/MYSQL_LOG | 1599816582 || wait/synch/mutex/mysys/THR_LOCK_malloc | 1530083250 || wait/io/file/sql/binlog_index | 1385291934 || wait/io/file/sql/FRM | 1292823243 || wait/io/file/myisam/kfile | 411193611 || wait/io/file/myisam/dfile | 322401645 || wait/synch/mutex/mysys/LOCK_alarm | 145126935 || wait/io/file/sql/casetest | 104324715 || wait/synch/mutex/sql/LOCK_plugin | 86027823 || wait/io/file/sql/pid | 72591750 |+----------------------------------------+----------------+ These results show that the THR_LOCK_malloc mutex is “hot,” both in terms of how often it is used and amount of time that threads wait attempting to acquire it.Note The THR_LOCK_malloc mutex is used only in debug builds. In production builds it is not hot because it is nonexistent. Instance tables document what