目录

 

测试环境

表结构

CASE1

结果

CASE 2

结果

CASE 3

结果

TPS修正

CASE 4

结果

结论

---

测试环境

Intel(R) Core(TM) i7-4790 CPU @ 3.60GHz

32G memory

SSD harddisk.

postgresql.conf

max_connections = 256 shared_buffers = 3GB # min 128kB work_mem = 16MB # min 64kB maintenance_work_mem = 256MB # min 1MB wal_buffers = 4MB checkpoint_timeout = 10min # range 30s-1d max_wal_size = 2GB checkpoint_completion_target = 0.9

表结构

zj_testdb=# \d+ t1 Table "public.t1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+--------+-----------+----------+---------+---------+--------------+------------- c1 | bigint | | | | plain | | c2 | bigint | | | | plain | | c3 | bigint | | | | plain | | c4 | bigint | | | | plain | | c5 | bigint | | | | plain | | c6 | bigint | | | | plain | | c7 | bigint | | | | plain | | c8 | bigint | | | | plain | | c9 | bigint | | | | plain | | c10 | bigint | | | | plain | | c11 | bigint | | | | plain | | c12 | bigint | | | | plain | | c13 | bigint | | | | plain | | c14 | bigint | | | | plain | | c15 | bigint | | | | plain | | c16 | bigint | | | | plain | | c17 | bigint | | | | plain | | c18 | bigint | | | | plain | | c19 | bigint | | | | plain | | c20 | bigint | | | | plain | | c21 | bigint | | | | plain | | c22 | bigint | | | | plain | | c23 | bigint | | | | plain | | c24 | bigint | | | | plain | | c25 | bigint | | | | plain | | c26 | bigint | | | | plain | | c27 | bigint | | | | plain | | c28 | bigint | | | | plain | | c29 | bigint | | | | plain | | c30 | bigint | | | | plain | | c31 | bigint | | | | plain | | c32 | bigint | | | | plain | | c33 | bigint | | | | plain | | c34 | bigint | | | | plain | | c35 | bigint | | | | plain | | c36 | bigint | | | | plain | | c37 | bigint | | | | plain | | c38 | bigint | | | | plain | | c39 | bigint | | | | plain | | c40 | bigint | | | | plain | | c41 | bigint | | | | plain | | c42 | bigint | | | | plain | | c43 | bigint | | | | plain | | c44 | bigint | | | | plain | | c45 | bigint | | | | plain | | c46 | bigint | | | | plain | | c47 | bigint | | | | plain | | c48 | bigint | | | | plain | | c49 | bigint | | | | plain | | c50 | bigint | | | | plain | | c51 | bigint | | | | plain | | c52 | bigint | | | | plain | | c53 | bigint | | | | plain | | c54 | bigint | | | | plain | | c55 | bigint | | | | plain | | c56 | bigint | | | | plain | | c57 | bigint | | | | plain | | c58 | bigint | | | | plain | | c59 | bigint | | | | plain | | c60 | bigint | | | | plain | | c61 | bigint | | | | plain | | c62 | bigint | | | | plain | | c63 | bigint | | | | plain | | c64 | bigint | | | | plain | | c65 | bigint | | | | plain | | c66 | bigint | | | | plain | | c67 | bigint | | | | plain | | c68 | bigint | | | | plain | | c69 | bigint | | | | plain | | c70 | bigint | | | | plain | | c71 | bigint | | | | plain | | c72 | bigint | | | | plain | | c73 | bigint | | | | plain | | c74 | bigint | | | | plain | | c75 | bigint | | | | plain | | c76 | bigint | | | | plain | | c77 | bigint | | | | plain | | c78 | bigint | | | | plain | | c79 | bigint | | | | plain | | c80 | bigint | | | | plain | | c81 | bigint | | | | plain | | c82 | bigint | | | | plain | | c83 | bigint | | | | plain | | c84 | bigint | | | | plain | | c85 | bigint | | | | plain | | c86 | bigint | | | | plain | | c87 | bigint | | | | plain | | c88 | bigint | | | | plain | | c89 | bigint | | | | plain | | c90 | bigint | | | | plain | | c91 | bigint | | | | plain | | c92 | bigint | | | | plain | | c93 | bigint | | | | plain | | c94 | bigint | | | | plain | | c95 | bigint | | | | plain | | c96 | bigint | | | | plain | | c97 | bigint | | | | plain | | c98 | bigint | | | | plain | | c99 | bigint | | | | plain | | c100 | bigint | | | | plain | | Tablespace: "zj_tablespace"

CASE1

batchsize=100, 8 threads

test_insert.sql

[root@server4 zjcktest]# cat test_insert.sql insert into t1 select generate_series(1,100),1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1

copy csv

copy (select generate_series(1,100),1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1) to '/data/zjcktest/csv';

copy.sql

[root@server4 zjcktest]# cat copy.sql copy t1 from '/data/zjcktest/csv' ;

pgbench命令执行insert和copy

INSERT: pgbench -M prepared -n -r -P 1 -f /data/zjcktest/test_insert.sql -h 127.0.0.1 -p 5432 -U postgres zj_testdb -c 8 -j 8 -T 120 COPY: pgbench -M prepared -n -r -P 1 -f /data/zjcktest/copy.sql -h 127.0.0.1 -p 5432 -U postgres zj_testdb -c 8 -j 8 -T 120

结果

两者速率相差不大，copy略快5%

8 threads batchsize=100行数事务数TPS batch 100 insert select120221001202211000.835batch 100 copy127711001277111057.615    batch 100 insert select129455001294551077.917batch 100 copy135522001355221120.751    batch 100 insert select131649001316491092.143batch 100 copy132996001329961107.717

 

CASE 2

1 thread, batchsize=100

修改pgbench执行命令

INSERT: pgbench -M prepared -n -r -P 1 -f /data/zjcktest/test_insert.sql -h 127.0.0.1 -p 5432 -U postgres zj_testdb -c 1 -j 1 -T 120 COPY: pgbench -M prepared -n -r -P 1 -f /data/zjcktest/copy.sql -h 127.0.0.1 -p 5432 -U postgres zj_testdb -c 1 -j 1 -T 120

结果

比8线程慢25%。 copy和insert很接近，insert比copy略好3%。

1 thread, batchsize=100行数事务数TPS batch 100 insert select900310090031750.254batch 100 copy890370089037741.454    batch 100 insert select897130089713747.577batch 100 copy879870087987733.224    batch 100 insert select883990088399736.622batch 100 copy877630087763731.356    batch 100 insert select904290090429753.572batch 100 copy882740088274735.607

 

CASE 3

1 thread, batchsize=1000

修改test_insert.sql和生成新的copy文件

copy (select generate_series(1,1000),1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1) to '/data/zjcktest/csv'; [root@server4 zjcktest]# cat test_insert.sql insert into t1 select generate_series(1,1000),1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1

结果

仍旧insert稍快 4%。 

1 thread, batchsize=1000行数事务数TPS batch 100 insert select111790001117993batch 100 copy107940001079489.948

 

TPS修正

考虑到copy需要读取文件，按照金士顿 SSD 读取500MB/s来计算

总读取字节读取速率cost time修正后TPS21792330425242880004.16 s93.18 

去掉读文件的时间，总的TPS和insert几乎一致。

 

CASE 4

1 thread, batchsize = 1

修改test_insert.sql 和 重新生成copy文件csv

cat test_insert.sql insert into t1 values (5,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1) zj_testdb=# copy (select 5,1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1,1, 1,1,1,1,1,1,1,1,1) to '/data/zjcktest/csv';

结果

insert要比copy快 15%. 

由于单行频繁打开关闭文件句柄导致copy性能下降。

1 thread, batchsize=1行数事务数TPS insert 8356328356326963.591copy6969416969415807.831

 

结论

在test_insert.sql中，使用generate_series 而不是显示一行一行insert语句的情况下，insert 整体性能和copy基本一致，单线程情况下insert性能更好。

对比test_insert.sql里有100行insert语句的测试，这里少了绝大部分的SQL语句的解析，性能与copy一样甚至超过。