[rt-users] TicketHistory slow on 4.2.0

[Tod Detre]

I'm learning more and more about mysql and RT trying to track down this
problem. 8-) I ran mysqldumpslow to try to find queries that are showing up
a lot and found the queries listed below. However, I'm not convinced it is
on the sql server as I can cause the problem to resurface once it's gone by
clearing my browser's cache.

Count: 192  Time=7.50s (1439s)  Lock=0.00s (0s)  Rows=1.0 (192), rt4[rt4]@
xxx.xxx.net
  SELECT count(main.id) FROM ObjectCustomFieldValues main  WHERE
(main.Disabled = 'S') AND (main.ObjectId = 'S') AND (main.ObjectType = 'S')
Count: 159  Time=0.17s (27s)  Lock=0.00s (0s)  Rows=1.0 (159), rt4[rt4]@
xxx.xxx.net
  SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Groups Groups_1  ON
( Groups_1.Domain = 'S' ) AND ( Groups_1.Instance = main.id ) LEFT JOIN
CachedGro
upMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.Disabled = 'S' )
AND ( CachedGroupMembers_2.MemberId = 'S' ) AND (
CachedGroupMembers_2.GroupId = G
roups_1.id )  WHERE ( (  ( main.Queue = 'S' OR main.Queue = 'S' OR
main.Queue = 'S' OR main.Queue = 'S' )  OR  ( CachedGroupMembers_2.MemberId
IS NOT NULL AN
D Groups_1.Name = 'S' )  OR  ( main.Owner = 'S' )  ) ) AND (main.IsMerged
IS NULL) AND (main.Status != 'S') AND (main.Type = 'S') AND (main.Priority
> 'S' AN
D  ( CASE WHEN main.Created BETWEEN 'S' AND 'S' THEN NULL ELSE main.Created
END > 'S' OR  ( main.Status = 'S' OR main.Status = 'S' OR main.Status = 'S'
)  )
)
Count: 159  Time=0.21s (32s)  Lock=0.00s (0s)  Rows=1.0 (159), rt4[rt4]@
xxx.xxx.net
  SELECT COUNT(DISTINCT main.id) FROM Tickets main JOIN Groups Groups_1  ON
( Groups_1.Domain = 'S' ) AND ( Groups_1.Instance = main.id ) LEFT JOIN
CachedGro
upMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.Disabled = 'S' )
AND ( CachedGroupMembers_2.MemberId = 'S' ) AND (
CachedGroupMembers_2.GroupId = G
roups_1.id )  WHERE ( (  ( main.Queue = 'S' OR main.Queue = 'S' )  OR  (
CachedGroupMembers_2.MemberId IS NOT NULL AND Groups_1.Name = 'S' )  OR  (
main.Owne
r = 'S' )  ) ) AND (main.IsMerged IS NULL) AND (main.Status != 'S') AND
(main.Type = 'S') AND (main.Priority > 'S' AND  ( CASE WHEN main.Created
BETWEEN 'S'
AND 'S' THEN NULL ELSE main.Created END > 'S' OR  ( main.Status = 'S' OR
main.Status = 'S' OR main.Status = 'S' )  ) )



On Wed, Oct 23, 2013 at 4:38 PM, Tod Detre <tod.detre at maine.edu> wrote:

> On Wed, Oct 23, 2013 at 2:25 PM, Alex Vandiver <alexmv at bestpractical.com>wrote:
>
>> On Wed, 2013-10-23 at 13:47 -0400, Tod Detre wrote:
>> > I've had the mysql slow log on, but it does not show any slow
>> > queries.
>>
>> That is surprising to me, as the symptoms of "first load is slow,
>> successive ones are fast" sounds like the MySQL query cache to me, as
>> I'm not aware of any other caching layers in that area.  I'd
>> double-check your slow query log threshold and relevant settings.
>
>
> Sorry, about that I am seeing slow queries, but not for every time I see
> the slow history problem. Here is an example of the slow queries we're
> seeing:
>
> # User at Host: rt4[rt4] @ rt4.xxx.net [xxx.xxx.xxx.xxx]
> # Query_time: 0.040443  Lock_time: 0.000247 Rows_sent: 10  Rows_examined:
> 26134
> SET timestamp=1382560324;
> SELECT DISTINCT main.* FROM Tickets main JOIN Groups Groups_1  ON (
> Groups_1.Domain = 'RT::Ticket-Role' ) AND ( Groups_1.Instance = main.id )
> LEFT JOIN Cache
> dGroupMembers CachedGroupMembers_2  ON ( CachedGroupMembers_2.Disabled =
> '0' ) AND ( CachedGroupMembers_2.MemberId = '37' ) AND (
> CachedGroupMembers_2.GroupI
> d = Groups_1.id )  WHERE ( (  ( main.Queue = '1' OR main.Queue = '1' OR
> main.Queue = '3' OR main.Queue = '4' OR main.Queue = '4' OR main.Queue =
> '5' OR main.
> Queue = '5' OR main.Queue = '6' OR main.Queue = '6' OR main.Queue = '7' OR
> main.Queue = '8' OR main.Queue = '9' OR main.Queue = '9' OR main.Queue =
> '10' OR m
> ain.Queue = '10' OR main.Queue = '11' OR main.Queue = '12' OR main.Queue =
> '13' OR main.Queue = '14' OR main.Queue = '15' OR main.Queue = '16' OR
> main.Queue
> = '17' OR main.Queue = '18' OR main.Queue = '19' OR main.Queue = '20' OR
> main.Queue = '21' OR main.Queue = '22' OR main.Queue = '23' OR main.Queue =
> '24' OR
> main.Queue = '25' OR main.Queue = '26' OR main.Queue = '27' OR main.Queue
> = '28' OR main.Queue = '29' OR main.Queue = '30' OR main.Queue = '31' OR
> main.Queue
>  = '32' OR main.Queue = '33' OR main.Queue = '34' OR main.Queue = '35' OR
> main.Queue = '36' OR main.Queue = '37' OR main.Queue = '38' OR main.Queue =
> '39' OR
>  main.Queue = '40' OR main.Queue = '41' OR main.Queue = '42' OR main.Queue
> = '43' OR main.Queue = '44' OR main.Queue = '45' OR main.Queue = '46' OR
> main.Queu
> e = '47' OR main.Queue = '48' OR main.Queue = '49' OR main.Queue = '50' OR
> main.Queue = '51' OR main.Queue = '52' OR main.Queue = '53' OR main.Queue =
> '54' O
> R main.Queue = '54' OR main.Queue = '56' OR main.Queue = '57' OR
> main.Queue = '58' OR main.Queue = '59' OR main.Queue = '60' OR main.Queue =
> '62' OR main.Que
> ue = '63' OR main.Queue = '64' OR main.Queue = '64' OR main.Queue = '64'
> OR main.Queue = '66' OR main.Queue = '68' OR main.Queue = '68' OR
> main.Queue = '70'
> OR main.Queue = '71' OR main.Queue = '71' OR main.Queue = '72' OR
> main.Queue = '73' OR main.Queue = '75' OR main.Queue = '76' OR main.Queue =
> '76' OR main.Qu
> eue = '77' OR main.Queue = '77' OR main.Queue = '78' OR main.Queue = '78'
> OR main.Queue = '79' OR main.Queue = '79' OR main.Queue = '80' OR
> main.Queue = '80'
>  OR main.Queue = '81' OR main.Queue = '81' OR main.Queue = '82' OR
> main.Queue = '83' )  OR  ( CachedGroupMembers_2.MemberId IS NOT NULL AND
> Groups_1.Name = '
> Requestor' )  OR  ( main.Owner = '37' )  ) ) AND (main.IsMerged IS NULL)
> AND (main.Status != 'deleted') AND (main.Type = 'ticket') AND
> (main.Priority > '49'
> AND  ( CASE WHEN main.Created BETWEEN '1969-12-31 11:59:59' AND
> '1970-01-01 12:00:01' THEN NULL ELSE main.Created END > '2013-09-23
> 20:32:04' OR  ( main.Stat
> us = 'new' OR main.Status = 'open' OR main.Status = 'stalled' )  ) )
>  ORDER BY main.Status ASC, main.Created DESC  LIMIT 10;
> # Time: 131023 16:32:05
>
>
>
>
>>  > Another interesting point, the mobile site does not seem to have the
>> > same problem.
>>
>> Do you have any local customizations?
>
>
> We do have one customization. However even if I remove it and clear the
> mason cache, the problem persists.
>
> --
> Tod
>



-- 
Tod
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20131024/9f826a06/attachment.htm>