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.
Isolation Level Dirty Read Non-Repeatable Read Phantom read.
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Not Possible Possible Possible
REPEATABLE READ Not Possible Not Possible Possible
SERIALIZABLE Not Possible Not Possible Not Possible
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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.
