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

[shepherd]

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 使用不平衡。
此外，是否还有其他可以优化的地方，无论是从语言上，还是处理逻辑上，还是数据库配置上。

手机上不支持 md 语法，本来想上电脑上编辑一下的，还没来得及修改就有回复了。

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

[strahe]

之前将几千万条股票数据插入 mongo 大概也是这个思路, 一台办公的笔记本执行程序, mongo 在内网的服务器上, 基本都能达到你这速度.

[mhycy]

压力应该在 IO，看看磁盘延迟，8400 条 /秒这个速度可以了

[billion]

不太了解 Postgresql，但是我之前导入 40G 的 JSON 到 MongoDB，使用了 gevent 的 monkey_patch()，它可以让 pymongo 支持异步，性能比多线程高很多。

[likuku]

[服务器板载 RAID 无法使用，用 windows 的带区卷将两块硬盘组成一个卷，极限 io 也就 170MB/s]

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

机械硬盘 IOPS 很低的，极大限制 DB 操作。

[shepherd]

@likuku 也不是正式的生产环境，算是提供一些数据支撑吧，最后还是用了单块硬盘。

[lianz]

@likuku 机械硬盘的顺序写速度还是可以的，楼主这种情况基本上就是顺序写。好点的硬盘可以达到几百 M/s，不过随机 IOPS 确实低，这个没办法。

[togodo]

两块盘单独用是非常正确的做法

[HowardMei]

最起码有 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]

copy_from 你值得拥有

[bkmi]

python 弱弱的问一句，不是讲 python 的多线程并没有真的并行吗

[exch4nge]

以下纯靠 Google 搜索跟已有知识猜的，本人没试过。

搜索以下发现 postgres 有叫 COPY 的命令，感觉会比 insert 快一些…… 大概方案是，先用 python 处理 json 生成适用于 COPY 命令的文件 A，然后为了速度，最好把这个文件放在内存盘中，然后调用 postgres 的 COPY 倒入进去。循环。根据内存大小决定每次处理多少个。

[min]

github 上有个项目是帮助导入 csv 和 json 的
另外就是用 postgresql 的 copy 命令

[shepherd]

@min pgfutter 这个工具导入 json 时需要先将 json 整行作为一个单元格导入，然后利用 postgres 对 json 类型的支持，重新插入到一个新表里面。不过这个工具真的挺快的。但因为我们要对 json 做一些简单的过滤，所以流程上还是感觉复杂了点。

[coolmenu]

先根据条件过滤，分成几个文件，然后用 copy,尽量利用数据库提供的工具，他会绕过一些数据库的转换等，如 oracle 的 sqlldr 就比批量 insert 要快

[catcn]

不会 python 的吧，PIL 的存在，首先就应该考虑多进程。

[shepherd]

@coolmenu copy 果然强悍，拿 10G 数据做测试，硬盘 io 平均可以到 60M/s，峰值有 110M/s。

[likuku]

@lianz 连续读写少量文件还 OK

[likuku]

@shepherd [硬盘 io 平均可以到 60M/s，峰值有 110M/s。] ... 2009 年硬盘就可以达到了吧。

[shepherd]

@likuku 万全 r510。。。。

[kuwater]

pg 的话，大规模导入也是用 copy 的接口吧，
另外可以考虑 pg_loader，看看能用不。