<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.6000.16939" name=GENERATOR></HEAD>
<BODY text=#000000 bgColor=#ffffff>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>Hi Max,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>today i found some time to try out shredder under rt
3.8.6.</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>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)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>OK, after perldoc Shredder.pm i found
this:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>NOTES<BR> Database transactions
support<BR> Since 0.03_01 RT::Shredder uses
database transactions and should be much safer to run on production
servers.</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2> Foreign
keys<BR> 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<BR> in MySQL:</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2> ALTER
TABLE Tickets ADD FOREIGN KEY (EffectiveId) REFERENCES
Tickets(id);<BR> ALTER TABLE
CachedGroupMembers ADD FOREIGN KEY (Via) REFERENCES
CachedGroupMembers(id);</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2> <<A
href="http://bugs.mysql.com/bug.php?id=4042">http://bugs.mysql.com/bug.php?id=4042</A>><BR></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>OK, i couldn't find the "posted DDL Scrip from Ruz" till
now but i have done some tests again:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>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)</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>1. Delete Tickets with default Shredder (./rt-shredder
--plugin 'Tickets=query,Status="new";limit,10"' --force)</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>real 1m10.415s<BR>user
0m11.384s<BR>sys 0m0.735s</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2><BR>2. Shredder without Logger Message (result after
comment out the logger entry -> for me this is point to
STDOUT)</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>real 1m7.595s<BR>user
0m10.439s<BR>sys 0m0.615s</FONT></SPAN></DIV>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2><BR>3. Shredder after Adding Indexes (taken from
Shredder.pm from RTx-Shredder / CPAN)</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>CREATE INDEX SHREDDER_CGM1 ON CachedGroupMembers(MemberId,
GroupId, Disabled);<BR>CREATE INDEX SHREDDER_CGM2 ON
CachedGroupMembers(ImmediateParentId, MemberId);</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>CREATE UNIQUE INDEX SHREDDER_GM1 ON GroupMembers(MemberId,
GroupId);</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>CREATE INDEX SHREDDER_TXN1 ON Transactions(ReferenceType,
OldReference);<BR>CREATE INDEX SHREDDER_TXN2 ON Transactions(ReferenceType,
NewReference);<BR>CREATE INDEX SHREDDER_TXN3 ON Transactions(Type,
OldValue);<BR>CREATE INDEX SHREDDER_TXN4 ON Transactions(Type,
NewValue);</FONT></SPAN></DIV>
<DIV> </DIV>
<DIV dir=ltr align=left><SPAN class=932212309-27112009><FONT face=Arial
color=#0000ff size=2>real 0m48.338s<BR>user
0m10.489s<BR>sys 0m0.677s</DIV>
<DIV></FONT></SPAN> </DIV>
<DIV><SPAN class=932212309-27112009><FONT face=Arial color=#0000ff size=2>OK,
does anyone know where the the Scrip to create foreign keys is
posted?</FONT></SPAN></DIV>
<DIV><SPAN class=932212309-27112009><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=932212309-27112009><FONT face=Arial color=#0000ff
size=2>Torsten</FONT></SPAN></DIV>
<DIV><BR></DIV>
<br>
<p class=MsoBodyText><span style='font-size:8.0pt;font-family:Arial'>Kühne +
Nagel (AG & Co.) KG, Geschäftsleitung: Hans-Georg Brinkmann (Vors.), Dirk Blesius (Stellv.), Reiner <span
class=SpellE>Heiken</span> (Stellv.), Bruno Mang, Alfred <span
class=SpellE>Manke</span>, Christian Marnetté (Stellv.), Mark Reinhardt (Stellv.), Jens <span
class=SpellE>Wollesen</span>, Rainer <span class=SpellE>Wunn</span>,
Sitz: Bremen, Registergericht: Bremen, HRA 21928, <span class=SpellE>USt-IdNr</span>.:
DE 812773878, Persönlich haftende Gesellschaft: Kühne & Nagel A.G., Sitz: <span
class=SpellE>Contern/Luxemburg</span>, Geschäftsführender Verwaltungsrat:
Klaus-Michael Kühne <o:p></o:p></span></p>
<br><DIV class=OutlookMessageHeader lang=de dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>Von:</B> rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] <B>Im Auftrag von </B>Maxwell
A. Rathbone<BR><B>Gesendet:</B> Montag, 23. November 2009 20:17<BR><B>An:</B>
rt-users@lists.bestpractical.com<BR><B>Betreff:</B> Re: [rt-users] Speeding up
CLI RT::Shredder<BR></FONT><BR></DIV>
<DIV></DIV>I noticed a typo in probably the most important line in my message.
The filename is actually:<BR><BR>/opt/rt3/lib/RT/Shredder/Record.pm<BR><BR>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. <BR><BR>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. <BR><BR>I hope others are able to confirm similar
experiences. Look forward to reading about it.<BR><BR>Max<BR><BR>Torsten Brumm
wrote:
<BLOCKQUOTE cite=mid:bdbd084b0911231110j5c5c6fe3s80962deb12720fe8@mail.gmail.com
type="cite">Oha, this sounds really useful. Any comment from ruslan if this is
save?<BR><BR>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!<BR><BR>Thanks for sharing this<BR><BR>Torsten<BR><BR>
<DIV class=gmail_quote>2009/11/23 Maxwell A. Rathbone <SPAN dir=ltr><<A
href="mailto:mrathbone@sagonet.com"
moz-do-not-send="true">mrathbone@sagonet.com</A>></SPAN><BR>
<BLOCKQUOTE class=gmail_quote
style="PADDING-LEFT: 1ex; MARGIN: 0pt 0pt 0pt 0.8ex; BORDER-LEFT: rgb(204,204,204) 1px solid">Hello,<BR><BR>I'm
in the same boat as many others I've seen post. We have 35k tickets<BR>in
one of our queues that I'm trying to shred(shame on us for not<BR>automating
this previously). I've found the web version of the Shredder<BR>to be
god-awful slow. We're talking 10min+ just to shred ONE ticket. So<BR>I
discovered the command-line /opt/rt3/sbin/rt-shredder utility. I was<BR>then
able to shred ONE ticket in about 5 minutes. I found some<BR>optimization
keys to add to the tables, which allowed me to them shred<BR>ONE ticket in
about a minute. I then discovered(this really should be in<BR>the
documentation!), that if you specify a timeframe with rt-shredder,<BR>you
can get MUCH faster processing. I was able to get it down to<BR>21seconds
for the shredding of ONE ticket.<BR><BR>I noticed it was spitting out
warning messages each time it deletes<BR>something. I honestly do not care
about the output as long as it is<BR>working as expected, so I hunted
through the code and was able to<BR>disable the on-screen logging
altogether. I'm now able to shred ONE<BR>ticket in about 8-10
seconds.<BR><BR>For those who are interested in about a 50% reduction in
processing time<BR>for the CLI Shredder, edit the
file:<BR>/opt/rt3/lib/RT/Shredder/Rercord.pm<BR><BR>Look for this
line:<BR> $RT::Logger->warning( $msg );<BR><BR>Comment it so
it looks like this:<BR># $RT::Logger->warning( $msg
);<BR><BR>a WORLD of difference from the 10 minutes per ticket I originally
was<BR>getting. Now it looks like to shred the 35k might actually take
a<BR>palatable amount of time.<BR><BR>I wanted to share this useful
information on the list so it is google<BR>searchable. I'm SURE others will
find this helpful.<BR><BR>BTW, the command I'm using to shred(again,
documentation is kinda poor) is:<BR>./rt-shredder --plugin
"Tickets=query,((status = 'deleted' OR status =<BR>'rejected')
AND<BR>LastUpdated='2008-10-03');limit,100;with_linked,FALSE;apply_query_to_linked,FALSE"<BR>--force<BR><BR>Max<BR>_______________________________________________<BR><A
href="http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users"
target=_blank
moz-do-not-send="true">http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users</A><BR><BR>Community
help: <A href="http://wiki.bestpractical.com" target=_blank
moz-do-not-send="true">http://wiki.bestpractical.com</A><BR>Commercial
support: <A href="mailto:sales@bestpractical.com"
moz-do-not-send="true">sales@bestpractical.com</A><BR><BR><BR>Discover RT's
hidden secrets with RT Essentials from O'Reilly Media.<BR>Buy a copy at <A
href="http://rtbook.bestpractical.com" target=_blank
moz-do-not-send="true">http://rtbook.bestpractical.com</A><BR></BLOCKQUOTE></DIV><BR><BR
clear=all><BR>-- <BR>MFG<BR><BR>Torsten Brumm<BR><BR><A
href="http://www.brumm.me"
moz-do-not-send="true">http://www.brumm.me</A><BR><A
href="http://www.elektrofeld.de"
moz-do-not-send="true">http://www.elektrofeld.de</A><BR></BLOCKQUOTE><BR></BODY></HTML>