View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000147 | Pgpool-II | Bug | public | 2015-09-30 12:23 | 2016-01-25 15:36 |
| Reporter | wthames | Assigned To | t-ishii | ||
| Priority | normal | Severity | major | Reproducibility | always |
| Status | resolved | Resolution | open | ||
| Platform | Linux | OS | RHEL | OS Version | 7.1 |
| Summary | 0000147: pgpool connection pool exhausted when connections should have been dropped | ||||
| Description | If 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 Reproduce | We 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 Information | Server: 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 | ||||
| Tags | No tags attached. | ||||
|
|
|
|
|
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/ |
|
|
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. |
|
|
|
|
|
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. |
|
|
|
|
|
Hi, As mentioned in #c579, the patch has not fixed the issue. Any chance that this issue will be worked on recently? Thanks. |
|
|
I have tried with your perl script and everything works great. Ubuntu 14.04 pgpool-II 3.4.3 PostgreSQL 9.4.5 |
|
|
|
|
|
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? |
|
|
Adding note to see if that affects issue status |
|
|
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? |
|
|
|
|
|
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. |
|
|
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. |
|
|
It seems the issue has been already solved in the 3.4 stable tree. Mark this item "resolved" now. |
| 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 |
