Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vFeiT-00FVB1-1M for pgpool-general@arkaria.postgresql.org; Sun, 02 Nov 2025 20:30:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vFeiR-00DpGX-VX for pgpool-general@arkaria.postgresql.org; Sun, 02 Nov 2025 20:30:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vFUbw-00AGwf-Ms for pgpool-general@lists.postgresql.org; Sun, 02 Nov 2025 09:42:55 +0000 Received: from mail-lj1-x22f.google.com ([2a00:1450:4864:20::22f]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vFUbt-0051dU-1R for pgpool-general@lists.postgresql.org; Sun, 02 Nov 2025 09:42:54 +0000 Received: by mail-lj1-x22f.google.com with SMTP id 38308e7fff4ca-378cffe5e1aso7650491fa.2 for ; Sun, 02 Nov 2025 01:42:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1762076572; x=1762681372; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=X+qJASrn1AE4h83wIDsaq7IJRTm/Tdom1Q7eaMTARX8=; b=YnmBRHrcMusMhWAtQyEWKllRKI41qJm5KhBJJHbExGJkOChPWVTtKnTXuUu8B1Ojb7 GVXdwX90embdm4ZpxyUgH3xzbmYK2Hj7ah/cftoLtFtcnMsf8jyCP/EFaISFgsVbtgTS w3oG/9GEuPQOGpk0zRAOzK1DYXoFRzSroxYHIzsq/tpjGmW+Jlvv9IMSlyK+bvr/ahIA kaP+bIWTQPdChd+9MM9QT1zbbVE5sp5BDeHRi9odM4mQyARbaRw0FlSgRYw1pQA8zdow V9F0ApJodnu9nUJR7Zxal1cnI2OeNFF4YyNJY2sfozoMmwWPYQ9UTa1WjH0zoicIsJPa YxxQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1762076572; x=1762681372; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=X+qJASrn1AE4h83wIDsaq7IJRTm/Tdom1Q7eaMTARX8=; b=d6OIPxldDkxTL6PEnJuqIqTVRIVfB/3VB6XBx5kpkjsTGqtsZMsuTGhk484x9oQS2U e9D7swntWpQHR/fM2kBZPFF7x38S19scaadw1GmaVZI//48vPuppbP67iqnyyG02h6ch 8EI9Zxo8ACeQcQBDKeyx2cSjI4NGVKljEwpysjnWUFBD86Iag7tooxNJIbEcJD3Zer+M iGH5XQOFrbM/4LcbzepQNNl9zu0XainQzfSo40IWrKDyMpqat3woAGclzinqJCqY3FNH qwCb4ilxp1oct0Wbietb/8G4HYiEIiOVYsoUCp9bhrdgbZ5uRSh0nY+JPK2N3IhnQv4x q+1w== X-Gm-Message-State: AOJu0YxvoQmU4uE3304cz4cWnKfKAM4M6U34qNO8zzAIng4zh9sEo5wY M0ss7w+HiLGYYHY+lVNa8JoeSBBoJSaUqcvS58XqeangEYL3pMIZTzAGNkh24DHU6F1nj54kkBG pSJnnbrwEULBTuVK1xacpTEV3K5t8+KKuhXS200c= X-Gm-Gg: ASbGncs7N66B5ZGRdefZEpkdRldZSwMkhMrir2l7fapukQEOBEEov/NvMmdPfh1gQSv S+/f3bHWCzuStzuik6rYwcT+VgzwPnqaNgjqLH9GnPNV+GdbVtCTRIgszQFrAuSlMLY808FZi9l 5+8u7DuAY8Q55TfetC9DdnhHCjVvMiugwaVQk5cyYX08hBZb3THrl2ajSCP7vv3wyxygFoHAfJa sjW49jyPITA91SemmFpixE3Z44GnlpsyFfszIX3Or7vI2WCui2gkPjvXrTNLNNH/ryvJH1kBM/Q NjChFBvUo9/G8IXfCUZN+dWpLtvW X-Google-Smtp-Source: AGHT+IGY4/AW7busgcP8nve+7UDO/WWxEYHib7+CbcWIEyykvM2xAO97AkI9GvF3JGYkt4MdLAQvzm5fwmkYwWec82I= X-Received: by 2002:a05:6512:3d23:b0:592:f6ac:a536 with SMTP id 2adb3069b0e04-5941d5472eamr2971472e87.42.1762076571096; Sun, 02 Nov 2025 01:42:51 -0800 (PST) MIME-Version: 1.0 Received: by 2002:a05:6022:5645:b0:e4:5e81:f7a0 with HTTP; Sun, 2 Nov 2025 01:42:49 -0800 (PST) In-Reply-To: References: From: Bob Ross Date: Sun, 2 Nov 2025 10:42:49 +0100 X-Gm-Features: AWmQ_bmPue4ugxBMoJbyh6xZBh_KpZWmJJGv9rhdKgm4K-pEE_E29LPsj2Sl6BI Message-ID: Subject: Re: SQL query latency when using pgPool (v4.6.2) To: Bo Peng Cc: "pgpool-general@lists.postgresql.org" Content-Type: multipart/alternative; boundary="000000000000165c3f0642996cef" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000165c3f0642996cef Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Bo, Do you have any updates regarding this case? Have you been able to reproduce it? In the last week I have created a new set of VMs in European and Australian datacenters (220ms latency), and some very simple SELECT SQL statements routed locally (with remote weights set to 0) still experience the same latency issue. The more statements I add to the transaction, the bigger the gap gets (e.g. 5ms vs 1100ms for 5 simple SQLs). Thanks, Bob On Wednesday, October 22, 2025, Bob Ross wrote: > ++ pgpool-general@lists.postgresql.org as missed it by accident. > > On Wed, Oct 22, 2025 at 11:40=E2=80=AFAM Bob Ross > wrote: > >> Hi Bo, >> >> I was able to narrow down the issue and reported it as a bug: >> https://github.com/pgpool/pgpool2/issues/130 >> All required information regarding versions is in the link. Affected >> pgPool is colocated with Postgres in the local (primary) region. >> See our pgpool.conf attached but it's quite irrelevant as this issue >> occurs with any parameter combination, as long as there are both local a= nd >> remote nodes configured as backends. >> Logs are clean, the only difference is the delay between SQLs when remot= e >> nodes are attached (~20ms delay) and detached (~1ms delay). >> >> For us this is a blocker - we are unable to use pgPool effectively in >> multi-region Postgres deployment. >> Please let us know in case of any further questions. >> >> Thanks, >> Bob >> >> On Fri, Oct 17, 2025 at 9:42=E2=80=AFAM Bo Peng wr= ote: >> >>> Hi, >>> >>> Thank you for your question. >>> >>> Could you please share the cluster configuration? >>> (For example, the number of nodes, Pgpool-II/PostgreSQL versions, and >>> whether pgpool and PostgreSQL are running on the same host.) >>> >>> Also, could you please share your pgpool.conf file and the pgpool logs? >>> >>> --- >>> Bo Peng >>> SRA OSS K.K. >>> TEL: 03-5979-2701 FAX: 03-5979-2702 >>> Mobile: 080-7752-0749 >>> URL: https://www.sraoss.co.jp/ >>> >>> >>> ________________________________________ >>> =E5=B7=AE=E5=87=BA=E4=BA=BA: Bob Ross >>> =E9=80=81=E4=BF=A1: 2025 =E5=B9=B4 10 =E6=9C=88 8 =E6=97=A5 (=E6=B0=B4= =E6=9B=9C=E6=97=A5) 15:30 >>> =E5=AE=9B=E5=85=88: pgpool-general@lists.postgresql.org >> postgresql.org> >>> =E4=BB=B6=E5=90=8D: SQL query latency when using pgPool (v4.6.2) >>> >>> >>> >>> >>> Hello, >>> I am currently testing SQL query performance using PgPool (v4.6.2) as a >>> load balancer, and I am observing approximately 18=E2=80=9320 ms latenc= y between >>> SQL statements within the same transaction. Execution time on the >>> PostgreSQL backend servers is less than 0.5 ms. >>> It=E2=80=99s important to note that both the application and database s= ervers >>> are located in the same cloud region, with network latency below 1 ms. >>> PgPool is colocated with the PostgreSQL backend servers. >>> For example, when executing a transaction containing 10 SQL statements, >>> the total execution time through pgPool is about 200 ms, as confirmed b= y >>> both application and pgPool logs. The delay consistently occurs between= the >>> =E2=80=9CExecute=E2=80=9D and =E2=80=9CParse=E2=80=9D phases. When conn= ecting directly to the PostgreSQL >>> backends, the same transaction completes in about 10 ms, with roughly 1= ms >>> between SQL statements. >>> We have tested the following configurations: >>> - Switched from the extended query protocol to the simple query protoco= l >>> =E2=80=94 for some queries this reduced inter-query latency to 2=E2=80= =933 ms, but for >>> others it increased to 30=E2=80=9340 ms due to additional parsing overh= ead. >>> - Adjusted various PgPool parameters related to load balancing and >>> connection pooling (e.g., load_balance_mode, statement_level_load_balan= ce, >>> serialize_accept, disable_load_balance_on_write) and enabled debug logg= ing, >>> but none of these changes brought a significant improvement. >>> Could you please advise whether there are additional pgPool parameters >>> or tuning approaches that could help improve throughput and reduce late= ncy >>> in extended query protocol? >>> Thank you, >>> Bob >>> >> --000000000000165c3f0642996cef Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Bo,=C2=A0

Do you have any updates regarding this case= ? Have you been able to reproduce it?=C2=A0

In the= last week I have created a new set of VMs in European and Australian datac= enters (220ms latency), and some very simple SELECT SQL statements routed l= ocally (with remote weights set to 0) still experience the same latency iss= ue. The more statements I add to the transaction, the bigger the gap gets (= e.g. 5ms vs 1100ms for 5 simple SQLs).=C2=A0

Thank= s,=C2=A0
Bob

On Wednesday, October 22, 2025, Bob Ross <= bob.ross.19821@gmail.com>= ; wrote:
++ =C2=A0pgpool-general@lists.postgresql.org as missed it by accid= ent.

On Wed, Oct 22, 2025 at 11:40=E2=80=AFAM Bob Ross <bob.ross.19821@gmail.com&g= t; wrote:
Hi Bo,

I was able to narrow down the issue an= d reported it as a bug:=C2=A0https://github.com/pgpool/pgpool2/issues/= 130
All required information regarding versions is in the lin= k. Affected pgPool is colocated with Postgres in the local (primary) region= .
See our pgpool.conf attached but it's quite irrelevant as t= his issue occurs with any parameter combination, as long as there are both = local and remote nodes configured as backends.
Logs are clean, th= e only difference is the delay between SQLs when remote nodes are attached = (~20ms delay) and detached (~1ms delay).

For us th= is is a blocker - we are unable to use pgPool effectively in multi-region P= ostgres deployment.
Please let us know in case of any further que= stions.

Thanks,
Bob

On Fri, Oct 17,= 2025 at 9:42=E2=80=AFAM Bo Peng <pengbo@sraoss.co.jp> wrote:
Hi,

Thank you for your question.

Could you please share the cluster configuration?
(For example, the number of nodes, Pgpool-II/PostgreSQL versions, and wheth= er pgpool and PostgreSQL are running on the same host.)

Also, could you please share your pgpool.conf file and the pgpool logs?

---
Bo Peng <pengbo= @sraoss.co.jp>
SRA OSS K.K.
TEL: 03-5979-2701 FAX: 03-5979-2702
Mobile: 080-7752-0749
URL: https://www.sraoss.co.jp/


________________________________________
=E5=B7=AE=E5=87=BA=E4=BA=BA: Bob Ross <bob.ross.19821@gmail.com>
=E9=80=81=E4=BF=A1: 2025 =E5=B9=B4 10 =E6=9C=88 8 =E6=97=A5 (=E6=B0=B4=E6= =9B=9C=E6=97=A5) 15:30
=E5=AE=9B=E5=85=88: pgpool-general@lists.postgresql.org <pgpool-ge= neral@lists.postgresql.org>
=E4=BB=B6=E5=90=8D: SQL query latency when using pgPool (v4.6.2)




Hello,
I am currently testing SQL query performance using PgPool (v4.6.2) as a loa= d balancer, and I am observing approximately 18=E2=80=9320 ms latency betwe= en SQL statements within the same transaction. Execution time on the Postgr= eSQL backend servers is less than 0.5 ms.
It=E2=80=99s important to note that both the application and database serve= rs are located in the same cloud region, with network latency below 1 ms. P= gPool is colocated with the PostgreSQL backend servers.
For example, when executing a transaction containing 10 SQL statements, the= total execution time through pgPool is about 200 ms, as confirmed by both = application and pgPool logs. The delay consistently occurs between the =E2= =80=9CExecute=E2=80=9D and =E2=80=9CParse=E2=80=9D phases. When connecting = directly to the PostgreSQL backends, the same transaction completes in abou= t 10 ms, with roughly 1 ms between SQL statements.
We have tested the following configurations:
- Switched from the extended query protocol to the simple query protocol = =E2=80=94 for some queries this reduced inter-query latency to 2=E2=80=933 = ms, but for others it increased to 30=E2=80=9340 ms due to additional parsi= ng overhead.
- Adjusted various PgPool parameters related to load balancing and connecti= on pooling (e.g., load_balance_mode, statement_level_load_balance, serializ= e_accept, disable_load_balance_on_write) and enabled debug logging, but non= e of these changes brought a significant improvement.
Could you please advise whether there are additional pgPool parameters or t= uning approaches that could help improve throughput and reduce latency in e= xtended query protocol?=C2=A0
Thank you,
Bob
--000000000000165c3f0642996cef--