Unexpected Result of Querying the Performance Schema Lock Wait Event

Symptom

The result of querying the Performance Schema (PFS) lock wait events is not as expected. Some expected wait events are missing, or some wait events that are not expected are generated. What are the causes and solutions?

Key Process and Cause Analysis

The performance_schema.events_waits_history_long table contains N latest wait events that have ended globally in all threads. N indicates the size of the event_waits_history_long table and can be set through the performance_schema_events_waits_history_long_size variable. The default value of the variable is 10000, and the maximum value is 1048576.

When the number of records in the table exceeds the configured value of this variable, the table is full. If a wait event to be recorded is generated, the earliest row record is discarded and the latest wait event row record is saved.

Conclusion and Solution

If some expected wait events are missing in the returned result from the query, you can set the size of the events_waits_history_long table to a larger value by adding the following line to the configuration file to obtain the expected wait events:

performance_schema_events_waits_history_long_size=1048576

If some wait events that are not expected to be generated are still returned, the possible cause is that the events_waits_history_long table contains wait event records of historical SQL statements. Run the following command to clear the wait event records generated by historical SQL statements, run the SQL statement again, and query wait events:

TRUNCATE table performance_schema.events_waits_history_long;

Other performance event records in PFS are similar to the situation of lock wait events. You can solve similar problems by configuring size parameters of the related tables and using the TRUNCATE table method.