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

[Foggi, Nicola]

I think i've tracked it down to:

commit  932a5a25a3520f42471e

for Users_Overlay.pm, when I back Users_Overlay back up to the version previous, no problems with a slow query, but whatever query this is building is the problem.  I'm going to try and dig into the code to see if I can tell where it goes wrong, but any ideas on where to start?  My guess is something around the "IncludeSystemRights" logic somewhere in WhoHaveRight or WhoHaveRoleRight?

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/029278fd/attachment.htm>