复杂业务下向Mysql导入30万条数据代码优化的踩坑记录

从毕业到现在第一次接触到超过30万条数据导入MySQL的场景(有点low),就是在顺丰公司接入我司EMM产品时需要将AD中的员工数据导入MySQL中,因此楼主负责的模块connector就派上了用场。在楼主的努力下,线上数据同步代码经历了从最初的将近16个小时(并且还出现其他问题这些问题,等后面慢慢细说),到最终25分钟的性能优化。 打个广告,楼主自己造的轮子,感兴趣的请点https://github.com/haifeiWu/lightconf

代码直接Jenkins打包上线

楼主负责的connector模块之前经历过的最大的数据量也仅仅是几千条,当然面对几千条数据代码也是跑的及其的快,没有啥影响,然而当第一次在顺丰的正式环境上线时,由于数据量比较大,楼主的代码又是串行执行的,事务保持的时间就相当长,也就因此出现了下面的错误信息:

Lock wait timeout exceeded; try restarting transaction

这里来说一下报这个错的解决方案,查看MySQL是否有锁

show OPEN TABLES where In_use > 0;

另外,在information_schema下面有三张表:INNODB_TRXINNODB_LOCKSINNODB_LOCK_WAITS(解决问题方法),通过这三张表,可以更简单地监控当前的事务并分析可能存在的问题。

比较常用的列:

  • trx_id: InnoDB存储引擎内部唯一的事物ID
  • trx_status: 当前事务的状态
  • trx_status: 事务的开始时间
  • trx_requested_lock_id: 等待事务的锁ID
  • trx_wait_started: 事务等待的开始时间
  • trx_weight: 事务的权重,反应一个事务修改和锁定的行数,当发现死锁需要回滚时,权重越小的值被回滚
  • trx_mysql_thread_id: MySQL中的进程ID,与show processlist中的ID值相对应
  • trx_query: 事务运行的SQL语句

kill 进程ID,发生上面错误的根本原因在业务逻辑代码对数据库的操作无视了大数据量的情况,比如当数据量比较大时就会出现刚刚修改完这条记录,接着再次修改就会出现上述出现的问题。

代码优化过程

使用线程池,并发执行,提高效率

由于数据量比较大,首先想到的方法是拿到数据后将数据分拆成n份,由多个线程并发执行数导入的操作。由此引出多线程的问题,在处理多线程问题时共享的数据结构像Map,List应该采用jdk提供的current包下的数据结构,另外在涉及到操作数据库的地方应该加锁,楼主用的是jdk提供的ReentrantLock。使用jdk自带的 jvisualvm,进行代码的监控进而找到最佳线程数,下面是监控的数据,

下面是线程池的创建代码

ThreadFactory namedThreadFactory = new ThreadFactoryBuilder()
                .setNameFormat("demo-pool-%d").build();
ExecutorService singleThreadPool = new ThreadPoolExecutor(1, 1,0L, TimeUnit.MILLISECONDS,
                new LinkedBlockingQueue<Runnable>(1024), namedThreadFactory, new ThreadPoolExecutor.AbortPolicy());

singleThreadPool.execute(()-> System.out.println(Thread.currentThread().getName()));
singleThreadPool.shutdown();

果不其然,在使用多线程之后,数据插入效率由原来的十几个小时降到了三个小时,然后并没有达到我们的预期效果,我们继续。

使用druid监控发现问题的SQL

  • 配置druid监控,在应用的web.xml添加配置,并放开/druid的拦截 <!-- druid 数据库监控 --> <filter> <filter-name>DruidWebStatFilter</filter-name> <filter-class>com.alibaba.druid.support.http.WebStatFilter</filter-class> <init-param> <param-name>exclusions</param-name> <param-value>*.js,*.gif,*.jpg,*.png,*.css,*.ico,*.jsp,/druid/*,/download/*</param-value> </init-param> <init-param> <param-name>sessionStatMaxCount</param-name> <param-value>2000</param-value> </init-param> <init-param> <param-name>sessionStatEnable</param-name> <param-value>true</param-value> </init-param> <init-param> <param-name>principalSessionName</param-name> <param-value>session_user_key</param-value> </init-param> <init-param> <param-name>profileEnable</param-name> <param-value>true</param-value> </init-param> </filter> <filter-mapping> <filter-name>DruidWebStatFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping> <servlet> <servlet-name>DruidStatView</servlet-name> <servlet-class>com.alibaba.druid.support.http.StatViewServlet</servlet-class> <!--<init-param> <param-name>allow</param-name> <param-value>*.*.*.*</param-value> </init-param>--> <init-param> <!-- 允许清空统计数据 --> <param-name>resetEnable</param-name> <param-value>true</param-value> </init-param> <init-param> <!-- 用户名 --> <param-name>loginUsername</param-name> <param-value>druid</param-value> </init-param> <init-param> <!-- 密码 --> <param-name>loginPassword</param-name> <param-value>druid</param-value> </init-param> </servlet> <servlet-mapping> <servlet-name>DruidStatView</servlet-name> <url-pattern>/druid/*</url-pattern> </servlet-mapping>

  • 配置数据库连接池
<!-- 数据源配置, 使用 BoneCP 数据库连接池 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
		<!-- 数据源驱动类可不写,Druid默认会自动根据URL识别DriverClass -->
		<property name="driverClassName" value="${jdbc.driver}" />

		<!-- 基本属性 url、user、password -->
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />

		<!-- 配置初始化大小、最小、最大 -->
		<property name="initialSize" value="${jdbc.pool.init}" />
		<property name="minIdle" value="${jdbc.pool.minIdle}" />
		<property name="maxActive" value="${jdbc.pool.maxActive}" />

		<!-- 配置获取连接等待超时的时间 -->
		<property name="maxWait" value="60000" />

		<!-- 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 -->
		<property name="timeBetweenEvictionRunsMillis" value="60000" />

		<!-- 配置一个连接在池中最小生存的时间,单位是毫秒 -->
		<property name="minEvictableIdleTimeMillis" value="300000" />

		<property name="validationQuery" value="${jdbc.testSql}" />
		<property name="testWhileIdle" value="true" />
		<property name="testOnBorrow" value="true" />
		<property name="testOnReturn" value="false" />

		<!-- 配置监控统计拦截的filters -->
		<property name="filters" value="stat" />
	</bean>
  • 使用druid的SQL监控发现问题SQL语句,优化SQL

通过命令查看程序的gc情况

通过命令jstat -gc pid 来查看程序的gc情况,下面是楼主程序数据同步完成之后的gc情况

简单说明:

这里写代码片S0C、S1C、S0U、S1U:Survivor 0/1区容量(Capacity)和使用量(Used)
EC、EU:Eden区容量和使用量
OC、OU:年老代容量和使用量
PC、PU:永久代容量和使用量
YGC、YGT:年轻代GC次数和GC耗时
FGC、FGCT:Full GC次数和Full GC耗时

小结

通过上面的优化过程,楼主的connector的数据同居效率也由小时级下降到了分钟级,同步30+万的时间可以在25分钟内完成。经此一役,楼主算是收获满满。抱着虔诚的心态学习,不浮不躁,快乐成长。

作 者:haifeiWu 原文链接:http://www.hchstudio.cn/article/2018/96ac/ 版权声明:非特殊声明均为本站原创作品,转载时请注明作者和原文链接。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏工作积累

回答:这几天找工作遇到的面试题目

https://laravel-china.org/articles/9983/the-interview-questions-we-met-in-the-la...

3.2K10
来自专栏PPV课数据科学社区

使用cProfile等工具来提高python的执行速度

摘要: 众所周知,python相较于其它语言速度较慢,但是我们可以通过优化的方法来提升效率。 本文假定你已经十分熟悉Python。 众所周知,Python是一种...

3027
来自专栏大数据杂谈

Python 爬虫实践:《战狼2》豆瓣影评分析

2485
来自专栏一名叫大蕉的程序员

今天聊聊分布式锁 No.86

首先祝大家新年快乐,感谢大家过去一年的陪伴。大蕉在这里给大家拜年啦啦。新年快乐,我爱学习。 恭喜发财,红包拿来~ 好了切入正题,一直在工作中会聊到很多锁的问题,...

48113
来自专栏大数据和云计算技术

Codegen技术学习

Codegen在spark中的应用 除了前面查询优化中讲到逻辑优化器之外,Spark在1.5版本中引入了比较大的一个动作就是DataFrame执行后端的优化,引...

6285
来自专栏施炯的IoT开发专栏

微软移动 Nokia Lumia SensorCore SDK 介绍及上手体验

    早在今年的BUILD大会上,诺基亚就宣布了SensorCore以及它的部分演示。今天,它终于面世了,大家可以去Building Apps for Win...

2777
来自专栏北京马哥教育

Python 爬虫实践:《战狼2》豆瓣影评分析

来源:hang segmentfault.com/a/1190000010473819 简介 刚接触python不久,做一个小项目来练练手。前几天看了《战狼2》...

5014
来自专栏圆方圆学院精选

【刘文彬】EOS技术研究:合约与数据库交互

原文链接:醒者呆的博客园,https://www.cnblogs.com/Evsward/p/multi_index.html

1312
来自专栏进击的程序猿

orm 系列 之 Eloquent演化历程2

上篇讲到了数据库Relation的实现,本篇接着讲migrations or database modification logic的功能,此处开始的git是g...

893
来自专栏我是攻城师

Redis 3.0.0正式版发布,全新的分布式高可用数据库

3837

扫码关注云+社区

领取腾讯云代金券