[pgpool-general: 8851] Re: queries not redirecting to primary when "delay_threshold_by_time" is exceeded

[zam bak]

On Mon, Jun 12, 2023 at 5:41 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>
> >> On Tue, Jun 6, 2023 at 12:40 AM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >>>
> >>> > On Mon, Jun 5, 2023 at 9:11 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >>> >>
> >>> >> > On Mon, Jun 5, 2023 at 6:05 PM Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> >>> >> >>
> >>> >> >> >> > I will look into this.
> >>> >> >> >>
> >>> >> >> >> It turned out that delay_treshold_by_time is broken since it was introduced.
> >>> >> >> >> I have fixed it and pushed to the git repository.
> >>> >> >> >> https://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=6aebf24aa9a627f4c852e44f6407f17a34d50528
> >>> >> >> >>
> >>> >> >> >> Unfortunately there's no workaround of this for now. Please use
> >>> >> >> >> delay_treshold instead if you like.
> >>> >> >> >
> >>> >> >> > Thanks for looking into this.
> >>> >> >> > This is really impacting us a lot - Is it possible to have a 4.4.4
> >>> >> >> > release with this fix?
> >>> >> >>
> >>> >> >> Yes, 4.4.4 will have the fix.
> >>> >> >>
> >>> >> >> > According to the site, next release is slated for August 17th, 2023
> >>> >> >> > (more than two months away)
> >>> >> >>
> >>> >> >> Yes, that's our plan.
> >>> >> >>
> >>> >> >
> >>> >> > What I meant to ask is - Is it possible to have a release sooner than that date?
> >>> >>
> >>> >> How do you install pgpool? If you can build pgpool from the source
> >>> >> code, you could deploy pgpool with the fix today.
> >>> >>
> >>> >
> >>> > We use official RPM packages from
> >>> > https://www.pgpool.net/mediawiki/index.php/Yum_Repository
> >>> > We have many customer installs of pgpool and, in most cases, packages
> >>> > are updated as part of the change control process customers have.
> >>> >
> >>> > I do understand that releasing minor versions more frequently than
> >>> > once per quarter would put more strain on maintainers though.
> >>> > Would it be easier to release a patch release for 4.4.3? For example
> >>> > 4.4.3-1 or similar that would include only fixes (no new features)....
> >>> > If not, I understand and thank you once more for consideration.
> >>>
> >>> Pengbo, who is responsible for the release process of Pgpool-II, said
> >>> that she maybe able to release 4.4.3-1 RPM packages *only* (with all
>                                    ~~~~~~~4.4.3-2
> >>> fixes for 4.4. including this, no new features). Is that useful for
> >>> you?
> >>
> >> Yes. That would be very helpful.
> >> Thank you!
> >
> > Ok, I will ask Peng Bo to prepare the RPM package.
>
> 4.4.3-2 RPM packages are out.


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 !!!





>
> Best reagards,
> --
> Tatsuo Ishii
> SRA OSS LLC
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp