为什么插入批处理时间增加？ [英] Why are the insert batch times increasing?
问题描述
每批次插入100k行，每批1k（肯定是批量生产），每次插入的时间都在增加。尝试理解原因。
原始问题：
JProfiler在ReadAheadInputStream.fill中显示了很多读取InputStream的时间。
更新：下面是来自EJP的每条评论在数据库服务器正在执行语句并准备响应时，时间在read（）中被阻塞了
什么可能导致这么多时间花在这里？
详情：
- 在1k批次中将行插入到单个表中。
- 批次位于触发Hibernate会话刷新的事务中。
- 启用Hibernate和MySQL连接批处理属性并进行分析确认SQL INSERT确实按预期进行了批处理。
- 随着表的增长，插入的时间会增加（例如，25秒内插入的第一个100k，30,35,40等中的第二个100k）。
我们有另一个应用程序（使用JDBC模板），它在同一个表上执行相同的INSERT，但不会像表增长。因此，虽然数据库调优可能会提高性能，但由于其他应用程序基本上执行的是相同插入而没有性能问题，因此次优化数据库配置并不能解释速度降低的问题。
Dev Stack：
- Java 8
- Hibernate 4.3.6
- Spring Data JPA 1.7.2
- MySQL 5.6.20
- MySQL驱动程序5.1.33
- HikariCP 2.3.2
我知道我是不提供代码示例。我基本上在寻找想法，并更好地理解什么可能会导致在ReadAheadInputStream.fill和调用堆栈中花费太多时间。
最后，我们无法确定根本原因并解决了使用JPA / Hibernate批量插入的降级问题。因此，对于大批量插入，我们已经恢复使用JDBC模板。我们不再看到任何性能下降。
Summary: Inserting 100k rows at a time with 1k batches (yes it's definitely being batched) and time to insert is increasing for each batch. Trying to understand why.
Original Question: JProfiler shows a lot of time spent reading an InputStream in ReadAheadInputStream.fill.
Update: Per comment below from EJP "The time is being spent blocked in read(), while the database server is executing the statement and preparing the response"
What could cause so much time to be spent here?
Details:
- Inserting rows into a single table in 1k batches.
- Batches are inside a transaction which triggers flush of Hibernate session.
- Hibernate and MySQL connection batch properties are enabled and profiling confirms SQL INSERT is indeed batched as expected.
- As the table grows, the time to insert grows (ie. first 100k inserted in 25 seconds, second 100k in 30, 35, 40, etc).
We have another app (which used JDBC Template) which executes the same INSERT on the same table but does NOT slow down as the table grows. Therefore while db tuning may improve performance, since another app basically does the same insert without performance issues, sub-optimal DB config does not explain the slow down.
Dev Stack:
- Java 8
- Hibernate 4.3.6
- Spring Data JPA 1.7.2
- MySQL 5.6.20
- MySQL driver 5.1.33
- HikariCP 2.3.2
I understand I'm not providing a code sample. I'm basically looking for ideas and a better understanding of what might cause so much time spent in ReadAheadInputStream.fill and below in the call stack.
In the end, we were unable to identify the root cause and fix the degradation issue for batch inserts using JPA/Hibernate. Therefore for large batch inserts we have reverted to using JDBC Template. We no longer see any performance degradation.
这篇关于为什么插入批处理时间增加？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
