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
– M. Deinum, Commented Sep 4, 2018 at 8: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
– Rudimentary Joe, Commented Sep 4, 2018 at 8:20

M. Deinum · Accepted Answer · 2018-09-04 08:32:36Z

2

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.

answered Sep 4, 2018 at 8:32

M. Deinum

127k22 gold badges235 silver badges253 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spring Boot MySQL not batching inserts

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related