Understanding the Impact of MYSQL_BIN_LOG::LOCK_commit Mutex on MySQL Performance

The wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit  and wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue mutexes in MySQL can have significant implications on database performance.

1. MYSQL_BIN_LOG::LOCK_commit:

   MySQL uses this mutex when a transaction is ready to commit to the binary log. The binary log records all database changes as “events,” which support replication and data recovery. When a transaction reaches the commit stage, it must acquire this mutex to maintain binary log consistency.

   If multiple transactions try to commit at the same time, they compete for this mutex, causing wait times. This contention can degrade performance, especially in high-concurrency environments. Long waits for LOCK_commit often indicate a disk I/O bottleneck in the binary log or slow commit processing by the storage engine.

2. MYSQL_BIN_LOG::LOCK_commit_queue:

   This mutex manages access to the queue of transactions waiting for binary log commits. After MySQL releases the LOCK_commit mutex, it selects the next transaction from the LOCK_commit_queue for processing.

   If LOCK_commit_queue experiences high contention, transactions are being added to the queue faster than they are written to the binary log. This issue may result from slow disk I/O, an under-provisioned server, or inefficient queries and transactions.

SELECT
    event_name AS 'Event',
    COUNT_STAR AS 'Total',
    SUM_TIMER_WAIT/1000000000000 AS 'Total Time Waited, in sec',
    AVG_TIMER_WAIT/1000000000000 AS 'Average Time Waited, in sec'
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit%'
ORDER BY SUM_TIMER_WAIT DESC;

In summary, high contention for these mutexes can be indicative of performance issues in MySQL, including I/O bottlenecks and inefficiencies in transaction processing. Monitoring these mutexes can therefore provide valuable insights into MySQL performance and help identify potential areas for optimization.