torsten.brumm at Kuehne-Nagel
Nov 27, 2009, 1:30 AM
Post #6 of 6
(720 views)
Permalink
|
Hi Max,
today i found some time to try out shredder under rt 3.8.6.
First thing i was supprised: Shredder is now part of RT and there are no indexes for on the new creaeted tables. (On 3.6.x with shredder from cpan this indexes have to created manually)
OK, after perldoc Shredder.pm i found this:
NOTES
Database transactions support
Since 0.03_01 RT::Shredder uses database transactions and should be much safer to run on production servers.
Foreign keys
Mainstream RT doesn't use FKs, but at least I posted DDL script that creates them in mysql DB, note that if you use FKs then this two valid keys don't allow delete Tickets because of bug
in MySQL:
ALTER TABLE Tickets ADD FOREIGN KEY (EffectiveId) REFERENCES Tickets(id);
ALTER TABLE CachedGroupMembers ADD FOREIGN KEY (Via) REFERENCES CachedGroupMembers(id);
<http://bugs.mysql.com/bug.php?id=4042>
OK, i couldn't find the "posted DDL Scrip from Ruz" till now but i have done some tests again:
0. I created a new setup inside RT with rt-filler scrip (created 5000 Queues, 50.000 Users and 500.000 Tickets with simple content)
1. Delete Tickets with default Shredder (./rt-shredder --plugin 'Tickets=query,Status="new";limit,10"' --force)
real 1m10.415s
user 0m11.384s
sys 0m0.735s
2. Shredder without Logger Message (result after comment out the logger entry -> for me this is point to STDOUT)
real 1m7.595s
user 0m10.439s
sys 0m0.615s
3. Shredder after Adding Indexes (taken from Shredder.pm from RTx-Shredder / CPAN)
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);
real 0m48.338s
user 0m10.489s
sys 0m0.677s
OK, does anyone know where the the Scrip to create foreign keys is posted?
Torsten
Kuehne + Nagel (AG & Co.) KG, Geschaeftsleitung: Hans-Georg Brinkmann (Vors.), Dirk Blesius (Stellv.), Reiner Heiken (Stellv.), Bruno Mang, Alfred Manke, Christian Marnetté (Stellv.), Mark Reinhardt (Stellv.), Jens Wollesen, Rainer Wunn, Sitz: Bremen, Registergericht: Bremen, HRA 21928, USt-IdNr.: DE 812773878, Persoenlich haftende Gesellschaft: Kuehne & Nagel A.G., Sitz: Contern/Luxemburg Geschaeftsfuehrender Verwaltungsrat: Klaus-Michael Kuehne
________________________________
Von: rt-users-bounces [at] lists [mailto:rt-users-bounces [at] lists] Im Auftrag von Maxwell A. Rathbone
Gesendet: Montag, 23. November 2009 20:17
An: rt-users [at] lists
Betreff: Re: [rt-users] Speeding up CLI RT::Shredder
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>
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
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
|
