View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0000616Pgpool-IIBugpublic2020-05-31 18:342024-07-21 23:05
Reporterraj.pandey1982@gmail.com Assigned Tohoshiai  
PriorityurgentSeveritymajorReproducibilityalways
Status assignedResolutionopen 
PlatformLinuxOSCentOS 7OS VersionCentOS 7
Product Version4.1.0 
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

Nikhil

2020-09-01 02:51

reporter   ~0003549

I am also facing this issue in one of my performance environment. I am using Pgpool version 4.0.9. Let me know if you were able to resolve this?

hoshiai

2020-09-25 09:48

developer   ~0003573

Sorry, I overlooked your responses.

I think the following error messages mean that client(jdbc) connection is dropped before pgpool realize it.
Therefore, it is possible for jdbc has any problems.

  ERROR: unable to read data from frontend
  DETAIL: socket read failed with an error "Connection reset by peer"

  ERROR: unable to read data from frontend
  DETAIL: socket read failed with an error "Connection timed out"

So you should check jdbc log with a more detailed.

raj.pandey1982@gmail.com

2022-03-29 18:22

reporter   ~0004012

After heavy load on DB today again i got "Sorry, too many clients already" for 10 mints then slowly it disappeared:-
my pgpool version is 4.1.
 max_pool = 1
num_init_children = 4000
reserved_connections = 1

At DB max_connections=4500.

while error "Sorry, too many clients already"was apearing in pgpool log", applcation was not able to make all connections even after application restart.After some times it disappeared and application connections were started happening.
I got to know its a bug and fixed in 4.1.5..is it true or u can provide some solution to my current version 4.1.0?

raj.pandey1982@gmail.com

2022-03-29 18:39

reporter   ~0004013

THe error:-2022-03-29 09:47:18: pid 7983:LOG: child process with pid: 12634 exits with status 256
2022-03-29 09:47:18: pid 7983:LOG: fork a new child process with pid: 19886
2022-03-29 09:47:18: pid 25729:ERROR: Sorry, too many clients already
2022-03-29 09:47:18: pid 19215:ERROR: Sorry, too many clients already
2022-03-29 09:47:18: pid 25729:ERROR: Sorry, too many clients already
2022-03-29 09:47:18: pid 8044:ERROR: Sorry, too many clients already
2022-03-29 09:47:19: pid 25729:ERROR: Sorry, too many clients already
2022-03-29 09:47:19: pid 28849:ERROR: Sorry, too many clients already
2022-03-29 09:47:19: pid 20456:ERROR: Sorry, too many clients already
2022-03-29 09:47:19: pid 30614:ERROR: Sorry, too many clients already
2022-03-29 09:47:20: pid 7983:LOG: child process with pid: 18363 exits with status 256
2022-03-29 09:47:20: pid 7983:LOG: fork a new child process with pid: 19899
2022-03-29 09:47:21: pid 16554:ERROR: Sorry, too many clients already
2022-03-29 09:47:21: pid 16554:ERROR: Sorry, too many clients already
2022-03-29 09:47:22: pid 7983:LOG: child process with pid: 334 exits with status 256
2022-03-29 09:47:22: pid 7983:LOG: fork a new child process with pid: 19903
2022-03-29 09:47:22: pid 7983:LOG: child process with pid: 29810 exits with status 256
2022-03-29 09:47:22: pid 7983:LOG: fork a new child process with pid: 19905
2022-03-29 09:47:23: pid 7983:LOG: child process with pid: 28846 exits with status 256
2022-03-29 09:47:23: pid 7983:LOG: fork a new child process with pid: 19915
2022-03-29 09:47:23: pid 7983:LOG: child process with pid: 31613 exits with status 256
2022-03-29 09:47:23: pid 7983:LOG: fork a new child process with pid: 19916
2022-03-29 09:47:23: pid 2528:ERROR: Sorry, too many clients already
2022-03-29 09:47:23: pid 28383:ERROR: Sorry, too many clients already
2022-03-29 09:47:25: pid 8950:ERROR: unable to read data
2022-03-29 09:47:25: pid 8950:DETAIL: child connection forced to terminate due to client_idle_limit:300 is reached
2022-03-29 09:47:25: pid 16956:ERROR: unable to read data
2022-03-29 09:47:25: pid 16956:DETAIL: child connection forced to terminate due to client_idle_limit:300 is reached
2022-03-29 09:47:26: pid 7983:LOG: child process with pid: 5061 exits with status 256
2022-03-29 09:47:26: pid 7983:LOG: fork a new child process with pid: 19966
2022-03-29 09:47:26: pid 6540:LOG: md5 authentication successful with frontend
2022-03-29 09:47:26: pid 1281:LOG: md5 authentication successful with frontend
2022-03-29 09:47:26: pid 7983:LOG: child process with pid: 16928 exits with status 256
2022-03-29 09:47:26: pid 7983:LOG: fork a new child process with pid: 19970
2022-03-29 09:47:26: pid 10827:ERROR: Sorry, too many clients already
2022-03-29 09:47:26: pid 29679:ERROR: Sorry, too many clients already

raj.pandey1982@gmail.com

2023-08-23 19:07

reporter   ~0004423

Dear Team , This issue still persist ....getting error "Sorry, too many clients already" is continiously appearing in pgpool log", application is throwing jdbc connection error because of this...not able to make all connections even after application restart.
when i do direct DB login there's no issue:-
[postgres@mwdp3prddm01 ~]$ /usr/local/pgsql11.5/bin/psql -h 10.70.185.62 -p 5432 -U postgres -d postgres
psql (11.5)
Type "help" for help.

postgres=# \q

But when i do login using pgpool VIP it gives same error again:-
[postgres@mwdp3prddm01 ~]$ /usr/local/pgsql11.5/bin/psql -h 10.70.185.66 -p 5433 -U postgres -d postgres
psql: FATAL: Sorry, too many clients already.

Only after multiple attempts i can login some times without error.

Pgpool log showing continuous and intermittent error:-

2023-08-23 13:05:38: pid 11671:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 19631:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 12121:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 19428:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 31060:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 13868:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 2613:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 2613:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 22964:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 18610:ERROR: Sorry, too many clients already
2023-08-23 13:05:38: pid 5345:LOG: child process with pid: 14603 exits with status 256
2023-08-23 13:05:38: pid 5345:LOG: fork a new child process with pid: 18327
2023-08-23 13:05:38: pid 5345:LOG: child process with pid: 31488 exits with status 256
2023-08-23 13:05:38: pid 5345:LOG: fork a new child process with pid: 18328
2023-08-23 13:05:39: pid 19953:ERROR: unable to read data

Please help its urgent and importent for my Prod DB. DB Version PostgreSql 11.5 . PGPOOL-II 4.1

ricardo.blanco

2024-07-21 23:05

reporter   ~0004520

Dear Team,

I am currently experiencing the same issue with Pgpool-II during peak hours, receiving the following error:
ERROR: Sorry, too many clients already

I found a post from 2023 where a user described a similar problem, which was causing JDBC connection errors and only resolved after multiple attempts.

Did anyone manage to resolve this issue? I am encountering the same problem with PostgreSQL 12 and Pgpool-II 4.5.1. Any guidance or solutions would be greatly appreciated.

Thank you!

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
2020-09-01 02:51 Nikhil Note Added: 0003549
2020-09-25 09:48 hoshiai Status assigned => feedback
2020-09-25 09:48 hoshiai Note Added: 0003573
2022-03-29 18:22 raj.pandey1982@gmail.com Note Added: 0004012
2022-03-29 18:22 raj.pandey1982@gmail.com Status feedback => assigned
2022-03-29 18:39 raj.pandey1982@gmail.com Note Added: 0004013
2023-08-23 19:07 raj.pandey1982@gmail.com Note Added: 0004423
2024-07-21 23:05 ricardo.blanco Note Added: 0004520