PostgreSQL单机、同步复制、异步复制性能测试对比

时间:2021-05-22 06:42:41

测试环境:

•测试机:PC
•内存:8GB
•CPU:Intel(R) Core(TM) i5-3450 3.10GHz
•硬盘:HDD
•数据量:20GB
•测试工具:pgbench
•PostgreSQL版本:9.5.1
•参数配置:默认配置
测试结果

PostgreSQL同步复制与PostgreSQL单机性能测试对比

terminals

10

20

30

40

50

PostgreSQL单机(tps)

54

54

61

68

71

PostgreSQL

同步复制(tps)

27

33

53

57

61

PostgreSQL

异步复制(tps)

70

65

77

83

87

测试过程中的数据如下所示:

PostgreSQL同步复制

20GB data    8GB Memory    4CPU

master:192.168.0.14

standby: 192.168.0.15

单机192.168.0.14默认配置测试

[postgres@minion1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 10  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 10

number of threads: 1

duration: 600 s

number of transactions actually processed: 32774

latency average: 183.072 ms

tps = 54.614739 (including connections establishing)

tps = 54.614954 (excluding connections establishing)

statement latencies in milliseconds:

0.007900 \set nbranches 1 * :scale

0.021082 \set ntellers 10 * :scale

0.027008 \set naccounts 100000 * :scale

0.017157 \setrandom aid 1 :naccounts

0.007154 \setrandom bid 1 :nbranches

0.006974 \setrandom tid 1 :ntellers

0.006926 \setrandom delta -5000 5000

0.387514 BEGIN;

57.706042    UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

0.531161 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

0.308336 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

0.418083 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.180357 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

123.453907  END;

[postgres@minion1 bin]$  ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 20  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 20

number of threads: 1

duration: 600 s

number of transactions actually processed: 32665

latency average: 367.366 ms

tps = 54.396128 (including connections establishing)

tps = 54.396331 (excluding connections establishing)

statement latencies in milliseconds:

0.007506 \set nbranches 1 * :scale

0.048462 \set ntellers 10 * :scale

0.015586 \set naccounts 100000 * :scale

0.027542 \setrandom aid 1 :naccounts

0.157314 \setrandom bid 1 :nbranches

0.006869 \setrandom tid 1 :ntellers

0.047938 \setrandom delta -5000 5000

2.134529 BEGIN;

177.995882  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

0.813658 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

0.292561 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

1.041568 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.243265 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

184.626413  END;

[postgres@minion1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 30  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 30

number of threads: 1

duration: 600 s

number of transactions actually processed: 36783

latency average: 489.356 ms

tps = 61.251723 (including connections establishing)

tps = 61.251950 (excluding connections establishing)

statement latencies in milliseconds:

0.026379 \set nbranches 1 * :scale

0.018963 \set ntellers 10 * :scale

0.030436 \set naccounts 100000 * :scale

0.059545 \setrandom aid 1 :naccounts

0.007369 \setrandom bid 1 :nbranches

0.006821 \setrandom tid 1 :ntellers

0.006753 \setrandom delta -5000 5000

1.081480 BEGIN;

237.262781  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

1.253963 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

0.957185 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

1.730359 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.236083 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

246.731468  END;

[postgres@minion1 bin]$  ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 40  -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 40

number of threads: 1

duration: 600 s

number of transactions actually processed: 41001

latency average: 585.352 ms

tps = 68.143229 (including connections establishing)

tps = 68.143475 (excluding connections establishing)

[postgres@minion1 bin]$  ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 50  -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 50

number of threads: 1

duration: 600 s

number of transactions actually processed: 43309

latency average: 692.697 ms

tps = 71.867790 (including connections establishing)

tps = 71.868053 (excluding connections establishing)

PG同步复制性能测试

[postgres@node1 bin]$  ./pgbench -d postgres -U postgres -h 192.168.0.14 -p 5432 -c 10  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 10

number of threads: 1

duration: 600 s

number of transactions actually processed: 16440

latency average: 364.964 ms

tps = 27.380710 (including connections establishing)

tps = 27.380806 (excluding connections establishing)

statement latencies in milliseconds:

0.008551 \set nbranches 1 * :scale

0.006313 \set ntellers 10 * :scale

0.006117 \set naccounts 100000 * :scale

0.007933 \setrandom aid 1 :naccounts

0.007494 \setrandom bid 1 :nbranches

0.007123 \setrandom tid 1 :ntellers

0.007161 \setrandom delta -5000 5000

0.203136 BEGIN;

119.792707  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

0.270357 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

0.535077 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

0.633911 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.145461 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

243.549121  END;

./pgbench -d postgres -U postgres -h localhost -p 5432 -c 20  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 20

number of threads: 1

duration: 600 s

number of transactions actually processed: 20203

latency average: 593.971 ms

tps = 33.628754 (including connections establishing)

tps = 33.628872 (excluding connections establishing)

statement latencies in milliseconds:

0.007198 \set nbranches 1 * :scale

0.005499 \set ntellers 10 * :scale

0.005457 \set naccounts 100000 * :scale

0.007148 \setrandom aid 1 :naccounts

0.014756 \setrandom bid 1 :nbranches

0.006343 \setrandom tid 1 :ntellers

0.006322 \setrandom delta -5000 5000

0.332977 BEGIN;

269.706515  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

0.655481 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

0.594572 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

1.425101 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.470575 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

321.324808  END;

./pgbench -d postgres -U postgres -h localhost -p 5432 -c 30  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 30

number of threads: 1

duration: 600 s

number of transactions actually processed: 32375

latency average: 555.985 ms

tps = 53.872946 (including connections establishing)

tps = 53.873141 (excluding connections establishing)

statement latencies in milliseconds:

0.006620 \set nbranches 1 * :scale

0.005146 \set ntellers 10 * :scale

0.005040 \set naccounts 100000 * :scale

0.006596 \setrandom aid 1 :naccounts

0.006322 \setrandom bid 1 :nbranches

0.005923 \setrandom tid 1 :ntellers

0.005909 \setrandom delta -5000 5000

0.336000 BEGIN;

241.561835  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

0.591474 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

0.473278 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

2.602180 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.163975 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

310.427730  END;

./pgbench -d postgres -U postgres -h localhost -p 5432 -c 40  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 40

number of threads: 1

duration: 600 s

number of transactions actually processed: 34765

latency average: 690.349 ms

tps = 57.691611 (including connections establishing)

tps = 57.691811 (excluding connections establishing)

statement latencies in milliseconds:

0.006601 \set nbranches 1 * :scale

0.005098 \set ntellers 10 * :scale

0.004962 \set naccounts 100000 * :scale

0.047420 \setrandom aid 1 :naccounts

0.006216 \setrandom bid 1 :nbranches

0.005986 \setrandom tid 1 :ntellers

0.005855 \setrandom delta -5000 5000

0.525683 BEGIN;

293.890014  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

1.446397 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

0.763961 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

3.871294 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.223840 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

391.076596  END;

[postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 50  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 50

number of threads: 1

duration: 600 s

number of transactions actually processed: 36851

latency average: 814.089 ms

tps = 61.288387 (including connections establishing)

tps = 61.288599 (excluding connections establishing)

statement latencies in milliseconds:

0.036619 \set nbranches 1 * :scale

0.015365 \set ntellers 10 * :scale

0.048885 \set naccounts 100000 * :scale

0.006212 \setrandom aid 1 :naccounts

0.005962 \setrandom bid 1 :nbranches

0.005748 \setrandom tid 1 :ntellers

0.028424 \setrandom delta -5000 5000

1.002420 BEGIN;

341.759347  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

1.338118 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

1.909057 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

5.901859 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.338450 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

462.642218  END;

PostgreSQL异步复制性能测试

[postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 10  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 10

number of threads: 1

duration: 600 s

number of transactions actually processed: 42402

latency average: 141.503 ms

tps = 70.656084 (including connections establishing)

tps = 70.656332 (excluding connections establishing)

statement latencies in milliseconds:

0.009604 \set nbranches 1 * :scale

0.008152 \set ntellers 10 * :scale

0.007491 \set naccounts 100000 * :scale

0.008265 \setrandom aid 1 :naccounts

0.007667 \setrandom bid 1 :nbranches

0.007304 \setrandom tid 1 :ntellers

0.127113 \setrandom delta -5000 5000

0.061162 BEGIN;

139.243102  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

0.990680 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

0.489713 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

0.230086 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.214473 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

0.101391 END;

[postgres@node1 bin]$  ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 20  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 20

number of threads: 1

duration: 600 s

number of transactions actually processed: 39253

latency average: 305.709 ms

tps = 65.333390 (including connections establishing)

tps = 65.333617 (excluding connections establishing)

statement latencies in milliseconds:

0.009897 \set nbranches 1 * :scale

0.007471 \set ntellers 10 * :scale

0.006880 \set naccounts 100000 * :scale

0.032549 \setrandom aid 1 :naccounts

0.097106 \setrandom bid 1 :nbranches

0.006860 \setrandom tid 1 :ntellers

0.125257 \setrandom delta -5000 5000

0.100855 BEGIN;

299.825466  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

4.123210 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

0.389870 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

0.347125 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.429468 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

0.495837 END;

[postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 30  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 30

number of threads: 1

duration: 600 s

number of transactions actually processed: 46800

latency average: 384.615 ms

tps = 77.815440 (including connections establishing)

tps = 77.815712 (excluding connections establishing)

statement latencies in milliseconds:

0.010180 \set nbranches 1 * :scale

0.007174 \set ntellers 10 * :scale

0.006647 \set naccounts 100000 * :scale

0.065834 \setrandom aid 1 :naccounts

0.026308 \setrandom bid 1 :nbranches

0.006901 \setrandom tid 1 :ntellers

0.054161 \setrandom delta -5000 5000

0.203354 BEGIN;

373.666099  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

6.878700 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

1.379165 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

0.812257 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

1.496860 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

0.414110 END;

[postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 40  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 40

number of threads: 1

duration: 600 s

number of transactions actually processed: 50331

latency average: 476.843 ms

tps = 83.765539 (including connections establishing)

tps = 83.765830 (excluding connections establishing)

statement latencies in milliseconds:

0.009836 \set nbranches 1 * :scale

0.010446 \set ntellers 10 * :scale

0.006072 \set naccounts 100000 * :scale

0.010755 \setrandom aid 1 :naccounts

0.024105 \setrandom bid 1 :nbranches

0.006772 \setrandom tid 1 :ntellers

0.036164 \setrandom delta -5000 5000

0.132524 BEGIN;

457.122401  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

15.298400    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

1.702279 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

0.676259 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

1.552850 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

0.482677 END;

[postgres@node1 bin]$ ./pgbench -d postgres -U postgres -h localhost -p 5432 -c 50  -r -T 600

transaction type: TPC-B (sort of)

scaling factor: 1428

query mode: simple

number of clients: 50

number of threads: 1

duration: 600 s

number of transactions actually processed: 52834

latency average: 567.816 ms

tps = 87.954780 (including connections establishing)

tps = 87.955086 (excluding connections establishing)

statement latencies in milliseconds:

0.009852 \set nbranches 1 * :scale

0.006589 \set ntellers 10 * :scale

0.005597 \set naccounts 100000 * :scale

0.081271 \setrandom aid 1 :naccounts

0.012686 \setrandom bid 1 :nbranches

0.006291 \setrandom tid 1 :ntellers

0.102962 \setrandom delta -5000 5000

0.326865 BEGIN;

543.207220  UPDATE pgbench_accounts SET abalance = abalance + :delta WHERE aid = :aid;

20.356945    SELECT abalance FROM pgbench_accounts WHERE aid = :aid;

1.614863 UPDATE pgbench_tellers SET tbalance = tbalance + :delta WHERE tid = :tid;

0.864676 UPDATE pgbench_branches SET bbalance = bbalance + :delta WHERE bid = :bid;

0.872985 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);

0.531862 END;