<div dir="ltr"><div class="gmail_default" style="font-family:verdana,sans-serif">From RT Wiki: FAQ Page
<dl><dd> In most case it's a problem with User/Groups rights that makes too
much users appears in the select owner dropdown. If this dropdown seems
to contains more than wanted people, then check everywhere (Global
rights/ Queue rights) in RT rights to see if OwnTicket right is not
granted to Everyone, Unprivileged or a group that shouldn't have this
right and contains many people. You can also run the following SQL query
on the RT database to find each objects that grant OwnTicket:
</dd><dd> <pre class="" style="font-family:monospace"><a href="http://search.oracle.com/search/search?group=MySQL&q=SELECT"><span style="color:rgb(153,0,153);font-weight:bold">SELECT</span></a> <span style="color:rgb(204,0,153)">*</span> <a href="http://search.oracle.com/search/search?group=MySQL&q=FROM"><span style="color:rgb(153,0,153);font-weight:bold">FROM</span></a> ACL <a href="http://search.oracle.com/search/search?group=MySQL&q=WHERE"><span style="color:rgb(153,0,153);font-weight:bold">where</span></a> RightName<span style="color:rgb(204,0,153)">=</span><span style="color:rgb(0,128,0)">'OwnTicket'</span><span style="color:rgb(0,0,51)">;</span></pre>
</dd><dd> e.g:
</dd><dd> <pre class="" style="font-family:monospace"><ol><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top">
mysql<span style="color:rgb(204,0,153)">></span> <a href="http://search.oracle.com/search/search?group=MySQL&q=SELECT"><span style="color:rgb(153,0,153);font-weight:bold">SELECT</span></a> <span style="color:rgb(204,0,153)">*</span> <a href="http://search.oracle.com/search/search?group=MySQL&q=FROM"><span style="color:rgb(153,0,153);font-weight:bold">FROM</span></a> ACL <a href="http://search.oracle.com/search/search?group=MySQL&q=WHERE"><span style="color:rgb(153,0,153);font-weight:bold">where</span></a> RightName<span style="color:rgb(204,0,153)">=</span><span style="color:rgb(0,128,0)">'OwnTicket'</span><span style="color:rgb(0,0,51)">;</span></div>
</li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"> </div></li><li style="font-weight:normal;vertical-align:top">
<div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"><span style="color:rgb(204,0,153)">+-----+---------------+-------------+-----------+------------+----------+---------+---------------------+---------------+---------------------+</span></div>
</li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"><span style="color:rgb(204,0,153)">|</span> id <span style="color:rgb(204,0,153)">|</span> PrincipalType <span style="color:rgb(204,0,153)">|</span> PrincipalId <span style="color:rgb(204,0,153)">|</span> RightName <span style="color:rgb(204,0,153)">|</span> ObjectType <span style="color:rgb(204,0,153)">|</span> ObjectId <span style="color:rgb(204,0,153)">|</span> Creator <span style="color:rgb(204,0,153)">|</span> Created <span style="color:rgb(204,0,153)">|</span> LastUpdatedBy <span style="color:rgb(204,0,153)">|</span> LastUpdated <span style="color:rgb(204,0,153)">|</span></div>
</li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"><span style="color:rgb(204,0,153)">+-----+---------------+-------------+-----------+------------+----------+---------+---------------------+---------------+---------------------+</span></div>
</li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"><span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">316</span> <span style="color:rgb(204,0,153)">|</span> Group <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">50</span> <span style="color:rgb(204,0,153)">|</span> OwnTicket <span style="color:rgb(204,0,153)">|</span> RT::Queue <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">1</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">0</span> <span style="color:rgb(204,0,153)">|</span> <a href="http://search.oracle.com/search/search?group=MySQL&q=NULL"><span style="color:rgb(153,0,255);font-weight:bold">NULL</span></a> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">0</span> <span style="color:rgb(204,0,153)">|</span> <a href="http://search.oracle.com/search/search?group=MySQL&q=NULL"><span style="color:rgb(153,0,255);font-weight:bold">NULL</span></a> <span style="color:rgb(204,0,153)">|</span></div>
</li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top">..</div></li><li style="font-weight:normal;vertical-align:top">
<div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top">..</div></li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top">
..</div></li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"><span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">557</span> <span style="color:rgb(204,0,153)">|</span> Group <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">50</span> <span style="color:rgb(204,0,153)">|</span> OwnTicket <span style="color:rgb(204,0,153)">|</span> RT::Queue <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">27</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">12</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">2012</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">06</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">03</span> <span style="color:rgb(0,128,128)">13</span>:<span style="color:rgb(0,128,128)">07</span>:<span style="color:rgb(0,128,128)">19</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">12</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">2012</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">06</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">03</span> <span style="color:rgb(0,128,128)">13</span>:<span style="color:rgb(0,128,128)">07</span>:<span style="color:rgb(0,128,128)">19</span> <span style="color:rgb(204,0,153)">|</span></div>
</li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"><span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">669</span> <span style="color:rgb(204,0,153)">|</span> AdminCc <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">271319</span> <span style="color:rgb(204,0,153)">|</span> OwnTicket <span style="color:rgb(204,0,153)">|</span> RT::Queue <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">28</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">12</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">2012</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">06</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">03</span> <span style="color:rgb(0,128,128)">14</span>:<span style="color:rgb(0,128,128)">06</span>:<span style="color:rgb(0,128,128)">24</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">12</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">2012</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">06</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">03</span> <span style="color:rgb(0,128,128)">14</span>:<span style="color:rgb(0,128,128)">06</span>:<span style="color:rgb(0,128,128)">24</span> <span style="color:rgb(204,0,153)">|</span></div>
</li><li style="font-weight:normal;vertical-align:top;background-color:rgb(255,255,204)"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"><span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">723</span> <span style="color:rgb(204,0,153)">|</span> Owner <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">271321</span> <span style="color:rgb(204,0,153)">|</span> OwnTicket <span style="color:rgb(204,0,153)">|</span> RT::Queue <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">28</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">12</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">2012</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">06</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">03</span> <span style="color:rgb(0,128,128)">14</span>:<span style="color:rgb(0,128,128)">06</span>:<span style="color:rgb(0,128,128)">24</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">12</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">2012</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">06</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">03</span> <span style="color:rgb(0,128,128)">14</span>:<span style="color:rgb(0,128,128)">06</span>:<span style="color:rgb(0,128,128)">24</span> <span style="color:rgb(204,0,153)">|</span></div>
</li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"><span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">911</span> <span style="color:rgb(204,0,153)">|</span> Group <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">349063</span> <span style="color:rgb(204,0,153)">|</span> OwnTicket <span style="color:rgb(204,0,153)">|</span> RT::Queue <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">33</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">12</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">2013</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">02</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">26</span> <span style="color:rgb(0,128,128)">10</span>:<span style="color:rgb(0,128,128)">57</span>:<span style="color:rgb(0,128,128)">44</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">12</span> <span style="color:rgb(204,0,153)">|</span> <span style="color:rgb(0,128,128)">2013</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">02</span><span style="color:rgb(204,0,153)">-</span><span style="color:rgb(0,128,128)">26</span> <span style="color:rgb(0,128,128)">10</span>:<span style="color:rgb(0,128,128)">57</span>:<span style="color:rgb(0,128,128)">44</span> <span style="color:rgb(204,0,153)">|</span></div>
</li><li style="font-weight:normal;vertical-align:top"><div style="font:1em/1.2em monospace;margin:0px;padding:0px;background:none repeat scroll 0% 0% transparent;vertical-align:top"><span style="color:rgb(204,0,153)">+-----+---------------+-------------+-----------+------------+----------+---------+---------------------+---------------+---------------------+</span></div>
</li></ol></pre>
</dd><dd> Here OwnTicket is granted to Owner on queue "28". Check this on the UI: <a href="https://rt/Admin/Queues/GroupRights.html?id=28">https://rt/Admin/Queues/GroupRights.html?id=28</a>
</dd></dl><p><br></p><p>I also found that <a href="http://blog.rabin.io/23/speed-up-and-improve-ticket-deleting-and-shredding-in-rt">adding index to several more tables</a> will speed up shredder as well, <br></p><p><br>
</p></div></div><div class="gmail_extra"><br clear="all"><div><div dir="ltr"><font face="arial, helvetica, sans-serif"><br>--<br><font color="#999999" size="1">Rabin</font></font></div></div>
<br><br><div class="gmail_quote">On Fri, May 23, 2014 at 7:11 PM, Patrick Muldoon <span dir="ltr"><<a href="mailto:doon.bulk@inoc.net" target="_blank">doon.bulk@inoc.net</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Recently upgraded from 4.0 to 4.2 and have noticed that we are now getting slow queries on pretty much all ticket history queries. I ’ve been reading the list and attempting to google but I cannot seem to figure out what the cause is.<br>
<br>
This RT database started life 11 years ago and has been dragged along from upgrade to upgrade and this is the first time we’ve run into any real issue. I’ve compared our Schema with a freshly installed version of 4.2 and don’t see any indexes missing, or anything along those lines.<br>
<br>
I think that it has something to do with the way either have groups setup, or permissions, since if I make my account an admin/super user all the queries are super fast..<br>
<br>
We are running on postgresql 9.3.4<br>
<br>
here is the query that is causing us issues..<br>
<br>
SQL(20.280673s):<br>
<br>
SELECT COUNT(DISTINCT <a href="http://main.id" target="_blank">main.id</a>)<br>
FROM Tickets main<br>
JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) AND ( LOWER(Groups_1.Name) = 'requestor' ) AND ( Groups_1.Instance = <a href="http://main.id" target="_blank">main.id</a> )<br>
JOIN Groups Groups_3 ON ( LOWER(Groups_3.Domain) = 'rt::ticket-role' ) AND ( Groups_3.Instance = <a href="http://main.id" target="_blank">main.id</a> )<br>
JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.GroupId = Groups_1.id )<br>
LEFT JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.Disabled = '0' ) AND ( CachedGroupMembers_4.MemberId = '22' ) AND ( CachedGroupMembers_4.GroupId = Groups_3.id )<br>
WHERE (<br>
(<br>
( main.Queue = '1' OR main.Queue = '4' OR main.Queue = '5' ) OR<br>
( main.Owner = '22' AND main.Queue = '2' ) OR<br>
( CachedGroupMembers_4.MemberId IS NOT NULL AND LOWER(Groups_3.Name) = 'admincc' AND main.Queue = '2' )<br>
)<br>
)<br>
AND (main.IsMerged IS NULL)<br>
AND (main.Status != 'deleted')<br>
AND (main.Type = 'ticket')<br>
AND (<br>
( CachedGroupMembers_2.MemberId = '364' ) AND<br>
( LOWER(main.Status) = 'new' OR LOWER(main.Status) = 'open' OR LOWER(main.Status) = 'stalled' )<br>
)<br>
<br>
<br>
And then the next query which returns the data takes about the same time to run. so a small ticket takes around 40 seconds to render.<br>
<br>
and the query plan<br>
<br>
Aggregate (cost=37.50..37.51 rows=1 width=4)<br>
-> Nested Loop Left Join (cost=1.95..37.50 rows=1 width=4)<br>
Filter: ((main.queue = 1) OR (main.queue = 4) OR (main.queue = 5) OR ((main.owner = 22) AND (main.queue = 2)) OR ((cachedgroupmembers_4.memberid IS NOT NULL) AND (lower((<a href="http://groups_3.name" target="_blank">groups_3.name</a>)::text) = 'admincc'::text) AND (main.queue = 2)))<br>
-> Nested Loop (cost=1.53..30.61 rows=1 width=22)<br>
-> Nested Loop (cost=1.11..22.16 rows=1 width=26)<br>
Join Filter: (groups_1.instance = <a href="http://main.id" target="_blank">main.id</a>)<br>
-> Nested Loop (cost=0.83..18.32 rows=1 width=22)<br>
-> Index Scan using groups2 on groups groups_1 (cost=0.42..8.44 rows=1 width=8)<br>
Index Cond: ((lower((domain)::text) = 'rt::ticket-role'::text) AND (lower((name)::text) = 'requestor'::text))<br>
-> Index Scan using groups2 on groups groups_3 (cost=0.41..9.87 rows=1 width=14)<br>
Index Cond: ((lower((domain)::text) = 'rt::ticket-role'::text) AND (instance = groups_1.instance))<br>
-> Index Scan using tickets_pkey on tickets main (cost=0.28..3.83 rows=1 width=12)<br>
Index Cond: (id = groups_3.instance)<br>
Filter: ((ismerged IS NULL) AND ((status)::text <> 'deleted'::text) AND ((type)::text = 'ticket'::text) AND ((queue = 1) OR (queue = 4) OR (queue = 5) OR (queue = 2) OR (queue = 2)) AND ((lower((status)::text) = 'new'::text) OR (lower((status)::text) = 'open'::text) OR (lower((status)::text) = 'stalled'::text)))<br>
-> Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_2 (cost=0.41..8.44 rows=1 width=4)<br>
Index Cond: ((groupid = <a href="http://groups_1.id" target="_blank">groups_1.id</a>) AND (memberid = 364) AND (disabled = 0::smallint))<br>
-> Index Only Scan using disgroumem on cachedgroupmembers cachedgroupmembers_4 (cost=0.41..6.85 rows=1 width=8)<br>
Index Cond: ((groupid = <a href="http://groups_3.id" target="_blank">groups_3.id</a>) AND (memberid = 22) AND (disabled = 0::smallint))<br>
(18 rows)<br>
<br>
<br>
So pointers on places to look or things to try would be very helpful. I am still trying to wrap my head around why if I am root it doesn’t slow down, which leads me to think it has something to do with the way we have permissions setup.<br>
<br>
<br>
Thanks,<br>
-Patrick<br>
<br>
--<br>
Patrick Muldoon<br>
Network/Software Engineer<br>
INOC (<a href="http://www.inoc.net" target="_blank">http://www.inoc.net</a>)<br>
PGPKEY (<a href="http://www.inoc.net/~doon" target="_blank">http://www.inoc.net/~doon</a>)<br>
Key ID: 0x2D808DE5<br>
<br>
I do not fear computers. I fear the lack of them. - Isaac Asimov<br>
<span class="HOEnZb"><font color="#888888"><br>
--<br>
RT Training - Boston, September 9-10<br>
<a href="http://bestpractical.com/training" target="_blank">http://bestpractical.com/training</a><br>
</font></span></blockquote></div><br></div>