Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 27.2Mb
PDF (A4) - 27.2Mb
PDF (RPM) - 26.2Mb
HTML Download (TGZ) - 6.6Mb
HTML Download (Zip) - 6.6Mb
HTML Download (RPM) - 5.6Mb
Man Pages (TGZ) - 170.5Kb
Man Pages (Zip) - 278.9Kb
Info (Gzip) - 2.6Mb
Info (Zip) - 2.6Mb
Excerpts from this Manual

MySQL 5.5 Reference Manual  /  ...  /  Monitoring InnoDB Mutex Waits Using Performance Schema

14.19.1 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 view InnoDB mutex wait instruments, how to verify that associated consumers are enabled, and how to query wait event data. It is assumed that Performance Schema was enabled at server startup. For information about enabling Performance Schema, see Section 22.1, “Performance Schema Quick Start”.

  1. To view available InnoDB mutex wait instruments, query the Performance Schema setup_instruments table, as shown below. Instruments are enabled 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             | YES     | YES   |
    | wait/synch/mutex/innodb/innobase_share_mutex          | YES     | YES   |
    | wait/synch/mutex/innodb/prepare_commit_mutex          | YES     | YES   |
    | wait/synch/mutex/innodb/autoinc_mutex                 | YES     | YES   |
    | wait/synch/mutex/innodb/btr_search_enabled_mutex      | YES     | YES   |
    | wait/synch/mutex/innodb/buf_pool_mutex                | YES     | YES   |
    | wait/synch/mutex/innodb/buf_pool_zip_mutex            | YES     | YES   |
    | wait/synch/mutex/innodb/cache_last_read_mutex         | YES     | YES   |
    | wait/synch/mutex/innodb/dict_foreign_err_mutex        | YES     | YES   |
    | wait/synch/mutex/innodb/dict_sys_mutex                | YES     | YES   |
    | wait/synch/mutex/innodb/file_format_max_mutex         | YES     | YES   |
    | wait/synch/mutex/innodb/fil_system_mutex              | YES     | YES   |
    | wait/synch/mutex/innodb/flush_list_mutex              | YES     | YES   |
    | wait/synch/mutex/innodb/log_flush_order_mutex         | YES     | YES   |
    | wait/synch/mutex/innodb/hash_table_mutex              | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
    | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
    | wait/synch/mutex/innodb/kernel_mutex                  | YES     | YES   |
    | wait/synch/mutex/innodb/log_sys_mutex                 | YES     | YES   |
    | wait/synch/mutex/innodb/mem_pool_mutex                | YES     | YES   |
    | wait/synch/mutex/innodb/mutex_list_mutex              | YES     | YES   |
    | wait/synch/mutex/innodb/purge_sys_bh_mutex            | YES     | YES   |
    | wait/synch/mutex/innodb/recv_sys_mutex                | YES     | YES   |
    | wait/synch/mutex/innodb/rseg_mutex                    | YES     | YES   |
    | wait/synch/mutex/innodb/rw_lock_list_mutex            | YES     | YES   |
    | wait/synch/mutex/innodb/rw_lock_mutex                 | YES     | YES   |
    | wait/synch/mutex/innodb/srv_dict_tmpfile_mutex        | YES     | YES   |
    | wait/synch/mutex/innodb/srv_innodb_monitor_mutex      | YES     | YES   |
    | wait/synch/mutex/innodb/srv_misc_tmpfile_mutex        | YES     | YES   |
    | wait/synch/mutex/innodb/srv_monitor_file_mutex        | YES     | YES   |
    | wait/synch/mutex/innodb/syn_arr_mutex                 | YES     | YES   |
    | wait/synch/mutex/innodb/trx_doublewrite_mutex         | YES     | YES   |
    | wait/synch/mutex/innodb/trx_undo_mutex                | YES     | YES   |
    +-------------------------------------------------------+---------+-------+
    34 rows in set (0.00 sec)
  2. Verify that wait event consumers are enabled by querying the setup_consumers table. The events_waits_current, events_waits_history, and events_waits_history_long consumers should be enabled by default.

    mysql> SELECT * FROM performance_schema.setup_consumers;
    +----------------------------------------------+---------+
    | NAME                                         | ENABLED |
    +----------------------------------------------+---------+
    | events_waits_current                         | YES     |
    | events_waits_history                         | YES     |
    | events_waits_history_long                    | YES     |
    | events_waits_summary_by_thread_by_event_name | YES     |
    | events_waits_summary_by_event_name           | YES     |
    | events_waits_summary_by_instance             | YES     |
    | file_summary_by_event_name                   | YES     |
    | file_summary_by_instance                     | YES     |
    +----------------------------------------------+---------+
    8 rows in set (0.00 sec)
  3. Run the workload that you want to monitor. In this example, the mysqlslap load emulation client is used to simulate a workload.

    shell> ./mysqlslap --auto-generate-sql --concurrency=100 --iterations=10
        -> --number-of-queries=1000 --number-char-cols=6 --number-int-cols=6;
  4. Query the wait event data. In this example, wait event data is queried from the events_waits_summary_global_by_event_name table which aggregates data found in the events_waits_current, events_waits_history, and events_waits_history_long tables. Data is summarized by event name (EVENT_NAME), which is the name of the instrument that produced the event. Summarized data includes:

    • COUNT_STAR

      The number of summarized wait events.

    • SUM_TIMER_WAIT

      The total wait time of the summarized timed wait events.

    • MIN_TIMER_WAIT

      The minimum wait time of the summarized timed wait events.

    • AVG_TIMER_WAIT

      The average wait time of the summarized timed wait events.

    • MAX_TIMER_WAIT

      The maximum wait time of the summarized timed wait events.

    The following query returns the instrument name (EVENT_NAME), the number of wait events (COUNT_STAR), and the total wait time for the events for that instrument (SUM_TIMER_WAIT). Because waits are timed in picoseconds (trillionths of a second) by default, wait times are divided by 1000000000 to show wait times in milliseconds. Data 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/buf_pool_mutex                |     154477 |         6258.6407 |
    | wait/synch/mutex/innodb/kernel_mutex                  |      54294 |         1747.1980 |
    | wait/synch/mutex/innodb/log_sys_mutex                 |      40578 |         3167.6126 |
    | wait/synch/mutex/innodb/dict_sys_mutex                |      34261 |           26.4183 |
    | wait/synch/mutex/innodb/log_flush_order_mutex         |      24463 |            0.5867 |
    | wait/synch/mutex/innodb/rseg_mutex                    |      18204 |            0.4750 |
    | wait/synch/mutex/innodb/flush_list_mutex              |      15949 |            0.7182 |
    | wait/synch/mutex/innodb/mutex_list_mutex              |      10439 |            0.2299 |
    | wait/synch/mutex/innodb/fil_system_mutex              |       9815 |            0.5027 |
    | wait/synch/mutex/innodb/rw_lock_list_mutex            |       8292 |            0.1763 |
    | wait/synch/mutex/innodb/trx_undo_mutex                |       6070 |            0.2339 |
    | wait/synch/mutex/innodb/innobase_share_mutex          |       1994 |            0.0761 |
    | wait/synch/mutex/innodb/file_format_max_mutex         |       1007 |            0.0245 |
    | wait/synch/mutex/innodb/trx_doublewrite_mutex         |        387 |            0.0214 |
    | wait/synch/mutex/innodb/recv_sys_mutex                |        186 |            0.0047 |
    | wait/synch/mutex/innodb/ibuf_mutex                    |        121 |            0.0030 |
    | wait/synch/mutex/innodb/purge_sys_bh_mutex            |         99 |            0.0033 |
    | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex |         40 |            0.0011 |
    | wait/synch/mutex/innodb/srv_innodb_monitor_mutex      |          3 |            0.0003 |
    +-------------------------------------------------------+------------+-------------------+
    19 rows in set (0.00 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;

User Comments
Sign Up Login You must be logged in to post a comment.