[pgpool-general: 8998] Massive performance and latency issues when remote replica is attached to cluster.
Brian Ismay
jismay at cenic.org
Sat Jan 20 04:04:55 JST 2024
Hello,
My group is in charge of a medium-size pgpool/postgresql cluster. As part
of an enhancement program we are moving from a postgres 9.6 cluster to a
postgres 12 cluster. We are also amending the configuration to be 3 DB
nodes with 2 at the same site, and the 3rd in a remote site ~400 miles away.
Version 4.2.15 of pgpool is in-place on our testing cluster, and version
4.2.13 is in-place on our production cluster.
Replication is configured as streaming-replication.
Load-balancing is turned off.
What we are experiencing on both our test-cluster and our production
cluster is reduced performance, but only when the "remote" DB replica is
present and "up" as shown by the "SHOW POOL_NODES" command.
pgbench testing on both clusters reveals the same behaviour although
different numbers due to different loading on the environments. The same
tests when performed directly to the primary DB server show much higher
TPS, and more importantly much lower latency on operations, especially on
SELECTS.
Any recommendations would be greatly appreciated. So far, after making
multiple tuning and settings changes there have been no meaningful changes.
What we are highly suspicious of is that the latency numbers seem to go
from around .2 ms to around 8 ms. This is very close to the round-trip-time
between our datacenters.
Thanks in advance,
Brian Ismay
jismay at cenic.org
PROD PG12 via pgpool, only the primary UP in the cluster:
# pgbench -h 192.168.1.1 -p1234 -U pgbench -c 75 -j 12 -t 500 -P 15 -r
pgbench1 -M extended
starting vacuum...end.
progress: 15.0 s, 822.9 tps, lat 85.953 ms stddev 114.279
progress: 30.0 s, 897.1 tps, lat 83.604 ms stddev 105.925
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: extended
number of clients: 75
number of threads: 12
number of transactions per client: 500
number of transactions actually processed: 37500/37500
latency average = 81.925 ms
latency stddev = 107.690 ms
tps = 870.003083 (including connections establishing)
tps = 872.407505 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.424 BEGIN;
0.604 UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
0.494 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
70.132 UPDATE pgbench_tellers SET tbalance = tbalance + :delta
WHERE tid = :tid;
9.245 UPDATE pgbench_branches SET bbalance = bbalance + :delta
WHERE bid = :bid;
0.499 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.525 END;
PROD PG12 via pgpool, primary and local replica UP in the cluster:
]# pgbench -h 192.168.1.1 -p1234 -U pgbench -c 75 -j 12 -t 500 -P 15 -r
pgbench1 -M extended
starting vacuum...end.
progress: 15.0 s, 852.8 tps, lat 83.382 ms stddev 99.485
progress: 30.0 s, 790.3 tps, lat 94.685 ms stddev 128.357
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: extended
number of clients: 75
number of threads: 12
number of transactions per client: 500
number of transactions actually processed: 37500/37500
latency average = 85.491 ms
latency stddev = 109.482 ms
tps = 839.909173 (including connections establishing)
tps = 841.987077 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.455 BEGIN;
0.613 UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
0.514 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
73.197 UPDATE pgbench_tellers SET tbalance = tbalance + :delta
WHERE tid = :tid;
9.647 UPDATE pgbench_branches SET bbalance = bbalance + :delta
WHERE bid = :bid;
0.523 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.538 END;
PROD PG12 via pgpool, primary, local, and remote replicas UP in the cluster:
# pgbench -h 192.168.1.1 -p1234 -U pgbench -c 75 -j 12 -t 500 -P 15 -r
pgbench1 -M extended
starting vacuum...end.
progress: 15.0 s, 84.5 tps, lat 761.525 ms stddev 935.700
progress: 30.0 s, 83.2 tps, lat 915.741 ms stddev 1466.682
progress: 45.0 s, 89.0 tps, lat 848.131 ms stddev 1112.052
progress: 60.0 s, 89.1 tps, lat 840.411 ms stddev 1023.020
progress: 75.0 s, 89.7 tps, lat 827.697 ms stddev 1005.904
progress: 90.0 s, 85.7 tps, lat 859.873 ms stddev 1215.931
progress: 105.0 s, 88.7 tps, lat 842.273 ms stddev 1254.175
progress: 120.0 s, 89.1 tps, lat 877.938 ms stddev 1157.652
progress: 135.0 s, 86.9 tps, lat 828.901 ms stddev 1006.725
progress: 150.0 s, 87.2 tps, lat 878.423 ms stddev 1203.388
progress: 165.0 s, 88.5 tps, lat 860.956 ms stddev 1019.511
progress: 180.0 s, 88.4 tps, lat 848.210 ms stddev 1218.284
progress: 195.0 s, 86.2 tps, lat 872.858 ms stddev 987.229
progress: 210.0 s, 87.3 tps, lat 840.890 ms stddev 994.654
progress: 225.0 s, 88.9 tps, lat 860.021 ms stddev 1038.107
progress: 240.0 s, 89.1 tps, lat 806.581 ms stddev 936.039
progress: 255.0 s, 87.7 tps, lat 877.816 ms stddev 1244.023
progress: 270.0 s, 87.5 tps, lat 865.829 ms stddev 956.762
progress: 285.0 s, 89.1 tps, lat 850.876 ms stddev 1175.058
progress: 300.0 s, 89.2 tps, lat 829.801 ms stddev 888.384
progress: 315.0 s, 88.3 tps, lat 847.570 ms stddev 1267.026
progress: 330.0 s, 86.3 tps, lat 864.622 ms stddev 1038.313
progress: 345.0 s, 88.6 tps, lat 853.584 ms stddev 1098.051
progress: 360.0 s, 88.9 tps, lat 835.857 ms stddev 992.015
progress: 375.0 s, 88.8 tps, lat 853.196 ms stddev 1010.982
progress: 390.0 s, 87.6 tps, lat 833.180 ms stddev 1051.568
progress: 405.0 s, 89.5 tps, lat 729.388 ms stddev 915.301
progress: 420.0 s, 89.0 tps, lat 500.181 ms stddev 456.801
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: extended
number of clients: 75
number of threads: 12
number of transactions per client: 500
number of transactions actually processed: 37500/37500
latency average = 823.000 ms
latency stddev = 1070.193 ms
tps = 87.584780 (including connections establishing)
tps = 87.609999 (excluding connections establishing)
statement latencies in milliseconds:
0.003 \set aid random(1, 100000 * :scale)
0.001 \set bid random(1, 1 * :scale)
0.001 \set tid random(1, 10 * :scale)
0.001 \set delta random(-5000, 5000)
8.844 BEGIN;
9.695 UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
8.878 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
685.873 UPDATE pgbench_tellers SET tbalance = tbalance + :delta
WHERE tid = :tid;
92.049 UPDATE pgbench_branches SET bbalance = bbalance + :delta
WHERE bid = :bid;
8.826 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
8.847 END;
PROD PG12 direct to primary server:
# pgbench -h 192.168.1.2 -p5678 -U pgbench -c 75 -j 12 -t 500 -P 15 -r
pgbench1 -M extended
starting vacuum...end.
progress: 15.0 s, 1819.1 tps, lat 39.188 ms stddev 47.442
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: extended
number of clients: 75
number of threads: 12
number of transactions per client: 500
number of transactions actually processed: 37500/37500
latency average = 38.186 ms
latency stddev = 47.941 ms
tps = 1837.524162 (including connections establishing)
tps = 1846.988426 (excluding connections establishing)
statement latencies in milliseconds:
0.001 \set aid random(1, 100000 * :scale)
0.000 \set bid random(1, 1 * :scale)
0.000 \set tid random(1, 10 * :scale)
0.000 \set delta random(-5000, 5000)
0.144 BEGIN;
0.269 UPDATE pgbench_accounts SET abalance = abalance + :delta
WHERE aid = :aid;
0.210 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;
32.785 UPDATE pgbench_tellers SET tbalance = tbalance + :delta
WHERE tid = :tid;
4.338 UPDATE pgbench_branches SET bbalance = bbalance + :delta
WHERE bid = :bid;
0.205 INSERT INTO pgbench_history (tid, bid, aid, delta, mtime)
VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);
0.231 END;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20240119/b4828c6f/attachment.htm>
More information about the pgpool-general
mailing list