Re: what does pg_activity mean when the database is stuck?

[Si Chen <sichen@xxxxxxxxxxxxxxxxxxxxxxxx>]

PgBouncer looks pretty cool.  Do you recommend using it with jdbc with about 50 - 100 normal connections?

On Thu, Jun 12, 2014 at 6:35 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:

From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Si Chen

Sent: Wednesday, June 11, 2014 10:44 PM

To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] what does pg_activity mean when the database is stuck?

Is there a way to configure postgresql to automatically release connections that have been idle for a set amount of time?

On Wed, Jun 11, 2014 at 3:41 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On Wed, Jun 11, 2014 at 5:37 PM, Si Chen
<sichen@xxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> The state is "idle".  I don't have the state_change, but I will try to
> collect it if it happens again.
If they are idle, then the problem is probably with your application
-- you're grabbing new connections and not closing them or reusing
them.  It's a very common problem.  The 'query' when idle represents
the last query run -- the database finished it and is sitting around.

merlin

--
Si Chen
Open Source Strategies, Inc.
sichen@xxxxxxxxxxxxxxxxxxxxxxxx
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps

The best solution for this is to use some connection pooler, such as PgBouncer.
B.t.w., PgBouncer can also disconnect idle client connections (if you really wish) based on configuration setting.

Regards,
Igor Neyman

--
Si Chen
Open Source Strategies, Inc.
sichen@xxxxxxxxxxxxxxxxxxxxxxxx
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps