[pgpool-general: 8189] Re: Unable to acquire JDBC Connection

[Bo Peng]

Hello

> Thank you for reply.
> 
> Ok, I understood. If reserved_connections = 0, a connection attempting 
> to go beyond num_init_children will simply hang silently until one of 
> the child processes becomes free.
> 
> But why does the error "Unable to acquire JDBC Connection" on the client 
> side of Pgpool appear? Maybe because Hikari Pool also works in the Java 
> application that is client of Pgpool?

I think this error appears when the number of clients
exceeds the concurrent limit "num_init_children".

I'm not sure if Hikari Pool causes this error.
If your application is using Hikari Pool and Hikari Pool
caches connections between client and pgpool,
the cached connecions should be reused by the
client requests from your application.

> Regards,
>    Nikola
> 
> 26.05.2022 6:02, Bo Peng пишет:
> > Hello,
> >
> >> Hello!
> >>
> >> Thank you for response.
> >>
> >> We run 3 replicas of Pgpool in k8s.
> >>
> >> NUM_INIT_CHILDREN=400, MAX_POOL=2, LISTEN_BACKLOG_MULTIPLIER=2
> >>
> >> On PostgreSQL max_connections=2500 . We set very big value here now to
> >> test only pgpool for a lack of connection.
> >>
> >> When load is running max number of connections to PostgreSQL is slightly
> >> over 200 (i.e. 212, 220). At that time command "SHOW POOL_PROCESSES" on
> >> Pgpool shows many idle processes.
> >>
> >> And if I understand correctly, if cause of the error "Unable to acquire
> >> JDBC Connection" was in lack of connections, Pgpool would issue an error
> >> message about that.
> > By default, Pgpool-II doesn't return an error message.
> > By default, Pgpool-II accepts up to num_init_children connection requests and
> > queues up more connection requests until one of child process becomes free.
> >
> > If you set reserved_connections parameter, Pgpool-II will return an error message
> > "Sorry, too many clients already" if the limit is reached.
> >
> > For example:
> > reserved_connections = 1
> >
> > https://www.pgpool.net/docs/latest/en/html/runtime-config-connection.html#GUC-RESERVED-CONNECTIONS
> >
> >> On 25.05.2022 09:15, Bo Peng wrote:
> >>> Hello,
> >>>
> >>>> Hello,
> >>>>
> >>>> Our application use Pgpool in k8s with docker image pgpool/pgpool2, and
> >>>> our PostgreSQL nodes are on virtual machines.
> >>>>
> >>>> When we tried to perform load test our application, we have received a
> >>>> lot of errors of application services that connect to database through
> >>>> Pgpool:
> >>>>
> >>>> "Could not open JPA EntityManager for transaction; nested exception is
> >>>> org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC
> >>>> Connection"
> >>>>
> >>>> If application connects to database directly  without Pgpool, this error
> >>>> does not appear.
> >>> The setting of num_init_children may cause this error.
> >>> Could you run ps command and check if there are processes in the "wait for connection request" status?
> >>>
> >>> How many Pgopol-II replicas do you configured?
> >>> What are the values of num_init_children, max_pool and PostgreSQL's max_connections.
> >>>
> >>>> Do you have any experience with such problem and could you tell why it
> >>>> occurs, i.e. why the service can't connect Pgpool and how to fix it?
> >>>>
> >>>> Thanks in advance.
> >>>>
> >>>> -- 
> >>>> Regards,
> >>>>      Nikola
> >>>>
> >>>> _______________________________________________
> >>>> pgpool-general mailing list
> >>>> pgpool-general at pgpool.net
> >>>> http://www.pgpool.net/mailman/listinfo/pgpool-general
> >


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