Hibernate autocommit commands force MySQL in excessive disk I/O

Dear All,

I am sure many of you use Hibernate and MySQL, I use it here and there myself. Generally the programming model is nice, but it is no secret that plain JDBC can be a lot faster. In this post I would like to draw your attention to a small problem that Hibernate causes in your MySQL server.

If you trace the SQL that Hibernate sends to the MySQL database, you see that consistently, Hibernate starts each transaction with “SET autocommit=0″ and ends it with “commit”, followed by “SET autocommit=1″. These statements may seem harmless, but they cause MySQL to flush some internal state out to disk. To put simply, each time Hibernate calls one of these two statements, MySQL will write stuff that normally it might not write. Thus, using Hibernate causes your MySQL server to lean on the disks a lot more than using plain JDBC would.

I did a little experiment to demonstrate this. To repeat this experiment, find an idle machine with a MySQL database. The machine should not be running anything that causes disk I/O, or the effect won’t be as easy to see as it was for me.

First I sent a whole slew of “SELECT 1 FROM DUAL;” commands into a MySQL prompt. Like so:

while true; do
        echo "SELECT 1 FROM DUAL;"
done | mysql

Looking at the output of iostat(8) showed that there was no I/O on the machine. top(1) did show that the machine was working. From this, we can see that these SELECT statements do not cause disk I/O.

Next I added Hibernate’s autocommit commands, as shown below.

while true; do
        echo "SET AUTOCOMMIT=0;"
        echo "SELECT 1 FROM DUAL;"
        echo "COMMIT;"
        echo "SET AUTOCOMMIT=1;"
done | mysql

This time, iostat(8) does show that there is disk I/O. The MySQL server is working a lot harder than it was before, while still serving precisely the same answers. This is only from a single thread. Your application will probably issue these statements concurrently on a number of threads and connections, aggravating the problem.

For queries that cause I/O anyway I imagine that this overhead may be negligible. For small read queries this means that you are suddenly doing disk I/O on the database server.

I have not been able to find a way to explain to Hibernate that I don’t want to have it send the autocommit statements to the database. You can switch off autocommitting in Hibernate, but that only switches off Hibernate’s internal autocommit. It does not stop sending these commands to the database.

Reading the Hibernate source code (in particular the sources for org.hibernate.transaction.JDBCTransaction) shows that what hibernate does it force the autocommit to be false on the connection before each transaction and reset it afterwards. This is hardcoded. Hibernate *wants* autocommit to be off.

If my MySQL server would only serve Hibernate-enabled applications, I might consider switching the default autocommit mode to off for my database server. Additionally I could use elideSetAutoCommits flag which might reduce the volume of autocommit toggles. However, that seriously breaks POLA. Plus, my server serves more than just Hibernate-enabled applications, so changing defaults like this will certainly break something elsewhere.

So, that leaves me stuck. I cannot tell Hibernate to not issue “SET autocommit”‘s, the JDBC driver won’t suppress them and I cannot tell MySQL to ignore them.

Reference: Hibernate sending autocommit commands forces MySQL to do excessive disk I/O from our JCG partner Kees Jan at the Java Monitor Forum

Happy coding
Byron

Related Articles:

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

JPA Mini Book

Learn how to leverage the power of JPA in order to create robust and flexible Java applications. With this Mini Book, you will get introduced to JPA and smoothly transition to more advanced concepts.

JVM Troubleshooting Guide

The Java virtual machine is really the foundation of any Java EE platform. Learn how to master it with this advanced guide!

Given email address is already subscribed, thank you!
Oops. Something went wrong. Please try again later.
Please provide a valid email address.
Thank you, your sign-up request was successful! Please check your e-mail inbox.
Please complete the CAPTCHA.
Please fill in the required fields.

Leave a Reply


five − 2 =



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy | Contact
All trademarks and registered trademarks appearing on Java Code Geeks are the property of their respective owners.
Java is a trademark or registered trademark of Oracle Corporation in the United States and other countries.
Java Code Geeks is not connected to Oracle Corporation and is not sponsored by Oracle Corporation.
Do you want to know how to develop your skillset and become a ...
Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you two of our best selling eBooks for FREE!

Get ready to Rock!
You can download the complementary eBooks using the links below:
Close