Обсуждение: Unexplained query activity.

<div class="WordSection1"><p class="MsoNormal">Hi,<p class="MsoNormal">Running PGAdmin 1.10.3  on win xp sp3 against
Greenplum3.3.6.6 (PG 8.1.4)<p class="MsoNormal">I’m looking over my pgadmin log and seeing frequent executions of these
queries.  <p class="MsoNormal">In this particular instance, the sql was executed 304 times (in 2 seconds),  but why is
itbeing executed so often. <p class="MsoNormal"> <p class="MsoNormal">I had resource_groups selected in display.  I’ve
turnedoff, but not been able to determine if it suppressed execution. <p class="MsoNormal"> <p class="MsoNormal">Any
thoughts?<pclass="MsoNormal"> <p class="MsoNormal"> <table border="0" cellpadding="0" cellspacing="0"
class="MsoNormalTable"style="width:710.0pt;margin-left:-.75pt;border-collapse:collapse" width="947"><tr
style="height:30.0pt"><tdnowrap style="width:215.0pt;padding:0in 5.4pt 0in 5.4pt; height:30.0pt" valign="bottom"
width="287"><pclass="MsoNormal"><span style="color:black">2010-08-03 14:15:18.654622 CDT</span></td><td nowrap
style="width:48.0pt;padding:0in5.4pt 0in 5.4pt; height:30.0pt" valign="bottom" width="64"><p class="MsoNormal"><span
style="color:black">LOG</span></td><tdstyle="width:300.45pt;padding:0in 5.4pt 0in 5.4pt; height:30.0pt" valign="bottom"
width="401"><pclass="MsoNormal"><span style="color:black">statement: SELECT rsqname FROM pg_resqueue WHERE
pg_resqueue.oid= 1496028</span></td><td nowrap style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:30.0pt"
valign="bottom"width="64"><p class="MsoNormal"><span style="color:black">con755</span></td><td nowrap
style="width:48.0pt;padding:0in5.4pt 0in 5.4pt; height:30.0pt" valign="bottom" width="64"><p class="MsoNormal"><span
style="color:black">cmd15</span></td><tdnowrap style="width:50.55pt;padding:0in 5.4pt 0in 5.4pt; height:30.0pt"
valign="bottom"width="67"><p class="MsoNormal"><span style="color:black">postgres</span></td></tr><tr
style="height:30.0pt"><tdnowrap style="width:215.0pt;padding:0in 5.4pt 0in 5.4pt; height:30.0pt" valign="bottom"
width="287"><pclass="MsoNormal"><span style="color:black">….  303 rows identical calls.</span></td><td nowrap
style="width:48.0pt;padding:0in5.4pt 0in 5.4pt; height:30.0pt" valign="bottom" width="64"></td><td
style="width:300.45pt;padding:0in5.4pt 0in 5.4pt; height:30.0pt" valign="bottom" width="401"></td><td nowrap
style="width:48.0pt;padding:0in5.4pt 0in 5.4pt; height:30.0pt" valign="bottom" width="64"></td><td nowrap
style="width:48.0pt;padding:0in5.4pt 0in 5.4pt; height:30.0pt" valign="bottom" width="64"></td><td nowrap
style="width:50.55pt;padding:0in5.4pt 0in 5.4pt; height:30.0pt" valign="bottom" width="67"></td></tr><tr
style="height:30.0pt"><tdnowrap style="width:215.0pt;padding:0in 5.4pt 0in 5.4pt; height:30.0pt" valign="bottom"
width="287"><pclass="MsoNormal"><span style="color:black">2010-08-03 14:15:20.019774 CDT</span></td><td nowrap
style="width:48.0pt;padding:0in5.4pt 0in 5.4pt; height:30.0pt" valign="bottom" width="64"><p class="MsoNormal"><span
style="color:black">LOG</span></td><tdstyle="width:300.45pt;padding:0in 5.4pt 0in 5.4pt; height:30.0pt" valign="bottom"
width="401"><pclass="MsoNormal"><span style="color:black">statement: SELECT rsqname FROM pg_resqueue WHERE
pg_resqueue.oid= 1496028</span></td><td nowrap style="width:48.0pt;padding:0in 5.4pt 0in 5.4pt; height:30.0pt"
valign="bottom"width="64"><p class="MsoNormal"><span style="color:black">con755</span></td><td nowrap
style="width:48.0pt;padding:0in5.4pt 0in 5.4pt; height:30.0pt" valign="bottom" width="64"><p class="MsoNormal"><span
style="color:black">cmd318</span></td><tdnowrap style="width:50.55pt;padding:0in 5.4pt 0in 5.4pt; height:30.0pt"
valign="bottom"width="67"><p class="MsoNormal"><span style="color:black">postgres</span></td></tr></table><p
class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">DougLittle</span></b><p class="MsoNormal"><b><span
style="font-size:12.0pt"> </span></b><pclass="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif"">Sr.Data Warehouse Architect | Enterprise Data Management |
OrbitzWorldwide </span><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Arial","sans-serif"">500 W.
Madison,Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741</span><span
style="font-size:12.0pt;
font-family:"Times New Roman","serif""></span><p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Arial","sans-serif""><ahref="mailto:Douglas.Little@orbitz.com"
title="mailto:dlittle@orbitz.com"><b><spanstyle="color:blue">Douglas.Little@orbitz.com</span></b></a></span><b><u><span
style="font-size:13.5pt;font-family:"TimesNew Roman","serif";color:blue"></span></u></b><p class="MsoNormal"> <img
alt="cid:image001.jpg@01CABEC8.D4980670"border="0" height="61" id="Picture_x0020_1"
src="cid:image001.jpg@01CB3319.7A5D3980"width="83" />  <a href="http://www.orbitz.com/"
title="http://www.orbitz.com/"><spanstyle="font-size:8.0pt;font-family:"Arial","sans-serif"; 
color:blue">orbitz.com</span></a> <span style="color:blue">|</span> <a href="http://www.ebookers.com/"
title="http://www.ebookers.com/"><span
style="font-size:8.0pt;font-family:"Arial","sans-serif";color:blue">ebookers.com</span></a><span
style="color:blue">|</span><a href="http://www.hotelclub.com/" title="http://www.hotelclub.com/"><span
style="font-size:8.0pt;font-family:
"Arial","sans-serif";color:blue">hotelclub.com</span></a> <span style="color:blue">|</span> <a
href="http://www.cheaptickets.com/"title="http://www.cheaptickets.com/"><span style="font-size:8.0pt;font-family: 
"Arial","sans-serif";color:blue">cheaptickets.com</span></a> <span style="color:blue">|</span> <a
href="http://www.ratestogo.com/"title="http://www.ratestogo.com/"><span style="font-size:8.0pt;font-family: 
"Arial","sans-serif";color:blue">ratestogo.com</span></a><span style="color:blue"> |</span> <a
href="http://www.asiahotels.com/"title="http://www.asiahotels.com/"><span style="font-size:8.0pt;font-family: 
"Arial","sans-serif";color:blue">asiahotels.com</span></a><p class="MsoNormal"> </div>

Le 03/08/2010 21:38, Little, Douglas a écrit :
> [...]
> Running PGAdmin 1.10.3  on win xp sp3 against Greenplum 3.3.6.6 (PG 8.1.4)
> I'm looking over my pgadmin log and seeing frequent executions of these queries.
> In this particular instance, the sql was executed 304 times (in 2 seconds),  but why is it being executed so often.
> 
> I had resource_groups selected in display.  I've turned off, but not been able to determine if it suppressed
execution.
> 
> Any thoughts?
> 

How many roles do you have? 304? that would be the only reason why you
should have this. If I'm right, this could be easy to fix.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

325 logon roles,  24 groups

Doug


-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: Tuesday, August 03, 2010 2:49 PM
To: Little, Douglas
Cc: pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] Unexplained query activity.

Le 03/08/2010 21:38, Little, Douglas a écrit :
> [...]
> Running PGAdmin 1.10.3  on win xp sp3 against Greenplum 3.3.6.6 (PG 8.1.4)
> I'm looking over my pgadmin log and seeing frequent executions of these queries.
> In this particular instance, the sql was executed 304 times (in 2 seconds),  but why is it being executed so often.
>
> I had resource_groups selected in display.  I've turned off, but not been able to determine if it suppressed
execution.
>
> Any thoughts?
>

How many roles do you have? 304? that would be the only reason why you
should have this. If I'm right, this could be easy to fix.


--
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Le 03/08/2010 21:50, Little, Douglas a écrit :
> 325 logon roles,  24 groups
> 

I suppose some of them don't have a ressource queue configured.

BTW, please, don't top-post.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com

Little, Douglas wrote: <br /><br /><blockquote
cite="mid:8585BA53443004458E0BAA6134C5A7FB0CB29045@EGEXCMB01.oww.root.lcl"type="cite"><div class="WordSection1"><p
class="MsoNormal">RunningPGAdmin 1.10.3  on win xp sp3 against Greenplum 3.3.6.6 (PG 8.1.4)</div></blockquote><br /> I
thinkyou mean PG 8.2.4 there.<br /><br /> If you're not already familiar with how Greenplum uses resource queues, some
backgroundthere might help explain why the things you're looking at require queries against it:<br /><br /><a
class="moz-txt-link-freetext"
href="http://www.greenplum.com/docs/3300/CREATE_RESOURCE_QUEUE.html">http://www.greenplum.com/docs/3300/CREATE_RESOURCE_QUEUE.html</a><br
/><aclass="moz-txt-link-freetext"
href="http://www.greenplum.com/docs/3300/sql-alterrole.html">http://www.greenplum.com/docs/3300/sql-alterrole.html</a><br
/><aclass="moz-txt-link-freetext"
href="http://www.greenplum.com/docs/3300/DROP_RESOURCE_QUEUE.html">http://www.greenplum.com/docs/3300/DROP_RESOURCE_QUEUE.html</a><br
/><br/> The DROP page in particular shows examples that retrieve similar things to the pg_resqueue query you're seeing
run.<br/><br /><pre class="moz-signature" cols="72">-- 
 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
<a class="moz-txt-link-abbreviated" href="mailto:greg@2ndQuadrant.com">greg@2ndQuadrant.com</a>   <a
class="moz-txt-link-abbreviated"href="http://www.2ndQuadrant.us">www.2ndQuadrant.us</a>
 
</pre>

Le 03/08/2010 22:08, Guillaume Lelarge a écrit :
> Le 03/08/2010 21:50, Little, Douglas a écrit :
>> 325 logon roles,  24 groups
>>
>
> I suppose some of them don't have a ressource queue configured.
>
> BTW, please, don't top-post.
>

If you can compile it on windows, here is a patch that should fix this.
I can't test it because I don't have a Greenplum database.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

Le 04/08/2010 00:22, Guillaume Lelarge a écrit :
> Le 03/08/2010 22:08, Guillaume Lelarge a écrit :
>> Le 03/08/2010 21:50, Little, Douglas a écrit :
>>> 325 logon roles,  24 groups
>>>
>>
>> I suppose some of them don't have a ressource queue configured.
>>
>> BTW, please, don't top-post.
>>
> 
> If you can compile it on windows, here is a patch that should fix this.
> I can't test it because I don't have a Greenplum database.
> 

BTW, the patch is commited.


-- 
Guillaumehttp://www.postgresql.frhttp://dalibo.com