標籤:ima arch art jdbc 記憶體參數 gre 記錄 run 系統
PostgreSQL 9.5.4資料庫快速INSERT大量資料研究背景
在一些應用情境中,需要向Abase資料庫中快速裝入大量的資料,比如資料庫遷移,SQL日誌分析等。在PG上快速插入資料有幾種方案,每種方案的效率怎麼樣?如何調優能加快的資料裝載?
情境設定
SQL日誌分析是一個採集JDBC日誌、分析SQL、發送分析結果工具。在分析階段,需要解析大量的JDBC日誌,並把解析後的結構化結果裝入資料庫供後續處理。以分析階段為實驗情境,以解析JDBC日誌(多個)為開始,以完成結構化資料裝入(包過索引建立完成)為結束,來測試不同方案的資料裝入效率。
環境準備
名稱 |
值 |
作業系統 |
CENTOS 6.5 |
CPU |
Intel(R) Xeon(R) CPU E5-2698 v3 @ 2.30GHz,邏輯64核 |
記憶體 |
316G |
磁碟 |
RAID 10,寫入速度1GB/s |
資料庫版本 |
PostgreSQL 9.5.4 |
資料庫記憶體參數 |
shared_buffers:30G work_mem:4MB maintenance_work_mem:64MB |
資料庫CPU參數 |
max_worker_processes:16 |
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);
-- 非同步提交,更改完重啟資料庫 alter system set synchronous_commit to off; -- unlogged table create unlogged table t_jdbc_sql_record ... create unlogged table t_jdbc_sql_content ...
方案名稱 |
方案描述 |
方案一 |
建立結構化表及其索引,多線程單個insert裝入資料 |
方案二 |
建立結構化表及其索引,多線程批量insert裝入資料 |
方案三 |
建立結構化表及其索引,庫設定為非同步提交,多線程批量insert裝入資料 |
方案四 |
建立結構化表,庫設定為非同步提交,多線程批量insert裝入資料,建立索引 |
方案五 |
建立結構化表及其索引,表設定為unlogged table,多線程批量insert裝入資料 |
方案六 |
建立結構化表,表設定為unlogged table,多線程批量insert裝入資料,建立索引 |
方案七 |
建立結構化表,多線程批量insert裝入資料,建立索引 |
實驗結果
每次實驗時,解析的JDBC日誌量,解析代碼和中介軟體環境保持不變。只調整流程順序和資料庫參數。
實驗次數 |
方案一 |
方案二 |
方案三 |
方案四 |
方案五 |
方案六 |
方案七 |
第一次 |
3596s |
2043s |
1164s |
779s |
545s |
528s |
1192s |
第二次 |
4092s |
2068s |
1283s |
843s |
528s |
528s |
1227s |
第三次 |
3891s |
2177s |
1378s |
858s |
536s |
537s |
1248s |
平均值 |
3859s |
2096s |
1275s |
826s |
536s |
531s |
1222s |
結果分析
- 方案一、方案二比較,資料庫參數不變,流程順序不變
- 方案一:單個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,需要繼續分析。
PostgreSQL 9.5.4資料庫快速INSERT大量資料研究