[rt-users] RTx-Shredder speed (or lack of!)

[Tim Cutts]

On 27 Nov 2008, at 1:14 pm, Howard Jones wrote:

> I've just been running RTx-Shredder for the first time in quite a  
> while,
> and I get *really* slow speeds out of it... 67 minutes to delete 10
> tickets. I'm sure I remember doing 1000s in a day with a previous  
> version.
>
> RT itself is quite responsive, so I don't think it's a general  
> server issue.
>
> I'm using
>   time ./rtx-shredder  --plugin  
> 'Tickets=queue,probablespam;status,deleted'
> and I get offered 10 tickets to delete, and then much later:
>   real    67m38.004s
>   user    0m8.891s
>   sys     0m0.320s
>
> Is there something I should be looking at here? A missing index or
> something? I can see that my Attachments and CachedGroupMembers tables
> have ~3M rows, for example.
>
> Thanks for any pointers...

Yes, it does take a lot of time, because the sorts of queries it makes  
are not "normal" for RT, so there are no indices.  I believe in 3.8  
much of this has been addressed, but certainly in 3.4 I have had to  
add a lot of extra indices to make Shredder go faster (and even after  
all that it's still fairly slow, but at least now I can delete several  
hundred tickets an hour, when necessary)

I created five indexes on the Transactions table, in particular:

| Transactions |          1 | tjrc_hack1    |            1 |  
OldReference  | A         |          17 |     NULL | NULL   | YES  |  
BTREE      |         |
| Transactions |          1 | tjrc_hack1    |            2 |  
ReferenceType | A         |          17 |     NULL | NULL   | YES  |  
BTREE      |         |
| Transactions |          1 | tjrc_hack2    |            1 |  
NewReference  | A         |      285372 |     NULL | NULL   | YES  |  
BTREE      |         |
| Transactions |          1 | tjrc_hack2    |            2 |  
ReferenceType | A         |      285372 |     NULL | NULL   | YES  |  
BTREE      |         |
| Transactions |          1 | tjrc_hack3    |            1 |  
OldValue      | A         |      109758 |     NULL | NULL   | YES  |  
BTREE      |         |
| Transactions |          1 | tjrc_hack3    |            2 |  
Type          | A         |      109758 |     NULL | NULL   | YES  |  
BTREE      |         |
| Transactions |          1 | tjrc_hack4    |            1 |  
NewValue      | A         |       13719 |     NULL | NULL   | YES  |  
BTREE      |         |
| Transactions |          1 | tjrc_hack4    |            2 |  
Type          | A         |       13719 |     NULL | NULL   | YES  |  
BTREE      |         |
| Transactions |          1 | tjrc_hack5    |            1 |  
Creator       | A         |       10119 |     NULL | NULL   |      |  
BTREE      |         |

Basically, I created these by logging into the RT database with mysql  
while running RTx-Shredder, and watching which queries were taking a  
long time, running EXPLAIN on them and then adding appropriate indexes  
to stop the full table scans which were going on.

I also added two indexes to CachedGroupMembers:

| CachedGroupMembers |          1 | tjrc_hack1 |            1 |  
ImmediateParentId | A         |      592001 |     NULL | NULL   | YES   
| BTREE      |         |
| CachedGroupMembers |          1 | tjrc_hack1 |            2 |  
MemberId          | A         |      592001 |     NULL | NULL   | YES   
| BTREE      |         |
| CachedGroupMembers |          1 | tjrc_hack2 |            1 |  
Via               | A         |      592001 |     NULL | NULL   | YES   
| BTREE      |         |
| CachedGroupMembers |          1 | tjrc_hack2 |            2 |  
id                | A         |      592001 |     NULL | NULL   |       
| BTREE      |         |

And a Creator index on Attachments:

| Attachments |          1 | tjrc_creator |            1 |  
Creator       | A         |        1634 |     NULL | NULL   |      |  
BTREE      |         |

Regards,

Tim


-- 
 The Wellcome Trust Sanger Institute is operated by Genome Research 
 Limited, a charity registered in England with number 1021457 and a 
 company registered in England with number 2742969, whose registered 
 office is 215 Euston Road, London, NW1 2BE.