记一次数据导入调优过程

问题

先说一下线上服务器的情况:

配置为 E5-2420(2 processor), 8G内存. 在上面正在运行的和数据导入相关的脚本有32个. 已占用资源为 cpu 10%, 内存 40%.

导入缓慢的脚本为一个解析 xml(每天生成的文件为1G左右大小的xml, 包含100000条左右的订单记录) 到数据库的脚本. 相关数据库为 postgresql, 数据表有三张, 分别为 wp_impt_order(368618条记录), wp_impt_order_style(4688006条记录, 每条记录20个字段), wp_impt_order_detail(3753845条记录, 每条记录15个字段). 这三张表主表为wp_impt_order, 其他两张表通过指向主表id 的外键 impt_order_id 关联. 所用的ORM 为SQLAlchemy.

主要的导入过程是每天的订单文件为客户批量生成的文件, 这个文件中有可能包含已在数据库中的订单或者存在更新的订单. 导入逻辑为存在已最新的为准. 当前的问题是每天的导入耗时为8个小时左右. 耗时过长导致生产部门时常处于等待的过程.

问题分析

先来看看数据处理的逻辑. 在处理文本出具入库的过程中, 有两个思路:

1. 边解析文本, 边入库.

这个思路的伪代码如下:

sax_parser(xml) # 流式获取数据
for each_order:
   query_db
   if exist:
       delete and update
   else:
       insert

这个解析过程的好处是使用内存少(可维持在200M的内存消耗), 但是因为频繁的与数据库交互, 导致数据库 IO 的压力巨大

2. 解析完成后, 批量入库

伪代码如下

sax_parser(xml)
globle_list = []

# 将所有数据解析为字典形式组成的列表, 存入内存
for each_order:
   globle_list.append(each_order)

bulk_delete(keys) # keys为主键组成的列表, 直接删除存在记录的记录
bulk_update or save # 批量更新插入

这个思路的优点是减少了数据库交互, 缺点是会增加内存.

在最开始, 这个项目采用的是思路1, 在数据量少的时候可以正常运行, 即使数据更新不及时, 延迟也在客户接受的程度内. 然而随着数据库中数据的增多, 频繁查询删除插入带来的是数据库操作时间的增长(删除和插入都涉及数据库主键索引的重建等过程). 最终导致入库时间越来越慢.

解决方法

通过上面的分析, 当主要矛盾在数据库交互时间过长的时候, 就要考虑减少相关操作的时间. 能减少与数据库交互的过程就尽量的减少或者放在一起操作. 同时针对待解析的文件来说, 文件本身在前后两个文件中存在相同也存在不同的数据. 对于相同的数据, 在第二天的文件导入过程中其实可以不导入(未做更新, 数据库中已经存在相关的记录). 即主要思路就是将能在内存中完成的工作全在内存中完成. 最终优化的效果是内存一次解析占用在1.5G-2G. 入库完成时间为10分钟.

总结

在这个过程中, 也接触到SQLAlchemy 的一些少用的接口. 比如在批量删除中使用的语句为:

DBSession.query(MandSOrderStyle).filter(MandSOrderStyle.impt_order_id.in_(ids)).delete(
                    synchronize_session=False)

这里对查询的结果在session中并不保存而是直接删除, 减少了内存的占用. 同时针对大批量插入过程, 使用 bulk_save_objects. 这里 bulk_save_objects 是在牺牲 flush 过程中对插入更新过程的维护已经安全性稳定性的前提下来提升插入速率的. 相关文章可参考