- 背景：
- 项目整合业务数据汇总录入数据库，数据规模为200～300w每月，按要求需要在月初导入上月全部数据。
- 要做到项目隔离。
- 要保证数据完整性。
- 数据导出方案：采用多线程读写配合文件服务器，可以保证数据导出效率。
- 数据下载导入方案：
- 下载文件到机器目录。
- 使用多线程同时分段读取文件到内存。
- 使用多线程执行批量入库操作(批量insert量为300每次)。
- 使用countDownLatch计数器来确保每一天数据的完整性；
- 数据库链接池使用c3p0，初始大小为50，最大链接数为100。
- 方案执行配置1.0:按每月天数初始化线程池，每天开启20个线程批量入库数据，最大线程数600
- 问题：
- 任务很慢，跑完全部数据用时16h...
- 线程争抢链接池资源会造成阻塞，导致执行完毕的work不能及时释放连接归还给链接池。
- 每天数据量10W/300=334work*31=10354work，抢占数据库链接的work过多，会造成c3p0链接池死锁（APPARENT DEADLOCK）。附上资料
AsynchronousRunner$DeadlockDetector@7803cef7 -- APPARENT DEADLOCK!!! Complete Status:
Managed Threads: 3
Active Threads: 3
Active Tasks:
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@791efed9
on thread: C3P0PooledConnectionPoolManager[identityToken->2zozfma31t2nvwwikd9tw|5d553603]-HelperThread-#2
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@7d572454
on thread: C3P0PooledConnectionPoolManager[identityToken->2zozfma31t2nvwwikd9tw|5d553603]-HelperThread-#0
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@6a0b3b13
on thread: C3P0PooledConnectionPoolManager[identityToken->2zozfma31t2nvwwikd9tw|5d553603]-HelperThread-#1
Pending Tasks:
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@77085bb7
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@79ee8bb9
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@3ddf7554
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@ebf025f
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask@3b5f25dc
Pool thread stack traces:
Thread[C3P0PooledConnectionPoolManager[identityToken->2zozfma31t2nvwwikd9tw|5d553603]-HelperThread-#2,5,main]
java.util.Arrays.copyOfRange(Arrays.java:3664)
java.lang.String.<init>(String.java:207)
java.nio.HeapCharBuffer.toString(HeapCharBuffer.java:567)
java.nio.CharBuffer.toString(CharBuffer.java:1241)
com.mysql.jdbc.StringUtils.toString(StringUtils.java:1871)
com.mysql.jdbc.ResultSetRow.getString(ResultSetRow.java:821)
com.mysql.jdbc.ByteArrayRow.getString(ByteArrayRow.java:70)
com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5815)
com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5692)
com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4989)
com.mysql.jdbc.Util.resultSetToMap(Util.java:508)
com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:982)
com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3481)
com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2445)
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2215)
com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:813)
com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
sun.reflect.GeneratedConstructorAccessor112.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:423)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
com.mysql.jdbc.NonRegisteringDriver.connect$dkimi$impl$1046(NonRegisteringDriver.java:334)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java)
com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Thread[C3P0PooledConnectionPoolManager[identityToken->2zozfma31t2nvwwikd9tw|5d553603]-HelperThread-#0,5,main]
com.mysql.jdbc.MysqlIO.nextRowFast(MysqlIO.java:2076)
com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1900)
com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3401)
com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:483)
com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3096)
com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2266)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2687)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2713)
com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2663)
com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1599)
com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:980)
com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3481)
com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2445)
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2215)
com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:813)
com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
sun.reflect.GeneratedConstructorAccessor112.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:423)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)
com.mysql.jdbc.NonRegisteringDriver.connect$dkimi$impl$1046(NonRegisteringDriver.java:334)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java)
com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:175)
com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:220)
com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:206)
com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:203)
com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1138)
com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1125)
com.mchange.v2.resourcepool.BasicResourcePool.access$700(BasicResourcePool.java:44)
com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1870)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Thread[C3P0PooledConnectionPoolManager[identityToken->2zozfma31t2nvwwikd9tw|5d553603]-HelperThread-#1,5,main]
java.nio.HeapCharBuffer.<init>(HeapCharBuffer.java:57)
java.nio.CharBuffer.allocate(CharBuffer.java:335)
java.nio.charset.CharsetDecoder.decode(CharsetDecoder.java:795)
java.nio.charset.Charset.decode(Charset.java:807)
com.mysql.jdbc.StringUtils.toString(StringUtils.java:1871)
com.mysql.jdbc.ResultSetRow.getString(ResultSetRow.java:821)
com.mysql.jdbc.ByteArrayRow.getString(ByteArrayRow.java:70)
com.mysql.jdbc.ResultSetImpl.getStringInternal(ResultSetImpl.java:5815)
com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5692)
com.mysql.jdbc.ConnectionImpl.loadServerVariables(ConnectionImpl.java:3931)
com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3473)
com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2445)
com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2215)
com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:813)
com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
sun.reflect.GeneratedConstructorAccessor112.newInstance(Unknown Source)
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
java.lang.reflect.Constructor.newInstance(Constructor.java:423)
com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:399)The last packet successfully received from the server was 1,288,063 milliseconds ago.
The last packet sent successfully to the server was 18,302 milliseconds ago.;
nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure- 方案执行配置2.0:修改初始化线程池 4（读取文件线程） * 6（写入线程），最大并发线程数24
- 经生产环境验证100w数据用时10min，并可确保数据完整性以及重推数据的灵活性。
- 生成环境跑批效果
数据量：2699973
31 * 3
线上跑数据，花费：61056631
execute download and batchInsert spend:61056631
数据量：1034158
4 * 6线程
execute download and batchInsert spend:667602- 代码片段
public void downLoadAndInsert(List<DepositTaskType> depositTaskTypes) {
if (null == depositTaskTypes || depositTaskTypes.size() == 0) {
log.info("no file need to downLoad!");
return;
}
CountDownLatch countDownLatch = new CountDownLatch(depositTaskTypes.size());
long startTime = System.currentTimeMillis();
for (DepositTaskType depositTaskType : depositTaskTypes) {
log.info("start task filename=" + depositTaskType.getTaskName());
fixedThreadPool.submit(() -> activeDownLoadAndInsert(countDownLatch, depositTaskType, tempDir + depositTaskType.getTaskName()));
}
try {
countDownLatch.await();
} catch (InterruptedException e) {
e.printStackTrace();
log.error("countDownLatch.await error. e:" + e.getMessage());
}
log.info("execute download and batchInsert spend:" + (System.currentTimeMillis() - startTime));
}
public void fileReader(Dto dto, String filePath) {
try {
Integer fileLine = this.getFileLine(filePath);
if (fileLine == 0) {
...
}
int total = fileLine / 300;
CountDownLatch countDownLatch = new CountDownLatch(total + 1);
log.info("countDownLatch total:" + countDownLatch.getCount());
List<Dto> batchList = Collections.synchronizedList(Lists.newArrayList());
FileInputStream fileInputStream = new FileInputStream(filePath);
InputStreamReader inputStreamReader = new InputStreamReader(fileInputStream, "UTF-8");
BufferedReader bufferedReader = new BufferedReader(inputStreamReader);
log.info("开始读取文件。。。");
Long start = System.currentTimeMillis();
String str;
while (null != (str = bufferedReader.readLine())) {
batchList.add(fullDetail);
if (batchList.size() >= 300) {
log.info(">>>>>>>>>>批量插入List的size为：" + batchList.size());
List<Dto> copy = Lists.newArrayList();
synchronized (FileReaderService.class) {
copy.addAll(batchList);
batchList.clear();
}
// insertThreadPool.submit(() -> mapper.batchInsertDetails(copy));
insertThreadPool.submit(() -> this.batchInsertDetail(copy, countDownLatch));
}
}
if (batchList.size() > 0) {
log.info(">>>>>>>>>>剩余插入List的size为：" + batchList.size());
List<Dto> copyList = Lists.newArrayList();
synchronized (FileReaderService.class) {
copyList.addAll(batchList);
batchList.clear();
}
// insertThreadPool.submit(() -> mapper.batchInsertDetails(copyList));
insertThreadPool.submit(() -> this.batchInsertDetail(copyList, countDownLatch));
}
try {
log.info("execute countDownLatch wait. countnumber" + countDownLatch.getCount());
countDownLatch.await();
log.info("--------批量插入文件" + filePath + "，共耗时" + (System.currentTimeMillis() - start));
//数据准备完毕处理业务逻辑
} catch (InterruptedException e) {
e.printStackTrace();
log.error("countDownLatch.await exception:" + e.getMessage());
} catch (Exception e) {
e.printStackTrace();
log.error("update depositTaskType exception:" + e.getMessage());
}
log.info("downLoad file success, fileName:" + filePath);
} catch (Exception e) {
...
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
