V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
推荐学习书目
Learn Python the Hard Way
Python Sites
PyPI - Python Package Index
http://diveintopython.org/toc/index.html
Pocoo
值得关注的项目
PyPy
Celery
Jinja2
Read the Docs
gevent
pyenv
virtualenv
Stackless Python
Beautiful Soup
结巴中文分词
Green Unicorn
Sentry
Shovel
Pyflakes
pytest
Python 编程
pep8 Checker
Styles
PEP 8
Google Python Style Guide
Code Style from The Hitchhiker's Guide
shepherd
V2EX  ›  Python

如何充分利用单台服务器的性能将 10 亿级的 json 数据尽可能高效的插入 postgresql?

  •  
  •   shepherd · 2017-12-14 10:09:09 +08:00 via Android · 6604 次点击
    这是一个创建于 2296 天前的主题,其中的信息可能已经有所发展或是发生改变。
    1. 问题说明:
    目前有一个文本格式的 json 数据,里面数据量约 15 亿,每一行数据的格式都是固定的,插入前先要将 json 反序列化。运行环境为:windows server 2016,postgresql 10,Xeon [email protected],16G ddr3 1333,硬盘 io 上限约 120MB/s,服务器板载 RAID 无法使用,用 windows 的带区卷将两块硬盘组成一个卷,极限 io 也就 170MB/s,性价比太低就没做,直接使用了单块硬盘。
    2. 思路和伪代码:
    基本思路,遍历 json 文本,每 100 万行文本做一次插入。插入时,将 100 万行文本切割成小的分组用多线程的方式并行插入,每个线程每次都建立一个新的数据库连接并处理一个分组。待 100 万行文本处理完毕后再继续遍历 json。
    > 首先进行一些数据库的基本优化:
    * 创建数据库和表,将表设置为 unlogged
    * 开启 postgresql 的异步提交
    ```python
    # python 伪代码

    def do_insert(rows):
    # 每次插入都建立新的连接
    conn=psycopg2.connect()
    cur=conn.cursor()
    # 遍历 rows,进行 json 反序列化,提取数据并构造 sql 语句,执行 sql 语句将 rows 一次性插入到数据库

    for row in rows:
    v = json.loads(row)
    insert_sql = "insert into ... values (%s,%s)" % (v[1], v[2])
    cur.execute(insert_sql)
    cur.commit()
    conn.close()

    def insert(Rows):
    # 将 Rows 切割成 100 份,获得 100 个 rows,启用 n 个 do_insert 线程
    rows_list = split_list(Rows, 100)
    pool = threadpool.ThreadPool(n)
    requests = threadpool.makeRequest(do_insert, rows_list)
    [pool.putRequest(req) for req in requests]
    pool.wait()

    def main():
    # 载入 json 文本数据源
    # 按行读取,每读取 100 万行数据调用一次 insert()
    with open('import.json','r') as f:
    index=0
    Rows=[]
    for line in f:
    Rows.append(line)
    index += 1
    if index % 1000000 == 0:
    insert(Rows)
    ```

    3. 目前尝试了几种参数组合,最终使用的是 10 个线程,每个线程插入 1 万条,每 100 万条做一次多线程批量插入耗时约 2min,平均插入速度约 8400 条 /s,跑完 15 亿条数据大约要 2 天。
    python 执行到稳定状态后:占用内存约 1G,cpu 占用率~30%,cpu 线程数持续缓慢上升(似乎没有回收线程)。
    总的 CPU 使用率一直保持在~80%,其中 python 只占 30%,另外有大量的 postgres.exe 进程,这部分应该占用了较多的 cpu。硬盘写 io 不固定,峰值 30M/s、其余时间都是 5M/s 以下,速度明显不行。
    4. 初步分析
    对每个 python 语句的执行时间进行统计,发现主要的业务都在 do_insert 内,也就是具体执行插入操作的线程。不知道跟 json.loads 有无关系,还要进一步验证。但是感觉 python 效率以及程序处理逻辑上还存在缺陷,所以没有去进一步优化。
    插入线程越多,postgresql 进程会越多,这样是否会导致 cpu 使用不平衡。
    此外,是否还有其他可以优化的地方,无论是从语言上,还是处理逻辑上,还是数据库配置上。
    第 1 条附言  ·  2017-12-14 11:25:55 +08:00
    手机上不支持 md 语法,本来想上电脑上编辑一下的,还没来得及修改就有回复了。
    第 2 条附言  ·  2017-12-14 13:02:41 +08:00

    可能我的python代码有问题,从昨天到现在只跑了3亿条数据,用threadpool创建的线程一直没回收,任务管理器里面可以看到python.exe的线程在持续上涨,而且导入的速度会越来越慢,已经将进程结束掉了。 目前准备采用@coolmenu 的方案,正在将json文件过滤并处理成csv格式,处理速度比插入数据库快很多大概每100万行40秒的样子。

    20 条回复    2017-12-16 18:30:31 +08:00
    strahe
        1
    strahe  
       2017-12-14 10:19:46 +08:00
    之前将几千万条股票数据插入 mongo 大概也是这个思路, 一台办公的笔记本执行程序, mongo 在内网的服务器上, 基本都能达到你这速度.
    mhycy
        2
    mhycy  
       2017-12-14 10:22:59 +08:00
    压力应该在 IO,看看磁盘延迟,8400 条 /秒这个速度可以了
    billion
        3
    billion  
       2017-12-14 10:41:26 +08:00
    不太了解 Postgresql,但是我之前导入 40G 的 JSON 到 MongoDB,使用了 gevent 的 monkey_patch(),它可以让 pymongo 支持异步,性能比多线程高很多。
    likuku
        4
    likuku  
       2017-12-14 11:10:06 +08:00   ❤️ 1
    [服务器板载 RAID 无法使用,用 windows 的带区卷将两块硬盘组成一个卷,极限 io 也就 170MB/s]

    这算软件 RAID0 ?胆子够壮... 这要是纯玩耍环境,无所谓,正式环境...老板知道了,肯定打死你。

    机械硬盘 IOPS 很低的,极大限制 DB 操作。
    shepherd
        5
    shepherd  
    OP
       2017-12-14 11:25:04 +08:00
    @likuku 也不是正式的生产环境,算是提供一些数据支撑吧,最后还是用了单块硬盘。
    lianz
        6
    lianz  
       2017-12-14 11:30:02 +08:00
    @likuku 机械硬盘的顺序写速度还是可以的,楼主这种情况基本上就是顺序写。好点的硬盘可以达到几百 M/s,不过随机 IOPS 确实低,这个没办法。
    togodo
        7
    togodo  
       2017-12-14 11:33:54 +08:00   ❤️ 1
    两块盘单独用是非常正确的做法
    HowardMei
        8
    HowardMei  
       2017-12-14 11:36:17 +08:00   ❤️ 1
    最起码有 3 种:
    1. 用 COPY https://stackoverflow.com/questions/8144002/use-binary-copy-table-from-with-psycopg2/8150329#8150329

    2. 多值插入 + 异步 调整参数 synchronous_commit=off 和 commit_delay 降低 fsync 消耗

    3. 用 TRANSACTION 把批量插入动作包含在内,一次性提交,跟 2 差不多
    oyosc
        9
    oyosc  
       2017-12-14 11:37:11 +08:00
    copy_from 你值得拥有
    bkmi
        10
    bkmi  
       2017-12-14 11:38:10 +08:00 via Android
    python 弱弱的问一句,不是讲 python 的多线程并没有真的并行吗
    exch4nge
        11
    exch4nge  
       2017-12-14 11:47:33 +08:00
    以下纯靠 Google 搜索跟已有知识猜的,本人没试过。

    搜索以下发现 postgres 有叫 COPY 的命令,感觉会比 insert 快一些…… 大概方案是,先用 python 处理 json 生成适用于 COPY 命令的文件 A,然后为了速度,最好把这个文件放在内存盘中,然后调用 postgres 的 COPY 倒入进去。循环。根据内存大小决定每次处理多少个。
    min
        12
    min  
       2017-12-14 12:11:08 +08:00   ❤️ 1
    github 上有个项目是帮助导入 csv 和 json 的
    另外就是用 postgresql 的 copy 命令
    shepherd
        13
    shepherd  
    OP
       2017-12-14 12:21:05 +08:00
    @min pgfutter 这个工具导入 json 时需要先将 json 整行作为一个单元格导入,然后利用 postgres 对 json 类型的支持,重新插入到一个新表里面。不过这个工具真的挺快的。但因为我们要对 json 做一些简单的过滤,所以流程上还是感觉复杂了点。
    coolmenu
        14
    coolmenu  
       2017-12-14 12:30:00 +08:00   ❤️ 1
    先根据条件过滤,分成几个文件,然后用 copy,尽量利用数据库提供的工具,他会绕过一些数据库的转换等,如 oracle 的 sqlldr 就比批量 insert 要快
    catcn
        15
    catcn  
       2017-12-14 12:30:55 +08:00
    不会 python 的吧,PIL 的存在,首先就应该考虑多进程。
    shepherd
        16
    shepherd  
    OP
       2017-12-14 15:06:51 +08:00
    @coolmenu copy 果然强悍,拿 10G 数据做测试,硬盘 io 平均可以到 60M/s,峰值有 110M/s。
    likuku
        17
    likuku  
       2017-12-14 15:08:45 +08:00
    @lianz 连续读写少量文件还 OK
    likuku
        18
    likuku  
       2017-12-14 15:09:36 +08:00
    @shepherd [硬盘 io 平均可以到 60M/s,峰值有 110M/s。] ... 2009 年硬盘就可以达到了吧。
    shepherd
        19
    shepherd  
    OP
       2017-12-14 22:19:33 +08:00 via Android
    @likuku 万全 r510。。。。
    kuwater
        20
    kuwater  
       2017-12-16 18:30:31 +08:00
    pg 的话,大规模导入也是用 copy 的接口吧,
    另外可以考虑 pg_loader,看看能用不。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   969 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 20:23 · PVG 04:23 · LAX 13:23 · JFK 16:23
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.