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: [email protected] [mailto:[email protected]] On Behalf Of Liviu Costea Sent: Friday, April 24, 2009 5:13 PM To: '[email protected]' 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.html<http://%3crt3_url%3e/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 (http://search.cpan.org/~ruz/RTx-Shredder-0.07/lib/RTx/Shredder.pm#NOTES ) 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
_______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [email protected] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
