I'm inserting 2500 records from Hibernate into a totally empty MySQL table. The insert is taking 5 minutes!
I've googled for hours and tried a few things like an autogenerated primary key but nothing seems to improve the performance.
An earlier version of my program was doing inserts concurrently (1 per thread with ~100 threads) and that was taking ~2 minutes. I thought batching should improve performance by ~10x but it seems to have backfired.
I'm using Google Cloud's MySQL with a db-f1-micro instance
This is what my table looks like (only table in the DB!):
CREATE TABLE `categories` ( `browse_node` varchar(60) NOT NULL, `name` varchar(60) DEFAULT NULL, `path` varchar(400) DEFAULT NULL, `url` varchar(200) NOT NULL, `level` int(11) NOT NULL, PRIMARY KEY (`browse_node`) ) This is the POJO:
package example.com; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Id; import javax.persistence.Table; /** * Represents a category from the categories table */ @Entity @Table(name = "categories") public class Category { @Id @Column(name = "browse_node") private String browseNode; @Column(name = "name") private String name; @Column(name = "path") private String path; @Column(name = "url") private String url; @Column(name = "level") private int level; public Category() { } public Category(String browseNode, String name, String path, String url, int level) { this.browseNode = browseNode; this.name = name; this.path = path; this.url = url; this.level = level; } // Omitting setters/getters } Here's the code doing the insertion:
private static void writeCategoriesToDb(Map<String, Category> categories) { StatelessSession session = sessionFactory.openStatelessSession(); // Session session = sessionFactory.openSession(); session.beginTransaction(); int i = 0; int batchSize = 50; for (Category category : categories.values()) { session.insert(category); // if (i % batchSize == 0) { // session.flush(); // session.clear(); // } // i++; } session.getTransaction().commit(); session.close(); } And here's the config file:
<?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- Database connection settings --> <property name="connection.driver_class">com.mysql.jdbc.Driver</property> <property name="connection.url">jdbc:mysql://someIp/myDB</property> <property name="connection.username">root</property> <property name="connection.password">password</property> <property name="connection.useSSL">false</property> <!-- JDBC connection pool (use the built-in) --> <property name="connection.pool_size">20</property> <property name="hibernate.jdbc.batch_size">3000</property> <property name="hibernate.id.new_generator_mappings">false</property> <property name="dialect">org.hibernate.dialect.MySQLDialect</property> <!-- Echo all executed SQL to stdout --> <property name="show_sql">true</property> <mapping class="example.com.Category"/> </session-factory> </hibernate-configuration> 
