View Issue Details

IDProjectCategoryView StatusLast Update
0000053Pgpool-IIBugpublic2013-05-14 12:01
ReportertmandkeAssigned Tot-ishii 
PriorityurgentSeverityblockReproducibilityalways
Status resolvedResolutionreopened 
PlatformOSubuntuOS Version12.04
Product Version 
Target VersionFixed in Version 
Summary0000053: pgpool_regclass hangs all connections
DescriptionEvery 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 InformationWe 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.
TagsNo tags attached.

Activities

tmandke

2013-04-04 13:38

reporter   ~0000252

Last edited: 2013-04-04 13:39

View 2 revisions

forgot to mentions version
pgpool 3.2.3
postgres 9.2.2

loadbalance mode on
healthcheck on

t-ishii

2013-04-04 15:09

developer   ~0000253

Do you know how reliably reproduce the problem?

tmandke

2013-04-04 16:40

reporter   ~0000254

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

t-ishii

2013-04-04 16:51

developer   ~0000255

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?

tmandke

2013-04-05 00:01

reporter   ~0000256

I can not use it through pgpool.
 If I connect to DB directly it works very lowly but it works.

tmandke

2013-04-05 07:39

reporter   ~0000258

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

t-ishii

2013-04-05 07:56

developer   ~0000259

Thanks for problem analysis. Let me think what pgpool can do for this.

t-ishii

2013-04-07 12:35

developer   ~0000260

I fixed this to use has_function_privilege for pgpool_regclass. Fix commited to master and all supported branches.

tmandke

2013-04-07 13:22

reporter   ~0000261

Awesome thanks

Issue History

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 View Revisions
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