[pgpool-general: 8964] Re: Java application occasionally gets "The connection attempt failed" errors

[Tatsuo Ishii]

From: Ron <ronljohnsonjr at gmail.com>
Subject: [pgpool-general: 8962] Re: Java application occasionally gets "The connection attempt failed" errors
Date: Sat, 11 Nov 2023 08:48:57 -0600
Message-ID: <045d9989-d20e-434d-a8c4-9918d1571ca7 at gmail.com>

> On 11/11/23 05:43, Tatsuo Ishii wrote:
>>> PgPool 4.3.7 and PG 14.7
>>>
>>> By "occasionally", I mean 1 or 2 times for every 500 "batches" of data
>>> we process.
>>>
>>> Attached is the error message text, the pgpool.conf file and
>>> postgresql.conf file.
>>>
>>> "ps | aux | grep <db application user name>" shows maybe a dozen
>>> connections to PgPool.
>>>
>>> We do not get these errors when connecting directly to the database on
>>> node "a".
>>>
>>> It's a three node cluster (named "a", "b" and "c").  The VIP is
>>> attached to node "c", and the PostgreSQL primary database is on node
>>> "a".  PG streaming replica is node "b".
>>>
>>> Putting the VIP on node "a" does _not_ solve the problem (but it does
>>> make things run slower).
>>>
>>> "num_init_children = 350" on all three nodes, and "max_connections =
>>> 400" on the two DB servers.
>>>
>>> There's nothing in the error logs where PgPool "log_min_messages =
>>> notice" and Postgresql log_min_error_statement and log_min_messages
>>> are both = info, and client_min_messages = notice.
>>>
>>> Where else should we look?
>> I am not familiar with JDBC driver but it looks like JDBC driver tried
>> to connect to pgpool then failed. So if pgpool is related to the
>> problem, I guess it may worth to check parameters of pgpool.conf that
>> are related to connection establishment. I noticed that
>> serialize_accept is enabled and reserved_connections = 3, both are
>> different from the defaults. If you set back one of them or both to
>> the default, do you see any difference?
> 
> I'll try changing reserved_connectionsto default.  However, the docs
> on serialize_accept say that it should be "off" when num_init_children
> is high, and my num_init_children is more than 300.  Thus, shouldn't
> it be set to "on"? (It'll be a week+, though, since the "application
> test guy" just went on vacation,

Yes, we recommend to set serialize_accept to on when num_init_children
is high. However, as the doc suggests, how many num_init_children is
actually "high" depends on your environment.

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