Why does "commit" appear in the mysql slow query log?
- by Tom
In our MySQL slow query logs I often see lines that just say "COMMIT". What causes a commit to take time?
Another way to ask this question is: "How can I reproduce getting a slow commit; statement with some test queries?"
From my investigation so far I have found that if there is a slow query within a transaction, then it is the slow query that gets output into the slow log, not the commit itself.
Testing
In mysql command line client:
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql UPDATE members SET myfield=benchmark(9999999, md5('This is to slow down the update')) WHERE id = 21560;
Query OK, 0 rows affected (2.32 sec)
Rows matched: 1 Changed: 0 Warnings: 0
At this point (before the commit) the UPDATE is already in the slow log.
mysql commit;
Query OK, 0 rows affected (0.01 sec)
The commit happens fast, it never appeared in the slow log.
I also tried a UPDATE which changes a large amount of data but again it was the UPDATE that was slow not the COMMIT. However, I can reproduce a slow ROLLBACK that takes 46s and gets output to the slow log:
mysql begin;
Query OK, 0 rows affected (0.00 sec)
mysql UPDATE members SET myfield=CONCAT(myfield,'TEST');
Query OK, 481446 rows affected (53.31 sec)
Rows matched: 481446 Changed: 481446 Warnings: 0
mysql rollback;
Query OK, 0 rows affected (46.09 sec)
I understand why rollback has a lot of work to do and therefore takes some time. But I'm still struggling to understand the COMMIT situation - i.e. why it might take a while.
