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

相关文章

来自专栏加米谷大数据

Hive的数据类型

本文介绍hive的数据类型,数据模型以及文件存储格式。这些知识大家可以类比关系数据库的相关知识。

892
来自专栏编程

JAVA面试常考系列八

题目一 JDBC是什么? JDBC(Java DataBase Connectivity,java数据库连接)是一种用于执行SQL语句的Java API,可以为...

32211
来自专栏芋道源码1024

【Netty 专栏】深入浅出 Netty 内存管理 PoolChunk

摘要: 原创出处 https://www.jianshu.com/p/c4bd37a3555b 「占小狼」欢迎转载,保留摘要,谢谢!

790
来自专栏GreenLeaves

oracle 表空间tablespace

一、Oracle 表空间的组成 Everoone knows Oracle数据库真正存放数据的是数据文件,Oracle表空间是逻辑上的概念,他在物理上是并不存在...

1848
来自专栏zingpLiu

深入理解计算机系统系列【计算机系统漫游】

操作系统原理是计算机行业基本功,想要成为一名计算机领域的专业人士,必不可少要打好基础。最近打算重点读一读《深入理解计算机系统》这本书,回顾和提升自己对计算机和操...

703
来自专栏Java技术分享

《Redis设计与实现》简读

最佳实践:因为对字符串的增长或缩短操作都有可能需要执行内存重分配,所以修改相同键使用SDS类型保存的值时保持修改前后长度一致。

1778
来自专栏编程

Cgroup原理解释及部署实例(3)

Cgroup设计原理分析 CGroups的源代码较为清晰,我们可以从进程的角度出发来剖析cgroups相关数据结构之间的关系。在Linux中,管理进程的数据结构...

1978
来自专栏Java技术分享

《Redis设计与实现》简读

一、数据结构与对象 简单动态字符串(SDS) 相比C字符串增加记录字符串长度的,获取字符串长度复杂度为O(1) 相比C字符串增加记录已分配内存空间,可以避免缓冲...

1735
来自专栏java、Spring、技术分享

Redis设计与实现读书笔记

  Redis底层的数据库采用的就是这种结构,还有哈希键的底层实现之一也是采用HashMap这种结构。 哈希表的节点结构如下:

922
来自专栏CSDN技术头条

Hadoop旧mapreduce的map任务切分原理

前言 最近在工作过程中接触一些Hive数据仓库中的表,这些表实际是从关系型数据库通过Sqoop抽到Hive的。在开发过程中对map任务的划分进行性能调优,发现...

18910

扫码关注云+社区