PostgreSQL快速导入千万条数据

2022年3月2日 1308点热度 0人点赞 0条评论

为了与MySQL做个对比，做一个PG的数据导入测试，使用COPY方式，测试环境保持一致，具体如下所述。

一、测试环境

■ 48CPU/376G

CPU(s):                48
Thread(s) per core:    2
Core(s) per socket:    12
Socket(s):             2
Model name:            Intel(R) Xeon(R) Gold 5118 CPU @ 2.30GHz
L1d cache:             32K
L1i cache:             32K
L2 cache:              1024K
L3 cache:              16896K

■ DISK

Model: AVAGO HW-SAS3508 (scsi)
Disk /dev/sdb: 12.0TB
test write：1.1GB/s
test read： 3.9GB/s

■ OS
Red Hat Enterprise Linux Server 7.4 (Maipo)
■ FS

Filesystem                  Type      Size  Used Avail Use% Mounted on
/dev/mapper/vgora-lv_u01    xfs        11T  792G   11T   8% /u01

■ db version

[postgres@adg1:2 ~]$ psql -V
psql (PostgreSQL) 14.2
[postgres@adg1:2 ~]$ pg_ctl -V
pg_ctl (PostgreSQL) 14.2

二、修改源数据为COPY可用的格式

源数据：与MySQL测试保持一致
格式：MySQL导出的DOS文本文件格式(\n\r)，修改为unix文本文件格式(\n)
【此处使用csv格式】
使用head、awk、sed工具取出所需的记录行，并修改各行数据格式。
由于源数据存在\'，导致无法正确导入，需把\'替换成"或其他字符。
sed -i "s/\\\'/\"/g" mydata.sql

三、DDL

■ 根据最原始的源数据，调整DDL语句

CREATE TABLE tablename (
  id int4 NOT NULL,
  code varchar(100) DEFAULT NULL,
  init_value text,
  master_id text DEFAULT NULL,
  code_id varchar(11) DEFAULT NULL,
  end_value text
);
ALTER TABLE tablename ADD PRIMARY KEY (id);
CREATE INDEX tablename_master_id ON tablename USING btree (master_id);
CREATE INDEX tablename_code ON tablename USING btree (code);

四、COPY

COPY tablename FROM '/u01/pg/mydata.sql' WITH DELIMITER ',' CSV QUOTE '''';
耗时记录如下：

■ 100万
COPY 1000000
Time: 8938.071 ms (00:08.938)

■ 500万
COPY 4999966
Time: 70209.830 ms (01:10.210)
COPY 5000000
Time: 56365.434 ms (00:56.365)
COPY 5000000
Time: 65061.424 ms (01:05.061)

■ 2000万
COPY 19999961
Time: 257502.571 ms (04:17.503)
COPY 19999962
Time: 252777.564 ms (04:12.778)
COPY 20000000
Time: 250620.213 ms (04:10.620)
COPY 10578983
Time: 138167.048 ms (02:18.167)

■ 3000万
COPY 30578975
Time: 397111.329 ms (06:37.111)
COPY 30578983
Time: 391768.993 ms (06:31.769)
COPY 30578983
Time: 388049.729 ms (06:28.050)