[rt-devel] Greatly increased query time after update to 4.2.9

[Jeff Albert]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-devel/attachments/20141127/36a7a71c/attachment.html>