磐维数据库pw_bulkload和copy入库性能对比测试

一、copy入库非分区表

1、数据准备

[omm@centos01 ~]$ cat emp.csv |wc -l
2752512
复制

[omm@centos01 ~]$ head -n 2 emp.csv 
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
复制

2、建表

t1=# create table emp_copy_nopart as select * from emp where 1=2;
INSERT 0 0
复制

3、copy入库

t1=# \timing on
启用计时功能.
t1=# \copy emp_copy_nopart from '/home/omm/emp.csv' csv;
时间：20813.486 ms
复制

copy入库非分区表2752512用时20秒左右

二、copy入库分区表

1、建表

t1=# create table emp_copy_part (
t1(# empno number(4,0) not null,
t1(# ename varchar(10),
t1(# job varchar(9),
t1(# mgr number(4,0),
t1(# hiredate date,
t1(# sal number(7,2),
t1(# comm number(7,2),
t1(# deptno number(2,0)
t1(# )
t1-# partition by range(sal)
t1-# (partition p1 values less than(1000),
t1(# partition p2 values less than(2000),
t1(# partition p3 values less than(3000),
t1(# partition p4 values less than(4000),
t1(# partition p5 values less than(5000),
t1(# partition p6 values less than(maxvalue));
CREATE TABLE
时间：22.879 ms
复制

2、copy入库分区表

t1=# \copy emp_copy_part from '/home/omm/emp.csv' csv;
时间：112504.459 ms
复制

copy入库分区表用时112秒左右

三、bulkload入库非分区表

1、建表

t1=# create table emp_bulk_nopart as select * from emp where 1=2;
INSERT 0 0
时间：17.106 ms
复制

2、bulkload入库

[omm@centos01 ~]$ pw_bulkload  -i /home/omm/emp.csv -O public.emp_bulk_nopart -d t1
NOTICE: BULK LOAD START
警告:  pw_bulkload entered
警告:  start transaction 77523
警告:  try to register 5 bgworkers and actually succeed in 5
警告:  bgworker threads 5 started, 0 hasn't started.
警告:  5 bgworkers started
警告:  all bgworkers ready.
警告:  starting Source reader ...
警告:    #0 loading slice #0 of file #0 by thread #140315251242752(bucket #0)
警告:  starting TABLE/PARTITION writer ...
警告:    #1 writing thread #140315219719936(bucket #1)
警告:    #2 terminating thread #140315377202944(bucket #2)
警告:    #3 terminating thread #140315299542784(bucket #3)
警告:    #4 terminating thread #140315150382848(bucket #4)
警告:  
4.all thread start: 0.04
警告:  thread #140315251242752 end loading, shutting it
警告:  all readers(1/1) end loading.
警告:  
5.all thread done: 5.97
警告:  writing termination to queue.
警告:  waiting all sub-threads quit ...
警告:  
6.all thread close: 2.00
信息:  
  0 Rows skipped.
  2752512 Rows successfully loaded.

信息:  
Run began on 2024-08-01 15:44:39.407917+08
Run ended on 2024-08-01 15:44:47.415777+08

CPU 0.01s/0.02u sec elapsed 8.01 sec
复制

bulkload入库非分区表用户8秒，比copy入库分区表效率高2.5倍左右

四、bulkload入库分区表

1、建表

t1=# create table emp_bulk_part (
t1(# empno number(4,0) not null,
t1(# ename varchar(10),
t1(# job varchar(9),
t1(# mgr number(4,0),
t1(# hiredate date,
t1(# sal number(7,2),
t1(# comm number(7,2),
t1(# deptno number(2,0)
t1(# )
t1-# partition by range(sal)
t1-# (partition p1 values less than(1000),
t1(# partition p2 values less than(2000),
t1(# partition p3 values less than(3000),
t1(# partition p4 values less than(4000),
t1(# partition p5 values less than(5000),
t1(# partition p6 values less than(maxvalue));

CREATE TABLE
t1=# 
t1=# \d+ emp_bulk_part
                  表 "public.emp_bulk_part"
   栏位   |    型别     | 修饰词 |   存储   | 统计目标 | 描述 
----------+-------------+--------+----------+----------+------
 empno    | number(4,0) | 非空   | plain    |          | 
 ename    | varchar(10) |        | extended |          | 
 job      | varchar(9)  |        | extended |          | 
 mgr      | number(4,0) |        | plain    |          | 
 hiredate | date        |        | plain    |          | 
 sal      | number(7,2) |        | plain    |          | 
 comm     | number(7,2) |        | plain    |          | 
 deptno   | number(2,0) |        | plain    |          | 
Partition By RANGE(sal)
分区个数：6（查看pg_partition以检查各分区范围。）
有 OIDs: 否
选项: orientation=row, compression=no, fillfactor=80
复制

2、入库

[omm@centos01 ~]$ pw_bulkload  -i /home/omm/emp.csv -O public.emp_bulk_part -d t1
NOTICE: BULK LOAD START
警告:  pw_bulkload entered
警告:  start transaction 77527
警告:  try to register 10 bgworkers and actually succeed in 10
警告:  bgworker threads 10 started, 0 hasn't started.
警告:  10 bgworkers started
警告:  all bgworkers ready.
警告:  starting Source reader ...
警告:    #0 loading slice #0 of file #0 by thread #140315150382848(bucket #0)
警告:  starting TABLE/PARTITION writer ...
警告:    #1 writing thread #140315102082816(bucket #1)
警告:    #2 writing thread #140315085301504(bucket #2)
警告:    #3 writing thread #140315051738880(bucket #3)
警告:    #4 writing thread #140315034957568(bucket #4)
警告:    #5 writing thread #140315018176256(bucket #5)
警告:    #6 writing thread #140314844063488(bucket #6)
警告:    #7 terminating thread #140314860844800(bucket #7)
警告:    #8 terminating thread #140315068520192(bucket #8)
警告:    #9 terminating thread #140315251242752(bucket #9)
警告:  
4.all thread start: 0.10
警告:  thread #140315150382848 end loading, shutting it
警告:  all readers(1/1) end loading.
警告:  
5.all thread done: 66.85
警告:  writing termination to queue.
警告:  waiting all sub-threads quit ...
警告:  
6.all thread close: 2.00
信息:  
  0 Rows skipped.
  2752512 Rows successfully loaded.

信息:  
Run began on 2024-08-01 15:49:06.027852+08
Run ended on 2024-08-01 15:50:14.978071+08

CPU 0.21s/0.08u sec elapsed 68.95 sec
复制

bulkload入库分区表用户69秒左右，比copy入库分区表快1.5倍左右。

五、copy入库列存非分区表

1、建表

t1=# create table emp_bulk_nopart_col WITH (ORIENTATION = COLUMN) as select * from emp_copy_nopart where 1=2;
INSERT 0 0
t1=# \d+ emp_bulk_nopart_col
               表 "public.emp_bulk_nopart_col"
   栏位   |    型别     | 修饰词 |   存储   | 统计目标 | 描述 
----------+-------------+--------+----------+----------+------
 empno    | number(4,0) |        | plain    |          | 
 ename    | varchar(10) |        | extended |          | 
 job      | varchar(9)  |        | extended |          | 
 mgr      | number(4,0) |        | plain    |          | 
 hiredate | date        |        | plain    |          | 
 sal      | number(7,2) |        | plain    |          | 
 comm     | number(7,2) |        | plain    |          | 
 deptno   | number(2,0) |        | plain    |          | 
有 OIDs: 否
选项: orientation=column, compression=low
复制

t1=# \d+ emp_copy_nopart_col
               表 "public.emp_copy_nopart_col"
   栏位   |    型别     | 修饰词 |   存储   | 统计目标 | 描述 
----------+-------------+--------+----------+----------+------
 empno    | number(4,0) |        | plain    |          | 
 ename    | varchar(10) |        | extended |          | 
 job      | varchar(9)  |        | extended |          | 
 mgr      | number(4,0) |        | plain    |          | 
 hiredate | date        |        | plain    |          | 
 sal      | number(7,2) |        | plain    |          | 
 comm     | number(7,2) |        | plain    |          | 
 deptno   | number(2,0) |        | plain    |          | 
有 OIDs: 否
选项: orientation=column, compression=low

复制

2、入库

\copy emp_bulk_nopart_col from '/home/omm/emp.csv' csv;
复制

t1=# \timing on
启用计时功能.
t1=# \copy emp_bulk_nopart_col from '/home/omm/emp.csv' csv;
时间：6755.401 ms
复制

六、copy入库列存分区表

1、建表

t1=# create table emp_copy_part_col (
t1(# empno number(4,0) not null,
t1(# ename varchar(10),
t1(# job varchar(9),
t1(# mgr number(4,0),
t1(# hiredate date,
t1(# sal number(7,2),
t1(# comm number(7,2),
t1(# deptno number(2,0)
t1(# ) WITH (ORIENTATION = COLUMN)
t1-# partition by range(sal)
t1-# (partition p1 values less than(1000),
t1(# partition p2 values less than(2000),
t1(# partition p3 values less than(3000),
t1(# partition p4 values less than(4000),
t1(# partition p5 values less than(5000),
t1(# partition p6 values less than(maxvalue));
CREATE TABLE
时间：148.498 ms
复制

2、入库

t1=# \copy emp_copy_part_col from '/home/omm/emp.csv' csv;
时间：58495.303 ms
复制

七、bulkload入库列存非分区表

1、建表

t1=# \d+ emp_bulk_nopart_col
               表 "public.emp_bulk_nopart_col"
   栏位   |    型别     | 修饰词 |   存储   | 统计目标 | 描述 
----------+-------------+--------+----------+----------+------
 empno    | number(4,0) |        | plain    |          | 
 ename    | varchar(10) |        | extended |          | 
 job      | varchar(9)  |        | extended |          | 
 mgr      | number(4,0) |        | plain    |          | 
 hiredate | date        |        | plain    |          | 
 sal      | number(7,2) |        | plain    |          | 
 comm     | number(7,2) |        | plain    |          | 
 deptno   | number(2,0) |        | plain    |          | 
有 OIDs: 否
选项: orientation=column, compression=low

复制

2、入库

[omm@centos01 ~]$ pw_bulkload  -i /home/omm/emp.csv -O public.emp_bulk_nopart_col -d t1
NOTICE: BULK LOAD START
警告:  loading pw_bulkload module ...
警告:  initializing pw_bulkload ...
警告:  pw_bulkload entered
警告:  ERROR IN vbbl_do_bulkload
ERROR: query failed: 错误:  pw_bulkload does not support CStore table.
DETAIL: query was: SELECT * FROM vbbl.pw_bulkload($1)
复制

不支持

八、对比

copy入库列存非分区表(7秒)<bulkload入库非分区表(8秒)<copy入库非分区表(20秒)<copy入库列存分区表(58秒)<bulkload入库分区表(69秒)<copy入库分区表(112秒)