15.15.2 Monitoring InnoDB Mutex Waits Using Performance Schema

To view available InnoDB mutex wait instruments, query the Performance Schema setup_instruments table. All InnoDB mutex wait instruments are disabled by default.

Some InnoDB mutex instances are created at server startup and are only instrumented if the associated instrument is also enabled at server startup. To ensure that all InnoDB mutex instances are instrumented and enabled, add the following performance-schema-instrument rule to your MySQL configuration file:

performance-schema-instrument='wait/synch/mutex/innodb/%=ON'

If you do not require wait event data for all InnoDB mutexes, you can disable specific instruments by adding additional performance-schema-instrument rules to your MySQL configuration file. For example, to disable InnoDB mutex wait event instruments related to full-text search, add the following rule:

performance-schema-instrument='wait/synch/mutex/innodb/fts%=OFF'

After adding the performance-schema-instrument rules to your configuration file, restart the server. All the InnoDB mutexes except for those related to full text search are enabled. To verify, query the setup_instruments table. The ENABLED and TIMED columns should be set to YES for the instruments that you enabled.

Enable wait event consumers by updating the setup_consumers table. Wait event consumers are disabled by default.

You can 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.

Once instruments and consumers are enabled, 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;

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:

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.