pgfincore安装

pgfincore

https://git.postgresql.org/gitweb


su - root
cd /opt/soft_bak
rz pgfincore-b2b53de
tar -zxvf pgfincore-b2b53de.tar.gz
cd pgfincore-b2b53de
which pg_config
export PATH=/opt/pgsql932/bin:$PATH
gmake clean
gmake
gmake install


create extension pgfincore


\df pgfad*


create table user_info(id int primary key, info text, crt_time timestamp);
insert into user_info select generate_series(1,500000), md5(random()::text), clock_timestamp();


select pg_total_relation_size('user_info');


select pg_total_relation_size('user_info')/1024/1024 MB


su - root


echo 3 > /proc/sys/vm/drop_caches  清理系统缓存


su - pg93




pgbench测试脚本
vi test.sql
\setrandom id 1 1000000
select * from user_info where id=:id;


pgbench -M prepared -n -r -f test.sql -c 16 -j 4 -T 10 postgres


select pgfadvise_willneed('user_info'::regclass);

select pgfadvise_willneed('user_info_pkey'::regclass);

select based on PK only

环境准备

单表1亿数据量，基于PK的查询。

考察高并发下的代码优化能力。

SQL如下

create table test(id int, info text, crt_time timestamp) with (autovacuum_freeze_max_age=1500000000, autovacuum_freeze_table_age=1400000000, autovacuum_multixact_freeze_max_age=1500000000, autovacuum_multixact_freeze_table_age=1400000000);
insert into test select generate_series(1,100000000),md5(random()::text),clock_timestamp();
set maintenance_work_mem='16GB';
alter table test add constraint test_pkey primary key (id);
vacuum analyze test;
select * from test limit 10;
 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 652802c64d630dfbde4770ed0d2a649c | 2016-10-02 15:38:12.866501
  2 | c31d0e4ddd63618dbbb1c2a7932eae87 | 2016-10-02 15:38:12.866581
  3 | f1689301bf26efd4050a88d50713ac66 | 2016-10-02 15:38:12.866586
  4 | 155df78e2cd8f14291ddfd3f9179cde3 | 2016-10-02 15:38:12.866589
  5 | 12aa2596dadb2af637bee07f05e78feb | 2016-10-02 15:38:12.866592
  6 | 915f06af99501e629631b37f46f23816 | 2016-10-02 15:38:12.866595
  7 | be79647d50351435b903c03a377e0ff5 | 2016-10-02 15:38:12.866597
  8 | 676bedb18ffe2c7cc30a0d7ff081e7da | 2016-10-02 15:38:12.8666
  9 | e7111e4c9f910ac00312f7a67ddbd162 | 2016-10-02 15:38:12.866602
 10 | 22c6dd399e49663f3f14ce7634ff56d8 | 2016-10-02 15:38:12.866604
(10 rows)

9.5

$ vi test.sql
\setrandom id 1 100000000
select * from test where id=:id;

$ vi bench.sh
pgbench -M prepared -n -r -f ./test.sql -c 16 -j 16 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 32 -j 32 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 64 -j 64 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 72 -j 72 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 86 -j 86 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 96 -j 96 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 128 -j 128 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 192 -j 192 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 256 -j 256 -T 120

$ . ./bench.sh

测试结果

并发数 , TPS

16 , 261687
32 , 514649
64 , 964129
72 , 946146
86 , 923699
96 , 931189
128 , 903589
192 , 891058
256 , 891150

9.6

$ vi test.sql
\set id random(1,100000000)
select * from test where id=:id;

$ vi bench.sh
pgbench -M prepared -n -r -f ./test.sql -c 16 -j 16 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 32 -j 32 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 64 -j 64 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 72 -j 72 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 86 -j 86 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 96 -j 96 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 128 -j 128 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 192 -j 192 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 256 -j 256 -T 120

$ . ./bench.sh

测试结果

并发数 , TPS

16 , 352524
32 , 611931
64 , 971911
72 , 994487
86 , 969640
96 , 970625
128 , 924109
192 , 893637
256 , 905555

对比

二、单表 update based on PK only

环境准备

单表1亿数据量，基于PK的更新。

考察高并发下的数据更新，autovacuum优化能力，XLOG优化能力。

SQL如下

create table test(id int, info text, crt_time timestamp) with (autovacuum_freeze_max_age=1500000000, autovacuum_freeze_table_age=1400000000, autovacuum_multixact_freeze_max_age=1500000000, autovacuum_multixact_freeze_table_age=1400000000);;
insert into test select generate_series(1,100000000),md5(random()::text),clock_timestamp();
set maintenance_work_mem='16GB';
alter table test add constraint test_pkey primary key (id);
vacuum analyze test;
select * from test limit 10;
 id |               info               |          crt_time          
----+----------------------------------+----------------------------
  1 | 652802c64d630dfbde4770ed0d2a649c | 2016-10-02 15:38:12.866501
  2 | c31d0e4ddd63618dbbb1c2a7932eae87 | 2016-10-02 15:38:12.866581
  3 | f1689301bf26efd4050a88d50713ac66 | 2016-10-02 15:38:12.866586
  4 | 155df78e2cd8f14291ddfd3f9179cde3 | 2016-10-02 15:38:12.866589
  5 | 12aa2596dadb2af637bee07f05e78feb | 2016-10-02 15:38:12.866592
  6 | 915f06af99501e629631b37f46f23816 | 2016-10-02 15:38:12.866595
  7 | be79647d50351435b903c03a377e0ff5 | 2016-10-02 15:38:12.866597
  8 | 676bedb18ffe2c7cc30a0d7ff081e7da | 2016-10-02 15:38:12.8666
  9 | e7111e4c9f910ac00312f7a67ddbd162 | 2016-10-02 15:38:12.866602
 10 | 22c6dd399e49663f3f14ce7634ff56d8 | 2016-10-02 15:38:12.866604
(10 rows)

9.5

$ vi test.sql
\setrandom id 1 100000000
update test set crt_time=now() where id=:id;

$ vi bench.sh
pgbench -M prepared -n -r -f ./test.sql -c 16 -j 16 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 32 -j 32 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 64 -j 64 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 72 -j 72 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 86 -j 86 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 96 -j 96 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 128 -j 128 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 192 -j 192 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 256 -j 256 -T 120

$ . ./bench.sh

并发数 , TPS

16 , 160502
32 , 202785
64 , 146669
72 , 136701
86 , 124060
96 , 116345
128 , 100642
192 , 76714
256 , 57945

9.6

$ vi test.sql
\set id random(1,100000000)
update test set crt_time=now() where id=:id;

$ vi bench.sh
pgbench -M prepared -n -r -f ./test.sql -c 16 -j 16 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 32 -j 32 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 64 -j 64 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 72 -j 72 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 86 -j 86 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 96 -j 96 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 128 -j 128 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 192 -j 192 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 256 -j 256 -T 120

$ . ./bench.sh

并发数 , TPS

16 , 216928
32 , 289555
64 , 249844
72 , 233400
86 , 214760
96 , 203196
128 , 178891
192 , 152073
256 , 129707

对比

三、单表 autocommit 单条 insert only

环境准备

一张空表，22个字段，每行约201字节，包含两个索引。

采用autocommit的模式，每个连接每个事务插入一条记录。

考察高并发下的数据插入，数据块扩展能力，XLOG优化能力。

SQL如下

create table test(id serial8, c1 int8 default 0, c2 int8 default 0, c3 int8 default 0, c4 int8 default 0, c5 int8 default 0, c6 int8 default 0, c7 int8 default 0, c8 int8 default 0, c9 int8 default 0, c10 int8 default 0, c11 int8 default 0, c12 int8 default 0, c13 int8 default 0, c14 int8 default 0, c15 int8 default 0, c16 int8 default 0, c17 int8 default 0, c18 int8 default 0, c19 int8 default 0, c20 int8 default 0, crt_time timestamptz) with (autovacuum_enabled=off, autovacuum_freeze_max_age=1500000000, autovacuum_freeze_table_age=1400000000, autovacuum_multixact_freeze_max_age=1500000000, autovacuum_multixact_freeze_table_age=1400000000); 
alter sequence test_id_seq cache 100000;
create index idx_test_1 on test using brin(id);
create index idx_test_2 on test using brin(crt_time);

测试脚本如下

$ vi test.sql
insert into test(crt_time) values(now());

$ vi bench.sh
pgbench -M prepared -n -r -f ./test.sql -c 16 -j 16 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 32 -j 32 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 64 -j 64 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 72 -j 72 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 86 -j 86 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 96 -j 96 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 128 -j 128 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 192 -j 192 -T 120
pgbench -M prepared -n -r -f ./test.sql -c 256 -j 256 -T 120

$ . ./bench.sh

9.5

并发数 , TPS

16 , 234043
32 , 263893
64 , 208993
72 , 199966
86 , 188826
96 , 182672
128 , 164270
192 , 130384
256 , 104563

9.6

并发数 , TPS

16 , 268877
32 , 313320
64 , 324775
72 , 318060
86 , 307001
96 , 296028
128 , 256317
192 , 202902
256 , 154469

对比

四、单表 autocommit 批量 insert only

环境准备

批量插入，考察的同样是高并发处理单表时XLOG的优化能力，数据文件的扩展优化能力。

测试脚本如下

一次插入400条记录。

$ vi test.sql
insert into test(crt_time) values(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now());


$ . ./bench.sh

9.5

并发数 , TPS

16 , 2875
32 , 2752
64 , 2534
72 , 2473
86 , 2424
96 , 2372
128 , 2362
192 , 2283
256 , 2140

9.6

并发数 , TPS

16 , 3450
32 , 3363
64 , 2905
72 , 2792
86 , 3155
96 , 3320
128 , 2992
192 , 3152
256 , 3070

对比

五、多表 autocommit 单条 insert only

环境准备

每个连接对应一张空表，22个字段，每行约201字节，包含两个索引。

采用autocommit的模式，每个连接每个事务插入一条记录。

考察高并发下的数据插入，XLOG优化能力。

与单表不同，因为没有单表的文件扩展并发要求，所以不考察数据块扩展能力。

SQL如下

create table test(id serial8, c1 int8 default 0, c2 int8 default 0, c3 int8 default 0, c4 int8 default 0, c5 int8 default 0, c6 int8 default 0, c7 int8 default 0, c8 int8 default 0, c9 int8 default 0, c10 int8 default 0, c11 int8 default 0, c12 int8 default 0, c13 int8 default 0, c14 int8 default 0, c15 int8 default 0, c16 int8 default 0, c17 int8 default 0, c18 int8 default 0, c19 int8 default 0, c20 int8 default 0, crt_time timestamptz) with (autovacuum_enabled=off, autovacuum_freeze_max_age=1500000000, autovacuum_freeze_table_age=1400000000, autovacuum_multixact_freeze_max_age=1500000000, autovacuum_multixact_freeze_table_age=1400000000);  
alter sequence test_id_seq cache 100000;
create index idx_test_1 on test using brin(id);
create index idx_test_2 on test using brin(crt_time);

批量创建测试表，测试脚本

for ((i=1;i<=256;i++)); do psql -c "create table test$i(like test including all) with (autovacuum_enabled=off, autovacuum_freeze_max_age=1500000000, autovacuum_freeze_table_age=1400000000, autovacuum_multixact_freeze_max_age=1500000000, autovacuum_multixact_freeze_table_age=1400000000)"; done

for ((i=1;i<=256;i++)); do echo "insert into test$i(crt_time) values(now());" > ~/test$i.sql; done

测试脚本如下

$ vi bench.sh
for ((i=1;i<=16;i++)); do psql -c "truncate test$i"; done
psql -c "checkpoint;"
for ((i=1;i<=16;i++)); do pgbench -M prepared -n -r -f ./test$i.sql -c 1 -j 1 -T 120 >/tmp/test_16_$i.log & done
sleep 130
for ((i=1;i<=32;i++)); do psql -c "truncate test$i"; done
psql -c "checkpoint;"
for ((i=1;i<=32;i++)); do pgbench -M prepared -n -r -f ./test$i.sql -c 1 -j 1 -T 120 >/tmp/test_32_$i.log & done
sleep 130
for ((i=1;i<=64;i++)); do psql -c "truncate test$i"; done
psql -c "checkpoint;"
for ((i=1;i<=64;i++)); do pgbench -M prepared -n -r -f ./test$i.sql -c 1 -j 1 -T 120 >/tmp/test_64_$i.log & done
sleep 130
for ((i=1;i<=72;i++)); do psql -c "truncate test$i"; done
psql -c "checkpoint;"
for ((i=1;i<=72;i++)); do pgbench -M prepared -n -r -f ./test$i.sql -c 1 -j 1 -T 120 >/tmp/test_72_$i.log & done
sleep 130
for ((i=1;i<=86;i++)); do psql -c "truncate test$i"; done
psql -c "checkpoint;"
for ((i=1;i<=86;i++)); do pgbench -M prepared -n -r -f ./test$i.sql -c 1 -j 1 -T 120 >/tmp/test_86_$i.log & done
sleep 130
for ((i=1;i<=96;i++)); do psql -c "truncate test$i"; done
psql -c "checkpoint;"
for ((i=1;i<=96;i++)); do pgbench -M prepared -n -r -f ./test$i.sql -c 1 -j 1 -T 120 >/tmp/test_96_$i.log & done
sleep 130
for ((i=1;i<=128;i++)); do psql -c "truncate test$i"; done
psql -c "checkpoint;"
for ((i=1;i<=128;i++)); do pgbench -M prepared -n -r -f ./test$i.sql -c 1 -j 1 -T 120 >/tmp/test_128_$i.log & done
sleep 130
for ((i=1;i<=192;i++)); do psql -c "truncate test$i"; done
psql -c "checkpoint;"
for ((i=1;i<=192;i++)); do pgbench -M prepared -n -r -f ./test$i.sql -c 1 -j 1 -T 120 >/tmp/test_192_$i.log & done
sleep 130
for ((i=1;i<=256;i++)); do psql -c "truncate test$i"; done
psql -c "checkpoint;"
for ((i=1;i<=256;i++)); do pgbench -M prepared -n -r -f ./test$i.sql -c 1 -j 1 -T 120 >/tmp/test_256_$i.log & done
sleep 130


$ . ./bench.sh

统计

$ vi res.sh

x=0; for ((i=1;i<=16;i++)); do y=`cat /tmp/test_16_$i.log | grep "excluding connections establishing" | awk '{print $3}' | awk -F "." '{print $1}'`; x=$(($x+$y)); done; echo "16 , $x"
x=0; for ((i=1;i<=32;i++)); do y=`cat /tmp/test_32_$i.log | grep "excluding connections establishing" | awk '{print $3}' | awk -F "." '{print $1}'`; x=$(($x+$y)); done; echo "32 , $x"
x=0; for ((i=1;i<=64;i++)); do y=`cat /tmp/test_64_$i.log | grep "excluding connections establishing" | awk '{print $3}' | awk -F "." '{print $1}'`; x=$(($x+$y)); done; echo "64 , $x"
x=0; for ((i=1;i<=72;i++)); do y=`cat /tmp/test_72_$i.log | grep "excluding connections establishing" | awk '{print $3}' | awk -F "." '{print $1}'`; x=$(($x+$y)); done; echo "72 , $x"
x=0; for ((i=1;i<=86;i++)); do y=`cat /tmp/test_86_$i.log | grep "excluding connections establishing" | awk '{print $3}' | awk -F "." '{print $1}'`; x=$(($x+$y)); done; echo "86 , $x"
x=0; for ((i=1;i<=96;i++)); do y=`cat /tmp/test_96_$i.log | grep "excluding connections establishing" | awk '{print $3}' | awk -F "." '{print $1}'`; x=$(($x+$y)); done; echo "96 , $x"
x=0; for ((i=1;i<=128;i++)); do y=`cat /tmp/test_128_$i.log | grep "excluding connections establishing" | awk '{print $3}' | awk -F "." '{print $1}'`; x=$(($x+$y)); done; echo "128 , $x"
x=0; for ((i=1;i<=192;i++)); do y=`cat /tmp/test_192_$i.log | grep "excluding connections establishing" | awk '{print $3}' | awk -F "." '{print $1}'`; x=$(($x+$y)); done; echo "192 , $x"
x=0; for ((i=1;i<=256;i++)); do y=`cat /tmp/test_256_$i.log | grep "excluding connections establishing" | awk '{print $3}' | awk -F "." '{print $1}'`; x=$(($x+$y)); done; echo "256 , $x"


$ . ./res.sh

9.5

并发数 , TPS

16 , 225198
32 , 280587
64 , 222368
72 , 213024
86 , 199209
96 , 190801
128 , 167913
192 , 131405
256 , 102913

9.6

并发数 , TPS

16 , 288706
32 , 351340
64 , 382612
72 , 377392
86 , 362909
96 , 334932
128 , 279157
192 , 200568
256 , 152104

对比

六、多表 autocommit 批量 insert only

### 环境准备
批量插入，考察的同样是高并发处理单表时XLOG的优化能力，数据文件的扩展优化能力。

测试脚本如下

一次插入400条记录。

for ((i=1;i<=256;i++)); do echo "insert into test$i(crt_time) values(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now()),(now());" > ~/test$i.sql; done


$ . ./bench.sh

统计

$ . ./res.sh

9.5

并发数 , TPS

16 , 5693
32 , 5767
64 , 5297
72 , 4073
86 , 5374
96 , 4978
128 , 5438
192 , 5247
256 , 5376

9.6

并发数 , TPS

16 , 6007
32 , 6120
64 , 5289
72 , 5501
86 , 5503
96 , 5605
128 , 5537
192 , 5467
256 , 5376

对比

小结

PostgreSQL 9.6的锁控制能力又有比较大的进步，在WAL的高并发管理，获取快照，扩展数据文件等方面都有较大改进，相比9.5在scale-up的扩展能力上又上了一个新的台阶，在高并发的读，插入，更新场景，都有非常明显的性能提升。

结合9.6的多核并行计算，可以适合高并发的TP场景，又能在业务低谷时充分发挥硬件能力，处理AP的报表和分析需求，完成业务对TP+AP的混合需求。

对于3,4,5,6的测试CASE，由于是批量入库，可以关闭测试表的autovacuum，达到更好的性能。

现在的CPU一直在往多核的方向发展，32核已经是非常普遍的配置，多的甚至可以达到上千核。

使用PostgreSQL可以更好的发挥硬件的性能，虽然PostgreSQL已经在内核层面支持sharding了，但是使用单机能解决的场景，不推荐使用sharding。

目前sharding对应用开发的限制还比较多，比如大多数sharding技术需要解决几个痛点：

分布式事务的控制，跨库JOIN，全局一致性，全局约束，数据倾斜，扩容，备份，容灾，迁移，确保全局一致性的高可用技术。等等一系列需要考虑的问题。