[rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8

[Foggi, Nicola]

Ruslan,

So I think I figured it out... the original EXPLAIN was showing it using Groups2 index.  I forced a query against the Groups1 index, and the results came back in under 1 second vs 11-15 seconds before.  I dropped the Groups2 index for the time being.  Maybe with all the re-writes around User_Overlay.pm the indexes were overlooked?  Do you know what the Groups2 index was created for?

Thoughts?  Current EXPLAIN without the Groups2 index is:

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                | Groups1    | 139     | NULL                        |    5 | Using where; Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | main                 | range  | PRIMARY                        | PRIMARY    | 4       | NULL                        |  317 | Using where                                               | 
|  1 | SIMPLE      | Principals_1         | eq_ref | PRIMARY                        | PRIMARY    | 4       | rt3.main.id                 |    1 | Using where; Distinct                                     | 
|  1 | SIMPLE      | CachedGroupMembers_2 | ref    | DisGrouMem,CachedGroupMembers3 | DisGrouMem | 10      | rt3.Groups_3.id,rt3.main.id |    1 | Using where; Using index; Distinct                        | 
+----+-------------+----------------------+--------+--------------------------------+------------+---------+-----------------------------+------+-----------------------------------------------------------+
4 rows in set (0.00 sec)


Nicola

-----Original Message-----
From: ruslan.zakirov at gmail.com on behalf of Ruslan Zakirov
Sent: Fri 6/25/2010 11:05 AM
To: Foggi, Nicola
Cc: rt-users at lists.bestpractical.com
Subject: Re: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8
 
Hello,

Do you use SQLForACLChecks option?
Where is EXPLAIN for this query?
Show indexes from Groups table.

On Fri, Jun 25, 2010 at 8:04 AM, Foggi, Nicola <NFOGGI at depaul.edu> wrote:
>
> hey everyone,
>
> after upgrading from 3.8.6 to 3.8.8 we're getting a slow query on this
> query:
>
> use rt3;
> 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;
> # Time: 100624 22:44:20
> # User at Host: rt_user[rt_user] @ rt.internal [10.12.10.72]
> # Query_time: 13  Lock_time: 0  Rows_sent: 1  Rows_examined: 0
> SELECT GET_LOCK('Apache-Session-dc95ab57bb8d19e23fa6fa70314e3c0e', 3600);
> # Time: 100624 22:49:28
>
> when loading any ticket page.  I've verified the cachedgroupmembers3 index
> is in place:
>
> show index from CachedGroupMembers;
> ...
> | CachedGroupMembers |          1 | CachedGroupMembers3 |            1 |
> MemberId          | A         |       36038 |     NULL | NULL   | YES  |
> BTREE      | NULL    |
> | CachedGroupMembers |          1 | CachedGroupMembers3 |            2 |
> ImmediateParentId | A         |       36038 |     NULL | NULL   | YES  |
> BTREE      | NULL    |
>
>
> but still extremely slow... any ideas?  it's pretty bad...
>
> Nicola
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20100625/e2502432/attachment.htm>