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

Fri Nov 27 04:30:48 EST 2009

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.bestpractical.com [mailto:rt-users-bounces at lists.bestpractical.com] Im Auftrag von Maxwell A. Rathbone
Gesendet: Montag, 23. November 2009 20:17
An: rt-users at lists.bestpractical.com
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.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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20091127/45dd8dfd/attachment.htm>