Speeding up CLI RT::Shredder

Maxwell_Rathbone · November 23, 2009, 6:19pm

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

Torsten_Brumm1 · November 23, 2009, 7:10pm

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

Torsten2009/11/23 Maxwell A. Rathbone mrathbone@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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

Maxwell_Rathbone · November 23, 2009, 7:16pm

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:

Torsten_Brumm1 · November 24, 2009, 11:48am

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

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

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@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

Ticket shreddered after removed logger entry in Record.pm

[root@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

Torsten2009/11/23 Maxwell A. Rathbone mrathbone@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@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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

Kevin_Falcone · November 24, 2009, 5:21pm

Look for this line:
$RT::Logger->warning( $msg );

Comment it so it looks like this:

$RT::Logger->warning( $msg );

If you raise your LogLevel above warning (something like error) this
won’t trigger and you can avoid editing the source.

-kevin

Torsten_Brumm · November 27, 2009, 9:30am

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:

I created a new setup inside RT with rt-filler scrip (created 5000 Queues, 50.000 Users and 500.000 Tickets with simple content)
Delete Tickets with default Shredder (./rt-shredder --plugin ‘Tickets=query,Status=“new”;limit,10"’ --force)

real 1m10.415s
user 0m11.384s
sys 0m0.735s

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

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