View Issue Details

IDProjectCategoryView StatusLast Update
0000147Pgpool-IIBugpublic2016-01-25 15:36
ReporterwthamesAssigned Tot-ishii 
PrioritynormalSeveritymajorReproducibilityalways
Status resolvedResolutionopen 
PlatformLinuxOSRHELOS Version7.1
Product Version 
Target VersionFixed in Version 
Summary0000147: pgpool connection pool exhausted when connections should have been dropped
DescriptionIf a connection is terminated abruptly (e.g. kill -9 on the client process), the connection is no longer usable, eventually causing thread pool depletion.
Steps To ReproduceWe set up our pgpool cluster to have num_init_children = 5

We then run a process (perl pgpool.pl) that opens a connection, talks to the database repeatedly until terminated. We then terminate that process (using ctrl-c)

If we do this six times, we can no longer obtain a new connection, even after waiting child_life_time or connection_life_time. (i.e. psql -p 9999 times out)
Additional InformationServer:

postgresql-pgpool-II-3.4.3-3.el7.x86_64
postgresql-server-9.2.13-1.el7_1.x86_64

Client:
perl-DBD-Pg-2.19.3-5
TagsNo tags attached.

Activities

wthames

2015-09-30 12:23

reporter  

pgpool.pl (448 bytes)

administrator

2015-09-30 14:17

administrator   ~0000574

Probably already fixed in commit:
http://git.postgresql.org/gitweb/?p=pgpool2.git;a=commit;h=04a288318951869587f74e808b243115dfcb2652

BTW, please use the official pgpool-II RPMs from:
http://www.pgpool.net/yum/

wthames

2015-10-12 15:15

reporter   ~0000578

Hi,
We built a new version of the pgpool RPM using the source RPMs you provide with the patch applied on top.

We still experience the same problem - it did take me more than five connections to exhaust the pool this time, but once it was exhausted, it stayed exhausted.

We would like to use the official RPMs but because we use stock RHEL7.1 with postgresql 9.2, we are unable to. As the standard RPM doesn't contain the patch, it wouldn't have helped us in this instance.

I'll attach the spec file I used to build the RPM.

wthames

2015-10-12 15:15

reporter  

pgpool.spec (12,999 bytes)

wthames

2015-10-12 15:33

reporter   ~0000579

Apologies, I got the spec file slightly wrong and the patch wasn't being applied.
However, we had tested it earlier just applying the patch to the tar ball.

Having updated the spec file, rebuilt the RPM and validated that the build sources had the patch applied, we still see the same problem.

I'll add the newer spec file.

wthames

2015-10-12 15:34

reporter  

pgpool.spec.v2 (13,011 bytes)

khong

2015-12-23 12:03

reporter   ~0000621

Hi,
As mentioned in #c579, the patch has not fixed the issue. Any chance that this issue will be worked on recently? Thanks.

t-ishii

2015-12-25 15:02

developer   ~0000624

I have tried with your perl script and everything works great.

Ubuntu 14.04
pgpool-II 3.4.3
PostgreSQL 9.4.5

yuzheng

2015-12-29 12:44

reporter  

pgpool.conf (6,861 bytes)

yuzheng

2015-12-29 12:45

reporter   ~0000632

OS: RHEL7.0
PostgreSQL: 9.4.5-1PGDG, source - http://yum.postgresql.org/9.4/redhat/rhel-7-x86_64/
pgpool-II: pg94-3.4.3-1pgdg, source - http://www.pgpool.net/yum/rpms/3.4/redhat/rhel-7-x86_64/

Repeat operation of 'perl pgpool.pl, keep running for a few seconds then ctrl-c' 10 times on two environments as follows:

** Single db node - primary only **
Pgpool connections are all alive after 10 times operations.

# ps -ef|grep 'pgpool: wait for'
root 11053 2400 0 10:10 ? 00:00:00 pgpool: wait for connection request
root 11055 2400 0 10:10 ? 00:00:00 pgpool: wait for connection request
root 11056 2400 0 10:10 ? 00:00:00 pgpool: wait for connection request
root 11057 2400 0 10:10 ? 00:00:00 pgpool: wait for connection request
root 11184 2400 0 10:13 ? 00:00:00 pgpool: wait for connection request
root 12000 12433 0 10:35 pts/1 00:00:00 grep --color=auto pgpool: wait for


** Multiple db nodes - primary & 1 slave **
After the 9th operation, all available connections are in DISCARD status. Later queries get no response and those DISCARD connections are not released until killing them manually.

# ps -ef|grep DISCARD
root 11053 2400 0 10:10 ? 00:00:01 pgpool: USERNAME APPLICATION_DATABASE CLIENT_HOSTNAME(55896) DISCARD
root 11055 2400 0 10:10 ? 00:00:01 pgpool: USERNAME APPLICATION_DATABASE CLIENT_HOSTNAME(56060) DISCARD
root 11056 2400 0 10:10 ? 00:00:00 pgpool: USERNAME APPLICATION_DATABASE CLIENT_HOSTNAME(56000) DISCARD
root 11057 2400 0 10:10 ? 00:00:02 pgpool: USERNAME APPLICATION_DATABASE CLIENT_HOSTNAME(55988) DISCARD
root 12143 2400 3 10:38 ? 00:00:07 pgpool: USERNAME APPLICATION_DATABASE CLIENT_HOSTNAME(55841) DISCARD
root 12352 12433 0 10:42 pts/1 00:00:00 grep --color=auto DISCARD

According to above results, this issue seems only happen when there's multiple db nodes. Configuration of pgpool is attached. Any thoughts?

wthames

2016-01-04 11:57

reporter   ~0000633

Adding note to see if that affects issue status

t-ishii

2016-01-23 10:36

developer   ~0000637

I did not succeeded in reproducing your problem. But there's an ongoing discussion on the similar issue and a patch was proposed (pgpool-general: 4355].

I have attached the patch. Can you please try with it?

t-ishii

2016-01-23 10:37

developer  

pool_stream.diff (881 bytes)
diff --git a/src/utils/pool_stream.c b/src/utils/pool_stream.c
index a8d2188..ad97125 100644
--- a/src/utils/pool_stream.c
+++ b/src/utils/pool_stream.c
@@ -226,7 +226,7 @@ int pool_read(POOL_CONNECTION *cp, void *buf, int len)
 			}
 			else
 			{
-                ereport(ERROR,
+                ereport(FRONTEND_ERROR,
 					(errmsg("unable to read data from frontend"),
                          errdetail("socket read failed with an error \"%s\"", strerror(errno))));
 			}
@@ -637,7 +637,7 @@ int pool_flush(POOL_CONNECTION *cp)
                     (errmsg("unable to flush data to frontend"),
                          errdetail("pgpool is in replication mode, ignoring error to keep consistency among backends")));
 			else
-                ereport(ERROR,
+                ereport(FRONTEND_ERROR,
                     (errmsg("unable to flush data to frontend")));
 
 		}
pool_stream.diff (881 bytes)

yuzheng

2016-01-25 13:53

reporter   ~0000638

Hi t-ishii,

I failed to build rpm with this patch. Source rpm is http://pgpool.net/yum/srpms/3.4/redhat/rhel-7-x86_64/pgpool-II-pg94-3.4.3-1pgdg.rhel7.src.rpm. Errors are as below:

utils/pool_stream.c: In function 'pool_read':
utils/pool_stream.c:208:25: error: 'FRONTEND_ERROR' undeclared (first use in this function)
                 ereport(FRONTEND_ERROR,
utils/pool_stream.c: In function 'pool_flush':
utils/pool_stream.c:601:25: error: 'FRONTEND_ERROR' undeclared (first use in this function)
                 ereport(FRONTEND_ERROR,

But I applied another patch - http://git.postgresql.org/gitweb/?p=pgpool2.git;h=78d2fe3fd82b4d2ee90e1369be8dd583196fd36e. It seems to fix the problem.

t-ishii

2016-01-25 14:10

developer   ~0000639

You need the 3.4 stable tree head source to apply the patch.

"FRONTEND_ERROR" was introduced in the commit below.

commit d1cc70653c766dca4ecab91ad639a34d77294fbe
Author: Muhammad Usama <m.usama@gmail.com>
Date: Wed Nov 18 18:31:20 2015 +0500

    Fixing reset query stuck problem [pgpool-hackers: 1097]
    
    The issue is already fixed in older branches by the
    commit id "cb735f22441001b6afdbb5bac72808db66094ca9", and this fix adopts the
    same solution used by 3.3 branch, i.e. closing the backend connection
    when client idle limit is reached.

t-ishii

2016-01-25 15:35

developer   ~0000640

It seems the issue has been already solved in the 3.4 stable tree.
Mark this item "resolved" now.

Issue History

Date Modified Username Field Change
2015-09-30 12:23 wthames New Issue
2015-09-30 12:23 wthames File Added: pgpool.pl
2015-09-30 14:17 administrator Note Added: 0000574
2015-09-30 14:17 administrator Assigned To => t-ishii
2015-09-30 14:17 administrator Status new => feedback
2015-10-12 15:15 wthames Note Added: 0000578
2015-10-12 15:15 wthames Status feedback => assigned
2015-10-12 15:15 wthames File Added: pgpool.spec
2015-10-12 15:33 wthames Note Added: 0000579
2015-10-12 15:34 wthames File Added: pgpool.spec.v2
2015-12-23 12:03 khong Note Added: 0000621
2015-12-25 15:02 t-ishii Note Added: 0000624
2015-12-25 15:02 t-ishii Status assigned => feedback
2015-12-29 12:44 yuzheng File Added: pgpool.conf
2015-12-29 12:45 yuzheng Note Added: 0000632
2016-01-04 11:57 wthames Note Added: 0000633
2016-01-04 11:57 wthames Status feedback => assigned
2016-01-23 10:36 t-ishii Note Added: 0000637
2016-01-23 10:37 t-ishii File Added: pool_stream.diff
2016-01-25 13:53 yuzheng Note Added: 0000638
2016-01-25 14:10 t-ishii Note Added: 0000639
2016-01-25 15:35 t-ishii Note Added: 0000640
2016-01-25 15:36 t-ishii Status assigned => resolved