Spring Boot MySQL not batching inserts

Question

I've applied pretty much all solutions posted here on SO regarding this issue but nothing worked.

My application.properties:

spring.jpa.hibernate.ddl-auto=create-drop
spring.datasource.url=jdbc:mysql://localhost:3306/mydb?logger=com.mysql.jdbc.log.Slf4JLogger&rewriteBatchedStatements=true&profileSQL=true&autoReconnect=true&useSSL=false
spring.datasource.username=user
spring.datasource.password=secret
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

spring.jpa.hibernate.jdbc.batch_size = 100
spring.jpa.hibernate.order_inserts   = true 
spring.jpa.hibernate.order_updates   = true

logging.level.root=info
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %highlight(%-5p) %gray(%c{0}::%M) - %m%n

My EntityRepository:

@Repository
public interface EntityRepository extends CrudRepository<Entity, Long> { }

My Entity:

@Data @Entity
public class Entity {

  @Id
  @GeneratedValue(generator = "generator")
  @GenericGenerator(name = "generator", strategy = "increment")
  private Long id;

  private Long attr;

}

And the simplified code that calls repository:

int batchSize = 100;
List<Entity> batchEntities = new ArrayList<>();
for(Entity entity : entities) {
  batchEntities.add(entity);
  if(batchEntities.size() >= batchSize) {
    entityRepository.saveAll(batchEntities);
    batchEntities.clear();
  }
}

What I've done:

As per this SO question I've enabled the profileSQL=true option and the log produces several individual inserts. Also, I've enabled global logging on SQL server and it too produces sequence of individual inserts.

As per this another SO question and yet another SO question I've made sure that batch_size is set in the application.properties file and although I have no parent-child relationship I've also tried to use the order_inserts and order_updates. Also, I've enabled the rewriteBatchedStatements=true option and use the saveAll(...) method.

I've also tried to ditch the premade CrudRepository and my a custom one, by flushing after every batchSize-th persist.

Nothing above helped.

`spring.jpa.hibernate.order_updates` won't do anything (the same applies to the other properties). Instead use `spring.jpa.properties.hibernate.order_updates` (same applies to the other custom hibernate properties as well). — M. Deinum, Sep 04 '18 at 08:19
@M.Deinum Thank you! It works now perfectly. Can't believe I've missed that. Would you like to rewrite your comment into an answer so I can accept it? — Rudimentary Joe, Sep 04 '18 at 08:20

score 2 · Accepted Answer · answered Sep 04 '18 at 08:32

The following properties don't exists in Spring Boot.

spring.jpa.hibernate.jdbc.batch_size = 100
spring.jpa.hibernate.order_inserts   = true 
spring.jpa.hibernate.order_updates   = true

To add custom properties use the spring.jpa.properties prefix instead.

spring.jpa.properties.hibernate.jdbc.batch_size = 100
spring.jpa.properties.hibernate.order_inserts   = true 
spring.jpa.properties.hibernate.order_updates   = true

Should do the trick.

See also the Spring Boot documentation on how to configure JPA.

Spring Boot MySQL not batching inserts

1 Answers1