[rt-users] Speeding up CLI RT::Shredder

Torsten Brumm torsten.brumm at googlemail.com
Tue Nov 24 06:48:44 EST 2009 

Hi Max,
now i found the time to try it out.

Btw: i'm under RT 3.6.5 with RTx-Shredder, but there the Logger Entry is
also set. Here the Results first from my test box (only RT running on it, no
users)

Empty DB

created 1000 tickets with rt-filler scrip

1. Tickets shreddered without any index set -> 10 ticket shreddered by
default

time ./rtx-shredder --force --plugin 'Tickets=status,new'
SQL dump file is '/opt/rt3tra/local/sbin/20091124T114147-0001.sql'

real    0m29.477s
user    0m6.638s
sys     0m0.323s


2. Ticket shreddered with the following indexes set:


           CREATE INDEX SHREDDER_CGM1 ON CachedGroupMembers(MemberId,
GroupId, Disabled);
           CREATE INDEX SHREDDER_CGM2 ON
CachedGroupMembers(ImmediateParentId, MemberId);

           CREATE UNIQUE INDEX SHREDDER_GM1 ON GroupMembers(MemberId,
GroupId);

           CREATE INDEX SHREDDER_TXN1 ON Transactions(ReferenceType,
OldReference);
           CREATE INDEX SHREDDER_TXN2 ON Transactions(ReferenceType,
NewReference);
           CREATE INDEX SHREDDER_TXN3 ON Transactions(Type, OldValue);
           CREATE INDEX SHREDDER_TXN4 ON Transactions(Type, NewValue);



mysql> CREATE INDEX SHREDDER_CGM1 ON CachedGroupMembers(MemberId, GroupId,
Disabled);
Query OK, 18411 rows affected (1.23 sec)
Records: 18411  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX SHREDDER_CGM2 ON CachedGroupMembers(ImmediateParentId,
MemberId);
Query OK, 18411 rows affected (1.45 sec)
Records: 18411  Duplicates: 0  Warnings: 0

mysql> CREATE UNIQUE INDEX SHREDDER_GM1 ON GroupMembers(MemberId, GroupId);
Query OK, 6902 rows affected (0.42 sec)
Records: 6902  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX SHREDDER_TXN1 ON Transactions(ReferenceType,
OldReference);
Query OK, 9940 rows affected (0.78 sec)
Records: 9940  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX SHREDDER_TXN2 ON Transactions(ReferenceType,
NewReference);
Query OK, 9940 rows affected (0.91 sec)
Records: 9940  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX SHREDDER_TXN3 ON Transactions(Type, OldValue);
Query OK, 9940 rows affected (1.02 sec)
Records: 9940  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX SHREDDER_TXN4 ON Transactions(Type, NewValue);
Query OK, 9940 rows affected (1.17 sec)
Records: 9940  Duplicates: 0  Warnings: 0

mysql> quit
Bye
[root at messenger sbin]# time ./rtx-shredder --force --plugin
'Tickets=status,new'
SQL dump file is '/opt/rt3tra/local/sbin/20091124T114403-0001.sql'

real    0m10.041s
user    0m6.612s
sys     0m0.354s


3. Ticket shreddered after removed logger entry in Record.pm

[root at messenger sbin]# time ./rtx-shredder --force --plugin
'Tickets=status,new'
SQL dump file is '/opt/rt3tra/local/sbin/20091124T114602-0001.sql'

real    0m9.475s
user    0m6.196s
sys     0m0.317s


Will try out the same with RT 3.8.6, lets seee what happens


Torsten

2009/11/23 Maxwell A. Rathbone <mrathbone at sagonet.com>

>  I noticed a typo in probably the most important line in my message. The
> filename is actually:
>
> /opt/rt3/lib/RT/Shredder/Record.pm
>
> The line that I suggest to comment out, calls RT's built in Logger()
> function that basically just writes information either to the log or to the
> screen.
>
> As with anytime you modify defaults, I make no claims other than what it
> had for me. :) I'm actually seeing slightly better than 50% improvement with
> that line disabled/commented out.
>
> I hope others are able to confirm similar experiences. Look forward to
> reading about it.
>
> Max
>
>
> Torsten Brumm wrote:
>
> Oha, this sounds really useful. Any comment from ruslan if this is save?
>
> I have to shred several houndret thousend tickets from 2002-2007 and we
> need also around 2 minutes per ticket, will try it out tomorrow!
>
> Thanks for sharing this
>
> Torsten
>
> 2009/11/23 Maxwell A. Rathbone <mrathbone at sagonet.com>
>
>> Hello,
>>
>> I'm in the same boat as many others I've seen post. We have 35k tickets
>> in one of our queues that I'm trying to shred(shame on us for not
>> automating this previously). I've found the web version of the Shredder
>> to be god-awful slow. We're talking 10min+ just to shred ONE ticket. So
>> I discovered the command-line /opt/rt3/sbin/rt-shredder utility. I was
>> then able to shred ONE ticket in about 5 minutes. I found some
>> optimization keys to add to the tables, which allowed me to them shred
>> ONE ticket in about a minute. I then discovered(this really should be in
>> the documentation!), that if you specify a timeframe with rt-shredder,
>> you can get MUCH faster processing. I was able to get it down to
>> 21seconds for the shredding of ONE ticket.
>>
>> I noticed it was spitting out warning messages each time it deletes
>> something. I honestly do not care about the output as long as it is
>> working as expected, so I hunted through the code and was able to
>> disable the on-screen logging altogether. I'm now able to shred ONE
>> ticket in about 8-10 seconds.
>>
>> For those who are interested in about a 50% reduction in processing time
>> for the CLI Shredder, edit the file:
>> /opt/rt3/lib/RT/Shredder/Rercord.pm
>>
>> Look for this line:
>>    $RT::Logger->warning( $msg );
>>
>> Comment it so it looks like this:
>> #    $RT::Logger->warning( $msg );
>>
>> a WORLD of difference from the 10 minutes per ticket I originally was
>> getting. Now it looks like to shred the 35k might actually take a
>> palatable amount of time.
>>
>> I wanted to share this useful information on the list so it is google
>> searchable. I'm SURE others will find this helpful.
>>
>> BTW, the command I'm using to shred(again, documentation is kinda poor)
>> is:
>> ./rt-shredder --plugin "Tickets=query,((status = 'deleted' OR status =
>> 'rejected') AND
>>
>> LastUpdated='2008-10-03');limit,100;with_linked,FALSE;apply_query_to_linked,FALSE"
>> --force
>>
>> Max
>> _______________________________________________
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>
>> Community help: http://wiki.bestpractical.com
>> Commercial support: sales at bestpractical.com
>>
>>
>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>> Buy a copy at http://rtbook.bestpractical.com
>>
>
>
>
> --
> MFG
>
> Torsten Brumm
>
> http://www.brumm.me
> http://www.elektrofeld.de
>
>
>
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
MFG

Torsten Brumm

http://www.brumm.me
http://www.elektrofeld.de
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20091124/c8659eb8/attachment.htm>

More information about the rt-users mailing list