>From RT Wiki: FAQ Page 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:
SELECT <http://search.oracle.com/search/search?group=MySQL&q=SELECT> * FROM <http://search.oracle.com/search/search?group=MySQL&q=FROM> ACL where <http://search.oracle.com/search/search?group=MySQL&q=WHERE> RightName='OwnTicket'; e.g: 1. mysql> SELECT <http://search.oracle.com/search/search?group=MySQL&q=SELECT> * FROM <http://search.oracle.com/search/search?group=MySQL&q=FROM> ACL where <http://search.oracle.com/search/search?group=MySQL&q=WHERE> RightName='OwnTicket'; 2. 3. +-----+---------------+-------------+-----------+------------+----------+---------+---------------------+---------------+---------------------+ 4. | id | PrincipalType | PrincipalId | RightName | ObjectType | ObjectId | Creator | Created | LastUpdatedBy | LastUpdated | 5. +-----+---------------+-------------+-----------+------------+----------+---------+---------------------+---------------+---------------------+ 6. | 316 | Group | 50 | OwnTicket | RT::Queue | 1 | 0 | NULL <http://search.oracle.com/search/search?group=MySQL&q=NULL> | 0 | NULL <http://search.oracle.com/search/search?group=MySQL&q=NULL> | 7. .. 8. .. 9. .. 10. | 557 | Group | 50 | OwnTicket | RT::Queue | 27 | 12 | 2012-06-03 13:07:19 | 12 | 2012-06-03 13:07:19 | 11. | 669 | AdminCc | 271319 | OwnTicket | RT::Queue | 28 | 12 | 2012-06-03 14:06:24 | 12 | 2012-06-03 14:06:24 | 12. | 723 | Owner | 271321 | OwnTicket | RT::Queue | 28 | 12 | 2012-06-03 14:06:24 | 12 | 2012-06-03 14:06:24 | 13. | 911 | Group | 349063 | OwnTicket | RT::Queue | 33 | 12 | 2013-02-26 10:57:44 | 12 | 2013-02-26 10:57:44 | 14. +-----+---------------+-------------+-----------+------------+----------+---------+---------------------+---------------+---------------------+ Here OwnTicket is granted to Owner on queue "28". Check this on the UI: https://rt/Admin/Queues/GroupRights.html?id=28 I also found that adding index to several more tables<http://blog.rabin.io/23/speed-up-and-improve-ticket-deleting-and-shredding-in-rt>will speed up shredder as well, -- Rabin On Fri, May 23, 2014 at 7:11 PM, Patrick Muldoon <[email protected]> wrote: > 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. > > 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. > > 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.. > > We are running on postgresql 9.3.4 > > here is the query that is causing us issues.. > > SQL(20.280673s): > > SELECT COUNT(DISTINCT main.id) > FROM Tickets main > JOIN Groups Groups_1 ON ( LOWER(Groups_1.Domain) = 'rt::ticket-role' ) > AND ( LOWER(Groups_1.Name) = 'requestor' ) AND ( Groups_1.Instance = > main.id ) > JOIN Groups Groups_3 ON ( LOWER(Groups_3.Domain) = 'rt::ticket-role' ) > AND ( Groups_3.Instance = main.id ) > JOIN CachedGroupMembers CachedGroupMembers_2 ON ( > CachedGroupMembers_2.Disabled = '0' ) AND ( CachedGroupMembers_2.GroupId = > Groups_1.id ) > LEFT JOIN CachedGroupMembers CachedGroupMembers_4 ON ( > CachedGroupMembers_4.Disabled = '0' ) AND ( CachedGroupMembers_4.MemberId = > '22' ) AND ( CachedGroupMembers_4.GroupId = Groups_3.id ) > WHERE ( > ( > ( 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(Groups_3.Name) = 'admincc' AND main.Queue = '2' ) > ) > ) > AND (main.IsMerged IS NULL) > AND (main.Status != 'deleted') > AND (main.Type = 'ticket') > AND ( > ( CachedGroupMembers_2.MemberId = '364' ) AND > ( LOWER(main.Status) = 'new' OR LOWER(main.Status) = 'open' > OR LOWER(main.Status) = 'stalled' ) > ) > > > 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. > > and the query plan > > Aggregate (cost=37.50..37.51 rows=1 width=4) > -> Nested Loop Left Join (cost=1.95..37.50 rows=1 width=4) > 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((groups_3.name)::text) > = 'admincc'::text) AND (main.queue = 2))) > -> Nested Loop (cost=1.53..30.61 rows=1 width=22) > -> Nested Loop (cost=1.11..22.16 rows=1 width=26) > Join Filter: (groups_1.instance = main.id) > -> Nested Loop (cost=0.83..18.32 rows=1 width=22) > -> Index Scan using groups2 on groups groups_1 > (cost=0.42..8.44 rows=1 width=8) > Index Cond: ((lower((domain)::text) = > 'rt::ticket-role'::text) AND (lower((name)::text) = 'requestor'::text)) > -> Index Scan using groups2 on groups groups_3 > (cost=0.41..9.87 rows=1 width=14) > Index Cond: ((lower((domain)::text) = > 'rt::ticket-role'::text) AND (instance = groups_1.instance)) > -> Index Scan using tickets_pkey on tickets main > (cost=0.28..3.83 rows=1 width=12) > Index Cond: (id = groups_3.instance) > 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))) > -> Index Only Scan using disgroumem on cachedgroupmembers > cachedgroupmembers_2 (cost=0.41..8.44 rows=1 width=4) > Index Cond: ((groupid = groups_1.id) AND (memberid = > 364) AND (disabled = 0::smallint)) > -> Index Only Scan using disgroumem on cachedgroupmembers > cachedgroupmembers_4 (cost=0.41..6.85 rows=1 width=8) > Index Cond: ((groupid = groups_3.id) AND (memberid = 22) > AND (disabled = 0::smallint)) > (18 rows) > > > 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. > > > Thanks, > -Patrick > > -- > Patrick Muldoon > Network/Software Engineer > INOC (http://www.inoc.net) > PGPKEY (http://www.inoc.net/~doon) > Key ID: 0x2D808DE5 > > I do not fear computers. I fear the lack of them. - Isaac Asimov > > -- > RT Training - Boston, September 9-10 > http://bestpractical.com/training >
-- RT Training - Boston, September 9-10 http://bestpractical.com/training
