Пакетные вставки Java Spring Boot show-sql, показывающие отдельные вставки
Я использую Spring Boot и пытаюсь массово вставлять ингредиенты, загружая файл Excel во внешнее приложение. API Spring Boot получает список ингредиентов, но очень медленно вставляет его. Обновления идут быстро, а вставки идут очень медленно.
Что мне здесь не хватает??
Установив show-sql=true, я вижу, что все вставки выполняются отдельно
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient (category_id, is_active, name, unit, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_property (is_active, name, unit, value, id) values (?, ?, ?, ?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Hibernate: insert into ingredient_properties (ingredient_id, properties_id) values (?, ?)
Статистика вставки:
КАЖДЫЙ ИНГРЕДИЕНТ ИМЕЕТ 28 свойств
15800 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
1316900 nanoseconds spent preparing 13 JDBC statements;
144703300 nanoseconds spent executing 10 JDBC statements;
9137380500 nanoseconds spent executing 110 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
9164292200 nanoseconds spent executing 1 flushes (flushing a total of 280 entities and 10 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
Класс ингредиентов:
@Entity
@Getter
@Setter
@RequiredArgsConstructor
@ToString
@Where(clause = "is_active=1")
public class Ingredient {
@Id
private String id;
@PrePersist
public void prePersist() {
if (this.id == null) {
this.id = UUID.randomUUID().toString();
}
}
@ManyToOne
private IngredientCategory category;
@OneToMany(mappedBy = "ingredient")
@JsonIgnore
private Set<IngredientAmount> ingredientAmounts;
@Column(length = 200)
@Size(max = 200)
private String unit;
@Column(length = 1000)
@Size(max = 1000)
private String name;
private boolean isActive;
@OneToMany(cascade = CascadeType.ALL, orphanRemoval = true)
private Set<IngredientProperty> properties;
}
Класс IngredientProperty
@Entity
@Getter
@Setter
@RequiredArgsConstructor
@ToString
@Where(clause = "is_active=1")
public class IngredientProperty {
@Id
private String id;
@PrePersist
public void prePersist() {
if (this.id == null) {
this.id = UUID.randomUUID().toString();
}
}
private String name;
private String value;
private String unit;
private boolean isActive;
}
ИнгредиентКонтроллер
@PostMapping("/bulk")
public ResponseEntity createOrUpdateInBulk(@RequestBody Set<Ingredient> ingredients) throws InterruptedException {
return new ResponseEntity(this.ingredientService.updateAndCreateBulkIngredients(ingredients), HttpStatus.OK);
}
ИнгредиентСервис
public boolean updateAndCreateBulkIngredients(Set<Ingredient> toUpdateIngredients) {
Set<Ingredient> toAddOrUpdateIngredients = new HashSet<>();
for (int i = 0; i < toUpdateIngredients.size(); i++) {
Ingredient ingredient = new ArrayList<>(toUpdateIngredients).get(i);
Ingredient existing = this.ingredientRepository.getIngredientById(ingredient.getId());
if (existing != null) {
existing.getProperties().clear();
existing.getProperties().addAll(ingredient.getProperties());
existing.setUnit(ingredient.getUnit());
toAddOrUpdateIngredients.add(existing);
} else {
toAddOrUpdateIngredients.add(ingredient);
}
if (i % batchSize == 0 && i > 0) {
this.ingredientRepository.saveAll(toAddOrUpdateIngredients);
toAddOrUpdateIngredients.clear();
}
}
if (toAddOrUpdateIngredients.size() > 0) {
this.ingredientRepository.saveAll(toAddOrUpdateIngredients);
toAddOrUpdateIngredients.clear();
}
return true;
}
Application.properties содержит следующее:
spring.datasource.driverClassName= com.mysql.cj.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.properties.hibernate.jdbc.batch_size=5
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.batch_versioned_data=true
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.generate_statistics=true
ОБНОВЛЯТЬ
Добавление следующего в application.properties значительно увеличило производительность. Я все еще вижу, что вставки выполняются отдельно, но вставка 10 ингредиентов с каждыми 28 IngredientProperty теперь занимает 800 мс вместо 8,5 с !!!
Добавлено следующее:
spring.datasource.url=${DATASOURCE_URL:jdbc:mysql://PATH:PORT?cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true}
?cachePrepStmts=true&useServerPrepStmts=true&rewriteBatchedStatements=true
Это также изменило количество партий:
15900 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
171364400 nanoseconds spent preparing 13 JDBC statements;
129932100 nanoseconds spent executing 10 JDBC statements;
166547600 nanoseconds spent executing 3 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
229440900 nanoseconds spent executing 1 flushes (flushing a total of 280 entities and 10 collections);
0 nanoseconds spent executing 0 partial-flushes (flushing a total of 0 entities and 0 collections)
НОВЫЙ ВОПРОС
Это максимальная производительность, которую я получаю?? Или есть еще вещи, которые я могу сделать, чтобы повысить производительность?