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: [email protected]
[mailto:[email protected]] Im Auftrag von Maxwell A.
Rathbone
Gesendet: Montag, 23. November 2009 20:17
An: [email protected]
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 <[email protected]>
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: [email protected]
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: [email protected]
Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
Buy a copy at http://rtbook.bestpractical.com
