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

what does pg_activity mean when the database is stuck?

From
Si Chen
Date:
I have a problem where postgresql 9.3 got stuck, and the number of postgresql processes increased from about 15 to 225 in 10 minutes.

I ran the query:
select pid, query_start, waiting, state, query from pg_stat_activity order by query_start;

But it showed mostly select statements -- all of them the same one, with a couple of joins.  They are not in a waiting state but have been running for over 2 hours.  

I also checked for locks with the query on

But it returned no locked tables.

So what does this mean?  Is the select query getting stuck?  
--
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps

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

From
Ciba Sadaf
Date:

Dnt have any unfrmtn

On 11-Jun-2014 9:30 PM, "Si Chen" <sichen@opensourcestrategies.com> wrote:
I have a problem where postgresql 9.3 got stuck, and the number of postgresql processes increased from about 15 to 225 in 10 minutes.

I ran the query:
select pid, query_start, waiting, state, query from pg_stat_activity order by query_start;

But it showed mostly select statements -- all of them the same one, with a couple of joins.  They are not in a waiting state but have been running for over 2 hours.  

I also checked for locks with the query on

But it returned no locked tables.

So what does this mean?  Is the select query getting stuck?  
--
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps

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

From
Jeff Janes
Date:
On Wed, Jun 11, 2014 at 8:59 AM, Si Chen <sichen@opensourcestrategies.com> wrote:
I have a problem where postgresql 9.3 got stuck, and the number of postgresql processes increased from about 15 to 225 in 10 minutes.

I ran the query:
select pid, query_start, waiting, state, query from pg_stat_activity order by query_start;

But it showed mostly select statements -- all of them the same one, with a couple of joins.  They are not in a waiting state but have been running for over 2 hours.  

I also checked for locks with the query on

But it returned no locked tables.

So what does this mean?  Is the select query getting stuck?  

Do you have a huge chunk of newly insert, not yet committed, rows?  This sounds like the issue where all of the processes fight with each other over the right to check uncommitted rows in order to verify that they are actually uncommitted.

Cheers,

Jeff

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

From
Si Chen
Date:
I didn't see any from the log.  It was just a whole bunch of pretty standard looking SELECT queries.  There were no INSERT/COMMIT statements which were still active before the SELECT's, just a few which are waiting after a lot of SELECT statements.

Also, if the process just shows COMMIT, is there any way to see what it's trying to commit?  


On Wed, Jun 11, 2014 at 9:29 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Jun 11, 2014 at 8:59 AM, Si Chen <sichen@opensourcestrategies.com> wrote:
I have a problem where postgresql 9.3 got stuck, and the number of postgresql processes increased from about 15 to 225 in 10 minutes.

I ran the query:
select pid, query_start, waiting, state, query from pg_stat_activity order by query_start;

But it showed mostly select statements -- all of them the same one, with a couple of joins.  They are not in a waiting state but have been running for over 2 hours.  

I also checked for locks with the query on

But it returned no locked tables.

So what does this mean?  Is the select query getting stuck?  

Do you have a huge chunk of newly insert, not yet committed, rows?  This sounds like the issue where all of the processes fight with each other over the right to check uncommitted rows in order to verify that they are actually uncommitted.

Cheers,

Jeff



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

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

From
Igor Neyman
Date:
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Si Chen
Sent: Wednesday, June 11, 2014 4:34 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] what does pg_activity mean when the database is stuck?

I didn't see any from the log.  It was just a whole bunch of pretty standard looking SELECT queries.  There were no
INSERT/COMMITstatements which were still active before the SELECT's, just a few which are waiting after a lot of SELECT
statements.

Also, if the process just shows COMMIT, is there any way to see what it's trying to commit?  

On Wed, Jun 11, 2014 at 9:29 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Jun 11, 2014 at 8:59 AM, Si Chen <sichen@opensourcestrategies.com> wrote:
I have a problem where postgresql 9.3 got stuck, and the number of postgresql processes increased from about 15 to 225
in10 minutes.
 

I ran the query:
select pid, query_start, waiting, state, query from pg_stat_activity order by query_start;

But it showed mostly select statements -- all of them the same one, with a couple of joins.  They are not in a waiting
statebut have been running for over 2 hours.  
 

I also checked for locks with the query on
http://wiki.postgresql.org/wiki/Lock_Monitoring

But it returned no locked tables.

So what does this mean?  Is the select query getting stuck?  

Do you have a huge chunk of newly insert, not yet committed, rows?  This sounds like the issue where all of the
processesfight with each other over the right to check uncommitted rows in order to verify that they are actually
uncommitted.

Cheers,

Jeff


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


When you query pg_stat_activity, what do you see in state column, and how state_change compares to query_start?

Regards,
Igor Neyman


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

From
Si Chen
Date:
The state is "idle".  I don't have the state_change, but I will try to collect it if it happens again.


On Wed, Jun 11, 2014 at 1:46 PM, Igor Neyman <ineyman@perceptron.com> wrote:
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Si Chen
Sent: Wednesday, June 11, 2014 4:34 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] what does pg_activity mean when the database is stuck?

I didn't see any from the log.  It was just a whole bunch of pretty standard looking SELECT queries.  There were no INSERT/COMMIT statements which were still active before the SELECT's, just a few which are waiting after a lot of SELECT statements.

Also, if the process just shows COMMIT, is there any way to see what it's trying to commit?  

On Wed, Jun 11, 2014 at 9:29 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Wed, Jun 11, 2014 at 8:59 AM, Si Chen <sichen@opensourcestrategies.com> wrote:
I have a problem where postgresql 9.3 got stuck, and the number of postgresql processes increased from about 15 to 225 in 10 minutes.

I ran the query:
select pid, query_start, waiting, state, query from pg_stat_activity order by query_start;

But it showed mostly select statements -- all of them the same one, with a couple of joins.  They are not in a waiting state but have been running for over 2 hours.  

I also checked for locks with the query on
http://wiki.postgresql.org/wiki/Lock_Monitoring

But it returned no locked tables.

So what does this mean?  Is the select query getting stuck?  

Do you have a huge chunk of newly insert, not yet committed, rows?  This sounds like the issue where all of the processes fight with each other over the right to check uncommitted rows in order to verify that they are actually uncommitted.

Cheers,

Jeff


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


When you query pg_stat_activity, what do you see in state column, and how state_change compares to query_start?

Regards,
Igor Neyman




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

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

From
Merlin Moncure
Date:
On Wed, Jun 11, 2014 at 5:37 PM, Si Chen
<sichen@opensourcestrategies.com> 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


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

From
Si Chen
Date:
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@gmail.com> wrote:
On Wed, Jun 11, 2014 at 5:37 PM, Si Chen
<sichen@opensourcestrategies.com> 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@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps

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

From
Michael Paquier
Date:
On Thu, Jun 12, 2014 at 11:44 AM, Si Chen
<sichen@opensourcestrategies.com> wrote:
> Is there a way to configure postgresql to automatically release connections
> that have been idle for a set amount of time?
Not directly. However with 9.3 you could use a background worker like this one:
https://github.com/michaelpq/pg_plugins/tree/master/kill_idle
It tracks connections that have been idle for a given amount of time
and closes them with pg_terminate_backend when theyr are idle for a
amount of time longer than the one set.
--
Michael


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

From
Michael Paquier
Date:
On Thu, Jun 12, 2014 at 6:34 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
> On Thu, Jun 12, 2014 at 11:44 AM, Si Chen
> <sichen@opensourcestrategies.com> wrote:
>> Is there a way to configure postgresql to automatically release connections
>> that have been idle for a set amount of time?
> Not directly. However with 9.3 you could use a background worker like this one:
> https://github.com/michaelpq/pg_plugins/tree/master/kill_idle
> It tracks connections that have been idle for a given amount of time
> and closes them with pg_terminate_backend when theyr are idle for a
> amount of time longer than the one set.
Just adding that this bgworker is rather experimental, and that the
common recoommendation is to have your application close correctly the
connection it uses...
--
Michael


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

From
Igor Neyman
Date:
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Si Chen
Sent: Wednesday, June 11, 2014 10:44 PM
To: pgsql-general@postgresql.org
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@gmail.com> wrote:
On Wed, Jun 11, 2014 at 5:37 PM, Si Chen
<sichen@opensourcestrategies.com> 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@opensourcestrategies.com
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


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

From
Jerry Sievers
Date:
Si Chen <sichen@opensourcestrategies.com> writes:

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

I've seen recent discussion in hackers that this may be added to an
upcoming release.


> On Wed, Jun 11, 2014 at 3:41 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
>
>     On Wed, Jun 11, 2014 at 5:37 PM, Si Chen
>     <sichen@opensourcestrategies.com> 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@opensourcestrategies.com
> http://www.OpenSourceStrategies.com
> LinkedIn: http://www.linkedin.com/in/opentaps
> Twitter: http://twitter.com/opentaps
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


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

From
Si Chen
Date:
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@perceptron.com> wrote:
Sent: Wednesday, June 11, 2014 10:44 PM
To: pgsql-general@postgresql.org
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@gmail.com> wrote:
On Wed, Jun 11, 2014 at 5:37 PM, Si Chen
<sichen@opensourcestrategies.com> 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@opensourcestrategies.com
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@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps

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

From
John R Pierce
Date:
On 6/12/2014 4:24 PM, Si Chen wrote:
> PgBouncer looks pretty cool.  Do you recommend using it with jdbc with
> about 50 - 100 normal connections?

Java has quite a few built in connection pooling options.

they all work best if your software is configured to grab a connection,
use it for a transaction, then release it back to the pool.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast



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

From
Si Chen
Date:
Yes, that would be nice, but it's a big app a lot of which we didn't write, etc.--you can imagine the rest ;)

Is there even a way for postgreql to get me all the queries that has gone through a particular process, so we can see what the app was trying to do that left the connection hanging? 


On Thu, Jun 12, 2014 at 4:55 PM, John R Pierce <pierce@hogranch.com> wrote:
On 6/12/2014 4:24 PM, Si Chen wrote:
PgBouncer looks pretty cool.  Do you recommend using it with jdbc with about 50 - 100 normal connections?

Java has quite a few built in connection pooling options.

they all work best if your software is configured to grab a connection, use it for a transaction, then release it back to the pool.

--
john r pierce                                      37N 122W
somewhere on the middle of the left coast



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



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