Impact of MYSQL_BIN_LOG::LOCK_commit Mutex 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.

MYSQL_BIN_LOG::LOCK_commit: This mutex is used when a transaction is ready to be committed to the binary log. The binary log records all changes made to the database in the form of “events”, which can be used for replication and data recovery. When a transaction is ready to be committed, it needs to acquire this mutex to ensure the consistency of the binary log.If many transactions are trying to commit simultaneously, there can be contention for this mutex, leading to wait times. This can degrade performance, particularly in high-concurrency scenarios. Long waits for LOCK_commitcan indicate a disk I/O bottleneck for the binary log, or an issue with the commit speed of the storage engine.
MYSQL_BIN_LOG::LOCK_commit_queue: This mutex controls access to the queue of transactions waiting to be written to the binary log.When the LOCK_commit mutex is released, the next transaction in the LOCK_commit_queue is chosen to commit next. If there is high contention for the LOCK_commit_queue, this can indicate that the speed at which transactions are being processed and added to the binary log is slower than the speed at which transactions are being prepared. This could be due to slow disk I/O, an under-provisioned server, or inefficient queries or 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;

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.