测试环境：
|
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;