Speeding up rt-shredder

ronald.higgins · July 27, 2010, 12:27pm

Hi all.

I’m trying to use rt-shredder to delete 700K tickets out of the RT
Database but at the moment it’s moving incredibly slowly.
1 ticket every few minutes is being deleted.

Is there anyway to speed up rt-shredder deletes ?

Command being issued: rt-shredder --plugin “Tickets=query,Subject like
’%UNDELIVERABLE%’;limit,1000” --sqldump
/tmp/shredder-restore-tickets.sql

RT Version 3.8.7 , MySQL DB Ver 5.0.77

Regards

Ronald

G.Booth · July 27, 2010, 1:30pm

Hi Ronald

You need to add some more indexes to a few of the tables.

this thread explains all. Think we got to a point where we could shred one
user/ticket every 3-6 secs or so

http://lists.fsck.com/pipermail/rt-users/2009-November/062395.html

hope it helps

regards
Garry

Dr Garry Booth
IT Services
Loughborough University

ronald.higgins · July 27, 2010, 2:45pm

Thanks for the link, it’s helped, but hoping for more gains.

I’ve applied the indexes as described and edited the Record.pm and
it’s knocked the deletes
down to 40 seconds per ticket. Which translates to about 115 200
records per day or a week solid
to delete my required 700 000 tickets.

Any other other ideas ?

RonaldOn Tue, Jul 27, 2010 at 3:30 PM, G.Booth G.Booth@lboro.ac.uk wrote:

Hi Ronald

You need to add some more indexes to a few of the tables.

this thread explains all. Think we got to a point where we could shred one
user/ticket every 3-6 secs or so

http://lists.fsck.com/pipermail/rt-users/2009-November/062395.html

hope it helps

regards
Garry

–

Dr Garry Booth
IT Services
Loughborough University

Maxwell_Rathbone · July 27, 2010, 4:00pm

What command are you running to run shredder?

For me, the best way to speed it up, was to ensure that I was specifying
a date range, and to break it down into chunks(like one command per month)

MaxOn 7/27/2010 10:45 AM, ronald higgins wrote:

Thanks for the link, it’s helped, but hoping for more gains.

I’ve applied the indexes as described and edited the Record.pm and
it’s knocked the deletes
down to 40 seconds per ticket. Which translates to about 115 200
records per day or a week solid
to delete my required 700 000 tickets.

Any other other ideas ?

Ronald

On Tue, Jul 27, 2010 at 3:30 PM, G.BoothG.Booth@lboro.ac.uk wrote:

Hi Ronald

You need to add some more indexes to a few of the tables.

this thread explains all. Think we got to a point where we could shred one
user/ticket every 3-6 secs or so

http://lists.fsck.com/pipermail/rt-users/2009-November/062395.html

hope it helps

regards
Garry

–

Dr Garry Booth
IT Services
Loughborough University

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

ronald.higgins · July 27, 2010, 5:04pm

Thanks for the response Maxwell,

So i’ve added in a date, but alas, still around the 40 second mark.

rt-shredder --force --plugin “Tickets=query,Subject like ‘%VIAGRA%’
and LastUpdated >= ‘2010-07-01 07:00:00’ AND LastUpdated <=
‘2010-07-26 07:00:00’;limit,2” --sqldump
/tmp/shredder-restore-tickets.sql

SQL dump file is ‘/tmp/shredder-restore-tickets.sql’

[Tue Jul 27 17:00:49 2010] [info]: RT::CachedGroupMember-9874009 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:49 2010] [info]: RT::Transaction-13303884 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:49 2010] [info]: RT::Group-6080735 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:49 2010] [info]: RT::Principal-6080735 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:56 2010] [info]: RT::CachedGroupMember-9874011 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:56 2010] [info]: RT::GroupMember-3867641 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:02 2010] [info]: RT::CachedGroupMember-9874006 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:02 2010] [info]: RT::Transaction-13303881 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:02 2010] [info]: RT::Group-6080732 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:03 2010] [info]: RT::Principal-6080732 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:09 2010] [info]: RT::CachedGroupMember-9878143 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:09 2010] [info]: RT::GroupMember-3869201 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:16 2010] [info]: RT::CachedGroupMember-9874007 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:16 2010] [info]: RT::Transaction-13303882 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:16 2010] [info]: RT::Group-6080733 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:16 2010] [info]: RT::Principal-6080733 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:23 2010] [info]: RT::CachedGroupMember-9874008 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:23 2010] [info]: RT::Transaction-13303883 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:23 2010] [info]: RT::Group-6080734 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:24 2010] [info]: RT::Principal-6080734 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:24 2010] [info]: RT::Attachment-7922989 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:24 2010] [info]: RT::Transaction-13303885 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:25 2010] [info]: RT::Attachment-7922996 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:25 2010] [info]: RT::Transaction-13303897 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:25 2010] [info]: RT::Transaction-13308538 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:25 2010] [info]: RT::Transaction-13308539 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:26 2010] [info]: RT::Attachment-7925504 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:26 2010] [info]: RT::Transaction-13308542 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:26 2010] [info]: RT::Transaction-13309288 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:26 2010] [info]: RT::Ticket-1603953 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:32 2010] [info]: RT::CachedGroupMember-9874027 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:33 2010] [info]: RT::Transaction-13303900 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:33 2010] [info]: RT::Group-6080747 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:33 2010] [info]: RT::Principal-6080747 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:40 2010] [info]: RT::CachedGroupMember-9874029 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:40 2010] [info]: RT::GroupMember-3867647 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:46 2010] [info]: RT::CachedGroupMember-9874024 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:46 2010] [info]: RT::Transaction-13303896 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:46 2010] [info]: RT::Group-6080744 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:46 2010] [info]: RT::Principal-6080744 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:53 2010] [info]: RT::CachedGroupMember-9874028 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:53 2010] [info]: RT::GroupMember-3867646 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:59 2010] [info]: RT::CachedGroupMember-9874025 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:59 2010] [info]: RT::Transaction-13303898 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:59 2010] [info]: RT::Group-6080745 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:00 2010] [info]: RT::Principal-6080745 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:06 2010] [info]: RT::CachedGroupMember-9874026 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:06 2010] [info]: RT::Transaction-13303899 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:06 2010] [info]: RT::Group-6080746 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Principal-6080746 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Attachment-7922997 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Transaction-13303901 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Attachment-7923000 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Transaction-13303904 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:08 2010] [info]: RT::Transaction-13313957 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:08 2010] [info]: RT::Ticket-1603956 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)

Guess i might just have to accept this this will be a lengthy process.On Tue, Jul 27, 2010 at 6:00 PM, Maxwell Rathbone mrathbone@sagonet.com wrote:

What command are you running to run shredder?

For me, the best way to speed it up, was to ensure that I was specifying a
date range, and to break it down into chunks(like one command per month)

Max

On 7/27/2010 10:45 AM, ronald higgins wrote:

Thanks for the link, it’s helped, but hoping for more gains.

I’ve applied the indexes as described and edited the Record.pm and
it’s knocked the deletes
down to 40 seconds per ticket. Which translates to about 115 200
records per day or a week solid
to delete my required 700 000 tickets.

Any other other ideas ?

Ronald

On Tue, Jul 27, 2010 at 3:30 PM, G.BoothG.Booth@lboro.ac.uk wrote:

Hi Ronald

You need to add some more indexes to a few of the tables.

this thread explains all. Think we got to a point where we could shred
one
user/ticket every 3-6 secs or so

http://lists.fsck.com/pipermail/rt-users/2009-November/062395.html

hope it helps

regards
Garry

–

Dr Garry Booth
IT Services
Loughborough University

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Joseph_Spenner · July 27, 2010, 5:35pm

Upon nearly completing my RT installation, and running:

make initialize-database

I got the message:

[Tue Jul 27 17:12:29 2010] [error]: The RTAddressRegexp option is not set in the config. Not setting this option results in additional SQL queries to check whether each address belongs to RT or not. It is especially important to set this option if RT recieves emails on addresses that are not in the database or config. (/home/packages/rt-3.8.8/sbin/…/lib/RT/Config.pm:343)
Now inserting data
Done inserting data
Done.
If I have 3 queues, ie:
support-help@bob.domain.com
sales-help@bob.domain.com
it-requests@bob.domain.com
Do I need to list all those addresses (and any future addresses) in that RTAddressRegexp option ? Or is this only if I have something at (ie:) help@jack.somewhere.com forwarding to my RT system in which case I’d want to add: help@jack.somewhere.com to the RTAddressRegexp option ?

G.Booth · July 27, 2010, 5:51pm

Thanks for the response Maxwell,

So i’ve added in a date, but alas, still around the 40
second mark.

rt-shredder --force --plugin “Tickets=query,Subject like
‘%VIAGRA%’
and LastUpdated >= ‘2010-07-01 07:00:00’ AND LastUpdated
<=
‘2010-07-26 07:00:00’;limit,2” --sqldump
/tmp/shredder-restore-tickets.sql

SQL dump file is ‘/tmp/shredder-restore-tickets.sql’

Hi Ronald

We do all of our ticket shredding from one queue with the attached script.
It might be worth running a few tests with it, to see if its the shredder
query causing some overheads. You’ll need to change the queue name from
SPAM-Shredder to whatever you use for testing and stop it checking for
whether the ticket is older than 7 days. Runs from /opt/rt3/local/bin

Hope its of some help. Am away from the list for 2 weeks from tomorrow, so
good luck.

regards
Garry

Dr Garry Booth
IT Services
Loughborough University

shredder.rtf (1.55 KB)

Maxwell_Rathbone · July 27, 2010, 5:55pm

Did you try turning off logging either through the raising of the
LogWarn level, or through my suggested modification? I was able to see
quite a significant difference in simply turning off output from the
script. It is an inconvenience, but I’m willing to accept that for the
speed difference.

Also, in general, you are doing an intensive query in your rt-shredder
command. You may consider breaking this into two separate actions.
First, marking all of your Subject LIKE ‘%VIAGRA%’ tickets as deleted or
rejected… and then use rt-shredder to delete the deleted or rejected
tickets. It’s going to be less intensive on your database server to do
a Status = ‘Rejected’ OR Status = ‘Deleted’ than to do a Subject LIKE
‘%VIAGRA%’.

Personally, I just have my Abuse techs mass-reject the spam we get…
Then me, as the admin, just regularly run rt-shredder set to shred
deleted or rejected tickets. This has allowed us to retain the speed
using RT & RT:IR. After two years of utilizing RT, I only had this
realization after I made the post you were linked to.

Hope this helps!

MaxOn 7/27/2010 1:04 PM, ronald higgins wrote:

Thanks for the response Maxwell,

So i’ve added in a date, but alas, still around the 40 second mark.

rt-shredder --force --plugin “Tickets=query,Subject like ‘%VIAGRA%’
and LastUpdated>= ‘2010-07-01 07:00:00’ AND LastUpdated<=
‘2010-07-26 07:00:00’;limit,2” --sqldump
/tmp/shredder-restore-tickets.sql

SQL dump file is ‘/tmp/shredder-restore-tickets.sql’

[Tue Jul 27 17:00:49 2010] [info]: RT::CachedGroupMember-9874009 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:49 2010] [info]: RT::Transaction-13303884 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:49 2010] [info]: RT::Group-6080735 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:49 2010] [info]: RT::Principal-6080735 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:56 2010] [info]: RT::CachedGroupMember-9874011 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:00:56 2010] [info]: RT::GroupMember-3867641 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:02 2010] [info]: RT::CachedGroupMember-9874006 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:02 2010] [info]: RT::Transaction-13303881 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:02 2010] [info]: RT::Group-6080732 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:03 2010] [info]: RT::Principal-6080732 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:09 2010] [info]: RT::CachedGroupMember-9878143 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:09 2010] [info]: RT::GroupMember-3869201 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:16 2010] [info]: RT::CachedGroupMember-9874007 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:16 2010] [info]: RT::Transaction-13303882 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:16 2010] [info]: RT::Group-6080733 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:16 2010] [info]: RT::Principal-6080733 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:23 2010] [info]: RT::CachedGroupMember-9874008 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:23 2010] [info]: RT::Transaction-13303883 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:23 2010] [info]: RT::Group-6080734 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:24 2010] [info]: RT::Principal-6080734 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:24 2010] [info]: RT::Attachment-7922989 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:24 2010] [info]: RT::Transaction-13303885 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:25 2010] [info]: RT::Attachment-7922996 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:25 2010] [info]: RT::Transaction-13303897 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:25 2010] [info]: RT::Transaction-13308538 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:25 2010] [info]: RT::Transaction-13308539 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:26 2010] [info]: RT::Attachment-7925504 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:26 2010] [info]: RT::Transaction-13308542 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:26 2010] [info]: RT::Transaction-13309288 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:26 2010] [info]: RT::Ticket-1603953 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:32 2010] [info]: RT::CachedGroupMember-9874027 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:33 2010] [info]: RT::Transaction-13303900 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:33 2010] [info]: RT::Group-6080747 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:33 2010] [info]: RT::Principal-6080747 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:40 2010] [info]: RT::CachedGroupMember-9874029 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:40 2010] [info]: RT::GroupMember-3867647 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:46 2010] [info]: RT::CachedGroupMember-9874024 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:46 2010] [info]: RT::Transaction-13303896 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:46 2010] [info]: RT::Group-6080744 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:46 2010] [info]: RT::Principal-6080744 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:53 2010] [info]: RT::CachedGroupMember-9874028 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:53 2010] [info]: RT::GroupMember-3867646 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:59 2010] [info]: RT::CachedGroupMember-9874025 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:59 2010] [info]: RT::Transaction-13303898 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:01:59 2010] [info]: RT::Group-6080745 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:00 2010] [info]: RT::Principal-6080745 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:06 2010] [info]: RT::CachedGroupMember-9874026 wiped
out (/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:06 2010] [info]: RT::Transaction-13303899 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:06 2010] [info]: RT::Group-6080746 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Principal-6080746 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Attachment-7922997 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Transaction-13303901 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Attachment-7923000 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:07 2010] [info]: RT::Transaction-13303904 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:08 2010] [info]: RT::Transaction-13313957 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)
[Tue Jul 27 17:02:08 2010] [info]: RT::Ticket-1603956 wiped out
(/root/rt-3.8.7/sbin/…/lib/RT/Shredder/Record.pm:236)

Guess i might just have to accept this this will be a lengthy process.

On Tue, Jul 27, 2010 at 6:00 PM, Maxwell Rathbonemrathbone@sagonet.com wrote:

What command are you running to run shredder?

For me, the best way to speed it up, was to ensure that I was specifying a
date range, and to break it down into chunks(like one command per month)

Max

On 7/27/2010 10:45 AM, ronald higgins wrote:

Thanks for the link, it’s helped, but hoping for more gains.

I’ve applied the indexes as described and edited the Record.pm and
it’s knocked the deletes
down to 40 seconds per ticket. Which translates to about 115 200
records per day or a week solid
to delete my required 700 000 tickets.

Any other other ideas ?

Ronald

On Tue, Jul 27, 2010 at 3:30 PM, G.BoothG.Booth@lboro.ac.uk wrote:

Hi Ronald

You need to add some more indexes to a few of the tables.

this thread explains all. Think we got to a point where we could shred
one
user/ticket every 3-6 secs or so

http://lists.fsck.com/pipermail/rt-users/2009-November/062395.html

hope it helps

regards
Garry

–

Dr Garry Booth
IT Services
Loughborough University

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Maxwell_Rathbone · July 27, 2010, 5:58pm

Cool! Thanks for posting this. I’ll run some benchmarks against what
I’m using now to see if this is any faster.

MaxOn 7/27/2010 1:51 PM, G.Booth wrote:

On Tue, 27 Jul 2010 19:04:35 +0200 ronald higginsronald.higgins@gmail.com wrote:

Thanks for the response Maxwell,

So i’ve added in a date, but alas, still around the 40
second mark.

rt-shredder --force --plugin “Tickets=query,Subject like
‘%VIAGRA%’
and LastUpdated>= ‘2010-07-01 07:00:00’ AND LastUpdated
<=
‘2010-07-26 07:00:00’;limit,2” --sqldump
/tmp/shredder-restore-tickets.sql

SQL dump file is ‘/tmp/shredder-restore-tickets.sql’
Hi Ronald

We do all of our ticket shredding from one queue with the attached script.
It might be worth running a few tests with it, to see if its the shredder
query causing some overheads. You’ll need to change the queue name from
SPAM-Shredder to whatever you use for testing and stop it checking for
whether the ticket is older than 7 days. Runs from /opt/rt3/local/bin

Hope its of some help. Am away from the list for 2 weeks from tomorrow, so
good luck.

regards
Garry

–

Dr Garry Booth
IT Services
Loughborough University

Mike_Johnson1 · July 27, 2010, 5:56pm

You need to include both, the queue email addresses, AND anything that
forwards email to RT.

That setting prevents RT from sending emails that will “loop” infinitely in
your system.

For example.

RT is setup with the basic autoreply, and reply on correspondence etc.

RT has 2 queues, support@here.com goes to general, and it@here.com goes to
IT queue.

If it@here.com emails support@here.com the general queue will autoreply to
it@here.com which will create a ticket and autoreply to
support@here.comwhich will create a ticket and auto-reply to
it@here.com etc etc etc…

Big loop, never ending, blow up RT

If you set the regular expression to support@here.com when RT emails out,
it’ll filter any emails going to support@here.com. This will ensure no loop
happens.

SO to recap, RTAddressRegexp has to be a regular expression that ALL email
addresses that send stuff to RT will validate through.

Hope this helps!
Mike.On Tue, Jul 27, 2010 at 1:35 PM, Joseph Spenner joseph85750@yahoo.comwrote:

Upon nearly completing my RT installation, and running:

make initialize-database

I got the message:

==
[Tue Jul 27 17:12:29 2010] [error]: The RTAddressRegexp option is not set
in the config. Not setting this option results in additional SQL queries to
check whether each address belongs to RT or not. It is especially important
to set this option if RT recieves emails on addresses that are not in the
database or config. (/home/packages/rt-3.8.8/sbin/…/lib/RT/Config.pm:343)
Now inserting data
Done inserting data
Done.

If I have 3 queues, ie:
support-help@bob.domain.com
sales-help@bob.domain.com
it-requests@bob.domain.com
Do I need to list all those addresses (and any future addresses) in that
RTAddressRegexp option ? Or is this only if I have something at (ie:)
help@jack.somewhere.com forwarding to my RT system in which case I’d want
to add: help@jack.somewhere.com to the RTAddressRegexp option ?

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Mike Johnson
Datatel Programmer/Analyst
Northern Ontario School of Medicine
955 Oliver Road
Thunder Bay, ON P7B 5E1
Phone: (807) 766-7331
Email: mike.johnson@nosm.ca

G.Booth · July 27, 2010, 6:18pm

Cool! Thanks for posting this. I’ll run some benchmarks
against what
I’m using now to see if this is any faster.

Max

Hi Max

One note for you and ronald, the script doesnt do the sql dump, so there’s
no way to get your stuff back apart from backup. Saves time, but loses a
safety feature (hence the 7 day delay). Good luck with your tests

Garry

ronald.higgins · July 27, 2010, 6:33pm

Thanks for the script Garry

Unfortunately it’s pretty much the same, every delete taking about 40
seconds, so it doesnt look like adding the SQL logging in is adding
any overhead pitty.On Tue, Jul 27, 2010 at 8:18 PM, G.Booth G.Booth@lboro.ac.uk wrote:

On Tue, 27 Jul 2010 13:58:25 -0400 Maxwell Rathbone mrathbone@sagonet.com wrote:

Cool! Thanks for posting this. I’ll run some benchmarks against what I’m
using now to see if this is any faster.

Max

Hi Max

One note for you and ronald, the script doesnt do the sql dump, so there’s
no way to get your stuff back apart from backup. Saves time, but loses a
safety feature (hence the 7 day delay). Good luck with your tests

Garry

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

G.Booth · July 27, 2010, 7:20pm

Thanks for the script Garry

Unfortunately it’s pretty much the same, every delete
taking about 40
seconds, so it doesnt look like adding the SQL logging
in is adding
any overhead pitty.

OK, so that rules out overhead on the shredder. How big is your database and
how many tickets are in your system? Im happy to benchmark what the script
does on my RT compared to yours and see where we go.
I can send you my pride and joy, which removes users that send spam, this
combined with removing tickets should shrink your DB and may make life
easier - hopefully you’ll have a slow, one off, hit and then a manageable
shred!
You might also want to look at dealing with v large attachments.

Off on hols now. Back 11th Aug - drop me a line if you dont get anywhere
g.booth@lboro.ac.uk.

Ditto the above, Max

regards
Garry

ronald.higgins · July 27, 2010, 7:28pm

Hi Garry,

The RT DB is currently 215GB in size, 1.5 Million Tickets (Attachments
table has 7 million entries).
Yes, this badboy is what I inherited with the new job. I’ve already
built another MySQL DB with the tables partitioned intended for
eventual cut over
but the last few days have seen the current prod db taking a hit
performance wise which is when i really started digging through the
tickets looking for a stop gap until we can cut over, and i’m sure
cutting the Tickets down by 700 000 will make a difference.

By all means, send it All tools of the trade are always gladly received :)On Tue, Jul 27, 2010 at 9:20 PM, G.Booth G.Booth@lboro.ac.uk wrote:

On Tue, 27 Jul 2010 20:33:03 +0200 ronald higgins ronald.higgins@gmail.com wrote:

Thanks for the script Garry

Unfortunately it’s pretty much the same, every delete taking about 40
seconds, so it doesnt look like adding the SQL logging in is adding
any overhead pitty.

OK, so that rules out overhead on the shredder. How big is your database and
how many tickets are in your system? Im happy to benchmark what the script
does on my RT compared to yours and see where we go.
I can send you my pride and joy, which removes users that send spam, this
combined with removing tickets should shrink your DB and may make life
easier - hopefully you’ll have a slow, one off, hit and then a manageable
shred!
You might also want to look at dealing with v large attachments.

Off on hols now. Back 11th Aug - drop me a line if you dont get anywhere
g.booth@lboro.ac.uk.

Ditto the above, Max

regards
Garry

G.Booth · July 27, 2010, 7:54pm

Hi Garry,

The RT DB is currently 215GB in size, 1.5 Million
Tickets (Attachments
table has 7 million entries).
Yes, this badboy is what I inherited with the new job.
I’ve already
built another MySQL DB with the tables partitioned
intended for
eventual cut over
but the last few days have seen the current prod db
taking a hit
performance wise which is when i really started digging
through the
tickets looking for a stop gap until we can cut over,
and i’m sure
cutting the Tickets down by 700 000 will make a
difference.

By all means, send it All tools of the trade are
always gladly received

OMFG!
Mine is 4GB, 150000 tickets, so it looks like you’ve got some big
attachments!

Think this will be painful!
will send script with explanation when I get back.
I’d seriously look at the attachments table as my RT is 10% of yours in
tickets, but only 2% the size.

Can you zap attachments above 1Gb?

regards
Garry

Asif_Iqbal2 · July 27, 2010, 8:08pm

Hi Garry,

The RT DB is currently 215GB in size, 1.5 Million Tickets (Attachments
table has 7 million entries).
Yes, this badboy is what I inherited with the new job. I’ve already
built another MySQL DB with the tables partitioned intended for
eventual cut over
but the last few days have seen the current prod db taking a hit
performance wise which is when i really started digging through the
tickets looking for a stop gap until we can cut over, and i’m sure
cutting the Tickets down by 700 000 will make a difference.

By all means, send it All tools of the trade are always gladly received

OMFG!
Mine is 4GB, 150000 tickets, so it looks like you’ve got some big
attachments!

Think this will be painful!
will send script with explanation when I get back.
I’d seriously look at the attachments table as my RT is 10% of yours in
tickets, but only 2% the size.

Can you zap attachments above 1Gb?

would be nice if the attachments could be converted to link.
we have a ftp server in same network that has lots of space.

Anyone has done anything like this?

If you reply please change the subject too.

regards
Garry

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com

Asif Iqbal
PGP Key: 0xE62693C5 KeyServer: pgp.mit.edu
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?

G.Booth · July 27, 2010, 8:16pm

Hi Ronald
what do you use as the mail server for your rt

we have exim and can set max packet allowed to whatever. Id have a quick
check if you’re allowing anything over stupid - stomp on it

Doesnt solve your current problem, but may fix it in future

regards
Garry

ronald.higgins · July 27, 2010, 8:28pm

OMFG indeed

I’m use to working with much smaller DB’s, this has been a challenge
to say the least.

We’re using sendmail for RT, but RT itself is sitting behind
MailMarshall & Exchange and
i believe the MS Admins have set a max message of 10MB so should be good.On Tue, Jul 27, 2010 at 10:16 PM, G.Booth G.Booth@lboro.ac.uk wrote:

Hi Ronald
what do you use as the mail server for your rt

we have exim and can set max packet allowed to whatever. Id have a quick
check if you’re allowing anything over stupid - stomp on it

Doesnt solve your current problem, but may fix it in future

regards
Garry

Joseph_Spenner · July 27, 2010, 8:29pm

You need to include both, the queue email addresses, AND anything that forwards email to RT.

That setting prevents RT from sending emails that will “loop” infinitely in your system.

For example.

RT is setup with the basic autoreply, and reply on correspondence etc.

RT has 2 queues, support@here.com goes to general, and it@here.com goes to IT queue.

If it@here.com emails support@here.com the general queue will autoreply to it@here.com which will create a ticket and autoreply to support@here.com which will create a ticket and auto-reply to it@here.com etc etc etc…

Big loop, never ending, blow up RT

If you set the regular expression to support@here.com when RT emails out, it’ll filter any emails going to support@here.com. This will ensure no loop happens.

SO to recap, RTAddressRegexp has to be a regular expression that ALL email addresses that send stuff to RT will validate through.

Hope this helps!
Mike.On Tue, Jul 27, 2010 at 1:35 PM, Joseph Spenner joseph85750@yahoo.com wrote:

Upon nearly completing my RT installation, and running:

make initialize-database

I got the message:

[Tue Jul 27 17:12:29 2010] [error]: The RTAddressRegexp option is not set in the config. Not setting this option results in additional SQL queries to check whether each address belongs to RT or not. It is especially important to set this option if RT recieves emails on addresses that are not in the database or config. (/home/packages/rt-3.8.8/sbin/…/lib/RT/Config.pm:343)

Now inserting data
Done inserting data
Done.
If I have 3 queues, ie:
support-help@bob.domain.com
sales-help@bob.domain.com

it-requests@bob.domain.com
Do I need to list all those addresses (and any future addresses) in that RTAddressRegexp option ? Or is this only if I have something at (ie:) help@jack.somewhere.com forwarding to my RT system in which case I’d want to add: help@jack.somewhere.com to the RTAddressRegexp option ?

So this ‘loop’ should only occur if:

auto respond/reply is enabled for the queue defined in the scrips
somehow, an RT queue address (with auto reply enabled) somehow gets included into another queues ticket

?

Is this potential something new? I’ve been using RT2 since about 2001 and never seen this happen. Or is it just a safeguard?

Maxwell_Rathbone · July 27, 2010, 8:33pm

I’d love the ability to export the attachments and store them on the
filesystem instead of in the database. Would certainly cut down the
database size!

I did find this, if it helps anyone:

github.com

bestpractical/rt-extension-utils/blob/master/sbin/rt-extract-attachment

#!/usr/bin/env perl

use 5.008003;
use strict;
use warnings;

### after: use lib qw(@RT_LIB_PATH@);
use lib qw(/opt/rt3/local/lib /opt/rt3/lib);

use RT;
RT::LoadConfig();
RT::Init();

my $aid = shift or die "Usage: $0 <attachment id>";

my $dbh = $RT::Handle->dbh;
my ($filename, $content) = $dbh->selectrow_array(
    "SELECT Filename, Content FROM Attachments WHERE id = ?", undef, $aid
);
unless ( defined $content && length $content ) {

This file has been truncated. show original

This allows the extraction or exporting of the attachments. However that
still doesn’t solve the problem of the attachment being in the database,
or how to reference it from the ticket.

I read on this mailing list on 4/29, regarding “Attachments on disk?”:
Hi Thierry,
there is a addon bps wrote but it is not available public at the moment.
Hopefully Jesse will push it out some day.

Torsten

So as it stands, it appears there still is no method of doing this. So
if anyone succeeds in writing a patch, please share it!

MaxOn 7/27/2010 4:08 PM, Asif Iqbal wrote:

On Tue, Jul 27, 2010 at 3:54 PM, G.BoothG.Booth@lboro.ac.uk wrote:

On Tue, 27 Jul 2010 21:28:37 +0200 ronald higginsronald.higgins@gmail.com wrote:

Hi Garry,

The RT DB is currently 215GB in size, 1.5 Million Tickets (Attachments
table has 7 million entries).
Yes, this badboy is what I inherited with the new job. I’ve already
built another MySQL DB with the tables partitioned intended for
eventual cut over
but the last few days have seen the current prod db taking a hit
performance wise which is when i really started digging through the
tickets looking for a stop gap until we can cut over, and i’m sure
cutting the Tickets down by 700 000 will make a difference.

By all means, send it All tools of the trade are always gladly received

OMFG!
Mine is 4GB, 150000 tickets, so it looks like you’ve got some big
attachments!

Think this will be painful!
will send script with explanation when I get back.
I’d seriously look at the attachments table as my RT is 10% of yours in
tickets, but only 2% the size.

Can you zap attachments above 1Gb?
would be nice if the attachments could be converted to link.
we have a ftp server in same network that has lots of space.

Anyone has done anything like this?

If you reply please change the subject too.

regards
Garry

Discover RT’s hidden secrets with RT Essentials from O’Reilly Media.
Buy a copy at http://rtbook.bestpractical.com