Query Builder very slow after system update

RT Users
1

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.htmlhttp://<rt3_url>/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 (RTx::Shredder - Cleanup RT database - metacpan.org )

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

2

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.htmlhttp://<rt3_url>/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 (RTx::Shredder - Cleanup RT database - metacpan.org )

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

3

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.

What does mysqltuner.pl say about your 5.0.45 database? Is it possible
for you to come up to 3.8.2?

4

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.

We massively sped up the Query Builder by overriding the PickBasics
element:

— /opt/rt3/share/html/Search/Elements/PickBasics 2009-02-03
11:53:01.000000000 +0000
+++ ./html/Search/Elements/PickBasics 2009-02-10 15:38:44.000000000
+0000
@@ -121,11 +121,10 @@
Path => ‘/Elements/SelectBoolean’,
Arguments => { TrueVal=> ‘=’, FalseVal => ‘!=’ },
},

  •    Value => {

  •        Type => 'component',

  •        Path => '/Elements/SelectOwner',

  •        Arguments => { ValueAttribute => 'Name' },

  •    },
  • Value => {
  •   Type => 'text',

  •        Size => 20,
  • },
    },
    {
    Name => ‘Watcher’,

It’s the SelectOwner dropdown box which takes so long to populate with
a large number of users in the system, so we just changed it to a text
input box. Can’t take credit for this - a previous poster pointed
this out a couple of months ago.

Tim

The Wellcome Trust Sanger Institute is operated by Genome Research
Limited, a charity registered in England with number 1021457 and a
company registered in England with number 2742969, whose registered
office is 215 Euston Road, London, NW1 2BE.

5

Hello all ,

Thanks for your answers .

I have updated our rt3 into RT 3.8.2 and updated again mysql to 5.0.45 .We have the same problem, this did not resolved our issue .That query is running for a long time, causing the table to lock, web interface of the user who is using the Search/Build.html page to timeout, and giving other users delays in accessing the tickets .
I have also installed mysqltuner.pl , there are no issues reported , everything is green .I have also used mysqlreport to check running parameters , and there was nothing that could cause an mysql server performance issue .

Any other ideas ?

PS : Liviu (the person who reported first this issue) and I ,we are colleagues working on this case .

Greetings,
Adrian Alexandrescu.

6

Hello all ,

Thanks for your answers .

I have updated our rt3 into RT 3.8.2 and updated again mysql to 5.0.45 .We have the same problem, this did not resolved our issue .That query is running for a long time, causing the table to lock, web interface of the user who is using the Search/Build.html page to timeout, and giving other users delays in accessing the tickets .
I have also installed mysqltuner.pl , there are no issues reported , everything is green .I have also used mysqlreport to check running parameters , and there was nothing that could cause an mysql server performance issue .

Any other ideas ?

PS : Liviu (the person who reported first this issue) and I ,we are colleagues working on this case .

Greetings,
Adrian Alexandrescu.From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Liviu Costea
Sent: Friday, April 24, 2009 5:13 PM
To: ‘rt-users@lists.bestpractical.com’
Subject: [rt-users] Query Builder very slow after system update

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.htmlhttp://<rt3_url>/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 (RTx::Shredder - Cleanup RT database - metacpan.org )

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

7

.