Hibernate Batch Processing with MySQL

ゝ一世哀愁。 2021-12-07 09:51 235阅读 0赞

Batch processing is a common solution to improve database performance by inserting or updating massive data in bulk. However, there are some hiccups you might run into. Let's walk through an example to discover the issues. Suppose we have a simple table named Account with column  *name*and  *age*. Followings are the SQL script (MySQL), Hibernate bean, and batch insert codes.  
  
**Account.sql**

[?][Link 1]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>CREATE</code> 
      <code>TABLE</code> 
      <code>`Account` (</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`id` </code> 
      <code>int</code> 
      <code>(20) </code> 
      <code>NOT</code> 
      <code>NULL</code> 
      <code>auto_increment,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`</code> 
      <code>name</code> 
      <code>` </code> 
      <code>varchar</code> 
      <code>(20) </code> 
      <code>NOT</code> 
      <code>NULL</code> 
      <code>,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`age` </code> 
      <code>int</code> 
      <code>(11) </code> 
      <code>NOT</code> 
      <code>NULL</code> 
      <code>,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>PRIMARY</code> 
      <code>KEY</code>&nbsp; 
      <code>(`id`)</code> 
     </div> 
     <div> 
      <code>) ENGINE=InnoDB </code> 
      <code>DEFAULT</code> 
      <code>CHARSET=latin1;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**Account.java**

[?][Link 1]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> 
    <div>
      15 
    </div> 
    <div>
      16 
    </div> 
    <div>
      17 
    </div> 
    <div>
      18 
    </div> 
    <div>
      19 
    </div> 
    <div>
      20 
    </div> 
    <div>
      21 
    </div> 
    <div>
      22 
    </div> 
    <div>
      23 
    </div> 
    <div>
      24 
    </div> 
    <div>
      25 
    </div> 
    <div>
      26 
    </div> 
    <div>
      27 
    </div> 
    <div>
      28 
    </div> 
    <div>
      29 
    </div> 
    <div>
      30 
    </div> 
    <div>
      31 
    </div> 
    <div>
      32 
    </div> 
    <div>
      33 
    </div> 
    <div>
      34 
    </div> 
    <div>
      35 
    </div> 
    <div>
      36 
    </div> 
    <div>
      37 
    </div> 
    <div>
      38 
    </div> 
    <div>
      39 
    </div> 
    <div>
      40 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>@Entity</code> 
     </div> 
     <div> 
      <code>@Table</code> 
      <code>(name=</code> 
      <code>"Account"</code> 
      <code>)</code> 
     </div> 
     <div> 
      <code>public</code> 
      <code>class</code> 
      <code>Account </code> 
      <code>implements</code> 
      <code>Serializable { </code> 
     </div> 
     <div> 
      <code>&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>private</code> 
      <code>static</code> 
      <code>final</code> 
      <code>long</code> 
      <code>serialVersionUID = 6954737637154168665L;</code> 
     </div> 
     <div> 
      <code>&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>private</code> 
      <code>long</code> 
      <code>id;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>private</code> 
      <code>String name;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>private</code> 
      <code>int</code> 
      <code>age;</code> 
     </div> 
     <div> 
      <code>&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>@Id</code> 
      <code>@GeneratedValue</code> 
      <code>(strategy=GenerationType.IDENTITY)</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>public</code> 
      <code>long</code> 
      <code>getId() { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>return</code> 
      <code>id;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>public</code> 
      <code>void</code> 
      <code>setId(</code> 
      <code>long</code> 
      <code>id) { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>this</code> 
      <code>.id = id;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>@Basic</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>@Column</code> 
      <code>(name=</code> 
      <code>"name"</code> 
      <code>, nullable=</code> 
      <code>false</code> 
      <code>, length=</code> 
      <code>20</code> 
      <code>)</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>public</code> 
      <code>String getName() { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>return</code> 
      <code>name;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>public</code> 
      <code>void</code> 
      <code>setName(String name) { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>this</code> 
      <code>.name = name;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
     <div> 
      <code>&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>@Basic</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>@Column</code> 
      <code>(name=</code> 
      <code>"age"</code> 
      <code>, nullable=</code> 
      <code>false</code> 
      <code>)</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>public</code> 
      <code>int</code> 
      <code>getAge() { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>return</code> 
      <code>age;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>public</code> 
      <code>void</code> 
      <code>setAge(</code> 
      <code>int</code> 
      <code>age) { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>this</code> 
      <code>.age = age;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
     <div> 
      <code>&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>}</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

**Test.java**

[?][Link 1]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>Transaction tx = session.beginTransaction();</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code>&nbsp; 
     </div> 
     <div> 
      <code>for</code> 
      <code>(</code> 
      <code>int</code> 
      <code>i=</code> 
      <code>0</code> 
      <code>; i &lt; accounts.size(); i++) { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>Account account = accounts.get(i);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>session.save(account);</code> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>if</code> 
      <code>(i % </code> 
      <code>50</code> 
      <code>== </code> 
      <code>0</code> 
      <code>) { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>session.flush();</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>session.clear();</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
     <div> 
      <code>}</code> 
     </div> 
     <div> 
      <code>tx.commit();</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

First thing to notice is you will need to flush and clear the data every N (50 here) records as Hibernate caches newly inserted records in session-level cache. Otherwise, you will see **OutOfMemoryException** somewhere if you have a large amount of records.  
  
Secondly, it's essential to configure  **hibernate.jdbc.batch\_size** to achieve optimal performance. The value should be the same with N. You could play with different values to find the best one for your application. In addition, the second-level cache needs to be disabled either in the configuration or through  *CacheMode* programmingly.  
  
But wait... Why didn't I see performance improvement??  
  
The problem is Hibernate explicitly says:

> *Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator.*

As a result, we have to use alternative identity generator. I am going to show you how to use **MultipleHiLoPerTableGenerator** due to efficiency of the HiLo algorithm for identity generation. Here are the updated annotation in Account.java as well as the new table to store the sequence's next hi value.

[?][Link 1]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>@GenericGenerator</code> 
      <code>(name=</code> 
      <code>"AccountIdGenerator"</code> 
      <code>, strategy=</code> 
      <code>"org.hibernate.id.MultipleHiLoPerTableGenerator"</code> 
      <code>,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>parameters = { </code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>@Parameter</code> 
      <code>(name=</code> 
      <code>"table"</code> 
      <code>, value=</code> 
      <code>"IdentityGenerator"</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>@Parameter</code> 
      <code>(name=</code> 
      <code>"primary_key_column"</code> 
      <code>, value=</code> 
      <code>"sequence_name"</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>@Parameter</code> 
      <code>(name=</code> 
      <code>"primary_key_value"</code> 
      <code>, value=</code> 
      <code>"Account"</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>@Parameter</code> 
      <code>(name=</code> 
      <code>"value_column"</code> 
      <code>, value=</code> 
      <code>"next_hi_value"</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>@Parameter</code> 
      <code>(name=</code> 
      <code>"primary_key_length"</code> 
      <code>, value=</code> 
      <code>"100"</code> 
      <code>),</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>@Parameter</code> 
      <code>(name=</code> 
      <code>"max_lo"</code> 
      <code>, value=</code> 
      <code>"1000"</code> 
      <code>)</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>}</code> 
     </div> 
     <div> 
      <code>)</code> 
     </div> 
     <div> 
      <code>@Id</code> 
      <code>@GeneratedValue</code> 
      <code>(strategy=GenerationType.TABLE, generator=</code> 
      <code>"AccountIdGenerator"</code> 
      <code>)</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

[?][Link 1]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>CREATE</code> 
      <code>TABLE</code> 
      <code>`IdentityGenerator` (</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`id` </code> 
      <code>int</code> 
      <code>(11) </code> 
      <code>NOT</code> 
      <code>NULL</code> 
      <code>auto_increment,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`sequence_name` </code> 
      <code>varchar</code> 
      <code>(100) </code> 
      <code>NOT</code> 
      <code>NULL</code> 
      <code>,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>`next_hi_value` </code> 
      <code>int</code> 
      <code>(11) </code> 
      <code>NOT</code> 
      <code>NULL</code> 
      <code>,</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>PRIMARY</code> 
      <code>KEY</code>&nbsp; 
      <code>(`id`)</code> 
     </div> 
     <div> 
      <code>) ENGINE= InnoDB </code> 
      <code>DEFAULT</code> 
      <code>CHARSET=latin1;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

Yes.. I know.. The performance still sucks.   
  
The reason is because MySQL does not support batch form of prepare statement parameter bindings. Fortunately, we could turn on  **rewriteBatchedStatements** so that it will rewrite them to the form as "INSERT INTO Account VALUES (...), (...), (...)".

[?][Link 1]

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <code>&lt;property name=</code> 
      <code>"hibernate.connection.url"</code> 
      <code>&gt;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>jdbc:mysql:</code> 
      <code>//127.0.0.1/sheng?rewriteBatchedStatements=true</code> 
     </div> 
     <div> 
      <code>&lt;/property&gt;</code> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

Congratulations, you are all set! You will see great performance gain with the same test set you run.

Posted by [Sheng Chien ][Sheng Chien]at [4:42 PM][Link 1]

#### 6 comments: ####

1.  ![openid36-rounded.png][]
    
    [developmentality][] [June 2, 2011 at 2:46 PM][June 2_ 2011 at 2_46 PM]
    
    Excellent summary of the problems involved with batch inserts, Hibernate, and MySQL.
    
    Reply
    
     
    
     
2.  ![b36-rounded.png][]
    
    [Eco-nemesis][] [October 19, 2012 at 5:30 AM][October 19_ 2012 at 5_30 AM]
    
    With JPA 2.0/Hibernate 2.1.7 the above suggestions make things worse rather than better. One insert with GenerationType.IDENTITY takes 41ms on my Mysql 5.4. Switching on logging I only see one batch query.  
      
    I tried to improve with the Identity Generator suggestions above, but the time doubled instead to 83ms for 1 insert.  
      
    When I switched on sql statement logging I noticed that instead of 1 query, hibernate was doing 3, two extra queries before my actual insert:  
      
    Hibernate: select tbl.next\_val from hibernate\_sequences tbl where tbl.sequence\_name=? for update  
      
    Hibernate: update hibernate\_sequences set next\_val=? where next\_val=? and sequence\_name=?  
      
    
    
    Reply
    
    Replies
    
     
    
     
3.  ![b36-rounded.png][]
    
    [Eco-nemesis][] [October 19, 2012 at 5:31 AM][October 19_ 2012 at 5_31 AM]
    
    Correction, Hibernate 4.1.7
    
     
    
     
4.  ![b36-rounded.png][]
    
    [Andrei Amariei][] [January 16, 2013 at 11:54 PM][January 16_ 2013 at 11_54 PM]
    
    This is an optimization when batch inserting. For example, when you insert 1000 records, you will have same 2 extra queries, and not extra 1000 selects of inserted ids, if i\`m not mistaken.
    
     
    
     
5.   
    
    Reply
    
     
6.  ![photo.jpg][]
    
    [Sudarshan Sreenivasan][] [March 3, 2014 at 10:43 AM][March 3_ 2014 at 10_43 AM]
    
    I am using Hibernate 4.1.9.Final and it throws a BatchedTooManyRowsAffectedException exception.  
      
    The org.hibernate.jdbc.Expectations.BasicExpectation.verifyOutcome(int rowCount, PreparedStatement statement, int batchPosition) method throws this exception
    
    Reply
    
    Replies
    
     
    
     
7.  ![photo.jpg 1][]
    
    [Jonathan Kolb][] [March 14, 2014 at 6:48 AM][March 14_ 2014 at 6_48 AM]
    
    I saw this error when using mysql-connector-java 5.1.29. When I changed down to version 5.1.26 the method above works.   
      
    I think it has to do with the bugs fixed in https://dev.mysql.com/doc/relnotes/connector-j/en/news-5-1-27.html.   
      
    Specifically: 'When rewriteBatchedStatements=true and useAffectedRows=true were set, executeBatch() did not return the number of affected rows as expected. (Bug \#16442951, Bug \#68562)'
    
     
    
     
8.   
    
    Reply
    
     
    
    from : http://shengchien.blogspot.com/2010/10/hibernate-batch-processing-with-mysql.html (Blocked by the GFW)

转载于:https://www.cnblogs.com/hb\_cattle/articles/3838734.html

[Link 1]: http://shengchien.blogspot.com/2010/10/hibernate-batch-processing-with-mysql.html
[Sheng Chien]: https://plus.google.com/100704209746873512591
[openid36-rounded.png]: /images/20211207/6e2b89d977ac444ab2438c02ea45ce66.png
[developmentality]: http://developmentality.wordpress.com/
[June 2_ 2011 at 2_46 PM]: http://shengchien.blogspot.com/2010/10/hibernate-batch-processing-with-mysql.html?showComment=1307051214570#c82256209147813510
[b36-rounded.png]: /images/20211207/f5a685e4bf1843f2984753dbe881e090.png
[Eco-nemesis]: http://www.blogger.com/profile/13349800134202594827
[October 19_ 2012 at 5_30 AM]: http://shengchien.blogspot.com/2010/10/hibernate-batch-processing-with-mysql.html?showComment=1350649844316#c2974290685473390931
[October 19_ 2012 at 5_31 AM]: http://shengchien.blogspot.com/2010/10/hibernate-batch-processing-with-mysql.html?showComment=1350649882596#c3220316873568342497
[Andrei Amariei]: http://www.blogger.com/profile/10766080231897845702
[January 16_ 2013 at 11_54 PM]: http://shengchien.blogspot.com/2010/10/hibernate-batch-processing-with-mysql.html?showComment=1358409264862#c8732554824348017995
[photo.jpg]: http://lh3.googleusercontent.com/-Elm7jhk9C0g/AAAAAAAAAAI/AAAAAAAAAow/1XQ9gFXXWHI/s512-c/photo.jpg
[Sudarshan Sreenivasan]: http://www.blogger.com/profile/01437129180441953264
[March 3_ 2014 at 10_43 AM]: http://shengchien.blogspot.com/2010/10/hibernate-batch-processing-with-mysql.html?showComment=1393872208415#c5137123970210019011
[photo.jpg 1]: http://lh6.googleusercontent.com/-lDUjp3QJun0/AAAAAAAAAAI/AAAAAAAAABk/OAxOuY2oZYM/s512-c/photo.jpg
[Jonathan Kolb]: http://www.blogger.com/profile/04863861087985632652
[March 14_ 2014 at 6_48 AM]: http://shengchien.blogspot.com/2010/10/hibernate-batch-processing-with-mysql.html?showComment=1394804909797#c3652024732537434898