PostgreSQL数据库如何快速插入大量数据
来源:互联网 发布:易众网络 编辑:程序博客网 时间:2024/05/21 08:40
PostgreSQL数据库如何快速插入大量数据
背景
在一些应用场景中,需要向PostgreSQL数据库中快速装入大量的数据,比如数据库迁移,SQL日志分析等。在Abase上快速插入数据有几种方案,每种方案的效率怎么样?如何调优能加快的数据装载?
场景设定
SQL日志分析是一个采集JDBC日志、分析SQL、发送分析结果工具。在分析阶段,需要解析大量的JDBC日志,并把解析后的结构化结果装入数据库供后续处理。以分析阶段为实验场景,以解析JDBC日志(多个)为开始,以完成结构化数据装入(包过索引建立完成)为结束,来测试不同方案的数据装入效率。
环境准备
- 数据库环境
- 建表语句
drop table if exists T_JDBC_SQL_RECORD ;--无主键,没有用到C_BH 查询,增加insert速度先去掉create table T_JDBC_SQL_RECORD (C_BH VARCHAR(32) ,C_BH_PARSE VARCHAR(32) NULL,C_BH_GROUP VARCHAR(32) NULL,C_BH_SQL VARCHAR(32) NULL,DT_ZXSJ TIMESTAMP NULL,N_RUNTIME INT NULL,C_RZLJ VARCHAR(600) NULL,N_STARTLINE INT NULL,N_ENDLINE INT NULL,N_SQLTYPE INT NULL,N_SQLCOMPLEX INT NULL,C_IP VARCHAR(100) NULL,C_PORT VARCHAR(100) NULL,C_XTBS VARCHAR(100) NULL,N_CHECKSTATUS INT default 0,N_SQL_LENGTH INT NULL,N_SQL_BYTE INT NULL,N_5MIN INT NULL,C_METHOD VARCHAR(600) NULL,C_PSSQL_HASH VARCHAR(300) NULL,N_IS_BATCH INT,N_RESULTSET INT);drop table if exists T_JDBC_SQL_CONTENT ;CREATE TABLE T_JDBC_SQL_CONTENT (C_BH VARCHAR(32) NOT NULL,C_PSSQL_HASH VARCHAR(300) NULL,C_SQL_TEXT varchar NULL,C_PSSQL_TEXT varchar NULL);
- 索引语句
create index i_jdbc_sql_record_zh01 on t_jdbc_sql_record(c_bh_group,dt_zxsj,N_CHECKSTATUS,C_PSSQL_HASH);create index i_jdbc_sql_record_pshash on t_jdbc_sql_record(c_pssql_hash);create index i_jdbc_sql_content_pshash on t_jdbc_sql_content(c_pssql_hash);alter table t_jdbc_sql_content add constraint t_jdbc_sql_content_pkey primary key (C_BH);
- 异步提交和unlogged table
-- 异步提交,更改完重启数据库 alter system set synchronous_commit to off; -- unlogged table create unlogged table t_jdbc_sql_record ... create unlogged table t_jdbc_sql_content ...
- JDBC日志量
19个JDBC日志文件,共2G日志,600万记录
方案设定
实验结果
每次实验时,解析的JDBC日志量,解析代码和中间件环境保持不变。只调整流程顺序和数据库参数。
结果分析
方案一、方案二比较,数据库参数不变,流程顺序不变
- 方案一:单个insert提交,用时3859秒
- 方案二:批量insert提交,用时2096秒
方案二、方案三、方案五比较,流程顺序不变,均为建表->建索引->多线程批量插入。
- 方案二:同步提交(等待WAL日志完成),用时2096秒
- 方案三:异步提交(不等待WAL日志完成),用时1275秒
- 方案五:不记录WAL日志,用时536秒
方案二、方案七比较,均为同步提交
- 方案二:插入数据前建立索引,用时2096秒
- 方案七:插入数据后建立索引,用时1222秒
方案三、方案四比较,均为异步提交
- 方案三:插入数据前建立索引,用时1275秒
- 方案四:插入数据后建立索引,用时826秒
方案五、方案六比较,均为不记录WAL日志
- 方案五:插入数据前建立索引,用时536秒
- 方案六:插入数据后建立索引,用时531秒
总结
在该场景下:
- 批量提交比单个提交快55%
- 异步提交比同步提交快40%
- 不记录日志提交比同步提交快75%
- 记录日志且同步提交时,后建立索引比先建立索引快40%
- 记录日志且异步提交时,后建立索引比先建立索引快35%
- 不记录日志时,后建立索引比先建立索引略快,但差别不大
插入数据最快组合为:
unlogged table + 多线程批量insert+后建索引
猜想:
在insert过程中,维护索引的时间占总时间的35%到40%,且主要花费在日志持久化上。
其他:
同时在实验过程中的一些其他指标信息,如不同方案下数据库的写IO从未超过100MB/s,需要继续分析。
阅读全文
0 0
- PostgreSQL数据库如何快速插入大量数据
- 如何在sqlserver数据库中快速插入大量数据.
- c# 快速 将大量数据插入数据库
- Oracle如何快速、大量的插入数据
- 如何快速向Mysql插入大量数据?
- 如何快速往表里插入大量的数据行
- mysql快速插入大量数据
- 如何快速在数据库中插入数据
- 大量数据快速插入到DB
- redis快速的插入大量的数据
- mysql嵌套查询,快速插入大量数据
- 数据库压力测试--MySQL数据库快速插入大量数据
- 数据库快速插入数据
- java jdbc向数据库插入大量数据
- Postgresql快速写入\/读取大量数据(.net)
- Postgresql快速写入/读取大量数据(.net)
- 如何向mysql中插入大量数据
- ios-如果一次性向数据库中插入大量数据,应该如何处理
- [leetcode]88. Merge Sorted Array[facebook](9)
- 经典算法之Shell排序及其优化
- 最全的微软msdn原版windows系统镜像和office下载地址集锦
- C++实现:合并两个有序的数组,合并后的数组依然有序
- 字符(串);宏定义;getchar与putchar;变量;if与switch
- PostgreSQL数据库如何快速插入大量数据
- 记录菜鸟的第一次比赛
- Python网络编程 4.2 IPv6简介与现代地址解析--getaddrinfo()方法的使用
- A
- 关于lambda的一个小例子
- IOS 前端 时间乱码(ios时间显示NaN) 问题解决
- 64K引用限制
- java 8 函数式编程
- 《数字技术》连载1:第1章 概述 第1节 数和数字。