Troubleshooting MySQL 8 locks and wait events, How they influence MySQL performance?

Locks and wait events can significantly impact MySQL performance as they can cause queries to wait, leading to slower response times and decreased performance. The main cause of lock and wait events is contention for resources, such as table or row locks, or waits for I/O operations.

To troubleshoot lock and wait events, it is important to understand the source of the problem and the queries that are waiting. The MySQL Performance Schema provides several tables and views that can be used to monitor lock and wait events, such as:

1. sys.innodb_lock_waits: Shows information about transactions that are waiting for a lock.
2. sys.schema_table_lock_waits: Provides information about table locks that are causing waits.
3. sys.events_waits_summary_global_by_event_name: Shows a summary of wait events, including the average wait time and the number of occurrences.
4. sys.events_waits_current: Provides information about the current wait events that are happening on the server.

To mitigate lock and wait events, you can make use of the following techniques:

1. Use the correct isolation level for your transactions to minimize lock contention.
2. Use indexes to improve query performance and reduce lock contention.
3. Use LIMIT clauses and FOR UPDATE clauses sparingly to reduce lock contention.
4. Use the innodb_lock_wait_timeout configuration setting to avoid long wait times.
5. Monitor lock and wait events regularly to identify performance bottlenecks and to take proactive measures to mitigate them.

By monitoring lock and wait events and taking the appropriate measures, you can improve the performance of your MySQL server and ensure that your applications are running smoothly.

Monitoring lock waits and I/O activity happening on MySQL

The purpose of this query is to provide insights into the performance of the MySQL and identify any issues that may be impacting its performance:

select
ilw.blocking_trx_id as Blocking_Transaction_ID,
ilw.blocking_query as Blocking_Query,
FORMAT(stlw.waiting_lock_duration, 2) as Waiting_lock_duration,
FORMAT(stlw.waiting_lock_duration / 1000000000, 2) AS Waiting_lock_duration_sec,
DATE_FORMAT(ilw.wait_started, ‘%Y-%m-%d’) AS Wait_started,
tiwsbt.SUM_TIMER_INSERT,
ROUND(tiwsbt.SUM_TIMER_WAIT / tiwsbt.COUNT_STAR, 2) AS avg_wait_time,
FORMAT(tiwsbt.SUM_TIMER_WAIT / 1000000000, 2) AS sum_timer_wait_sec,
tiwsbt.COUNT_READ as Total_Reads,
tiwsbt.COUNT_WRITE as Total_WRITEs,
tiwsbt.COUNT_DELETE as Total_DELETEs,
tiwsbt.COUNT_INSERT as Total_INSERTs,
tiwsbt.COUNT_UPDATE as Total_UPDATEs,
stlw.blocking_lock_duration,
FORMAT(stlw.blocking_lock_duration / 1000000000, 2) AS blocking_lock_duration_sec,
CASE
WHEN stlw.blocking_lock_duration < 1000000000 THEN ‘Less than 1 second’
WHEN stlw.blocking_lock_duration BETWEEN 1000000000 AND 2000000000 THEN ‘1-2 seconds’
ELSE ‘More than 2 seconds’
END AS lock_wait_duration_range,
stlw.waiting_lock_type,
stlw.waiting_query
from sys.innodb_lock_waits ilw
left join sys.schema_table_lock_waits stlw on ilw.blocking_pid = stlw.blocking_pid
left join table_io_waits_summary_by_table tiwsbt on stlw.object_schema = tiwsbt.OBJECT_SCHEMA and stlw.object_name = tiwsbt.OBJECT_NAME
left outer join sys.innodb_lock_waits i on stlw.blocking_pid = i.blocking_pid
left join sys.schema_table_lock_waits s on ilw.waiting_pid = s.waiting_pid;

This SQL code provides information about lock waits and wait events that may be affecting the performance of the MySQL database. The code performs a left join on multiple system schema tables, including innodb_lock_waits, schema_table_lock_waits, and table_io_waits_summary_by_table, to gather data about the blocking transactions, queries, lock durations, and wait times, as well as the type of lock and the query that is waiting.

The resulting output provides several key pieces of information:

1. Blocking_Transaction_ID: The ID of the transaction that is blocking other transactions from accessing a particular resource.
2. Blocking_Query: The query being executed by the blocking transaction.
3. Waiting_lock_duration: The duration of time that the waiting transaction has been waiting for the lock to be released.
4. Waiting_lock_duration_sec: The waiting lock duration in seconds.
5. Wait_started: The date when the waiting transaction started waiting for the lock.
6. SUM_TIMER_INSERT: The total time spent on INSERT operations for the table.
7. avg_wait_time: The average wait time for the table.
8. sum_timer_wait_sec: The total wait time for the table in seconds.
9. Total_Reads: The total number of READ operations performed on the table.
10. Total_WRITEs: The total number of WRITE operations performed on the table.
11. Total_DELETEs: The total number of DELETE operations performed on the table.
12. Total_INSERTs: The total number of INSERT operations performed on the table.
13. Total_UPDATEs: The total number of UPDATE operations performed on the table.
14. blocking_lock_duration: The duration of time that the blocking transaction has been holding the lock.
15. blocking_lock_duration_sec: The blocking lock duration in seconds.
16. lock_wait_duration_range: A categorical variable indicating whether the lock wait duration is less than 1 second, between 1 and 2 seconds, or more than 2 seconds.
17. waiting_lock_type: The type of lock that the waiting transaction is waiting for.
18. waiting_query: The query being executed by the waiting transaction.