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@xxxxxxxxxxxxxx> wrote:
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Si Chen
Sent: Wednesday, June 11, 2014 4:34 PM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] what does pg_activity mean when the database is stuck?
When you query pg_stat_activity, what do you see in state column, and how state_change compares to query_start?
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@xxxxxxxxx> wrote:
On Wed, Jun 11, 2014 at 8:59 AM, Si Chen <sichen@xxxxxxxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxxxxxxxx
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps
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
