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

[Ron]

On 11/12/23 17:20, Tatsuo Ishii wrote:
> 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.

It started at 230, and is now 350.

-- 
Born in Arizona, moved to Babylonia.