title: Hibernate performance issue while inserting massive data
body: <p>We will migrate large amounts of data (a single type of entity) from Amazon's DynamoDB into a MySQL DB. We are using Hibernate to map this class into a mysql entity. There are around <strong>3 million entities (excluding rows of list property)</strong>. Here is our class mapping summary:</p>
  
  <pre><code>@Entity
  @Table(name = "CUSTOMER")
  public class Customer {
      @Id
      @Column(name = "id")
      private String id;
  
      //Other properties in which all of them are primitive types/String
  
      @ElementCollection
      @CollectionTable(name = "CUSTOMER_USER", joinColumns = @JoinColumn(name = "customer_id"))
      @Column(name = "userId")
      private List&lt;String&gt; users;
  
      // CONSTRUCTORS, GETTERS, SETTERS, etc.
  }
  </code></pre>
  
  <p><strong>users</strong> is a list of String. We have created two mysql tables like following:</p>
  
  <pre><code>CREATE TABLE CUSTOMER(id VARCHAR(100), PRIMARY KEY(id));
  CREATE TABLE CUSTOMER_USER(customer_id VARCHAR(100), userId VARCHAR(100), PRIMARY KEY(customer_id, userId), FOREIGN KEY (customer_id) REFERENCES CUSTOMER(id));
  </code></pre>
  
  <p><strong>Note:</strong> We do not make hibernate generate any id value, we are assigning our IDs to Customer entities which are guaranteed to be unique.</p>
  
  <p>Here is our hibernate.cfg.xml:</p>
  
  <pre><code>&lt;hibernate-configuration&gt;    
      &lt;session-factory&gt;    
      &lt;property name="hibernate.dialect"&gt;   org.hibernate.dialect.MySQLDialect &lt;/property&gt;    
      &lt;property name="hibernate.connection.driver_class"&gt; com.mysql.jdbc.Driver &lt;/property&gt;  
      &lt;property name="hibernate.connection.url"&gt; jdbc:mysql://localhost/xxx &lt;/property&gt;    
      &lt;property name="hibernate.connection.username"&gt; xxx &lt;/property&gt;    
      &lt;property name="hibernate.connection.password"&gt; xxx &lt;/property&gt;
      &lt;property name="hibernate.connection.provider_class"&gt;org.hibernate.c3p0.internal.C3P0ConnectionProvider&lt;/property&gt;
      &lt;property name="hibernate.jdbc.batch_size"&gt; 50 &lt;/property&gt;
      &lt;property name="hibernate.cache.use_second_level_cache"&gt;false&lt;/property&gt;
      &lt;property name="c3p0.min_size"&gt;30&lt;/property&gt;
      &lt;property name="c3p0.max_size"&gt;70&lt;/property&gt;
      &lt;/session-factory&gt; 
  &lt;/hibernate-configuration&gt;
  </code></pre>
  
  <p>We are creating some number of threads each reading data from Dynamo and inserting them to our MySQl DB via Hibernate. Here is what each thread does:</p>
  
  <pre><code>// Each single thread brings resultItems from DynamoDB
  Session session = factory.openSession();
  Transaction tx = session.beginTransaction();
  for(int i = 0; i &lt; resultItems.size(); i++) {
      Customer cust = new Customer(resultItems.get(i));
      session.save(cust);
      if(i % BATCH_SIZE == 0) {
          session.flush();
          session.clear();
      }
  }
  tx.commit();
  session.close();
  </code></pre>
  
  <p>We have our own performance monitoring functions and we are continuously logging the overall read/write performance. The problem is, migration starts with reading/writing 1500 items/sec (on average), but keeps getting slowed as long as number of rows in CUSTOMER and CUSTOMER_USER tables increases (after a few minutes, r/w speed was around 500 items/sec). I am not experienced on Hibernate and here are my questions:</p>
  
  <ol>
  <li>What should hibernate.cfg.xml be like for a multi-threaded task like ours? Is the content which i gave above fits for such a task or is there any wrong/missing point?</li>
  <li>There are exactly 50 threads and each does following: Read from DynamoDB first, and then insert the results into mysql db, then read from dynamo, and so on. Therefore, uptime of communication with hibernate is not 100%. Under these circumstances, what do you recommend to set min_size and max_size of c3p0 connection pool sizes? To be able to understand the concept, should I also set remaining c3p0-related tags in hibernate.cfg.xml?</li>
  <li>What can be done to maximize the speed of bulk inserting?</li>
  </ol>
  
  <p><strong>NOTE 1</strong> I did not write all of the properties, because the remaining ones other than list of users are all int, boolean, String, etc. </p>
  
  <p><strong>NOTE 2</strong> All of points are tested and have no negative effect on performance. When we dont insert anything into mysql db, read speed stays stable for hours.</p>
  
  <p><strong>NOTE 3</strong> Any recommendation/guidance about the structure of mysql tables, configuration settings, sessions/transactions, number of connection pools, batch sizes, etc. will be really helpful!    </p>