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 条评论
登录 后参与评论

相关文章

来自专栏Android群英传

Gradle函数复用的一点实践

921
来自专栏Java技术分享

Redis特性和应用场景

Redis使用标准C编写实现,而且将所有数据加载到内存中,所以速度非常快。官方提供的数据表明,在一个普通的Linux机器上,Redis读写速度分别达到81000...

2367
来自专栏后端技术探索

vim神奇高效功能--批量生成Sql实例

可以通过写代码,读取文件进行入库,无论用什么语言,这代码逻辑都很容易实现。唯一的问题是代码需要上线后执行,每次上线可是一个大工程,恐怕会被pm同学嘲笑说:“你这...

983
来自专栏技术专栏

慕课网Flask构建可扩展的RESTful API-3. 自定义异常对象

因为注册的形式就非常多,所以我们不可能用万能的方式来解决。如果我们不能很好的处理多种多样的形式,我们的代码就会非常的杂乱

1042
来自专栏数据库

Redis列表的“绝地反击”

大家晚上好,今天介绍Redis中的列表数据结构。 Redis中的列表是用来存储多个有序的字符串的,最神奇的地方是:竟然可以在列表两端插入(push)和弹出(po...

20910
来自专栏Java技术分享

50道Java线程题

线程是操作系统能够进行运算调度的最小单位,它被包含在进程之中,是进程中的实际运作单位。程序员可以通过它进行多处理器编程,你可以使用多线程对 运算密集型任务提速。...

2127
来自专栏CSDN技术头条

史上最难的一道Java面试题:分析篇

无意中了解到如下题目,觉得蛮好。 题目如下: ? 该程序的输出结果? 在java中,多线程的程序最难理解、调试,很多时候执行结果并不像我们想象的那样执行。所以在...

1847
来自专栏JAVA技术zhai

JVM虚拟机详解

  JVM是Java Virtual Machine(Java虚拟机)的缩写,JVM是一种用于计算设备的规范,它是一个虚构出来的计算机,是通过在实际的计算机上仿...

1222
来自专栏个人随笔

房上的猫:JavaDoc注释

//这是一个注释 /*   *这是一个演示程序   */ /**    *@这是JavaDoc注释。   */ JavaDoc注释    背景:       j...

35210
来自专栏JAVA高级架构

《深入理解java虚拟机-高效并发》读书笔记

Java内存模型与线程 概述   多任务处理在现代计算机操作系统中几乎已是一项必备的功能,多任务运行是压榨手段,就如windows一样,我们使劲的压榨它运行多个...

3197

扫码关注云+社区