Troubleshooting MySQL Locks and Wait Events Performance – MinervaDB DBA Series

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 ReadPhantom read.
READ UNCOMMITTEDPossiblePossible Possible
READ COMMITTEDNot PossiblePossiblePossible
REPEATABLE READNot PossibleNot PossiblePossible
SERIALIZABLENot Possible Not PossibleNot 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:

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

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.

About Shiv Iyer 72 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.