I've just updated the development instance of our RT service, which had been running at 4.0.8, to the latest 4.2.9 in preparation for a planned production update to follow. Unfortunately, we've hit a significant snag: the UI is now hugely slow in some views that were relatively snappy before the update. The slow query log identifies a likely culprit:
# Query_time: 23 Lock_time: 0 Rows_sent: 28 Rows_examined: 858133
SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1') ORDER BY main.Name ASC;
It appears that this query is reasonably well-indexed, but clearly something has changed in the schema updates between 4.0.8 and 4.2.9:
mysql> explain extended SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1') ORDER BY main.Name ASC;
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+------------------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | CachedGroupMembers_2 | range | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem | 10 | NULL | 606 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | main | eq_ref | PRIMARY | PRIMARY | 4 | rt34.CachedGroupMembers_2.MemberId | 1 | |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt34.main.id | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_4 | ref | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | CachedGroupMembers3 | 5 | rt34.CachedGroupMembers_2.MemberId | 1 | Using where; Distinct |
| 1 | SIMPLE | ACL_3 | range | ACL1 | ACL1 | 85 | NULL | 13 | Using where; Using index; Distinct |
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+------------------------------------+------+-----------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
Can anyone offer any advice on what might have precipitated this sudden drop in query performance?
Cheers,
Jeff
# Query_time: 23 Lock_time: 0 Rows_sent: 28 Rows_examined: 858133
SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1') ORDER BY main.Name ASC;
It appears that this query is reasonably well-indexed, but clearly something has changed in the schema updates between 4.0.8 and 4.2.9:
mysql> explain extended SELECT DISTINCT main.* FROM Users main CROSS JOIN ACL ACL_3 JOIN Principals Principals_1 ON ( Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN CachedGroupMembers CachedGroupMembers_4 ON ( CachedGroupMembers_4.MemberId = Principals_1.id ) WHERE ((ACL_3.ObjectType = 'RT::Queue' AND ACL_3.ObjectId = 3) OR (ACL_3.ObjectType = 'RT::System' AND ACL_3.ObjectId = 1)) AND (ACL_3.PrincipalId = CachedGroupMembers_4.GroupId) AND (ACL_3.PrincipalType = 'Group') AND (ACL_3.RightName = 'OwnTicket' OR ACL_3.RightName = 'SuperUser') AND (CachedGroupMembers_2.Disabled = '0') AND (CachedGroupMembers_2.GroupId = '4') AND (CachedGroupMembers_4.Disabled = '0') AND (Principals_1.Disabled = '0') AND (Principals_1.PrincipalType = 'User') AND (Principals_1.id != '1') ORDER BY main.Name ASC;
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+------------------------------------+------+-----------------------------------------------------------+
| 1 | SIMPLE | CachedGroupMembers_2 | range | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | DisGrouMem | 10 | NULL | 606 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | main | eq_ref | PRIMARY | PRIMARY | 4 | rt34.CachedGroupMembers_2.MemberId | 1 | |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY | PRIMARY | 4 | rt34.main.id | 1 | Using where; Distinct |
| 1 | SIMPLE | CachedGroupMembers_4 | ref | DisGrouMem,CachedGroupMembers3,cachedgroupmembers1 | CachedGroupMembers3 | 5 | rt34.CachedGroupMembers_2.MemberId | 1 | Using where; Distinct |
| 1 | SIMPLE | ACL_3 | range | ACL1 | ACL1 | 85 | NULL | 13 | Using where; Using index; Distinct |
+----+-------------+----------------------+--------+----------------------------------------------------+---------------------+---------+------------------------------------+------+-----------------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)
Can anyone offer any advice on what might have precipitated this sudden drop in query performance?
Cheers,
Jeff