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

[Ruslan Zakirov]

Known issue with mysql's optimizer. Either drop index or upgrade mysql
to 5.0.45 and newer. Mysql 5.1.x have some optmiser issues as well.

On Thu, May 22, 2008 at 10:08 AM, David Hobley
<david.hobley at mionegroup.com> wrote:
> 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 ( 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 ((ACL_4.ObjectType = 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System'))
> AND ((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
> ----- Original Message -----
> From: "David Hobley" <david.hobley at mionegroup.com>
> To: "rt-users" <rt-users at lists.bestpractical.com>
> Sent: Thursday, 22 May, 2008 3:30:21 PM GMT +10:00 Brisbane
> Subject: [rt-users] RT running slowly...
>
> Hello,
>
> Our RT has been running slowly for a while; having turned on mysql slow
> query logging, it appears to only be a single query which is causing grief.
> Anyone any suggestions as to what can be done to fix this?
>
> # Time: 080522 15:32:21
> # User at Host: rt[rt] @ localhost []
> # Query_time: 40  Lock_time: 0  Rows_sent: 0  Rows_examined: 6888927
> 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 ((ACL_4.ObjectType =
> 'RT::Queue') OR (ACL_4.ObjectType = 'RT::System')) AND ((Groups_3.Domain =
> 'RT::Queue-Role') OR (Groups_3.Domain = 'RT::System-Role'))  ORDER BY
> main.Name ASC;
>
> --
> Cheers,
> David
>
> _______________________________________________
> 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
>
> --
> Cheers,
> David Hobley
>
> IT Manager
> Creators of Miessence, MiVitality and MiEnviron
>
> Phone: +61 (7) 5582 7020
> Fax: +61 (7) 5539 6719
> USA Fax 1800 840 0827
> Email : david.hobley at mionegroup.com
> Website: www.mionegroup.com
>
>
>
> _______________________________________________
> 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.