[pgpool-general: 7769] Re: Error: sorry, too many clients

[Bo Peng]

Hello,

> Hello PgPool
> 
> We are using PgPool 4.2.4 (from Alpine 3.13) as the frontend of a
> master/standby PostgreSQL 10.14 cluster (2 synchronous standbys in
> streaming replication mode). PgPool is operating as a load balancer and a
> connection pool.
> 
> PgPool forks 90 pool workers (num_init_children=90, max_pool=2). All
> PostgreSQL backend servers are configured properly to accept a maximum of
> 190 connections (so, num_init_children * max_pool is below this maximum
> with a safety margin of 10).
> 
> The relevant (pool-specific) part of the configuration is:
> num_init_children = 90
> max_pool = 2
> child_life_time = 5min
> child_max_connections = 0
> connection_life_time = 0
> client_idle_limit = 0
> 
> After a while (5-10 days of operation), we face several errors of the type
> of "Sorry, too many clients".  These errors are not originating from a
> PostgreSQL backend (error logs are completely clear and pg_stat_activity
> shows no more than 140 (from 190) active connections).
> 
> This problem has been around for some time now, and we have to resort to
> restarting the entire PgPool service. Of course, this is not a perfect
> solution because it disrupts some database clients (so, we also have to
> cascade those restarts to some other services, and so on).
> 
> The majority of database clients are connecting from a Hikari pool (Java
> Spring/Boot) which creates long-standing connections (i.e. not one
> per-transaction).
> 
> Any light on this issue is highly appreciated!

The connections hasn't been resued?
Because you are using connection pooling between client and pgpool,
I think the cached connections should be reused.

You may need to configure "client_idle_limit" paramater
to prevent the Pgpool-II child processes from being occupied for long time
by broken connections.

  https://www.pgpool.net/docs/latest/en/html/runtime-config-connection-pooling.html#GUC-CLIENT-IDLE-LIMIT

-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS, Inc. Japan
http://www.sraoss.co.jp/