Python 插入百万数据的时间优化与 OOM 问题的解决

一.背景

我们小组需要从 IT 部门同步客户信息和机构信息到本地,这两部分数据大概各 400W,总共 800W 的数据量。IT 部门提供两个存储过程用于分别获取这两部分数据,因此在使用 Python 处理数据时,只能调用存储过程将两部分数据分别一次性全部读入内存再处理。每个存储过程从 IT 部门的数据库获取数据大概需要 20min 的时间,总共 40min 的样子。

二 . 问题

之前一位已经离开公司的同事已经实现了一个版本的该需求的功能,思路是一个 Python 类的两个方法分别处理客户信息和机构信息,使用 MySQLdb 模块拼接一条 sql 语句便插入一条数据。在实际执行过程中的现象是程序大概执行了 4 个小时,然后进程被 Linux 的 OOM killer(Out_Of_Memory killer) 机制给杀掉了。下图是截取自 Linux 的系统日志,可以看到 anon-rss 系统的物理内存是 9G 多些,然而进程使用的 total-vm 虚拟内存已经到达了 11G+。

这样就有两个问题需要优化处理:

  • 处理时间过长,扣除固定的获取数据的 40min,竟然 3 个多小时都没能将 800w 的数据入本地 mysql。
  • 内存溢出,内存使用过大被 Linux 内核杀死导致入库的数据不完整。

三. 定位与解决

1. 时间优化

通过打印处理时间,可以很容易发现处理时间主要都消耗在数据入本地 mysql 上。原始的入库逻辑是拼接一条 insert 语句,然后执行一次入库动作,这样的效率肯定不高。提升数据库入库的效率的一条原则就是以“insert table(XX) values(XX),(XX)…”来代替多次插入单条数据。因此对代码进行改造,简单粗暴地将读入的 400w 数据拼接成一条“insert table(XX) values(XX),(XX)…”的 sql 语句。但是执行的结果就是“Got a packet bigger than 'max_allowed_packet' bytes”,原来 mysql 对输入 sql 语句长度有限制,最大能够多长就是有“max_allowed_packet”这个参数决定的。

数据库才允许 16M 的输入,而 400w 的数据将就 900M 的长度,肯定处理不了。另外查资料发现“max_allowed_packet”不支持热修改,也就是或者修改配置文件然后重启 db,或者执行“set global max_allowed_packet=XX”语句,但是要重新登录。这样对于 python 脚本的执行都不太友好,所以想到的办法是把 400w 的数据进行拆分,组装成小于 16M 的 insert 语句执行。思路:使用迭代器对查询的结果集进行分割处理,返回“(XXX),(XXX)”形式的 insert 语句后半部分,以便拼接 sql 字符串。

class NextValues(object):
    def __init__(self, properties, interval, source_list):
        # 查询结果集
        self.source_list = source_list
        # 结果集长度
        self.max = len(source_list)
        # 分割间距
        self.interval = interval
        self.step = self.max / self.interval + 1
        # 需要进行插入字段名称列表
        self.properties = properties
        self.index = 0

    def __iter__(self):
        return self

    def next(self):
        values = bytearray('')
        if (self.index <= self.step):
            for j in range(0, self.interval):
                flag = self.index * self.interval + j
                # 超过最大范围则返回
                if (flag >= len(self.source_list)):
                    break
                item = self.source_list[flag]
                value = bytearray(",(%s)" % (','.join(["'%s'"] * len(self.properties))))
                # 替换实际的数据
                for proper in self.properties:
                    value = value.replace("%s", str(item.get(proper, '')).replace("'", r"\'"), 1)
                values += value
            self.index += 1
            # 去除开头多余的逗号
            if values:
                return values[1:]

        raise StopIteration

使用方法:

# 需要插入的字段名称列表
properties = ['CUSTOMER_ID', 'PARTY_NUMBER', 'ORG_ID', 'CUSTOMER_NAME', 'ACCOUNT_NUMBER', 'NAME', 'ADDRESS_ID','ADDRESS1', 'REGION_CODE', 'REGION_NAME']
# 初始化迭代器用于获取 value 值
next_value = NextValues(properties, 200000, result_set.result_info)
for values in next_value:

   if not values:

      break

   sql = "insert into db_code.t_erp_customer(Fcustomer_code,Forg_code,Fou_id, Fcustomer_name,Faccount_code,Fou_name,Fchannel_id,Fchannel_name,Fregion_code,Fregion_name) values %s" % values

   ret = self.db_local.update(sql)

上述代码以 200000 条记录为分割,拼接 insert 的 sql 语句入库。(实验 30W 条记录为分割也可以,但是为了防止过长的记录集中在一起导致超长,所以选择 20w 为间隔)。时间优化提升:

“2017-02-08 20:19:17”开始执行,到“2017-02-08 20:35:26”通过存储过程获取 4246379 条数据,使用时间 26 分钟,而“2017-02-08 20:39:34”数据入 mysqldb 完成,只有了 4 分钟,从 4 小时到 4 分钟时间优化提升挺明显的。

2. OOM 解决

通过观察 Linux 的 top 命令的输出以及 python 日志的打印,发现在处理完第一个方法里面涉及的 400w 数据后,系统内存使用了 7G 左右。第二个方法需要处理另外 400w 的数据,但是在第二个方法执行时内存没有减少,是在 7G 的基础上持续增加,当增加到 11G 左右时,被 OOM Killer 干掉。

可以看出第一个方法使用的内存没有被释放掉,这种现象很奇怪,理论上说第一个方法作用域结束后,内部的局部变量的引用计数应该都变为 0 了,应该会被内存回收机制清理掉,但是没有被清理。所以当时怀疑是否 python 内存回收机制没有触发,于是做了对第一个方法里面的所有表面能看到的变量进行了“del”,主动减少其引用计数,并调用了“gc.collect()”主动回收内存。但是 OOM 依然存在,现在一样。

由于之前过长的 insert 语句插入 mysql 会报错,顺着这个思路怀疑是否是 python 中拼接 sql 的 string 占用了大量的内存没回收。因为 python 中的 string 使用了常量池技术,创建一个不同内容的 string 就会在常量池中存在一份,800w 记录的 insert 语句量也是很大的。于是又尝试了“del”string 变量以及使用 bytearray 可变的“string”代替原来的 string 变量。但 OOM 依然存在,没办法只能查看 python 运行时的内存到底是怎么样了。

选择安装了 heapy 模块查看 python 运行时的内存。下载位置:https://pypi.python.org/pypi/guppy/ , 安装简单“python setup.py build”“python setup.py install”两条命令就安装完成。另外 heapy 对原来的代码侵入性也比较小,下面三行代码几乎就能将 python 运行时的内存大概情况看清楚:

from guppy import hpy
p = hpy()
print(p.heap())

介绍得很详细。

查看第一个方法执行完内存的使用情况,可以看到总共使用了 6883496272bytes 的内存,而使用的内存中 65%是 dic 类型的数据占用了 4452265816bytes。

这个“dict”提示挺明显的,在我们的方法里面没有什么大的 dict,这么大的 dict 只有通过存储过程查询出来的结果集了。顺着这个线索检查发现在一个连接对象中竟然留有对结果集的引用,导致结果集在执行第二个方法前没有被回收。即:a 对象的 methodA 和 methodB 方法分别处理 400w 数据,这两个方法中的所有对象引用都已经释放。同时 a 对象又有一个叫 db_conn 的属性,这个属性是被封装的数据库连接对象,当它查询结果集时,同时有留有了这个结果集的引用。所以,methodA 执行完后,a.db_conn 依然保持着结果集引用导致无用内存没有被释放。a.db_conn 的引用也“del”之后,内存使用情况:

可以看到内存迅速减小,只剩下 9M 左右,dict 占用内存也不存在。整个 python 脚本能够顺利执行,不再出现 OOM 问题。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

王帅的专栏

1 篇文章1 人订阅

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大内老A

我所理解的RESTful Web API [设计篇]

《我所理解的RESTful Web API [Web标准篇]》Web服务已经成为了异质系统之间的互联与集成的主要手段,在过去一段不短的时间里,Web服务几乎清一...

1996
来自专栏技术翻译

Elasticsearch文档和映射

在Elasticsearch的说法中,文档是序列化的JSON数据。在典型的ELK设置中,当您发送日志或度量标准时,它通常会发送到Logstash,Logstas...

501
来自专栏互联网技术杂谈

beanstalkc Tutorial 中文版

英文原版:https://github.com/earl/beanstalkc/blob/wip-doc-rtfd/doc/tutorial.rst

2918
来自专栏程序员的碎碎念

ajax异步提交数据到数据库

很多时候我们提交数据到服务器端数据库都是用form表单,这样子是最普通最简单提交数据的方法,你填写完表单后,post提交到后台文件.php,处理完后返回...

3364
来自专栏土豆专栏

Java面试之基本概念(一)

首先heap是堆,stack是栈,stack的空间是由操作系统自动分配和释放,heap的空间是手动申请和释放的;heap的空间是手动申请和释放的,heap常用n...

2407
来自专栏程序员互动联盟

【专业技术】如何搞定多线程?

存在问题: 多线程在我们开发中比较常见的方式,如何搞定多线程呢? 解决方案: 1,为什么需要多线程? 因为现代的CPU运行速度很快,哪怕是一个简单的嵌入式芯片也...

2677
来自专栏coder修行路

《深入理解计算机系统》阅读笔记--计算机系统漫游

842
来自专栏服务端思维

良好的 API 设计指南

在 RESTful API 中,API 接口应该尽量兼容之前的版本。但是,在实际业务开发场景中,可能随着业务需求的不断迭代,现有的 API 接口无法支持旧版本的...

812
来自专栏蛋未明的专栏

Node.js如何处理健壮性

1955
来自专栏Android群英传

Android Native Crash 收集

本文是『张涛的NDK之旅』,本来很早以前就有很多读者希望我能写一些关于MDK的文章,但是由于我本身对NDK不熟悉,所以找来了同事张涛的文章。欢迎大家关注他的博客...

1161

扫码关注云+社区