java
Java-Monitor Forum > Java Application Server Administration > Solving Programmatic Problems » Hibernate sending autocommit commands forces MySQL to do excessive disk I/O
Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 17-04-2010, 14:58
kjkoster kjkoster is offline
Forum Operator
 
Join Date: Jul 2008
Posts: 1,117
Default Hibernate sending autocommit commands forces MySQL to do 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:
Code:
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.
Code:
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.

Kees Jan
Reply With Quote
  #2  
Old 17-04-2010, 16:28
Kees de Kooter Kees de Kooter is offline
Moderator
 
Join Date: Jul 2008
Location: Rotterdam
Posts: 197
Default

Did you try the relaxAutoCommit=true jdbc parameter?

Or setting autocommit to false explicity on the Connection.

See this thread for more details.
__________________
Kees
moderator Tomcat Administration forum

Last edited by Kees de Kooter; 17-04-2010 at 16:41.
Reply With Quote
  #3  
Old 17-04-2010, 19:16
kjkoster kjkoster is offline
Forum Operator
 
Join Date: Jul 2008
Posts: 1,117
Default

Dear Kees,

Thanks for the suggestion, but that does not change Hibernate's behaviour of sending autcommit statements every single time. I tested with relaxAutoCommit=true in the JDB connection string and hibernate.connection.autocommit=false in the Hibernate properties. Still, the MySQL general log contains the following idiom for each query:

Code:
		  116 Query	SET autocommit=0
		  116 Query	 query snipped....
		  116 Query	 query snipped....
		  116 Query	 query snipped....
		  116 Query	commit
		  116 Query	SET autocommit=1
Quoting from the MySQL Connector/J reference:

Quote:
relaxAutoCommit: If the version of MySQL the driver connects to does not support transactions, still allow calls to commit(), rollback() and setAutoCommit() (true/false, defaults to 'false')?
That suggests that this option allows you to use JDBC against older MySQL server versions, rather than stop Hibernate from sending autocommit statements.

Kees Jan
Reply With Quote
  #4  
Old 17-04-2010, 19:53
kjkoster kjkoster is offline
Forum Operator
 
Join Date: Jul 2008
Posts: 1,117
Default

Dear All,

After reading a little more I found elideSetAutoCommits as a potential help.

Quote:
elideSetAutoCommits: If using MySQL-4.1 or newer, should the driver only issue 'set autocommit=n' queries when the server's state doesn't match the requested state by Connection.setAutoCommit(boolean)?
Unfortunately, this does not change anything for my situation. The problem is that Hibernate wants to use autocommit=0. The elideSetAutoCommits setting only suppresses no-op autocommit sets.

Kees Jan
Reply With Quote
  #5  
Old 17-04-2010, 20:12
kjkoster kjkoster is offline
Forum Operator
 
Join Date: Jul 2008
Posts: 1,117
Default

Dear All,

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. Together with the aforementioned elideSetAutoCommits that 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.

Sooo, 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.

Kees Jan
Reply With Quote
  #6  
Old 16-05-2010, 15:56
poi212003 poi212003 is offline
Junior Member
 
Join Date: May 2010
Posts: 3
Default

Hi kjkoster,

Did you figure out how to disable the autocommit hibernate issues on every select?

Here is my problem:

I have a master/slave DB config and to send the the read quries to the slave i need to use the @Transactional(readonly=true) but then mysql output shows:
SET autocommit=0
select ....
SET autocommit=1
on everu query(even though its a readonly query) which cause impacts the performance.

Is it possible to mark the query as readonly without the @Transactional?
Thanks.
Reply With Quote
  #7  
Old 16-05-2010, 18:27
kjkoster kjkoster is offline
Forum Operator
 
Join Date: Jul 2008
Posts: 1,117
Default

Dear poi212003,

No, I have not found a solution. The problem is that this is essentially the way transactions work in Java and MySQL does not deal gracefully at all.

Sun's JDBC tutorials teach us that toggling autocommit mode is the way to do transactions. JDBC does not even have a "begin transaction" method on the connection interface. No wonder all the frameworks do it this way.

Frankly, the only way around this is to use straight JDBC and don't touch the autocommit setting at all. I am in the process of removing all JDBC-assisting frameworks and just using plain JDBC.

If you think that is too harsh, and you would like to keep using @Transactional, review your code carefully to only use it when you really want a transaction. In all other cases, simply run the query as is.

Does that help?

Kees Jan
Reply With Quote
  #8  
Old 16-05-2010, 18:57
poi212003 poi212003 is offline
Junior Member
 
Join Date: May 2010
Posts: 3
Default

Quote:
Originally Posted by kjkoster View Post
Dear poi212003,

No, I have not found a solution. The problem is that this is essentially the way transactions work in Java and MySQL does not deal gracefully at all.

Sun's JDBC tutorials teach us that toggling autocommit mode is the way to do transactions. JDBC does not even have a "begin transaction" method on the connection interface. No wonder all the frameworks do it this way.

Frankly, the only way around this is to use straight JDBC and don't touch the autocommit setting at all. I am in the process of removing all JDBC-assisting frameworks and just using plain JDBC.

If you think that is too harsh, and you would like to keep using @Transactional, review your code carefully to only use it when you really want a transaction. In all other cases, simply run the query as is.

Does that help?

Kees Jan
I dont agree with going back to straight JDBC - its just too much work.

Im thinking maybe just to implement another datasource -
one will be Master and the other one is SLAVE
the problem with it is that i will need to take care of synchromizing both after a write operation (clearing the slave objects after i added new data using the Master datasource)

What do you think? is this solution common?

BTW - if i use hibernateTemplate directly without using @Transactional i dont have the problem of the autocommit toggle
the problem is when i dont use @Transactional - i dont have a way of setting the connection as readonly so it will go to the slave.

Last edited by poi212003; 16-05-2010 at 19:06.
Reply With Quote
  #9  
Old 16-05-2010, 19:36
kjkoster kjkoster is offline
Forum Operator
 
Join Date: Jul 2008
Posts: 1,117
Default

Dear poi212003,

Right, you find going back to JDBC to be too much work. While I can see your point, I actually found going back to JDBC good. My queries are a lot better and performant. It only took me a day or so. My data model is relatively straightforward. You application may differ.

I now have two data sources to do the read/write splitting. I ended up going this route because I want to control what query goes where. Some read queries go to the write data source in order to avoid issues when there are replication delays of more than a few seconds.

I have seen setups that have two data sources like I do, but they use a load balancer to send the read queries to the master and to two slaves. But this is a very read-heavy application.

Before you manually do read/write splitting, consider that for simple cases there are JDBC drivers that can actually do this for you. The first hit on Google is http://ha-jdbc.sourceforge.net/ although I have no experience with that driver.

Kees Jan
Reply With Quote
  #10  
Old 16-05-2010, 19:54
kjkoster kjkoster is offline
Forum Operator
 
Join Date: Jul 2008
Posts: 1,117
Default

Dear poi212003,

Oh, and I resolved your caching issue by having both the read and write data sources and a cache in each DAO. Thus, when there is a write query for a particular entity I can choose to either invalidate the cache or even update the cache (although that opens me up to a whole can of concurrency worms).

I don't like to cache very far back in the application. I don't use Hibernate's 2nd level cache for example. The reason is that in the layers of the application closer to the user, I have a lot more information about how and how long I can cache data. Caches closer to the database tend to be rather ineffective. Sure, they may help, but not nearly as much as what you can achieve with manual caching.

Thus, right now I rely on MySQL's query cache for what others may use Hibernate's second-level cache for, plus caching in the user's session object. Using MySQL's cache has the benefit that I can do queries on the database and see the effect in the application. It is kind'a stupid to have to restart applications to see the effects of your nightly cleanup scripts.

Kees Jan

PS. Truth be told, I am not doing that today. There is no caching for now, other than MySQL's query cache. I'll add that later, once I find that I need it. Caching means more memory and longer GC cycles. When I add it, that will be the model I use, though.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump