Batch insert statements are not being rewritten

Hibernate ORM
1

Hello, I’m currently working on enhancing write performance by changing to to batch inserts, but looking at the DB logs it does not appear to be working. I am using hibernate version 5.4.25 on MySQL 5.7 with the standard batching loop from hibernate docs here: Hibernate ORM 5.4.30.Final User Guide. To set the JDBC batch size, I am using the call to sesssion.setJdbcBatchSize seen here: SharedSessionContract (Hibernate JavaDocs)

I’ve enabled logging for statistics as well as BatchingBatch and this was the output:

[ BatchingBatch ] - Executing batch size: 3
[ StatisticalLoggingSessionEventListener ] - Session Metrics {
    4587 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    237535 nanoseconds spent preparing 1 JDBC statements;
    0 nanoseconds spent executing 0 JDBC statements;
    7376834 nanoseconds spent executing 1 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    42524632 nanoseconds spent executing 1 flushes (flushing a total of 3 entities and 6 collections);
    0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
}

However, when going to the general logs for MySQL, I can still see 3 individual insert statements rather than a single multi-valued insert. It is worth noting that the identifier generation is done in the app not in MySQL. I am not understanding why the rewrite is not occurring, so any help would be appreciated.

2

JDBC batching is implemented by the JDBC driver in different ways. Usually, the same prepared statement handle is executed with the passed values. The batching effect comes from sending multiple values at once to the database, but in the end, the database still executes the same statement for every set of values that you want to insert, there is just fewer round-trips. I don’t know MySQL, but to me, it would sound totally normal if the database logged the statement for every value set that is inserted, as that is logically what is happening.

What kind of performance would you like to achieve? There are many components between Hibernate and the database that could be an issue. Maybe the network is slow. Maybe the driver is misconfigured. Or maybe you have to many indexes on the target table or simply slow disks.