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 <iney...@perceptron.com> wrote: > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@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.ja...@gmail.com> wrote: > On Wed, Jun 11, 2014 at 8:59 AM, Si Chen <sic...@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. > sic...@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. sic...@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps
