Troubleshooting MySQL Locks and Wait Events Performance – MinervaDB MySQL DBA Tricks and Techniques Series 

Overview

Relational Database Management Systems are built to manage ACID (Atomicity, Consistency, Isolated and Durable) complaint Database Infrastructure for Real-Time Transaction Processing systems. When building MySQL Database Systems Operations for performance, scalability and reliability, I strongly recommend understanding MySQL Locks and Wait for Events. Transaction Isolation Levels in MySQL create a balanced equation between performance, scalability and reliability in an extremely concurrent Database Infrastructure Operation. InnoDB supports all four types of Transaction Isolation Levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. The default isolation level in InnoDB is “REPEATABLE READ“.

Read Uncommitted

“Read Uncommitted” technically don’t have any isolation level, as there are no locks between the transactions. So one transaction will get to see uncommitted changes made by another transaction. This is called a Dirty Read

READ COMMITTED

Dirty Reads don’t happen in the READ COMMITTED isolation level, but non-repeatable reads result in data inconsistency because data modified by another transaction is committed at the same time.

REPEATABLE READ

Repeatable Read (MySQL default isolation level) avoids non-repeatable reads. But, It also causes Phantom Reads, In which a user repeats the read operations on the same set of records with a new set of records in the output

SERIALIZABLE

This is the most durable Isolation Level but will lead to a state where WRITERS will block READERS, and READERS will block WRITERS. So SERIALIZABLE isolation level is not recommended for every transaction.

Monitoring the top 50 CPU-intensive MySQL queries by CPU usage and also how locks influence latency / Response Time

When troubleshooting MySQL query performance, you must also remember to discount the Locks and Wait Events metrics and how they impact performance. While I have several Linux Tools to troubleshoot the load and Wait Events happening on infrastructure, I still heavily depend on the following Performance_Schema objects:

•  performance_schema.events_waits_history
• performance_schema.data_locks
• performance_schema.events_stages_history
• performance_schema.events_transactions_history
• performance_schema.events_statements_history
• performance_schema.events_statements_summary_by_digest
• performance_schema.events_waits_history
• performance_schema.events_statements_summary_by_thread_by_event_name p
• performance_schema.events_statements_current

MySQL DBA Script for monitoring high load / CPU intensive queries, including the details of Locking and the Wait Events:

select substring(e.SQL_TEXT,1,70) as SQL_Text,
       ewh.OBJECT_SCHEMA as Database_Name,
       sys.format_time(ewh.TIMER_START) as Timer_Start,
       sys.format_time(ewh.TIMER_END) as Timer_End,
       ewh.OBJECT_NAME as Table_Name,
       h.INDEX_NAME as Index_Name,
       eth.ACCESS_MODE as Access_Mode,
       dl.LOCK_TYPE as Lock_Type,
       eth.EVENT_NAME as Event_Name,
       eth.ISOLATION_LEVEL as Transaction_Isolation_Level,
       eth.STATE as Current_State,
       eth.SOURCE as Application_Source,
       sys.format_time(eth.TIMER_WAIT) as Event_Elapsed_Time,
       essbd.SUM_CPU_TIME as Total_CPU_Time,
       h.SPINS as Total_Spins_for_Mutex,
       h.OPERATION as MySQL_Operation,
       h.NUMBER_OF_BYTES as Total_Bytes,
       essbtben.SUM_ROWS_EXAMINED Total_Rows_Examined,
       essbtben.SUM_ROWS_SENT Total_Rows_Sent,
       essbtben.SUM_LOCK_TIME as Total_Lock_Time,
       essbtben.MAX_CONTROLLED_MEMORY as Max_Controlled_Memory_used_by_SQL
from performance_schema.events_waits_history ewh
join data_locks dl on ewh.EVENT_ID = dl.EVENT_ID
left join events_stages_history esh on dl.THREAD_ID = esh.THREAD_ID and dl.EVENT_ID = esh.EVENT_ID
left join events_transactions_history eth on esh.THREAD_ID = eth.THREAD_ID
left join events_statements_history e on dl.THREAD_ID = e.THREAD_ID
left join events_waits_history h on dl.THREAD_ID = h.THREAD_ID
left outer join events_statements_summary_by_thread_by_event_name essbtben on esh.THREAD_ID = essbtben.THREAD_ID
left outer join events_statements_summary_by_digest essbd on e.MAX_CONTROLLED_MEMORY = essbd.MAX_CONTROLLED_MEMORY
left outer join events_statements_current esc on dl.THREAD_ID = esc.THREAD_ID
order by Total_CPU_Time desc
limit 50;

Note: The query copied above annotates the following metrics for troubleshooting MySQL Performance (including the locks and wait events): 

• Actual query triggered to MySQL
• Query Duration
• Table / Index Names
• Transaction Isolation Level
• The current state of MySQL Server
• Query Source and Latency
• CPU and Memory Usage
• Total Spins for a Mutex
• Total Transaction Size
• Total Rows Examined
• Total Rows Sent

More detailed Analysis of MySQL Locks Performance with Performance_Schema Scripts

select
    esh.SQL_TEXT as SQL_Program,
    sys.format_time(ewh.TIMER_START) as Start_Time,
    sys.format_time(ewh.TIMER_END) as End_Time,
    eth.ISOLATION_LEVEL as Transaction_Isolation_Level,
    ewh.SPINS,
    esh.LOCK_TIME,
    eth.ACCESS_MODE,
    esh.CPU_TIME,
    eth.STATE as Current_State,
    esh.ROWS_EXAMINED,
    esh.ROWS_SENT,
    esh.ROWS_AFFECTED,
    ewh.OBJECT_SCHEMA,
    ewh.OBJECT_NAME,
    ewh.INDEX_NAME,
    ewh.NUMBER_OF_BYTES Total_Bytes,
    esh.MAX_TOTAL_MEMORY,
    esh2.EVENT_NAME,
    sys.format_time(esh.TIMER_WAIT) as Wait_Time
from
    performance_schema.events_statements_history esh,
    performance_schema.events_waits_history ewh ,
    performance_schema.events_transactions_history eth,
    performance_schema.events_stages_history esh2
where
    esh.THREAD_ID=ewh.THREAD_ID
and
    ewh.THREAD_ID=eth.THREAD_ID
and
    eth.THREAD_ID=esh2.THREAD_ID;

Conclusion

Locks affect MySQL performance by creating process operations bottlenecks, i.e., A process waiting for another process to complete its transaction and release its locks. Eventually the overall latency and throughput is impacted. A deadlock is a serious issue to troubleshoot as it causes one transaction to be aborted, and the transaction must be restarted by the application. A frequent deadlock deeply impact throughput of MySQL query operations.