[rt-users] RT running slowly... Caused by RTx-Shredder
Ruslan Zakirov
ruz at bestpractical.com
Tue Jul 29 16:50:12 EDT 2008
It's know issue of mysql, here is reference to a description on
request-tracker.ru site http://request-tracker.ru/node/65 (in russian)
On Thu, May 29, 2008 at 1:14 AM, Boris Lytochkin
<boris.lytochkin at e-port.ru> wrote:
>
> 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
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>
--
Best regards, Ruslan.
More information about the rt-users
mailing list