Why does "commit" appear in the mysql slow query log?

Posted by Tom on Server Fault See other posts from Server Fault or by Tom
Published on 2013-10-31T20:02:30Z Indexed on 2013/10/31 21:58 UTC
Read the original article Hit count: 453

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.

© Server Fault or respective owner

Related posts about mysql

Related posts about profiling