
The Performance Schema in MySQL also provides a way to monitor wait events, which can help you understand what resources are being waited upon, thereby identifying potential performance bottlenecks.
Wait events include things like I/O waits, lock waits, and other types of waits.
The events_waits_summary_global_by_event_name table in the Performance Schema contains information about all wait events that have occurred.
Here’s a simple script to monitor these wait events:
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT EVENT_NAME, COUNT_STAR AS `Total Waits`, SUM_TIMER_WAIT/1000000000000 AS `Total Wait Time in Sec`, MAX_TIMER_WAIT/1000000000000 AS `Max Wait Time in Sec`, AVG_TIMER_WAIT/1000000000000 AS `Avg Wait Time in Sec` FROM performance_schema.events_waits_summary_global_by_event_name WHERE COUNT_STAR > 0 ORDER BY `Total Wait Time in Sec` DESC; |
This script will return a list of events sorted by the total wait time, displaying the following information for each event:
- Total number of waits
- Total wait time
- Maximum wait time
- Average wait time (all in seconds)
Just as with memory consumption, make sure the Performance Schema is enabled for your MySQL instance before running the script.
It’s important to note that the interpretation of these wait events requires some understanding of MySQL internals and may require deeper investigation into specific events to identify and resolve performance issues.