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:

Related Whitepaper:

Functional Programming in Java: Harnessing the Power of Java 8 Lambda Expressions

Get ready to program in a whole new way!

Functional Programming in Java will help you quickly get on top of the new, essential Java 8 language features and the functional style that will change and improve your code. This short, targeted book will help you make the paradigm shift from the old imperative way to a less error-prone, more elegant, and concise coding style that’s also a breeze to parallelize. You’ll explore the syntax and semantics of lambda expressions, method and constructor references, and functional interfaces. You’ll design and write applications better using the new standards in Java 8 and the JDK.

Get it Now!  

Leave a Reply


− seven = 2



Java Code Geeks and all content copyright © 2010-2014, Exelixis Media Ltd | Terms of Use | Privacy Policy
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.

Sign up for our Newsletter

20,709 insiders are already enjoying weekly updates and complimentary whitepapers! Join them now to gain exclusive access to the latest news in the Java world, as well as insights about Android, Scala, Groovy and other related technologies.

As an extra bonus, by joining you will get our brand new e-books, published by Java Code Geeks and their JCG partners for your reading pleasure! Enter your info and stay on top of things,

  • Fresh trends
  • Cases and examples
  • Research and insights
  • Two complimentary e-books