I use mysql with hibernate and spring boot data jpa (spring-boot-starter-data-jpa and mysql-connector-java). Recently I upgraded my spring boot project from 1.5 to 2.0. The API of the spring data CrudRepository to save an iterable has changed form save() to saveAll(). I made the changes in the code and it works but it is very slow:

insert 10 items -> 2 times slower (49ms -> 95ms)

insert 100 items -> 6 times slower (132ms -> 840ms)

insert 1000 items -> 10 times slower (792ms -> 8028ms)

insert 10000 items -> 15 times slower (4912ms -> 73542ms)

insert 100000 items -> 22 times slower (32042ms -> 712702ms)

I have tested the inserts of both spring versions with an empty table. The mysql server version hasn't change: 5.7.21 - MySQL Community Server (GPL)

I need to insert ~2M items daily so this slowdown is dramatically. This is my configuration:

spring.datasource.url = jdbc:mysql://localhost:3306/service?useSSL=false&rewriteBatchedStatements=true

spring.datasource.username = service

spring.datasource.password = service

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.hikari.maximum-pool-size=5

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

spring.jpa.properties.hibernate.jdbc.batch_size=50

spring.jpa.properties.hibernate.order_inserts=true

spring.jpa.properties.hibernate.order_updates=true

spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true

Does anybody know what changed with the update and how to speed it up again?

解决方案

in application.properties set spring.jpa.properties.hibernate.generate_statistics = true

hibernate:

generate_statistics: true