I am trying to do batch inserts into mysql at very high rates. I wanted to try the rewriteBatchedStatements config option as I have read it can make significantly affect performance. When I add the option however I get the following exception:
java.lang.NullPointerException
at com.mysql.jdbc.PreparedStatement.computeMaxParameterSetSizeAndBatchSize(PreparedStatement.java:1694)
at com.mysql.jdbc.PreparedStatement.computeBatchSize(PreparedStatement.java:1651)
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1515)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1272)
at com.zaxxer.hikari.proxy.StatementProxy.executeBatch(StatementProxy.java:116)
at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeBatch(PreparedStatementJavassistProxy.java)
This is my code that does the inserts:
try (Connection connection = DBUtil.getInstance().getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(query)) {
connection.setAutoCommit(false);
for (TransactionBatch batch : batches) {
try {
preparedStatement.setString(1, batch.getDeviceID());
preparedStatement.setBinaryStream(2, new ByteArrayInputStream(dataArray));
preparedStatement.addBatch();
} catch (Exception e) {
e.printStackTrace();
}
}
preparedStatement.executeBatch();
} catch (Exception e) {
e.printStackTrace();
}
This is my jdbc url:
jdbc:mysql://url:port/tableName?user=userame&password=password&useServerPrepStmts=false&rewriteBatchedStatements=true
Also I am using HikariCP as my connection pool.
EDIT: Update - looks like the problem relates to having a varbinary(10000) column in the table
The solution was to stop using:
instead I used
In order to rewrite it must need to calculate the total size which it can't do upfront from an input stream. It is working great now and my write speeds are awesome!