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

Question

1. 问题说明：
   目前有一个文本格式的json数据，里面数据量约15亿，每一行数据的格式都是固定的，插入前先要将json反序列化。运行环境为：windows server 2016，postgresql 10，Xeon E5506@2.13GHz，16G ddr3 1333，硬盘io上限约120MB/s，服务器板载RAID无法使用，用windows的带区卷将两块硬盘组成一个卷，极限io也就170MB/s，性价比太低就没做，直接使用了单块硬盘。

2. 思路和伪代码：
   基本思路，遍历json文本，每100万行文本做一次插入。插入时，将100万行文本切割成小的分组用多线程的方式并行插入，每个线程每次都建立一个新的数据库连接并处理一个分组。待100万行文本处理完毕后再继续遍历json。

   首先进行一些数据库的基本优化：

   • 创建数据库和表，将表设置为unlogged
   • 开启postgresql的异步提交

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

Answer 1

import.json文件有多大?一些建议:

1.数据库参数需要优化
    shared_buffers, max_connections, maintenance_work_mem, effective_cache_size, sync, commit_delay , commit_siblings ,checkpoint_segments, wal_buffers, wal_writer_delay,
2. 是不是可以先处理下文件,按照COPY的文件格式格式化好,再用cppy命令导入,或者外部表,但处理文件也会稍微麻烦些