首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Python 插入百万数据的时间优化与 OOM 问题的解决

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

原创
作者头像
王帅
修改2017-06-19 18:56:06
5.4K0
修改2017-06-19 18:56:06
举报
文章被收录于专栏:王帅的专栏王帅的专栏

一.背景

我们小组需要从 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+。

[1496371670137_9104_1496371670355.png]
[1496371670137_9104_1496371670355.png]

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

  • 处理时间过长,扣除固定的获取数据的 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”这个参数决定的。

[1496371727365_9021_1496371727499.png]
[1496371727365_9021_1496371727499.png]
[1496371737195_799_1496371737297.png]
[1496371737195_799_1496371737297.png]

数据库才允许 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 为间隔)。时间优化提升:

[1496371802017_2933_1496371802321.png]
[1496371802017_2933_1496371802321.png]

“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。

[1496371876055_9771_1496371876359.png]
[1496371876055_9771_1496371876359.png]

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

[1496371888297_5812_1496371888499.png]
[1496371888297_5812_1496371888499.png]

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

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

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

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

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

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一.背景
  • 二 . 问题
  • 三. 定位与解决
    • 1. 时间优化
      • 2. OOM 解决
      领券
      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档