What does "waiting for handler commit" mean in mysql processlist?

Question

When restoring db from a snapshot I frequently see processes that can take 8+ minutes to complete in the state "waiting for handler commit". I read somewhere that this is when events are being written to the binary log but I have binary log disabled with disable_log_bin.

I'm interested in trying to improve performance here but I first need to understand what is happening. Since the processes don't point to any specific query I am a bit in the dark. How can I find out what is actually happening when I see "waiting for handler commit"?

Here is an example of what I see when I run show processlist:

+----+------------------+-----------+------+---------+------+----------------------------+------------------+
| Id | User             | Host      | db   | Command | Time | State                      | Info             |
+----+------------------+-----------+------+---------+------+----------------------------+------------------+
|  5 | event_scheduler  | localhost | NULL | Daemon  | 1272 | Waiting on empty queue     | NULL             |
|  9 | debian-sys-maint | localhost | NULL | Sleep   |   39 |                            | NULL             |
| 14 | root             | localhost | NULL | Sleep   | 1030 |                            | NULL             |
| 16 | root             | localhost | dev  | Query   |   53 | waiting for handler commit | COMMIT           |
| 17 | root             | localhost | dev  | Sleep   | 1030 |                            | NULL             |
| 18 | root             | localhost | dev  | Sleep   | 1030 |                            | NULL             |
| 19 | root             | localhost | dev  | Sleep   | 1030 |                            | NULL             |
| 20 | root             | localhost | dev  | Sleep   | 1030 |                            | NULL             |
| 23 | root             | localhost | dev  | Query   |  511 | waiting for handler commit | COMMIT           |
| 25 | root             | localhost | dev  | Query   |  467 | waiting for handler commit | COMMIT           |
| 26 | root             | localhost | dev  | Query   |  520 | waiting for handler commit | COMMIT           |
| 27 | root             | localhost | dev  | Query   |  520 | waiting for handler commit | COMMIT           |
| 98 | root             | localhost | NULL | Query   |    0 | init                       | SHOW PROCESSLIST |
+----+------------------+-----------+------+---------+------+----------------------------+------------------+

Answer 1

It seems to be a redo log contention, innodb_log_file_size should be tuned to hold around 1 hour worth of write traffic at peak load.

Try to increase it to 1 or 2 GB.

Also you can set innodb_flush_log_at_trx_commit=0, but please, look to the documentation to know if it makes sense and is safe to do in your case.

Answer 2

The "waiting for handler commit" state in MySQL’s process list generally indicates that a transaction is waiting for a commit operation to be processed by the storage engine. Here’s a breakdown of what this means and some steps to address it:

Understanding "Waiting for Handler Commit" Definition:

When a transaction is in the "waiting for handler commit" state, it means that the SQL thread is waiting for the storage engine (e.g., InnoDB) to complete the commit operation. This often occurs during transactions that involve data modifications. Possible Causes:

Locks: Long-running transactions or locks held by other transactions can cause delays. If another transaction is holding a lock that needs to be released, the commit can be delayed. Storage Engine: Some storage engines might handle commits differently or might have performance issues causing delays. Disk I/O: Performance issues with disk I/O or storage subsystem can slow down commit operations, especially during large transactions. Steps to Diagnose and Improve Performance Check for Locks:

Run SHOW ENGINE INNODB STATUS to check for any locks or transactions that might be holding up the commit. Examine Transaction Duration:

Ensure that transactions are not running longer than necessary. Long transactions can lead to performance issues. Monitor Disk I/O:

Check the performance of your disk subsystem. Slow disk I/O can impact commit performance. Tools like iostat or vmstat can help diagnose disk performance. Review MySQL Configuration:

Ensure that your MySQL configuration is optimized for your workload. Parameters like innodb_flush_log_at_trx_commit can influence how commits are handled. For example, setting it to 1 ensures that commits are fully synchronized to disk at each commit. Check for Binary Log Issues:

Although you have binary logging disabled, make sure there are no issues related to binary logs or replication setup that might affect commit operations. Upgrade MySQL Version:

Ensure you are using a stable and recent version of MySQL. Sometimes performance issues are addressed in newer versions of MySQL. Example Query To get more details on the transactions and locks, you might use:

SHOW FULL PROCESSLIST;
SHOW ENGINE INNODB STATUS;

These commands will provide more insights into what might be causing the delay.

Conclusion The "waiting for handler commit" state can be influenced by various factors such as locks, storage engine performance, and disk I/O. By diagnosing these areas and making necessary adjustments, you can improve the performance of transactions and reduce the wait time for commits.

Feel free to update this answer with additional details specific to your setup or findings from further investigation.