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
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
- Hibernate mapped collections performance problems
- DataNucleus 3.0 vs Hibernate 3.5
- Revving Up Your Hibernate Engine
- GWT 2 Spring 3 JPA 2 Hibernate 3.5 Tutorial
- JBoss 4.2.x Spring 3 JPA Hibernate Tutorial
Java Platform, Enterprise Edition is a widely used platform for enterprise server programming in the Java programming language.
This book covers exciting recipes on securing, tuning and extending enterprise applications using a Java EE 6 implementation.The book starts with the essential changes in Java EE 6. Then they will dive into the implementation of some of the new features of the JPA 2.0 specification, and look at implementing auditing for relational data stores.They will then look into how they can enable security for their software system using Java EE built-in features as well as using the well-known Spring Security framework. They will then look at recipes on testing various Java EE technologies including JPA, EJB, JSF, and Web services.Next they will explore various ways to extend a Java EE environment with the use of additional dynamic languages as well as frameworks.At the end of the book, they will cover managing enterprise application deployment and configuration, and recipes that will help you debug problems and enhance the performance of your applications.