[rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8

[Alex Young]

Thanks Nicola,
	I have just tried this and it's not made an difference to the performance. I found your previous emails on this subject so tried the same EXPLAIN and it's pretty quick. 

mysql> explain SELECT DISTINCT main.* FROM Users main 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 (Principals_1.id != '1') AND (Principals_1.PrincipalType = 'User') AND
    -> ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance = '3') OR
    -> (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type = 'AdminCc')  ORDER
    -> BY main.Name ASC;
+----+-------------+----------------------+--------+----------------------------------------------+------------+---------+------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table                | type   | possible_keys                                | key        | key_len | ref                                | rows | Extra                                                     |
+----+-------------+----------------------+--------+----------------------------------------------+------------+---------+------------------------------------+------+-----------------------------------------------------------+
|  1 | SIMPLE      | Groups_3             | range  | PRIMARY,Groups1,Groups2                      | Groups1    | 139     | NULL                               |    5 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,CachedGroupMembers3,SHREDDER_CGM1 | DisGrouMem | 5       | rtdb.Groups_3.id                   |    1 | Using where; Using index                                  |
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                                      | PRIMARY    | 4       | rtdb.CachedGroupMembers_2.MemberId |    1 | Using where                                               |
|  1 | SIMPLE      | main                 | eq_ref | PRIMARY                                      | PRIMARY    | 4       | rtdb.Principals_1.id               |    1 | Using where                                               |
+----+-------------+----------------------+--------+----------------------------------------------+------------+---------+------------------------------------+------+-----------------------------------------------------------+
4 rows in set (0.00 sec)

From: Foggi, Nicola [mailto:NFOGGI at depaul.edu] 
Sent: 18 August 2010 15:02
To: Alex Young; rt-users at lists.bestpractical.com
Subject: RE: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8


I had the same problem and ended up dropping the Groups2 index and performance went back to "normal"

Nicola

-----Original Message-----
From: rt-users-bounces at lists.bestpractical.com on behalf of Alex Young
Sent: Wed 8/18/2010 6:07 AM
To: rt-users at lists.bestpractical.com
Subject: [rt-users] Slow MySQL after upgrade from 3.8.6 to 3.8.8

Hi,

                Since upgrading from 3.8.6 to 3.8.8 our RT system has
been very slow to return any pages containing ticket data such as the
home page, searches and tickets themselves.



I enabled the MySQL slow running query log and this was at the top of
the list:



mysqldumpslow -t 10 /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log

Count: 47  Time=10.12s (475s)  Lock=0.00s (0s)  Rows=1.0 (47),
rtuser[rtuser]@*************

  SELECT GET_LOCK('S', N)



Any ideas?



Thanks.