RT running slowly

RT Users
1

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@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

2

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@mionegroup.com
To: “rt-users” rt-users@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@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

_______________________________________________ The rt-users Archives Community help: http://wiki.bestpractical.com Commercial support: sales@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@mionegroup.com
Website: www.mionegroup.com

3

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@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;

Using DISTINCT with MySQL is bad thing. It could even freeze the engine.
You could use FORCE INDEX or USE INDEX to force MySQL to use some index of
that table, that usually solves the problem.

Hope this helps.

4

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@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@mionegroup.com
To: “rt-users” rt-users@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@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

The rt-users Archives Community
help: http://wiki.bestpractical.com Commercial support:
sales@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@mionegroup.com
Website: www.mionegroup.com

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.