Home > Admin > The strange commit behavior and the invisible Xid_log_event

The strange commit behavior and the invisible Xid_log_event

Did you see this when you are migrating from your lovely MySQL 5.1 to MySQL 5.5?
Oh, sorry, you remain attached to your pretty 4.1. Yes, I know, MyISAM has become so important in your life…

Ok, seriously, I would like to share this little observation I made recently when switching to MySQL 5.5 on one slave.
You can see below two graphs for the transactional activity, there is exactly the same volume of update, delete and insert queries :

MySQL 5.1

Screenshot 2013-04-15 at 06.34.23 PM

MySQL 5.5Screenshot 2013-04-15 at 06.32.42 PM

But the gray area represents the number of commit per second.
I find that I have much more commit with MySQL 5.5, why?
The first question I asked to myself was whether MySQL was telling the truth…

And I had three options to know the truth :

  • The com_commit counter : I compared the number of begin and commit during ten minutes
    • MySQL 5.1 : I got much more begin than commit
    • MySQL 5.5 : I got exactly the same result between begin and commit
  • The relay logs : In a sample of logs, I compared the number of begin and commit 
    • MySQL 5.1 : I got exactly the same result between begin and commit
    • MySQL 5.5 : I got exactly the same result between begin and commit
  • The genaral log : I compared the number of begin and commit during ten minutes
    • MySQL 5.1 : I got exactly the same result between begin and commit
    • MySQL 5.5 : I got exactly the same result between begin and commit

Hum, ok, the com_commit counter want to play with me!

Let’s go in the general log, I had two kinds of commit output :

Query COMMIT
Query COMMIT /* implicit, from Xid_log_event */

So, I called a friend : Hey Google, what is this (f…) Xid_log_event?

http://ftp.nchu.edu.tw/MySQL/sources/doxygen/mysql-5.1/classXid__log__event.html 
http://bugs.mysql.com/bug.php?id=54605

Hum, it smells replication…

Yes, you guessed it, the commit statements from the replication stream are not recorded in the com_commit counter in 5.1.
But the begin statements are recorded in the com_begin counter…

Look at your own slave servers and compare!

 

Congratulations, you've read this post till the end !
If you enjoyed this article, please leave a comment


You can also subscribe to RSS feed or by email to automatically receive real-time notification of new posts

Thanks for your visit on MySQL+. Cédric

Categories: Admin
  1. No comments yet.
  1. No trackbacks yet.