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

  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使用不平衡。
    此外,是否还有其他可以优化的地方,无论是从语言上,还是处理逻辑上,还是数据库配置上。
阅读 3.4k
评论
    1 个回答
    • 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命令导入,或者外部表,但处理文件也会稍微麻烦些
      撰写回答

      登录后参与交流、获取后续更新提醒

      相似问题
      推荐文章