Hibernate + MySQL simple batch insert extremely slow

Question

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>

Answer 1

Found the answer here.

Adding rewriteBatchedStatements=true to my JDBC url fixed it!

It now takes ~2.2 seconds to insert all the records.

<property name="connection.url">jdbc:mysql://someIp/myDB?rewriteBatchedStatements=true</property>