[rt-users] Query Builder very slow after system update

[Liviu Costea]

Hello,

We recently updated our RT3 server ( RT 3.8.1 , around 200000 tickets) from Red Hat Enterprise Linux 5.1 to 5.3 (yum update). The new MySQL installed version is 5.0.45.

Since the upgrade RT3 is working normally except Query Builder ( http://<rt3_url>/rt3/Search/Build.html<http://%3crt3_url%3e/rt3/Search/Build.html> ) which is very slow. I identified the query that's taking too long:

mysql> 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 (Groups_3.Domain =   'RT::Queue-Role') AND ((ACL_4.ObjectType = 'RT::Queue') OR   (ACL_4.ObjectType = 'RT::System'))  ORDER BY main.Name ASC;
Empty set (2 min 57.79 sec)


Additional info:

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 (Groups_3.Domain =   'RT::Queue-Role') AND ((ACL_4.ObjectType = 'RT::Queue') OR   (ACL_4.ObjectType = 'RT::System'))  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                             |   21 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                        | PRIMARY             | 4       | rt3.main.id                      |    1 | Using where; Distinct                        |
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,CachedGroupMembers3 | CachedGroupMembers3 | 5       | rt3.Principals_1.id              |    1 | Using where; Distinct                        |
|  1 | SIMPLE      | ACL_4                | range  | ACL1                           | ACL1                | 54      | NULL                             |   36 | Using where; Using index; Distinct           |
|  1 | SIMPLE      | Groups_3             | eq_ref | PRIMARY,Groups1,Groups2        | PRIMARY             | 4       | rt3.CachedGroupMembers_2.GroupId |    1 | Using where; Distinct                        |
+----+-------------+----------------------+--------+--------------------------------+---------------------+---------+----------------------------------+------+----------------------------------------------+
5 rows in set (0.04 sec)

mysql> select count(*) from CachedGroupMembers;
+----------+
| count(*) |
+----------+
|  1382038 |
+----------+
1 row in set (2.56 sec)

mysql> show indexes from CachedGroupMembers;
+--------------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name            | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| CachedGroupMembers |          0 | PRIMARY             |            1 | id                | A         |     2138547 |     NULL | NULL   |      | BTREE      |         |
| CachedGroupMembers |          1 | DisGrouMem          |            1 | GroupId           | A         |     2138547 |     NULL | NULL   | YES  | BTREE      |         |
| CachedGroupMembers |          1 | DisGrouMem          |            2 | MemberId          | A         |     2138547 |     NULL | NULL   | YES  | BTREE      |         |
| CachedGroupMembers |          1 | DisGrouMem          |            3 | Disabled          | A         |     2138547 |     NULL | NULL   |      | BTREE      |         |
| CachedGroupMembers |          1 | CachedGroupMembers3 |            1 | MemberId          | A         |     2138547 |     NULL | NULL   | YES  | BTREE      |         |
| CachedGroupMembers |          1 | CachedGroupMembers3 |            2 | ImmediateParentId | A         |     2138547 |     NULL | NULL   | YES  | BTREE      |         |
+--------------------+------------+---------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
6 rows in set (0.01 sec)

I've also tried to add some indexes for this table with no results (http://search.cpan.org/~ruz/RTx-Shredder-0.07/lib/RTx/Shredder.pm#NOTES )

The temporary workaround was to downgrade MySQL to 5.0.22 (Previous version in RHel 5.1) and the Query Builder is working normally again:

mysql> 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 (Groups_3.Domain =   'RT::Queue-Role') AND ((ACL_4.ObjectType = 'RT::Queue') OR   (ACL_4.ObjectType = 'RT::System'))  ORDER BY main.Name ASC;
Empty set (0.13 sec)


But I want MySQL 5.0.45 installed. Can anyone help on this?

Thank you.


Regards,
Liviu

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