Monitoring Wait Events in MySQL 8 Using Performance Schema: A Practical SQL Script Approach

MySQL Wait Events Monitoring

MySQL Wait Events Monitoring uses the Performance Schema to track wait events, helping you identify which resources are causing delays. This insight allows you to detect potential performance bottlenecks and optimise your database for better efficiency.

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:

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.

MySQL Wait Events Monitoring requires an understanding of MySQL internals, and analyzing specific events may help identify and resolve performance issues effectively. By continuously tracking wait events, you can detect bottlenecks early and optimize queries or system resources. This proactive approach ensures better database performance and stability.