Certain RT operations painfully slow

RT Users
1

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?

Thanks.

2

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?

3

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)

4

Sorry, forgot the list.

KennOn Tue, Jan 18, 2011 at 9:55 AM, Kenneth Crocker kfcrocker@lbl.gov wrote:

Todd,

I’m thinking this AdminCc group has a whole bunch of Global rights. That
would do it.

We did something similar but made it a bit more granular; we create a
Global Group for AdminCc but only gave them these rights; AdminUsers,
AssignCustomFields, SeeCustomFields, SeeGroup, ShowConfigTab, ShowScrips,
ShowTemplates, WatchAsAdminCc). Then we created an AdminCc group for each
set of Queues that belong to a specific Support Group (like Financial).
These AdminCc Sub-groups would be given rights more specific to the
Queues they support. This saves a lot of time when RT is searching for
user privileges per ticket.

Global rights that involve Ticket privileges can be real time burners.

Hope this helps.

Kenn
LBNL

On Tue, Jan 18, 2011 at 9:43 AM, Todd Chapman todd@chaka.net 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?

Thanks.

5

Todd,

Which causes this long running mysql query:

Time: 86
Info: SELECT main.* FROM CachedGroupMembers main WHERE ((main.Via = ‘28522070’)) AND ((main.id != ‘28522070’))

Can anyone recommend an approach to fixing this problem?

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
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)

What happens if you add an index on Via?

Jesse

6

Todd,

Which causes this long running mysql query:

Time: 86
Info: SELECT main.* FROM CachedGroupMembers main WHERE ((main.Via = ‘28522070’)) AND ((main.id != ‘28522070’))

Can anyone recommend an approach to fixing this problem?

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
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)

What happens if you add an index on Via?

I’m considering that, but with 20 million+ rows it will take a
non-trivial amount of time.

7

And here is the slow query for adding a new member to a group that is
AdminCc on a large number of tickets:

 db: rt3

Command: Query
Time: 59
State: Sending data
Info: SELECT main.* FROM CachedGroupMembers main WHERE
((main.MemberId = 541915))

mysql> explain SELECT main.* FROM CachedGroupMembers main WHERE
((main.MemberId = 541915))\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main
type: ref
possible_keys: CachedGroupMembers3
key: CachedGroupMembers3
key_len: 5
ref: const
rows: 417216
Extra: Using where
1 row in set (0.00 sec)

Even when the query completes RT takes a long time to complete.
Perhaps the query is timing out and RT goes off into the weeds?

RT version 3.6.3.On Tue, Jan 18, 2011 at 12:43 PM, Todd Chapman todd@chaka.net 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?

Thanks.

8

RT version 3.6.3.

You really need to come up to (at least) 3.8 for anyone here at BPS to
be able to even hope to help you on this one. We’ve made many, many
improvements to the codepaths you’re touching. (To say nothing of how
much we’ve cut down the size of the CachedGroupMembers table.)

Best,
Jesse

9

Understood. Thanks.On Tue, Jan 18, 2011 at 1:45 PM, Jesse Vincent jesse@bestpractical.com wrote:

RT version 3.6.3.

You really need to come up to (at least) 3.8 for anyone here at BPS to
be able to even hope to help you on this one. We’ve made many, many
improvements to the codepaths you’re touching. (To say nothing of how
much we’ve cut down the size of the CachedGroupMembers table.)

Best,
Jesse

10

Todd,

Another approach is to dissect DeleteWatcher into its row sql and re-write your script to talk sql directly instead of going through the api … (Its highly not recommended) but with 20 million rows its an option.
The advantages you gain is not having to do as many selects.
You may still get queries lasting > 86s , but I would bet those will be 50% less than the api.

Good luck
Roy