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: 450
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