It took me 3 days to get the batch inserts working with a proper performance and I want to share my experience here so others don't have to go through all of this. Finding the right documentation online has been really hard, especially if you don't know what to search for.
Here is what I have done in addition to what is said in chapter 13 of the hibernate docu:
In addition to hibernate.jdbc.batch_size, you should also set hibernate.order_inserts to true if you are inserting more than one Entity class.
The hibernate docu says:
Quote:
Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.
What this actually is trying to say is that if you simply use
Code:
@Id @GeneratedValue
private int id;
it is not going to work. You have to use a generator that supports it. I found that the @TableGenerator works for MySQL. Example:
Code:
@TableGenerator(name = "EntityIdGen", table = "TABLE_GENERATOR", pkColumnValue = "Entity")
@Id
@GeneratedValue(strategy = GenerationType.TABLE, generator = "EntityIdGen")
private int id;
I think the hibernate docu should be updated/clarified because it's really hard to understand those things from the given text.
However, if you enable the MySQL logging you are still not seeing any "Insert into Entity (...) values (...) (...) (...) but only single inserts.
I guess this is because MySQL does not support the standard JDBC batch inserts (??). However, they have a parameter that makes their JDBC driver to transfrom several small inserts into single big one. It's called rewriteBatchedStatements and you can easily set it in your connection url, example:
Code:
<connection-url>jdbc:mysql:///databasename?rewriteBatchedStatements=true</connection-url>
One last thing I noticed, and I couldn't find out what is causing it, is that if you're transaction is getting really big, the commit takes forever. Profiling did not show that it is hibernate causing it, but the com.arjuna.ats ... transaction classes used by JBoss. But it also may be that MySQL is taking forever to do the commit.
To give you an example in times, if i inserted data for 30 seconds, then commiting took like 90 seconds.
I improved my overall to 45 seconds by having a lot of small transactions.
Again, I don't know if it is Hibernate, JBoss or MySQL that is responsible for that, or if this may be gone in future versions.
Hope this saves you some time. Good luck!
Login
FAQ
Search