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

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(at)perceptron(dot)com> wrote:

> From: pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Si Chen
> Sent: Wednesday, June 11, 2014 4:34 PM
> To: pgsql-general(at)postgresql(dot)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(dot)janes(at)gmail(dot)com> wrote:
> On Wed, Jun 11, 2014 at 8:59 AM, Si Chen <sichen(at)opensourcestrategies(dot)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(at)opensourcestrategies(dot)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(at)opensourcestrategies(dot)com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps