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