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

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:

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.

About Shiv Iyer 497 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.