[rt-users] RT running slowly... Caused by RTx-Shredder indexes

[David Hobley]

All, 

I ran an explain on the query in MySQL and noticed that it was using one of the RTx-Shredder indexes. After I deleted all those indexes things went back to normal. Phew. 

In case this is useful: 

mysql> explain select distinct main.* from Users main cross join ACL ACL_4 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type) AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND (ACL_4.RightName = 'OwnTicket') AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System')) AND ((Groups_3.Domain = 'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role')) ORDER BY main.Name ASC; 
+----+-------------+----------------------+--------+--------------------------+---------------+---------+-----------------------------+-------+----------------------------------------------+ 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | 
+----+-------------+----------------------+--------+--------------------------+---------------+---------+-----------------------------+-------+----------------------------------------------+ 
| 1 | SIMPLE | main | range | PRIMARY | PRIMARY | 4 | NULL | 28920 | Using where; Using temporary; Using filesort | 
| 1 | SIMPLE | Groups_3 | range | PRIMARY,Groups1,Groups2 | Groups1 | 67 | NULL | 119 | Using where; Using index; Distinct | 
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt3.main.id | 1 | Using where; Distinct | 
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,SHREDDER_CGM1 | SHREDDER_CGM1 | 10 | rt3.main.id,rt3.Groups_3.id | 1 | Using where; Using index; Distinct | 
| 1 | SIMPLE | ACL_4 | range | ACL1 | ACL1 | 54 | NULL | 45 | Using where; Using index; Distinct | 
+----+-------------+----------------------+--------+--------------------------+---------------+---------+-----------------------------+-------+----------------------------------------------+ 

Cheers, 
David 
----- Original Message ----- 
From: "David Hobley" <david.hobley at mionegroup.com> 
To: "rt-users" <rt-users at lists.bestpractical.com> 
Sent: Thursday, 22 May, 2008 3:30:21 PM GMT +10:00 Brisbane 
Subject: [rt-users] RT running slowly... 

Hello, 

Our RT has been running slowly for a while; having turned on mysql slow query logging, it appears to only be a single query which is causing grief. Anyone any suggestions as to what can be done to fix this? 

# Time: 080522 15:32:21 
# User at Host: rt[rt] @ localhost [] 
# Query_time: 40 Lock_time: 0 Rows_sent: 0 Rows_examined: 6888927 
SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_4 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type) AND (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND (ACL_4.RightName = 'OwnTicket') AND ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System')) AND ((Groups_3.Domain = 'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role')) ORDER BY main.Name ASC; 

-- 
Cheers, 
David 

_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: sales at bestpractical.com Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com 

-- 
Cheers, 
David Hobley 

IT Manager 
Creators of Miessence, MiVitality and MiEnviron 

Phone: +61 (7) 5582 7020 
Fax: +61 (7) 5539 6719 
USA Fax 1800 840 0827 
Email : david.hobley at mionegroup.com 
Website: www.mionegroup.com 


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080522/e9a8d7fc/attachment.htm>