[rt-users] RT3 Speed Info

[Simon Woodward]

Hi All,

I've been getting MySQL to log "slow queries" for the last 24 hours now
and typically its queries such as:

SELECT DISTINCT main.* FROM Tickets main, Groups Groups_1, Principals
Principals_2, CachedGroupMembers CachedGroupMembers_3, Principals
Principals_4, Users Users_5    WHERE ((main.EffectiveId = main.id)) AND
((main.Type = 'ticket')) AND ( ( ( (Users_5.EmailAddress = '<USERS
EMAIL>')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(Groups_1.Type =
'Requestor')AND(Principals_2.PrincipalType = 'Group') )  ) AND (
(main.Status = 'new')OR(main.Status = 'open') ) )  AND Groups
_1.Instance = main.id  AND Groups_1.id = Principals_2.ObjectId  AND
Principals_2.id = CachedGroupMembers_3.GroupId  AND
CachedGroupMembers_3.MemberId = Principa ls_4.id  AND
Principals_4.ObjectId = Users_5.id  ORDER BY main.Priority DESC LIMIT
10;

And also:

SELECT DISTINCT main.* FROM Users main, Principals Principals_1, Groups
Groups_2, Principals Principals_3, Principals Principals_4, ACL ACL_5,
CachedGroupMembers CachedGroupMembers_6, CachedGroupMembers
CachedGroupMembers_7    WHERE ((ACL_5.RightName =
'SuperUser')OR(ACL_5.RightName = 'OwnTicket')) AND ((CachedGroupMem
bers_7.GroupId = '4')) AND ((Principals_1.Disabled = '0')) AND
((Principals_1.PrincipalType = 'User')) AND ((Principals_3.PrincipalType
= 'User')) AND (ACL_5.ObjectType = 'RT::System'  OR (ACL_5.ObjectType =
'RT::Queue'  AND ACL_5.ObjectId= '18') ) AND ( (ACL_5.PrincipalId =
Principals_4.Id AND Principals_4.id = Groups_2.Id AND
ACL_5.PrincipalType = 'Group' AND (Groups_2.Domain = 'SystemInternal'
 OR Groups_2.Domain = 'UserDefined' OR Groups_2.Domain
='ACLEquivalence'))  OR( ( (Groups_2.Domain = 'RT::Queue-Role' AND
Groups_2.Instance = '18')  OR ( Groups_2.Domain = 'RT::Ticket-Role' AND
Groups_2.Instance = '39574')  )  AND Groups_2.Type = ACL_5.PrincipalType
AND Groups_2.Id = Principals_4.id AND Principals_4.PrincipalType =
'Group') )  AND main.id = Principals_1.id  AND Principals_1.id =
 CachedGroupMembers_7.MemberId  AND main.id = Principals_3.id  AND
CachedGroupMembers_6.MemberId = Principals_3.Id 
ANDCachedGroupMembers_6.GroupId = Principals_4.Id  ORDER BY main.Name
ASC;

Which seem to crop up quite a lot. All with Query times ranging from
13-26 seconds a time.

However that is nothing in comparison to the Apache::Session module
who's SELECT GET_LOCK('Apache-Session-<session', 3600); requests appear
to take on average 20-90 seconds, and I have even seen one at 1185, but
I'll class that as an anomaly.

However hope this helps with the tracking ... I'd be interested to see
if other people seeing these speed problems are noticing a similar
problem.

Cheers,

Simon.