Обсуждение: PG9.2.3. Query hanging: SELECT count(*) FROM pg_catalog.pg_class...
Howdy!
This query is coming from PgPool I believe.
SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname = 'import_jobs' AND c.relpersistence = 'u'
This is a very small database, like 10/15 tables, it's basically empty.
If i run this query manually, it comes back immediatly.
However according to this it's been running for about an hour.
v3=# select now();
now
-------------------------------
2013-02-15 21:49:34.470466+00
(1 row)
-[ RECORD 5
]----+---------------------------------------------------------------------------------------------------------
datid | 16389
datname | v3
pid | 13905
usesysid | 20415
usename | workling
application_name |
client_addr | 10.0.0.132
client_hostname |
client_port | 58009
backend_start | 2013-02-15 20:49:09.098768+00
xact_start | 2013-02-15 20:49:09.189625+00
query_start | 2013-02-15 20:49:09.198742+00
state_change | 2013-02-15 20:49:09.198743+00
waiting | f
state | active
query | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname = 'import_jobs' AND c.relpersistence =
'u'
(I actually restarted the application and now there are 4 of these)
Any idea why the query would hang like this?
I don't see it waiting on locks or anything like that.
any ideas?
Thanks
Re: PG9.2.3. Query hanging: SELECT count(*) FROM pg_catalog.pg_class...
От
"René Romero Benavides"
Дата:
<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Ican confirm it comes from pgpool, and is very weird , I guess the pgpool list would be a better fit.<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;-qt-user-state:0;">Cheers.<p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">On Friday, February 15, 2013 01:58:55PM David Kerr wrote:<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> Howdy!<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>This query is coming from PgPool I believe.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname =<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> 'import_jobs'AND c.relpersistence = 'u'<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> This is a very small database,like 10/15 tables, it's basically empty.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> If i run this query manually, it comes backimmediatly.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;-qt-user-state:0;">> However according to this it's been running for about an hour.<p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">><p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> v3=# select now();<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> now<p style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>-------------------------------<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> 2013-02-15 21:49:34.470466+00<p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>(1 row)<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> -[ RECORD 5<p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>]----+---------------------------------------------------------------------<p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>------------------------------------ datid | 16389<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> datname | v3<p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>pid | 13905<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> usesysid | 20415<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> usename| workling<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;-qt-user-state:0;">> application_name |<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> client_addr | 10.0.0.132<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;-qt-user-state:0;">> client_hostname |<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> client_port | 58009<p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>backend_start | 2013-02-15 20:49:09.098768+00<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> xact_start | 2013-02-15 20:49:09.189625+00<pstyle=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;-qt-user-state:0;">> query_start | 2013-02-15 20:49:09.198742+00<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> state_change| 2013-02-15 20:49:09.198743+00<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> waiting | f<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> state |active<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px;-qt-user-state:0;">> query | SELECT count(*) FROM pg_catalog.pg_class AS c WHERE<p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>c.relname = 'import_jobs' AND c.relpersistence = 'u'<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>(I actually restarted the application and now there are 4 of these)<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>Any idea why the query would hang like this?<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>I don't see it waiting on locks or anything like that.<p style=" margin-top:0px; margin-bottom:0px;margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> <p style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">>any ideas?<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; -qt-user-state:0;">> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">> Thanks<br />-- <p style="margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">RenéRomero Benavides<span style=" font-family:'liberation sans';"> </span><a href="https://twitter.com/iCodeiExist"><spanstyle=" font-family:'liberation sans'; text-decoration: underline; color:#0057ae;">@iCodeiExist</span></a><a href="https://twitter.com/PgsqlMx"><span style=" text-decoration: underline; color:#0057ae;">@PgsqlMx</span></a><pstyle="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style=" margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; -qt-user-state:0;">Postgresql Tips en español parala comunidad de México e Hispanoamérica.<p style=" margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px;-qt-block-indent:0; text-indent:0px; -qt-user-state:0;"><a href="http://postgresql.org.mx"><span style="text-decoration: underline; color:#0057ae;">http://postgresql.org.mx</span></a><p style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; "> <p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style="-qt-paragraph-type:empty; margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style="-qt-paragraph-type:empty;margin-top:0px; margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0;text-indent:0px; "> <p style="-qt-paragraph-type:empty; margin-top:0px; margin-bottom:0px; margin-left:0px;margin-right:0px; -qt-block-indent:0; text-indent:0px; "> <p style="-qt-paragraph-type:empty; margin-top:0px;margin-bottom:0px; margin-left:0px; margin-right:0px; -qt-block-indent:0; text-indent:0px; ">
On Fri, Feb 15, 2013 at 07:01:20PM -0600, Ren Romero Benavides wrote: - I can confirm it comes from pgpool, and is very weird , I guess the pgp= ool list=20 - would be a better fit. - Cheers. hmm, ok thanks I'll post there as well. Also, if anyone else stumbles upon this, it only seems to happen with jru= by. I have standard ruby programs where this does not occur. - On Friday, February 15, 2013 01:58:55 PM David Kerr wrote: - > Howdy! - >=20 - > This query is coming from PgPool I believe. - >=20 - > SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.relname =3D - > 'import_jobs' AND c.relpersistence =3D 'u' - >=20 - > This is a very small database, like 10/15 tables, it's basically empt= y. - > If i run this query manually, it comes back immediatly. - > However according to this it's been running for about an hour. - >=20 - > v3=3D# select now(); - > now - > ------------------------------- - > 2013-02-15 21:49:34.470466+00 - > (1 row) - >=20 - > -[ RECORD 5 - > ]----+---------------------------------------------------------------= ------ - > ------------------------------------ datid | 16389 - > datname | v3 - > pid | 13905 - > usesysid | 20415 - > usename | workling - > application_name | - > client_addr | 10.0.0.132 - > client_hostname | - > client_port | 58009 - > backend_start | 2013-02-15 20:49:09.098768+00 - > xact_start | 2013-02-15 20:49:09.189625+00 - > query_start | 2013-02-15 20:49:09.198742+00 - > state_change | 2013-02-15 20:49:09.198743+00 - > waiting | f - > state | active - > query | SELECT count(*) FROM pg_catalog.pg_class AS c WHER= E - > c.relname =3D 'import_jobs' AND c.relpersistence =3D 'u' - >=20 - > (I actually restarted the application and now there are 4 of these) - >=20 - > Any idea why the query would hang like this? - >=20 - > I don't see it waiting on locks or anything like that. - >=20 - > any ideas? - >=20 - > Thanks - --=20 - Ren=E9 Romero Benavides @iCodeiExist @PgsqlMx=20 -=20 - Postgresql Tips en espa=F1ol para la comunidad de M=E9xico e Hispanoam=E9= rica. - http://postgresql.org.mx=20 -=20 -=20 -=20 -=20 -=20
David Kerr <dmk@mr-paradox.net> wrote:=0A=0A> Also, if anyone else stumbles= upon this, it only seems to happen with jruby.=0A=0A> I have standard ruby= programs where this does not occur.=0A=0AIt sounds like it is at least pos= sible that it is the issue I described here:=0A=0Ahttp://www.postgresql.org= /message-id/4AFBE872020000250002C70E@gw.wicourts.gov=0A=0A=0A--=0AKevin Gri= ttner=0AEnterpriseDB: http://www.enterprisedb.com=0AThe Enterprise PostgreS= QL Company=0A
On Sat, Feb 16, 2013 at 10:30:44AM -0800, Kevin Grittner wrote: - David Kerr <dmk@mr-paradox.net> wrote: - - > Also, if anyone else stumbles upon this, it only seems to happen with jruby. - - > I have standard ruby programs where this does not occur. - - It sounds like it is at least possible that it is the issue I described here: - - http://www.postgresql.org/message-id/4AFBE872020000250002C70E@gw.wicourts.gov - Thanks, I don't think that's this particular issue, but it's something I also need to watch out for!