[pgpool-general: 8852] Re: queries not redirecting to primary when "delay_threshold_by_time" is exceeded
Tatsuo Ishii
ishii at sraoss.co.jp
Wed Jun 28 11:26:19 JST 2023
> After installing updated packages, I can confirm that replication lag
> now does cause all SELECT queries to go to primary...
> However, after the replication lag subsides below threshold, all
> SELECT queries remain "sticking" to primary (they are no longer load
> balancing)
>
> For testing I used pgbench initialization (this causes replication lag)
>
> 1. in one session runa simple query in a loop:
> while true; do psql -U postgres -h localhost -p 9999 -d bench_test -c
> "select case inet_server_addr() when '10.1.10.15' then 'replica_1'
> else 'primary' end,count(*) from pg_tables WHERE schemaname !=
> 'pg_catalog' AND schemaname != 'information_schema'"; sleep 2; done
>
> 2. tail the pgpool log (with per node logging enabled). You will
> notice that the statement load balances (db node: 0, db node:1 show
> select in log)
>
> 3. use pgbench initialize to generate load, causing replication lag
> pgbench --quiet --initialize --scale=1000 --foreign-keys bench_test
>
> 4. initially in pgpool log once the replication lag threshold is
> reached (you will see log entries with "....node: 1 is behind' X.XXXX
> seconds...."), you will see select statement going against node: 0
> only...
> but once lag falls below 1s (treshold) and long after pgbench is
> done with initialization, select queries remain going to primary
> server only !!!
The test query above will never be load balanced regardless
replication delay because it involves system catalogs
(pg_tables). Please try test queries that do not involve system
catalogs.
Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
More information about the pgpool-general
mailing list