View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000053 | Pgpool-II | Bug | public | 2013-04-04 13:35 | 2013-05-14 12:01 |
| Reporter | tmandke | Assigned To | t-ishii | ||
| Priority | urgent | Severity | block | Reproducibility | always |
| Status | resolved | Resolution | reopened | ||
| OS | ubuntu | OS Version | 12.04 | ||
| Summary | 0000053: pgpool_regclass hangs all connections | ||||
| Description | Every 0000004:0000005 hrs we see the following on out Master DB. select waiting, now() -query_start as query_duration,pid, usename, state, query from pg_stat_activity where query_start < now() - '3 minutes'::interval and usename = 'user1' order by now() -query_start desc; waiting | query_duration | pid | usename | state | query ---------+-----------------+-------+------------+--------+-------------------------------------------------------------------------------------------------------------------------------- f | 03:14:36.013483 | 59868 | user1 | active | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pgpool_regclass('orgs') AND c.relpersistence = 'u' f | 03:14:27.934869 | 25200 | user1 | active | SELECT count(*) FROM pg_class AS c, pg_namespace AS n WHERE c.oid = pgpool_regclass('pg_namespace') AND c.relnamespace = n.oid AND n.nspname = 'pg_catalog' f | 03:14:26.793803 | 16003 | user1 | active | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pgpool_regclass('orgs') AND c.relpersistence = 'u' f | 03:12:54.47275 | 19087 | user1 | active | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pgpool_regclass('orgs') AND c.relpersistence = 'u' f | 03:12:31.468402 | 59817 | user1 | active | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid = pgpool_regclass('orgs') AND c.relpersistence = 'u' ... all 300 connections are calling pgpool_regclass in some way. When I go to htop I see all the queries are in a PARSE state. Our setup is pgbouncer -> pgpool -> (Master-Slave streaming replication) I really do not know how to debug this and I can not find anything wrong with the setup (it works fine for a few hours and then this happens). Really appreciate the help | ||||
| Additional Information | We sometimes need to get schema dumps to put on the various stages. And we do not use pgpool in them and did not want to have to install the pgpool libraries. so we moved the pgpool functions to their own schema and set the search_path for the database to include their schema. | ||||
| Tags | No tags attached. | ||||
|
|
forgot to mentions version pgpool 3.2.3 postgres 9.2.2 loadbalance mode on healthcheck on |
|
|
Do you know how reliably reproduce the problem? |
|
|
I have not been able to reproduce it on a smaller scale. But on our large production database when we have regular traffic and then I run a job that requires a lot of DB resources(reindexing our solr all 600 mill of complicated documents which takes 5-6 hrs with load-balancing). It really starts piling up. Regular connections : 253 (50 active connections hitting pgpool on regular use) Reindex job connections : 400 pgbouncer is allowed to only send 300 connections to pgpool. i.e. 653 connections hitting pgbouncer -> 300 connections hitting pgpool pgpool excellently load balances them across both master and slave (0000031:0000150 on both active). I have been looking through pgpool, postgres and pgbouncer logs and only found one thing that was weird. I am getting postgres logs 2013-04-04 02:59:24 UTC:172.31.40.41(51049):user1@production:[60145]: ERROR: function pgpool_regclass(unknown) does not exist at character 61 2013-04-04 02:59:24 UTC:172.31.40.41(51049):user1@production:[60145]: HINT: No function matches the given name and argument types. You might need to add explicit type casts. 2013-04-04 02:59:24 UTC:172.31.40.41(51049):user1@production:[60145]: STATEMENT: SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid =pgpool_regclass('inverse_friends') AND c.relpersistence = 'u' I know for a fact that user1 on DB production has the function pgpool_regclass And this seems to be happening a lot. I will try again tomorrow to setup something that can be reproduced in a control environment. Hope it helps |
|
|
I just want to make sure that when the case happens, you cannot use database at all? Or you just see lots of the pgpool_regclass() calling? |
|
|
I can not use it through pgpool. If I connect to DB directly it works very lowly but it works. |
|
|
Ok so I figured out the problem. pgpool saw that the function existed but the user did not have privileges to use/view it. I was mistaken before because I login as a superuser always and I was able to view the function. Sorry about the trouble. but it would be nice to throw a message when this happens saying that the function exists but pgpool can not find it plz check privileges of the user. Thanks |
|
|
Thanks for problem analysis. Let me think what pgpool can do for this. |
|
|
I fixed this to use has_function_privilege for pgpool_regclass. Fix commited to master and all supported branches. |
|
|
Awesome thanks |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2013-04-04 13:35 | tmandke | New Issue | |
| 2013-04-04 13:38 | tmandke | Note Added: 0000252 | |
| 2013-04-04 13:39 | tmandke | Note Edited: 0000252 | |
| 2013-04-04 15:09 | t-ishii | Note Added: 0000253 | |
| 2013-04-04 16:40 | tmandke | Note Added: 0000254 | |
| 2013-04-04 16:51 | t-ishii | Note Added: 0000255 | |
| 2013-04-05 00:01 | tmandke | Note Added: 0000256 | |
| 2013-04-05 07:39 | tmandke | Note Added: 0000258 | |
| 2013-04-05 07:56 | t-ishii | Note Added: 0000259 | |
| 2013-04-05 07:56 | t-ishii | Assigned To | => t-ishii |
| 2013-04-05 07:56 | t-ishii | Status | new => resolved |
| 2013-04-07 12:35 | t-ishii | Note Added: 0000260 | |
| 2013-04-07 12:35 | t-ishii | Status | resolved => feedback |
| 2013-04-07 12:35 | t-ishii | Resolution | open => reopened |
| 2013-04-07 13:22 | tmandke | Note Added: 0000261 | |
| 2013-04-07 13:22 | tmandke | Status | feedback => assigned |
| 2013-05-14 12:01 | t-ishii | Status | assigned => resolved |
