[rt-users] Certain RT operations painfully slow.

[Todd Chapman]

On Tue, Jan 18, 2011 at 12:48 PM, Jesse Vincent <jesse at bestpractical.com> wrote:
>
>
>
> On Tue, Jan 18, 2011 at 12:43:47PM -0500, Todd Chapman wrote:
>> RT Users,
>>
>> A few years ago we started adding a group as AdminCc to many tickets.
>> It made sense for our process and seemed like a good idea at the time.
>> Fast forward to today. This group is AdminCc on many, many tickets.
>> Now when we add a new member to the group it can take 15 minutes for
>> the add to complete, usually with the web interface timing out.
>>
>> To alleviate this situation I wrote a script to remove this group from
>> many old tickets, but this too is taking a long time to complete.
>> Here's the key line from the script:
>>
>> my ($status, $msg) = $t->DeleteWatcher(PrincipalId => $pid, Type =>
>> "AdminCc", Silent => 1);
>>
>> Which causes this long running mysql query:
>>
>>
>> Command: Query
>>    Time: 86
>>   State: Sending data
>>    Info: SELECT main.* FROM CachedGroupMembers main   WHERE ((main.Via
>> = '28522070')) AND ((main.id != '28522070'))
>>
>>
>> Ouch.
>>
>> Can anyone recommend an approach to fixing this problem?
>
>
> Todd,
>
> What does an EXPLAIN on that select tell you?

mysql> explain SELECT main.* FROM CachedGroupMembers main   WHERE
((main.Via = '28522070')) AND ((main.id != '28522070'))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: main
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 20568305
        Extra: Using where
1 row in set (0.02 sec)


>
>>
>> Thanks.
>
> --
>