View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000254 | Pgpool-II | Bug | public | 2016-10-18 23:45 | 2018-02-16 11:00 |
| Reporter | tatzlwurm2 | Assigned To | t-ishii | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | closed | Resolution | open | ||
| Product Version | 3.5.4 | ||||
| Summary | 0000254: Same as http://www.pgpool.net/mantisbt/view.php?id=53 | ||||
| Description | I see a fix for this but I don't see it in 3.5.4. After running pgpool for a number of days I see quite a few hanging sessions: pid type s utime stime guest read write uss age db user query 14102 backend S 0.0 0.0 0.0 0.0 0.0 4.7 3d18:04:58 irdb groups_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"group_types_x... 23678 backend S 0.0 0.0 0.0 0.0 0.0 1.6 20:51:55 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 23679 backend S 0.0 0.0 0.0 0.0 0.0 1.6 20:51:55 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 23680 backend S 0.0 0.0 0.0 0.0 0.0 1.6 20:51:55 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 23681 backend S 0.0 0.0 0.0 0.0 0.0 1.6 20:51:55 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 19249 backend S 0.0 0.0 0.0 0.0 0.0 1.6 19:52:22 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 4207 backend S 0.0 0.0 0.0 0.0 0.0 1.6 12:43:30 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 4211 backend S 0.0 0.0 0.0 0.0 0.0 1.6 12:43:30 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 4212 backend S 0.0 0.0 0.0 0.0 0.0 1.6 12:43:30 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 4214 backend S 0.0 0.0 0.0 0.0 0.0 1.6 12:43:30 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 4215 backend S 0.0 0.0 0.0 0.0 0.0 1.6 12:43:30 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 4216 backend S 0.0 0.0 0.0 0.0 0.0 1.6 12:43:29 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"... 4219 backend S 0.0 0.0 0.0 0.0 0.0 1.6 12:43:29 irdb instreg_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions_... | ||||
| Steps To Reproduce | If I use pg_cancel_backend() -- this has no effect and sessions stay. If I use pg_terminate_backend() pgpool disconnects the master. I couldn't reproduce this pgbench. The app is using apaches connection pooling. | ||||
| Tags | No tags attached. | ||||
|
|
https://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html -- this is the connection pooling that the application is using. |
|
|
I see this in source code. source/pgpool-II-3.5.4 >grep has_function ./src/utils/pool_select_walker.c bool pool_has_function_call(Node *node) ctx.has_function_call = false; return ctx.has_function_call; ctx->has_function_call = true; ctx->has_function_call = true; #define HASPGPOOL_REGCLASSQUERY "SELECT count(*) from (SELECT has_function_privilege('%s', 'pgpool_regclass(cstring)', 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'pgpool_regclass')) AS s" #define HAS_TOREGCLASSQUERY "SELECT count(*) from (SELECT has_function_privilege('%s', 'to_regclass(cstring)', 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'to_regclass')) AS s" |
|
|
http://www.sraoss.jp/pipermail/pgpool-hackers/2013-April/000191.html From source I tried: SELECT count(*) from (SELECT has_function_privilege('groups_u', 'to_regclass(cstring)', 'execute') WHERE EXISTS(SELECT * FROM pg_catalog.pg_proc AS p WHERE p.proname = 'to_regclass')) AS s; |
|
|
Also tried SELECT has_function_privilege('instreg_u', 'to_regclass(cstring)', 'execute'); and didn't seem to help. |
|
|
Please let me know if you find a way to reproduce the problem. |
|
|
I haven't been able manually but reproduces on demand with that tomcat connection pooling. I think its related to that. I asked the developer if he could remove and use a different connection pool. Any suggestions? |
|
|
pid type s utime stime guest read write uss age db user query 14102 backend S 0.0 0.0 0.0 0.0 0.0 4.7 3d18:04:58 irdb groups_u SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"group_types_x... Does not seem to be a output from ps command. What's that? |
|
|
Thats from pg_view. I had the developer just disable the connection pool from the app. This means that the app would just request a new connection every time it had a request verses using its own connection pool. The hung session is no longer a issue. That seems to be the issue having a connection pool keeping sessions open in pgpool. |
|
|
I just checked again, and still is a problem I didn't observe long enough. Sessions like the one above are still hanging for days. -[ RECORD 1 ]----+--------------------------------------------------------------------------------------------------------------------- datid | 16385 datname | irdb pid | 14822 usesysid | 16386 usename | instreg_u application_name | client_addr | 10.32.90.62 client_hostname | postgresdb01dxdu.dev.oclc.org client_port | 39983 backend_start | 2016-11-10 01:12:40.729423-05 xact_start | 2016-11-10 01:14:40.764156-05 query_start | 2016-11-10 10:44:22.312468-05 state_change | 2016-11-10 10:44:22.31249-05 waiting | f state | active backend_xid | backend_xmin | 2699397 query | select institution_id from synch_requests order by added -[ RECORD 2 ]----+--------------------------------------------------------------------------------------------------------------------- datid | 16385 datname | irdb pid | 28351 usesysid | 16386 usename | instreg_u application_name | client_addr | 10.32.90.62 client_hostname | postgresdb01dxdu.dev.oclc.org client_port | 40935 backend_start | 2016-10-28 03:55:18.100668-04 xact_start | 2016-10-28 03:55:18.130025-04 query_start | 2016-10-28 03:55:18.142601-04 state_change | 2016-10-28 03:55:18.142602-04 waiting | f state | active backend_xid | backend_xmin | query | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"') AND c.relpersistence = 'u' -[ RECORD 3 ]----+--------------------------------------------------------------------------------------------------------------------- datid | 16385 datname | irdb pid | 21048 usesysid | 16386 usename | instreg_u application_name | client_addr | 10.32.90.62 client_hostname | postgresdb01dxdu.dev.oclc.org client_port | 35832 backend_start | 2016-10-30 22:00:34.176635-04 xact_start | 2016-10-30 22:00:34.20748-04 query_start | 2016-10-30 22:00:34.227646-04 state_change | 2016-10-30 22:00:34.22765-04 waiting | f state | active backend_xid | backend_xmin | query | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"') AND c.relpersistence = 'u' -[ RECORD 4 ]----+--------------------------------------------------------------------------------------------------------------------- datid | 16385 datname | irdb pid | 10707 usesysid | 16386 usename | instreg_u application_name | client_addr | 10.32.90.62 client_hostname | postgresdb01dxdu.dev.oclc.org client_port | 48269 backend_start | 2016-11-10 02:12:38.876755-05 xact_start | 2016-11-10 02:12:38.907243-05 query_start | 2016-11-10 02:12:38.921026-05 state_change | 2016-11-10 02:12:38.921027-05 waiting | f state | active backend_xid | backend_xmin | query | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"') AND c.relpersistence = 'u' -[ RECORD 5 ]----+--------------------------------------------------------------------------------------------------------------------- datid | 16385 datname | irdb pid | 5636 usesysid | 16386 usename | instreg_u application_name | client_addr | 10.32.90.62 client_hostname | postgresdb01dxdu.dev.oclc.org client_port | 45384 backend_start | 2016-10-24 22:00:18.123636-04 xact_start | 2016-10-24 22:00:18.154571-04 query_start | 2016-10-24 22:00:18.166983-04 state_change | 2016-10-24 22:00:18.166985-04 waiting | f state | active backend_xid | backend_xmin | query | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = to_regclass('"institutions"') AND c.relpersistence = 'u' -[ RECORD 6 ]----+--------------------------------------------------------------------------------------------------------------------- datid | 16385 datname | irdb pid | 24872 usesysid | 16386 usename | instreg_u application_name | client_addr | 10.32.90.62 client_hostname | postgresdb01dxdu.dev.oclc.org client_port | 41068 backend_start | 2016-10-24 17:28:42.806725-04 xact_start | 2016-10-24 17:28:42.840085-04 query_start | 2016-10-24 17:28:42.858612-04 state_change | 2016-10-24 17:28:42.858613-04 waiting | f state | active backend_xid | backend_xmin | 1551997 query | select * from institutions limit 1 -[ RECORD 7 ]----+--------------------------------------------------------------------------------------------------------------------- datid | 16385 datname | irdb pid | 7291 usesysid | 16386 usename | instreg_u application_name | client_addr | 10.32.90.62 client_hostname | postgresdb01dxdu.dev.oclc.org client_port | 43821 backend_start | 2016-10-24 17:51:36.33618-04 xact_start | 2016-10-24 17:51:36.359848-04 query_start | 2016-10-24 17:51:36.375739-04 state_change | 2016-10-24 17:51:36.375742-04 waiting | f state | active backend_xid | backend_xmin | 1553082 query | select * from institutions limit 1 |
|
|
Do you still see the issue with the latest version of Pgpool-II? If not, I would like to close the issue. |
|
|
No response over 2 months. Issue closed. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2016-10-18 23:45 | tatzlwurm2 | New Issue | |
| 2016-10-19 00:04 | tatzlwurm2 | Note Added: 0001120 | |
| 2016-10-19 00:20 | tatzlwurm2 | Note Added: 0001121 | |
| 2016-10-19 00:26 | tatzlwurm2 | Note Added: 0001122 | |
| 2016-10-19 00:30 | tatzlwurm2 | Note Added: 0001123 | |
| 2016-10-19 11:58 | t-ishii | Assigned To | => t-ishii |
| 2016-10-19 11:58 | t-ishii | Status | new => assigned |
| 2016-10-19 11:58 | t-ishii | Note Added: 0001125 | |
| 2016-10-19 11:59 | t-ishii | Status | assigned => feedback |
| 2016-10-19 22:40 | tatzlwurm2 | Note Added: 0001126 | |
| 2016-10-19 22:40 | tatzlwurm2 | Status | feedback => assigned |
| 2016-10-20 07:45 | t-ishii | Note Added: 0001128 | |
| 2016-10-20 07:46 | t-ishii | Status | assigned => feedback |
| 2016-10-21 03:51 | tatzlwurm2 | Note Added: 0001131 | |
| 2016-10-21 03:51 | tatzlwurm2 | Status | feedback => assigned |
| 2016-11-10 14:25 | t-ishii | Status | assigned => feedback |
| 2016-11-11 00:44 | tatzlwurm2 | Note Added: 0001166 | |
| 2016-11-11 00:44 | tatzlwurm2 | Status | feedback => assigned |
| 2017-12-13 15:21 | t-ishii | Note Added: 0001863 | |
| 2017-12-13 15:21 | t-ishii | Status | assigned => feedback |
| 2018-02-16 11:00 | t-ishii | Note Added: 0001933 | |
| 2018-02-16 11:00 | t-ishii | Status | feedback => closed |
