<div dir="ltr">I am not aware of anything in my configuration being especially non-standard.<br><br>Here is a sanitized version of my config.<br>--------------------------------------------------------------<div>backend_clustering_mode = 'streaming_replication'<br><br>listen_addresses = '*'<br>port = 1234<br>socket_dir = '/var/run/postgresql'<br>reserved_connections = 0<br>pcp_listen_addresses = '*'<br>pcp_port = 9898<br>pcp_socket_dir = '/var/run/postgresql'<br>listen_backlog_multiplier = 2<br>serialize_accept = on<br>backend_hostname0 = '10.1.1.3'<br>backend_port0 = 1234<br>backend_weight0 = 1<br>backend_data_directory0 = '/var/lib/pgsql/12/data'<br>backend_flag0 = 'ALLOW_TO_FAILOVER'<br>backend_application_name0 = 'walreceiver'<br>backend_hostname1 = '10.1.1.2'<br>backend_port1 = 1234<br>backend_weight1 = 1<br>backend_data_directory1 = '/var/lib/pgsql/12/data'<br>backend_flag1 = 'ALLOW_TO_FAILOVER'<br>backend_application_name1 = 'walreceiver'<br>backend_hostname2 = '10.1.1.17'<br>backend_port2 = 1234<br>backend_weight2 = 0<br>backend_data_directory2 = '/var/lib/pgsql/12/data'<br>backend_flag2 = 'ALLOW_TO_FAILOVER'<br>backend_application_name2 = 'walreceiver'<br><br>enable_pool_hba = on<br>pool_passwd = 'pool_passwd'<br>authentication_timeout = 1min<br>allow_clear_text_frontend_auth = on<br><br>ssl = off<br><br>num_init_children = 600<br>max_pool = 4<br>child_life_time = 5min<br>child_max_connections = 20<br>connection_life_time = 60<br>client_idle_limit = 0<br><br>log_destination = 'stderr'<br>log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.<br>log_connections = on<br>log_disconnections = on<br>log_hostname = on<br>log_statement = on<br>log_per_node_statement = on<br>log_client_messages = off<br>log_standby_delay = 'if_over_threshold'<br>syslog_facility = 'LOCAL0'<br>syslog_ident = 'pgpool'<br>log_error_verbosity = verbose<br>logging_collector = on<br>log_directory = '/var/log/pgpool'<br>log_filename = 'pgpool-%a.log'<br>log_truncate_on_rotation = on<br>log_rotation_age = 1d<br>log_rotation_size = 0<br>pid_file_name = '/var/run/pgpool/pgpool.pid'<br>logdir = '/var/log/pgpool'<br>connection_cache = on<br>reset_query_list = 'ABORT; DISCARD ALL'<br><br>replicate_select = off<br>insert_lock = off<br>lobj_lock_table = ''<br>replication_stop_on_mismatch = off<br>failover_if_affected_tuples_mismatch = off<br>load_balance_mode = off<br>ignore_leading_white_space = on<br>read_only_function_list = ''<br>write_function_list = ''<br>primary_routing_query_pattern_list = '.*'<br>app_name_redirect_preference_list = ''<br>allow_sql_comments = off<br>disable_load_balance_on_write = 'transaction'<br>dml_adaptive_object_relationship_list= ''<br>statement_level_load_balance = on<br>sr_check_period = 10<br>sr_check_user = 'monitor'<br>sr_check_password = '*****'<br>sr_check_database = 'monitor'<br>delay_threshold = 10240<br>follow_primary_command = '/etc/pgpool-II/follow_master.sh %d %h %p %D %m %M %H %P %r %R'<br>health_check_period = 1<br>health_check_timeout = 20<br>health_check_user = 'monitor'<br>health_check_password = '*****'<br>health_check_database = ''<br>health_check_max_retries = 3<br>health_check_retry_delay = 1<br>connect_timeout = 10000<br>failover_command = '/etc/pgpool-II/failover.sh %d %h %p %D %m %H %M %P %r %R %N %S'<br>failback_command = ''<br>failover_on_backend_error = on<br>detach_false_primary = off<br>search_primary_node_timeout = 5min<br>recovery_user = 'postgres'<br>recovery_password = ''<br>recovery_1st_stage_command = 'recovery_1st_stage'<br>recovery_2nd_stage_command = ''<br>recovery_timeout = 90<br>client_idle_limit_in_recovery = 0<br>auto_failback = off<br>auto_failback_interval = 1min<br>use_watchdog = on<br>trusted_servers = 'node1,node2,node3'<br>ping_path = '/bin'<br>hostname0 = 'node01<br>wd_port0 = 9000<br>pgpool_port0 = 1234<br>hostname1 = 'node02'<br>wd_port1 = 9000<br>pgpool_port1 = 1234<br>hostname2 = 'node03'<br>wd_port2 = 9000<br>pgpool_port2 = 1234<br>hostname3 = 'node04'<br>wd_port3 = 9000<br>pgpool_port3 = 1234<br>wd_priority = 4<br>wd_authkey = ''<br>wd_ipc_socket_dir = '/var/run/postgresql'<br>delegate_IP = '10.1.1.1'<br>if_cmd_path = '/sbin'<br>if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth1 label eth1:0'<br>if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth1'<br>arping_path = '/usr/sbin'<br>arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth1'<br>clear_memqcache_on_escalation = on<br>wd_escalation_command = ''<br>wd_de_escalation_command = ''<br>failover_when_quorum_exists = on<br>failover_require_consensus = on<br>allow_multiple_failover_requests_from_node = on<br>enable_consensus_with_half_votes = on<br>wd_monitoring_interfaces_list = ''<br>wd_lifecheck_method = 'heartbeat'<br>wd_interval = 10<br>heartbeat_hostname0 = 'node01'<br>heartbeat_port0 = 9694<br>heartbeat_device0 = 'eth1'<br>heartbeat_hostname1 = 'node02'<br>heartbeat_port1 = 9694<br>heartbeat_device1 = 'eth1'<br>heartbeat_hostname2 = 'node03'<br>heartbeat_port2 = 9694<br>heartbeat_device2 = 'eth1'<br>heartbeat_hostname3 = 'node04'<br>heartbeat_port3 = 9694<br>heartbeat_device3 = 'eth1'<br>wd_heartbeat_keepalive = 2<br>wd_heartbeat_deadtime = 30<br>wd_life_point = 3<br>wd_lifecheck_query = 'SELECT 1'<br>wd_lifecheck_dbname = 'template1'<br>wd_lifecheck_user = 'nobody'<br>wd_lifecheck_password = ''<br>relcache_expire = 0<br>relcache_size = 256<br>check_temp_table = catalog<br>check_unlogged_table = on<br>enable_shared_relcache = on<br>relcache_query_target = primary<br>memory_cache_enabled = off<br>memqcache_method = 'shmem'<br>memqcache_memcached_host = 'localhost'<br>memqcache_memcached_port = 11211<br>memqcache_total_size = 64MB<br>memqcache_max_num_cache = 1000000<br>memqcache_expire = 0<br>memqcache_auto_cache_invalidation = on<br>memqcache_maxcache = 400kB<br>memqcache_cache_block_size = 1MB<br>memqcache_oiddir = '/var/log/pgpool/oiddir'<br>cache_safe_memqcache_table_list = ''<br>cache_unsafe_memqcache_table_list = ''<br></div><div><br></div><div><br></div><div><br></div><div>--</div><div>Brian Ismay<br><a href="mailto:jismay@cenic.org">jismay@cenic.org</a></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Fri, Jan 26, 2024 at 4:16 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">> Sure thing:<br>
> <br>
> postgres=# show pool_nodes;<br>
> node_id | hostname | port | status | lb_weight | role | select_cnt |<br>
> load_balance_node | replication_delay | replication_state |<br>
> replication_sync_state | last_status_change<br>
> ---------+-------------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------<br>
> 0 | 10.1.1.3 | 1234 | up | 0.500000 | standby | 0 |<br>
> false | 0 | |<br>
> | 2024-01-23 10:09:51<br>
> 1 | 10.1.1.2 | 1234 | up | 0.500000 | primary | 82024640 |<br>
> true | 0 | |<br>
> | 2024-01-23 10:09:51<br>
> 2 | 10.1.1.17 | 1234 | up | 0.000000 | standby | 0 |<br>
> false | 0 | |<br>
> | 2024-01-23 10:09:51<br>
> (3 rows)<br>
<br>
All selects were sent to node 1 as expected. However,<br>
replication_state and replication_sync_state are shown as NULL. Is it<br>
intended?<br>
<br>
Moreover I noticed that in the log BEGIN and END were sent to all<br>
nodes. This is unexpected. Do you have any non standard configuration<br>
in pgpool.con?<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>
> postgres=# PGPOOL SHOW load_balance_mode;<br>
> load_balance_mode<br>
> -------------------<br>
> off<br>
> (1 row)<br>
> <br>
> --<br>
> Brian<br>
> <br>
> On Thu, Jan 25, 2024 at 5:55 PM Tatsuo Ishii <<a href="mailto:ishii@sraoss.co.jp" target="_blank">ishii@sraoss.co.jp</a>> wrote:<br>
> <br>
>> 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>><br>
>> 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<br>
>> 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 +<br>
>> :delta<br>
>> >> >> WHERE aid = :aid;<br>
>> >> >> 8.921 SELECT abalance FROM pgbench_accounts WHERE aid =<br>
>> :aid;<br>
>> >> >> 9.230 UPDATE pgbench_tellers SET tbalance = tbalance +<br>
>> :delta<br>
>> >> >> WHERE tid = :tid;<br>
>> >> >> 10.102 UPDATE pgbench_branches SET bbalance = bbalance +<br>
>> :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<br>
>> 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<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: 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<br>
>> 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<br>
>> 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 +<br>
>> $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<br>
>> 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<br>
>> + $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 =<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: Bind: 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: 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<br>
>> 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 +<br>
>> $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 +<br>
>> $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<br>
>> 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<br>
>> + $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,<br>
>> 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<br>
>> 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<br>
>> should<br>
>> >> >>> be sent to primary except the connection establishing. But even if<br>
>> >> >>> load balance mode is off, pgpool establish connections to both<br>
>> 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.<br>
>> 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>
>><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>
</blockquote></div>