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
Related Articles:
- 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
