[pgpool-general: 1314] Re: Database connections seemingly hanging

Yugo Nagata nagata at sraoss.co.jp
Fri Jan 11 14:12:34 JST 2013

I'm investigating it.

Could you tell me the following information?

- What mode are you using pgpool in?
  -- master-slave mode?
  -- replication mode?
  -- how about load blance?

- How frequent is the quartz job triggered?

On Wed, 9 Jan 2013 11:21:02 +0100
Fredrik.HuitfeldtMadsen at schneider-electric.com wrote:

> Hi All,
> We have a setup where 2 JBoss (5.1) servers communicate with 1 instance of 
> PgPool (3.04), which again communicates with 2 Postgresql (8.4) servers. 
> The JBoss servers host some Java code for us and as part of that they run 
> some quartz jobs. 
> These jobs are triggered right after startup and as part of that we get 
> what seems to get stuck. At least when we can see in the database that 
> when inspecting pg_locks, there exists a virtual transaction that has all 
> desired locks granted but seems to be stuck. When we inspect 
> pg_stat_activity, it seems that the process is still waiting for the query 
> (SELECT ... FOR UPDATE) to finish.
> The locking transaction is described here: http://pastebin.com/3pEn6vPe
> We know that the quartz thread is attempting to obtain a row share lock. 
> We know that we have enough connections available in postgres and in 
> pgpool. We also know that the issue occurs much more frequently when we 
> enable postgres statememt logging. We assume that this is due to postgres 
> becomming slower as a result of the additionsl logging. When we look at 
> the server thread dump, we can see that all quartz threads are either 
> sleeping or waiting for postgres.
> A thread dump of the relevant quartz threads is described here: 
> http://pastebin.com/iPhuFLrM
> It is important to note that the issue does not only occur with quartz 
> jobs, but it is where we see it most frequently. This is likely to be due 
> to the fact that it is the place where we have the highest level of 
> concurrency.
> We suspect that a connection to the database acquires its locks but 
> somehow does not return to the application. If this is true, it would 
> either be a postgresql or a pgpool problem. We would appreciate any help 
> in further debugging or resolving the situation. 
> Kind regards,
> Fredrik

Yugo Nagata <nagata at sraoss.co.jp>

More information about the pgpool-general mailing list