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

[Boris Lytochkin]

After adding this key:
ALTER TABLE CachedGroupMembers Add KEY `SHREDDER_CGM1` (`GroupId`,`MemberId`,`Disabled`);
- MemberId & GroupId swapped comparing to original SHREDDER_CGM1 - explain (and query too :) ) is OK.

mysql> explain extended SELECT DISTINCT main.* FROM Users main
mysql>   CROSS JOIN ACL ACL_4
mysql>   JOIN Principals Principals_1  ON ( Principals_1.id = main.id )
mysql>   JOIN CachedGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.MemberId = Principals_1.id )
mysql>   JOIN Groups Groups_3  ON ( Groups_3.id = CachedGroupMembers_2.GroupId )
mysql>    WHERE (Principals_1.Disabled = '0') AND (ACL_4.PrincipalType = Groups_3.Type)
mysql>     AND (Principals_1.id != '1')    AND (Principals_1.PrincipalType = 'User')
mysql>     AND (ACL_4.RightName = 'OwnTicket')    AND (Groups_3.Domain = 'RT::System-Role')
mysql>     AND ((ACL_4.ObjectType = 'RT::Ticket' AND ACL_4.ObjectId = 147792) OR (ACL_4.ObjectType = 'RT::Queue' AND ACL_4.ObjectId = 10) OR (ACL_4.ObjectType = 'RT::System'))
mysql>    ORDER BY main.Name ASC;
+----+-------------+----------------------+--------+--------------------------+---------------+---------+----------------------------------+-------+------------------------------------+
| id | select_type | table                | type   | possible_keys            | key           | key_len | ref                              | rows  | Extra                              |
+----+-------------+----------------------+--------+--------------------------+---------------+---------+----------------------------------+-------+------------------------------------+
|  1 | SIMPLE      | main                 | ALL    | PRIMARY                  | NULL          |    NULL | NULL                             | 39464 | 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,SHREDDER_CGM1 | SHREDDER_CGM1 |       5 | rt3.Principals_1.id              |     1 | Using where; Using index; Distinct |
|  1 | SIMPLE      | ACL_4                | range  | ACL1                     | ACL1          |      54 | NULL                             |     5 | Using where; Using index; Distinct |
|  1 | SIMPLE      | Groups_3             | eq_ref | PRIMARY,Groups1,Groups2  | PRIMARY       |       4 | rt3.CachedGroupMembers_2.GroupId |     1 | Using where; Distinct              |
+----+-------------+----------------------+--------+--------------------------+---------------+---------+----------------------------------+-------+------------------------------------+

After dropping SHREDDER_CGM1:
+----+-------------+----------------------+--------+-------------------------+------------+---------+-----------------------------------+------+-----------------------------------------------------------+
| id | select_type | table                | type   | possible_keys           | key        | key_len | ref                               | rows | Extra                                                     |
+----+-------------+----------------------+--------+-------------------------+------------+---------+-----------------------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | Groups_3             | ref    | PRIMARY,Groups1,Groups2 | Groups1    |      65 | const                             |    4 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem              | DisGrouMem |       5 | rt3.Groups_3.id                   |    1 | Using where; Using index                                  |
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                 | PRIMARY    |       4 | rt3.CachedGroupMembers_2.MemberId |    1 | Using where                                               |
|  1 | SIMPLE      | ACL_4                | range  | ACL1                    | ACL1       |      54 | NULL                              |    5 | Using where; Using index                                  |
|  1 | SIMPLE      | main                 | eq_ref | PRIMARY                 | PRIMARY    |       4 | rt3.Principals_1.id               |    1 |                                                           |
+----+-------------+----------------------+--------+-------------------------+------------+---------+-----------------------------------+------+-----------------------------------------------------------+

SHREDDER_CGM1:
mysql> show index from CachedGroupMembers;
+--------------------+------------+---------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name      | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+---------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
...
| CachedGroupMembers |          1 | SHREDDER_CGM1 |            1 | MemberId          | A         |      400337 |     NULL | NULL   | YES  | BTREE      |         |
| CachedGroupMembers |          1 | SHREDDER_CGM1 |            2 | GroupId           | A         |      400337 |     NULL | NULL   | YES  | BTREE      |         |
| CachedGroupMembers |          1 | SHREDDER_CGM1 |            3 | Disabled          | A         |      400337 |     NULL | NULL   |      | BTREE      |         |
...
+--------------------+------------+---------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+


> Message: 6
> Date: Thu, 22 May 2008 16:08:27 +1000 (EST)
> From: David Hobley <david.hobley at mionegroup.com>
> Subject: Re: [rt-users] RT running slowly... Caused by RTx-Shredder
>         indexes
> To: rt-users <rt-users at lists.bestpractical.com>
> Message-ID: <10763813.80751211436507065.JavaMail.root at mail.onegrp.com>
> Content-Type: text/plain; charset="utf-8"

> 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 (
mysql>> 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)
mysql>> 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
mysql>> ((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 
-- 
Best regards,
 Boris Lytochkin                            mailto:boris.lytochkin at e-port.ru