<div dir="ltr">Sure thing:<br><br>postgres=# show pool_nodes;<br> node_id | hostname | port | status | lb_weight | role | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change<br>---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------<br> 0 | 10.1.1.3 | 1234 | up | 0.500000 | standby | 0 | false | 0 | | | 2024-01-23 10:09:51<br> 1 | 10.1.1.2 | 1234 | up | 0.500000 | primary | 82024640 | true | 0 | | | 2024-01-23 10:09:51<br> 2 | 10.1.1.17 | 1234 | up | 0.000000 | standby | 0 | false | 0 | | | 2024-01-23 10:09:51<br>(3 rows)<br><br>postgres=# PGPOOL SHOW load_balance_mode;<br> load_balance_mode<br>-------------------<br> off<br>(1 row)<br><br>--<div>Brian</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jan 25, 2024 at 5:55 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp">ishii@sraoss.co.jp</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Ok. To make sure that can you share the result of following commands?<br>
<br>
1) show pool_nodes<br>
<br>
2) pgpool show load_balance_mode<br>
<br>
Best reagards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS LLC<br>
English: <a href="http://www.sraoss.co.jp/index_en/" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
<br>
> I actually did have the load_balance_mode set to ON, but I caught that<br>
> before I sent the initial data-dump to the list.<br>
> <br>
> --<br>
> Brian<br>
> <br>
> On Wed, Jan 24, 2024 at 10:34 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
>> > Brian,<br>
>> ><br>
>> > I have looked into the log and found some queries were sent to remote<br>
>> node (node id 2).<br>
>> > This is pretty strange because you said:<br>
>> >> Load-balancing is turned off.<br>
>> ><br>
>> > In this case every queries should be sent to primary node only (node<br>
>> > id 1).<br>
>> ><br>
>> > Are you sure that you set load_balance_node to off?<br>
>> ~~~~~~~~~~~~~~~~<br>
>> I meant "load_balance_mode".<br>
>><br>
>> > Best reagards,<br>
>> > --<br>
>> > Tatsuo Ishii<br>
>> > SRA OSS LLC<br>
>> > English: <a href="http://www.sraoss.co.jp/index_en/" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
>> > Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> ><br>
>> >> Tatsuo,<br>
>> >><br>
>> >> As requested, I re-ran pgbench with the log_per_node_statement option<br>
>> >> turned on.<br>
>> >> I included just a small excerpt, but everything I saw looked to be the<br>
>> same<br>
>> >> pattern.<br>
>> >><br>
>> >> $ pgbench -h 10.1.1.1 -p1234 -U pgbench -c 75 -j 12 -t 500 -P 15 -r<br>
>> >> pgbench1 -M extended<br>
>> >> starting vacuum...end.<br>
>> >> progress: 15.0 s, 1044.2 tps, lat 69.690 ms stddev 7.344<br>
>> >> progress: 30.0 s, 1060.1 tps, lat 69.940 ms stddev 7.635<br>
>> >> transaction type: <builtin: TPC-B (sort of)><br>
>> >> scaling factor: 100<br>
>> >> query mode: extended<br>
>> >> number of clients: 75<br>
>> >> number of threads: 12<br>
>> >> number of transactions per client: 500<br>
>> >> number of transactions actually processed: 37500/37500<br>
>> >> latency average = 69.725 ms<br>
>> >> latency stddev = 7.464 ms<br>
>> >> tps = 1003.125524 (including connections establishing)<br>
>> >> tps = 1004.224113 (excluding connections establishing)<br>
>> >> statement latencies in milliseconds:<br>
>> >> 0.001 \set aid random(1, 100000 * :scale)<br>
>> >> 0.001 \set bid random(1, 1 * :scale)<br>
>> >> 0.000 \set tid random(1, 10 * :scale)<br>
>> >> 0.000 \set delta random(-5000, 5000)<br>
>> >> 11.958 BEGIN;<br>
>> >> 8.933 UPDATE pgbench_accounts SET abalance = abalance + :delta<br>
>> >> WHERE aid = :aid;<br>
>> >> 8.921 SELECT abalance FROM pgbench_accounts WHERE aid = :aid;<br>
>> >> 9.230 UPDATE pgbench_tellers SET tbalance = tbalance + :delta<br>
>> >> WHERE tid = :tid;<br>
>> >> 10.102 UPDATE pgbench_branches SET bbalance = bbalance + :delta<br>
>> >> WHERE bid = :bid;<br>
>> >> 8.907 INSERT INTO pgbench_history (tid, bid, aid, delta,<br>
>> mtime)<br>
>> >> VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);<br>
>> >> 11.671 END;<br>
>> >><br>
>> >><br>
>> >><br>
>> >><br>
>> >> -----------------------<br>
>> >><br>
>> >> Looped through the logfile to grab the connections coming from pgbench<br>
>> and<br>
>> >> then find those:<br>
>> >><br>
>> >> for i in `cat pgpool-Tue.log | grep "connecting\ host" | grep<br>
>> >> "10\.1\.1\.150"|awk '{print $4}'|sed "s/://"`;do cat pgpool-Tue.log<br>
>> |grep<br>
>> >> $i;done|less<br>
>> >><br>
>> >> A snippet from there:<br>
>> >> NOTE: node_id 0 and 1 are the "local" db_nodes<br>
>> >> node_id 2 is the "remote" db_node<br>
>> >> node_id 1 is the current primary.<br>
>> >><br>
>> >><br>
>> >><br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: new connection received<br>
>> >> 2024-01-23 10:10:03: pid 4507: DETAIL: connecting host=10.1.1.150<br>
>> >> port=52802<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: child.c:1860<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: select count(*) from<br>
>> >> pgbench_branches<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:224<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: SELECT pg_catalog.version()<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: SELECT count(*) FROM pg_catalog.pg_proc AS p,<br>
>> >> pg_catalog.pg_namespace AS n WHERE p.proname = 'count' AND n.oid =<br>
>> >> p.pronamespace AND n.nspname ~ '.*' AND p.provolatile = 'v'<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: select count(*) from pgbench_branches<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: vacuum pgbench_branches<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:224<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: vacuum pgbench_branches<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: vacuum pgbench_tellers<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:224<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: vacuum pgbench_tellers<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: truncate pgbench_history<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:224<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: truncate pgbench_history<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: DISCARD ALL<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:224<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 0 backend pid: 19865<br>
>> >> statement: DISCARD ALL<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: DISCARD ALL<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 2 backend pid: 17091<br>
>> >> statement: DISCARD ALL<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: frontend disconnection: session<br>
>> time:<br>
>> >> 0:00:00.215 user=pgbench database=pgbench1 host=10.1.1.150 port=52802<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: child.c:2076<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: new connection received<br>
>> >> 2024-01-23 10:10:03: pid 4507: DETAIL: connecting host=10.1.1.150<br>
>> >> port=52828<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: child.c:1860<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 0 backend pid: 19865<br>
>> >> statement: Parse: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Parse: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 2 backend pid: 17091<br>
>> >> statement: Parse: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 0 backend pid: 19865<br>
>> >> statement: Bind: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Bind: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 2 backend pid: 17091<br>
>> >> statement: Bind: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 0 backend pid: 19865<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 2 backend pid: 17091<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:914<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 0 backend pid: 19865<br>
>> >> statement: Execute: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Execute: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 2 backend pid: 17091<br>
>> >> statement: Execute: BEGIN;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Parse: UPDATE pgbench_accounts SET abalance = abalance + $1<br>
>> >> WHERE aid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Bind: UPDATE pgbench_accounts SET abalance = abalance + $1<br>
>> WHERE<br>
>> >> aid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: UPDATE pgbench_accounts<br>
>> SET<br>
>> >> abalance = abalance + $1 WHERE aid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:914<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Execute: UPDATE pgbench_accounts SET abalance = abalance + $1<br>
>> >> WHERE aid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Parse: SELECT abalance FROM pgbench_accounts WHERE aid = $1;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Bind: SELECT abalance FROM pgbench_accounts WHERE aid = $1;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: SELECT abalance FROM<br>
>> >> pgbench_accounts WHERE aid = $1;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:914<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Execute: SELECT abalance FROM pgbench_accounts WHERE aid =<br>
>> $1;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Parse: UPDATE pgbench_tellers SET tbalance = tbalance + $1<br>
>> WHERE<br>
>> >> tid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Bind: UPDATE pgbench_tellers SET tbalance = tbalance + $1<br>
>> WHERE<br>
>> >> tid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: UPDATE pgbench_tellers<br>
>> SET<br>
>> >> tbalance = tbalance + $1 WHERE tid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:914<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Execute: UPDATE pgbench_tellers SET tbalance = tbalance + $1<br>
>> >> WHERE tid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Parse: UPDATE pgbench_branches SET bbalance = bbalance + $1<br>
>> >> WHERE bid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Bind: UPDATE pgbench_branches SET bbalance = bbalance + $1<br>
>> WHERE<br>
>> >> bid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: UPDATE pgbench_branches<br>
>> SET<br>
>> >> bbalance = bbalance + $1 WHERE bid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:914<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Execute: UPDATE pgbench_branches SET bbalance = bbalance + $1<br>
>> >> WHERE bid = $2;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Parse: INSERT INTO pgbench_history (tid, bid, aid, delta,<br>
>> mtime)<br>
>> >> VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP);<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Bind: INSERT INTO pgbench_history (tid, bid, aid, delta,<br>
>> mtime)<br>
>> >> VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP);<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: INSERT INTO<br>
>> pgbench_history<br>
>> >> (tid, bid, aid, delta, mtime) VALUES ($1, $2, $3, $4,<br>
>> CURRENT_TIMESTAMP);<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:914<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Execute: INSERT INTO pgbench_history (tid, bid, aid, delta,<br>
>> >> mtime) VALUES ($1, $2, $3, $4, CURRENT_TIMESTAMP);<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 0 backend pid: 19865<br>
>> >> statement: Parse: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Parse: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 2 backend pid: 17091<br>
>> >> statement: Parse: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 0 backend pid: 19865<br>
>> >> statement: Bind: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Bind: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 2 backend pid: 17091<br>
>> >> statement: Bind: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 0 backend pid: 19865<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 2 backend pid: 17091<br>
>> >> statement: D message<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: statement: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:914<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 0 backend pid: 19865<br>
>> >> statement: Execute: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 1 backend pid: 6857<br>
>> >> statement: Execute: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOG: DB node id: 2 backend pid: 17091<br>
>> >> statement: Execute: END;<br>
>> >> 2024-01-23 10:10:03: pid 4507: LOCATION: pool_proto_modules.c:3596<br>
>> >><br>
>> >> --<br>
>> >> Brian Ismay<br>
>> >> <a href="mailto:jismay@cenic.org" target="_blank">jismay@cenic.org</a><br>
>> >><br>
>> >> On Sat, Jan 20, 2024 at 10:39 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>><br>
>> wrote:<br>
>> >><br>
>> >>> > Any recommendations would be greatly appreciated. So far, after<br>
>> making<br>
>> >>> > multiple tuning and settings changes there have been no meaningful<br>
>> >>> changes.<br>
>> >>> ><br>
>> >>> > What we are highly suspicious of is that the latency numbers seem to<br>
>> go<br>
>> >>> > from around .2 ms to around 8 ms. This is very close to the<br>
>> >>> round-trip-time<br>
>> >>> > between our datacenters.<br>
>> >>><br>
>> >>> > statement latencies in milliseconds:<br>
>> >>> > 0.003 \set aid random(1, 100000 * :scale)<br>
>> >>> > 0.001 \set bid random(1, 1 * :scale)<br>
>> >>> > 0.001 \set tid random(1, 10 * :scale)<br>
>> >>> > 0.001 \set delta random(-5000, 5000)<br>
>> >>> > 8.844 BEGIN;<br>
>> >>> > 9.695 UPDATE pgbench_accounts SET abalance = abalance +<br>
>> :delta<br>
>> >>> > WHERE aid = :aid;<br>
>> >>> > 8.878 SELECT abalance FROM pgbench_accounts WHERE aid =<br>
>> :aid;<br>
>> >>> > 685.873 UPDATE pgbench_tellers SET tbalance = tbalance +<br>
>> :delta<br>
>> >>> > WHERE tid = :tid;<br>
>> >>> > 92.049 UPDATE pgbench_branches SET bbalance = bbalance +<br>
>> :delta<br>
>> >>> > WHERE bid = :bid;<br>
>> >>> > 8.826 INSERT INTO pgbench_history (tid, bid, aid, delta,<br>
>> mtime)<br>
>> >>> > VALUES (:tid, :bid, :aid, :delta, CURRENT_TIMESTAMP);<br>
>> >>> > 8.847 END;<br>
>> >>><br>
>> >>> If BEGIN and END were sent to the remote standby node, then the<br>
>> >>> latency number makes sense. But theoretically it should not happen<br>
>> >>> because you turn off load balance mode. In this case everything should<br>
>> >>> be sent to primary except the connection establishing. But even if<br>
>> >>> load balance mode is off, pgpool establish connections to both primary<br>
>> >>> and standbys. However the default option of pgbench is establishing<br>
>> >>> connection only once at the beginning of session, meaning the<br>
>> >>> connection overhead should only happen only once in the session. Thus<br>
>> >>> I can not think of plausible reason for the latency numbers.<br>
>> >>><br>
>> >>> Anyway, can you turn on log_per_node_statement and see everything is<br>
>> >>> sent to standby? I want to confirm that first.<br>
>> >>><br>
>> >>> Best reagards,<br>
>> >>> --<br>
>> >>> Tatsuo Ishii<br>
>> >>> SRA OSS LLC<br>
>> >>> English: <a href="http://www.sraoss.co.jp/index_en/" rel="noreferrer" target="_blank">http://www.sraoss.co.jp/index_en/</a><br>
>> >>> Japanese:<a href="http://www.sraoss.co.jp" rel="noreferrer" target="_blank">http://www.sraoss.co.jp</a><br>
>> >>><br>
>> > _______________________________________________<br>
>> > pgpool-general mailing list<br>
>> > <a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
>> > <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>> _______________________________________________<br>
>> pgpool-general mailing list<br>
>> <a href="mailto:pgpool-general@pgpool.net" target="_blank">pgpool-general@pgpool.net</a><br>
>> <a href="http://www.pgpool.net/mailman/listinfo/pgpool-general" rel="noreferrer" target="_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
>><br>
</blockquote></div>