View Issue Details

IDProjectCategoryView StatusLast Update
0000616Pgpool-IIBugpublic2020-06-23 04:37
Reporterraj.pandey1982@gmail.comAssigned Tohoshiai 
PriorityurgentSeveritymajorReproducibilityalways
Status assignedResolutionopen 
PlatformLinuxOSCentOS 7OS VersionCentOS 7
Product Version4.1.0 
Target VersionFixed in Version 
Summary0000616: sorry, too many clients already
DescriptionPGPOOL Log started with error:-

First it started with below errror
: unable to read data from frontend
: EOF encountered with frontend

And then The pgpool log continuous giving

 "sorry, too many clients already" error. and new connections not happening through pgpool VIP.


Steps To Reproduce[root@mwdp3prddm01 log]# /usr/local/pgsql11.5/bin/psql -h 10.70.185.66 -p 5433 -U postgres -d mawidprod01
Password for user postgres:
psql: FATAL: Sorry, too many clients already
[root@mwdp3prddm01 log]#

Even when login through PGADMIN using VIP same error. But when i login direct to DB ip with PGAdmin there's no issue.
Additional Information[root@mwdp3prddm01 pgpool-II]# tail -20f /var/log/pgpool/pgpool.log
2020-05-31 12:32:48: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:48: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:50: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:51: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:51: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:55: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:55: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:56: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:56: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:57: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:32:57: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:33:02: pid 16615:ERROR: Sorry, too many clients already
2020-05-31 12:33:02: pid 16615:ERROR: Sorry, too many clients already
TagsNo tags attached.

Activities

raj.pandey1982@gmail.com

2020-05-31 18:34

reporter  

pgpool.conf (42,106 bytes)

raj.pandey1982@gmail.com

2020-05-31 18:43

reporter  

pgpoll500rows_31may.log (359,937 bytes)

raj.pandey1982@gmail.com

2020-05-31 18:53

reporter   ~0003372

Major pgpool connection parameters:-

[root@mwdp3prddm01 pgpool-II]# cat pgpool.conf|grep max_pool
max_pool = 3

[root@mwdp3prddm01 pgpool-II]# cat pgpool.conf|grep init_children

num_init_children = 975
[root@mwdp3prddm01 pgpool-II]# cat pgpool.conf|grep reserved_connection

reserved_connections = 1


[root@mwdp3prddm01 pgpool-II]# cat pgpool.conf|grep listen_backlog_multiplier
listen_backlog_multiplier = 2


Postgresql.log connection parameters:-

Max_connections=3000
superuser_reserverd_connection=3

kansvignesh

2020-06-02 12:51

reporter   ~0003377

I upgraded from pgpool 4.0.1 to pgpool 4.1.2 and facing this issue since then, thanks. Our settings as below:

#DB
max_connections=1000
superuser_reserverd_connection=3

#Pgpool
num_init_children=256
max_pool=2
reserved_connections = 0

raj.pandey1982@gmail.com

2020-06-10 17:04

reporter   ~0003389

We check from Application side , the number of total connection coming were 2500 around,while num_init_children = 975.

 was able to resolve the issue by reducing the max_pool=1 and num_init_children=2975 .

but i have another issue, right now max application connections can be made is 2500 and num_init_children=2975. But Still some of the application services shows intermittent jdbc connection issue.
I have total 8 application servers out of which i made one application server down. If i start that , i get jdbc connection issue.

My question is even when num_init_children=2975 is greater than Applications max connections2500, why i get jdbc conection issue intermittently .But no error in pgpool log.

raj.pandey1982@gmail.com

2020-06-14 05:02

reporter   ~0003390

But i still get below error intermittently:- what could be reason for this?

2020-06-13 22:58:30: pid 5309:ERROR: unable to read data from frontend
2020-06-13 22:58:30: pid 5309:DETAIL: socket read failed with an error "Connection timed out"
2020-06-13 22:58:30: pid 10347:ERROR: unable to read data from frontend
2020-06-13 22:58:30: pid 10347:DETAIL: socket read failed with an error "Connection timed out"
2020-06-13 22:58:42: pid 533:LOG: child process with pid: 7162 exits with status 256
2020-06-13 22:58:42: pid 533:LOG: fork a new child process with pid: 19409
2020-06-13 22:58:47: pid 9233:ERROR: unable to read data from frontend
2020-06-13 22:58:47: pid 9233:DETAIL: socket read failed with an error "Connection timed out"
2020-06-13 22:58:47: pid 21452:ERROR: unable to read data from frontend
2020-06-13 22:58:47: pid 21452:DETAIL: socket read failed with an error "Connection timed out"

hoshiai

2020-06-15 11:46

developer   ~0003391

> My question is even when num_init_children=2975 is greater than Applications max connections2500, why i
> get jdbc conection issue intermittently .But no error in pgpool log.

It means that this app don't sometimes get connection to pgpool?

Could you share app(jdbc) error log and pgpool.log?
you should set the following parameter:
  log_client_messages = on

raj.pandey1982@gmail.com

2020-06-16 16:57

reporter   ~0003394

Pgpool log show below error intermitently during peak hours:-

2020-06-13 22:32:48: pid 11519:ERROR: unable to read data from frontend
2020-06-13 22:32:48: pid 11519:DETAIL: socket read failed with an error "Connection reset by peer"
2020-06-13 22:58:21: pid 533:LOG: fork a new child process with pid: 19333
2020-06-13 22:58:30: pid 5309:ERROR: unable to read data from frontend
2020-06-13 22:58:30: pid 5309:DETAIL: socket read failed with an error "Connection timed out"
2020-06-13 22:58:30: pid 10347:ERROR: unable to read data from frontend
2020-06-13 22:58:30: pid 10347:DETAIL: socket read failed with an error "Connection timed out"
2020-06-13 22:58:42: pid 533:LOG: child process with pid: 7162 exits with status 256
2020-06-13 22:58:42: pid 533:LOG: fork a new child process with pid: 19409
2020-06-13 22:58:47: pid 9233:ERROR: unable to read data from frontend
2020-06-13 22:58:47: pid 9233:DETAIL: socket read failed with an error "Connection timed out"
2020-06-13 22:58:47: pid 21452:ERROR: unable to read data from frontend
2020-06-13 22:58:47: pid 21452:DETAIL: socket read failed with an error "Connection timed out"
2020-06-13 22:58:50: pid 533:LOG: child process with pid: 5713 exits with status 256
2020-06-13 22:58:50: pid 533:LOG: fork a new child process with pid: 19452
2020-06-13 22:58:53: pid 533:LOG: child process with pid: 7861 exits with status 256
2020-06-13 22:58:53: pid 533:LOG: fork a new child process with pid: 19480
2020-06-13 22:58:58: pid 533:LOG: child process with pid: 6209 exits with status 256
2020-06-13 22:58:58: pid 533:LOG: fork a new child process with pid: 19542
2020-06-13 22:59:03: pid 6399:ERROR: unable to read data from frontend
2020-06-13 22:59:03: pid 6399:DETAIL: socket read failed with an error "Connection timed out"
2020-06-13 22:59:03: pid 18919:ERROR: unable to read data from frontend
2020-06-13 22:59:03: pid 18919:DETAIL: socket read failed with an error "Connection timed out"
2020-06-13 22:59:03: pid 20875:ERROR: unable to read data from frontend
2020-06-13 22:59:03: pid 20875:DETAIL: socket read failed with an error "Connection timed out"

raj.pandey1982@gmail.com

2020-06-16 17:08

reporter   ~0003395

Application error:-
2020-06-15 10:28:14: pid 533:LOG: child process with pid: 9275 exits with status 256
2020-06-15 10:28:14: pid 533:LOG: fork a new child process with pid: 12037
2020-06-15 10:28:15: pid 533:LOG: child process with pid: 4859 exits with status 256
2020-06-15 10:28:15: pid 533:LOG: fork a new child process with pid: 12038
2020-06-15 10:28:20: pid 533:LOG: child process with pid: 24225 exits with status 256
2020-06-15 10:28:20: pid 533:LOG: fork a new child process with pid: 12064
2020-06-15 10:28:22: pid 533:LOG: child process with pid: 21483 exits with status 256
2020-06-15 10:28:22: pid 533:LOG: fork a new child process with pid: 12068
2020-06-15 10:28:22: pid 533:LOG: child process with pid: 23308 exits with status 256
2020-06-15 10:28:22: pid 533:LOG: fork a new child process with pid: 12070
2020-06-15 10:28:25: pid 533:LOG: child process with pid: 13002 exits with status 256

raj.pandey1982@gmail.com

2020-06-16 18:20

reporter   ~0003396

Please ignore above Application Error(its just pgpool log pasted by mistake)..

Please see below Application error i get intermittently during peak hours:- JDBC connection error in springboot applcation log:-

[] cas-appointment-svc traceId: spanId: 2020-06-15 10:28:16 [kafka-producer-network-thread | producer-10] WARN o.apache.kafka.clients.NetworkClient - [Producer clientId=producer-10, transactionalId=trans-bfccf785-1d7c-4240-8360-790c61e3443a-9] 3 partitions have leader brokers without a matching listener, including [cas.appointment.report.topic.stcprod.cloud-0, cas.appointment.report.topic.stcprod.cloud-6, cas.appointment.report.topic.stcprod.cloud-3]
[] cas-appointment-svc traceId: spanId: 2020-06-15 10:28:16 [kafka-producer-network-thread | producer-6] WARN o.apache.kafka.clients.NetworkClient - [Producer clientId=producer-6, transactionalId=trans-bfccf785-1d7c-4240-8360-790c61e3443a-5] 3 partitions have leader brokers without a matching listener, including [cas.appointment.report.topic.stcprod.cloud-0, cas.appointment.report.topic.stcprod.cloud-6, cas.appointment.report.topic.stcprod.cloud-3]
[4948ceb1-ac9e-4a04-b472-d775b672fa5b] cas-appointment-svc traceId:0ada10680164e405 spanId:a773be8a5b99d568 2020-06-15 10:28:16 [http-nio-7081-exec-380] ERROR c.m.c.a.c.AppointmentController - Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
    at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:448)
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:376)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:572)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:360)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99)

raj.pandey1982@gmail.com

2020-06-23 04:37

reporter   ~0003418

Hello Friends, Could any one get this issue:- JDBC connection error in springboot application log, why sometimes jdbc connection fails with pgpool:-

org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

Issue History

Date Modified Username Field Change
2020-05-31 18:34 raj.pandey1982@gmail.com New Issue
2020-05-31 18:34 raj.pandey1982@gmail.com File Added: pgpool.conf
2020-05-31 18:43 raj.pandey1982@gmail.com File Added: pgpoll500rows_31may.log
2020-05-31 18:53 raj.pandey1982@gmail.com Note Added: 0003372
2020-06-01 17:31 hoshiai Assigned To => hoshiai
2020-06-01 17:31 hoshiai Status new => assigned
2020-06-02 12:51 kansvignesh Note Added: 0003377
2020-06-10 17:04 raj.pandey1982@gmail.com Note Added: 0003389
2020-06-14 05:02 raj.pandey1982@gmail.com Note Added: 0003390
2020-06-15 11:46 hoshiai Status assigned => feedback
2020-06-15 11:46 hoshiai Note Added: 0003391
2020-06-16 16:57 raj.pandey1982@gmail.com Note Added: 0003394
2020-06-16 16:57 raj.pandey1982@gmail.com Status feedback => assigned
2020-06-16 17:08 raj.pandey1982@gmail.com Note Added: 0003395
2020-06-16 18:20 raj.pandey1982@gmail.com Note Added: 0003396
2020-06-23 04:37 raj.pandey1982@gmail.com Note Added: 0003418