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

[Bo Peng]

Hello,

> Hello,
> 
> Thanks for reply.
> 
> We set the parameters reserved_connections=0, 
> listen_backlog_multiplier=2 and num_init_children=400  in Pgpool settings.
> 
> So if the number of connections becomes 401 the error "Unable to acquire 
> JDBC Connection" in application service can be raised? Is it true?

> If so, what is the matter of the listen_backlog_multiplier parameter?

Normally, Pgpool-II accepts up to 400 requests.
After that, if the #401 request comes, the #401 request will be queued.

I guess, you are using a benchmarking tool to do this load testing.
If your benchmarking tool sends all of the 401 requests at once,
your benchmarking tool will get stuck.

Below is a FAQ related to pgbench:
https://www.pgpool.net/mediawiki/index.php/FAQ#When_I_run_pgbench_to_test_pgpool-II.2C_pgbench_hangs._If_I_directly_run_pgbench_against_PostgreSQL.2C_it_works_fine._Why.3F

I guess the issue above may cause "Unable to acquire JDBC Connection" error.

> Regards,
>    Nikolay
> 
> On 30.05.2022 09:48, Bo Peng wrote:
> > 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/