<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7655.8">
<TITLE>RE: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<BR>
<P><FONT SIZE=2>I think i've tracked it down to:<BR>
<BR>
commit 932a5a25a3520f42471e<BR>
<BR>
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?<BR>
<BR>
Nicola<BR>
<BR>
-----Original Message-----<BR>
From: ruslan.zakirov@gmail.com on behalf of Ruslan Zakirov<BR>
Sent: Fri 6/25/2010 11:05 AM<BR>
To: Foggi, Nicola<BR>
Cc: rt-users@lists.bestpractical.com<BR>
Subject: Re: [rt-users] slow mysql query after upgrade from 3.8.6 to 3.8.8<BR>
<BR>
Hello,<BR>
<BR>
Do you use SQLForACLChecks option?<BR>
Where is EXPLAIN for this query?<BR>
Show indexes from Groups table.<BR>
<BR>
On Fri, Jun 25, 2010 at 8:04 AM, Foggi, Nicola <NFOGGI@depaul.edu> wrote:<BR>
><BR>
> hey everyone,<BR>
><BR>
> after upgrading from 3.8.6 to 3.8.8 we're getting a slow query on this<BR>
> query:<BR>
><BR>
> use rt3;<BR>
> SELECT DISTINCT main.* FROM Users main JOIN Principals Principals_1 ON (<BR>
> Principals_1.id = main.id ) JOIN CachedGroupMembers CachedGroupMembers_2 ON<BR>
> ( CachedGroupMembers_2.MemberId = Principals_1.id ) JOIN Groups Groups_3 ON<BR>
> ( Groups_3.id = CachedGroupMembers_2.GroupId ) WHERE (Principals_1.Disabled<BR>
> = '0') AND (Principals_1.id != '1') AND (Principals_1.PrincipalType =<BR>
> 'User') AND ((Groups_3.Domain = 'RT::Queue-Role' AND Groups_3.Instance =<BR>
> '3') OR (Groups_3.Domain = 'RT::System-Role')) AND (Groups_3.Type =<BR>
> 'AdminCc') ORDER BY main.Name ASC;<BR>
> # Time: 100624 22:44:20<BR>
> # User@Host: rt_user[rt_user] @ rt.internal [10.12.10.72]<BR>
> # Query_time: 13 Lock_time: 0 Rows_sent: 1 Rows_examined: 0<BR>
> SELECT GET_LOCK('Apache-Session-dc95ab57bb8d19e23fa6fa70314e3c0e', 3600);<BR>
> # Time: 100624 22:49:28<BR>
><BR>
> when loading any ticket page. I've verified the cachedgroupmembers3 index<BR>
> is in place:<BR>
><BR>
> show index from CachedGroupMembers;<BR>
> ...<BR>
> | CachedGroupMembers | 1 | CachedGroupMembers3 | 1 |<BR>
> MemberId | A | 36038 | NULL | NULL | YES |<BR>
> BTREE | NULL |<BR>
> | CachedGroupMembers | 1 | CachedGroupMembers3 | 2 |<BR>
> ImmediateParentId | A | 36038 | NULL | NULL | YES |<BR>
> BTREE | NULL |<BR>
><BR>
><BR>
> but still extremely slow... any ideas? it's pretty bad...<BR>
><BR>
> Nicola<BR>
><BR>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.<BR>
> Buy a copy at <A HREF="http://rtbook.bestpractical.com">http://rtbook.bestpractical.com</A><BR>
><BR>
<BR>
<BR>
<BR>
--<BR>
Best regards, Ruslan.<BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>