Mysql Inserts really slow

[GitDov]

I'm trying to insert about 40 thousant inserts using mysql but the time is really high.
Using the normal method without pool it execute in 50 seconds.
Using the pool it takes around 5 minutes.

Thats how i'm using the pool:

HikariConfig config = new HikariConfig();
HikariDataSource dataSource;
config.setJdbcUrl("jdbc:mysql://localhost:3306/test");
config.setUsername("root");
config.setPassword("root");
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
config.setMaximumPoolSize(10); // I have tried 10, 50, 100;
dataSource = new HikariDataSource(config); 

Connection con = dataSource.getConnection();
            for (int x = 1; x < 5000; x++) {
                database.Insert(con, "Insert 1", null);
                database.Insert(con, "Insert 2", null);
                database.Insert(con, "Insert 3", null);
                database.Insert(con, "Insert 4", null);
        database.Insert(con, "Insert 5", null);
        database.Insert(con, "Insert 6", null);
        database.Insert(con, "Insert 7", null);
        database.Insert(con, "Insert 8", null);
            }
con.close();

I also tried the code below with the same result:

            for (int x = 1; x < 5000; x++) {
                Connection con = dataSource.getConnection();
                database.Insert(con, "Insert 1", null);
                database.Insert(con, "Insert 2", null);
                database.Insert(con, "Insert 3", null);
                database.Insert(con, "Insert 4", null);
        database.Insert(con, "Insert 5", null);
        database.Insert(con, "Insert 6", null);
        database.Insert(con, "Insert 7", null);
        database.Insert(con, "Insert 8", null);
                con.close();
            }

Whats wrong???

[brettwooldridge]

@GitDov You might try this pattern:

...
config.setAutoCommit(false);
config.addDataSourceProperty("rewriteBatchedStatements", "true");
dataSource = new HikariDataSource(config); 


try (Connection con = dataSource.getConnection()) {
   for (int x = 1; x < 5000; x++) {
      database.Insert(con, "Insert 1", null);
      ...
      database.Insert(con, "Insert 8", null);
      if (x % 500 == 0) {
         con.commit();
      }
   }
}

(The try-with-resources will automatically close the connection)

[ruixiangTan]

nice.

[brettwooldridge]

@ruixiangTan Does that mean the above pattern worked for you?