Any way to speed up shredding?

RT Users
1

Hi,

I’m trying to get rid of old crud (deleted and rejected tickets, mostly
spam) in our RT-DB, and so use rt-shredder. Which seems to work fine,
but is so slow that my best estimate for it to finish wiping ~80k
tickets is “upwards of 6 months”:

time ( rt-shredder --force --plugin “Tickets=query,(status = ‘rejected’
OR status = ‘deleted’) AND id <610000;limit,10”)

real 9m33.764s
user 0m6.984s
sys 0m0.708s

Any hints on possibilities to speed this up?

(rt-shredder from RT 3.8 (Debian), incl. RT-IR 2.4, postgres backend)

Kind regards,
robert
// CERT Austria - Robert Waldner waldner@cert.at
// http://www.cert.at/ - T: +43 1 5056416 78
// Eine Initiative der nic.at GmbH
// http://www.nic.at/ - Firmenbuchnummer 172568b, LG Salzburg

2

Hi,

I’m trying to get rid of old crud (deleted and rejected tickets, mostly
spam) in our RT-DB, and so use rt-shredder. Which seems to work fine,
but is so slow that my best estimate for it to finish wiping ~80k
tickets is “upwards of 6 months”:

time ( rt-shredder --force --plugin “Tickets=query,(status = ‘rejected’
OR status = ‘deleted’) AND id <610000;limit,10”)

real 9m33.764s
user 0m6.984s
sys 0m0.708s

Any hints on possibilities to speed this up?

(rt-shredder from RT 3.8 (Debian), incl. RT-IR 2.4, postgres backend)

There are some database indexes suggested:
https://docs.bestpractical.com/rt/4.4.1/RT/Shredder.html#Database-Indexes

I use just some if them, es they improve shredding tickets enough for me:
CREATE INDEX SHREDDER_CGM3 ON CachedGroupMembers (Via, Id);
CREATE UNIQUE INDEX SHREDDER_GM1 ON GroupMembers(MemberId, GroupId);
CREATE INDEX SHREDDER_TXN3 ON Transactions(TYPE, OldValue);
CREATE INDEX SHREDDER_TXN4 ON Transactions(TYPE, NewValue);

Chris

3

Am 10.06.2016 um 12:02 schrieb Robert Waldner:

I’m trying to get rid of old crud (deleted and rejected tickets, mostly
spam) in our RT-DB, and so use rt-shredder. Which seems to work fine,
but is so slow that my best estimate for it to finish wiping ~80k
tickets is “upwards of 6 months”:

time ( rt-shredder --force --plugin “Tickets=query,(status = ‘rejected’
OR status = ‘deleted’) AND id <610000;limit,10”)

real 9m33.764s
user 0m6.984s
sys 0m0.708s

Any hints on possibilities to speed this up?

(rt-shredder from RT 3.8 (Debian), incl. RT-IR 2.4, postgres backend)

There are some database indexes suggested:
RT::Shredder - RT 4.4.1 Documentation - Best Practical

I use just some if them, es they improve shredding tickets enough for me:
CREATE INDEX SHREDDER_CGM3 ON CachedGroupMembers (Via, Id);
CREATE UNIQUE INDEX SHREDDER_GM1 ON GroupMembers(MemberId, GroupId);
CREATE INDEX SHREDDER_TXN3 ON Transactions(TYPE, OldValue);
CREATE INDEX SHREDDER_TXN4 ON Transactions(TYPE, NewValue);

Thanks for the tip - sadly, it doesn’t help (it’s still nearly 1
minute/ticket deleted).

Kind regards,
robert
// CERT Austria - Robert Waldner waldner@cert.at
// http://www.cert.at/ - T: +43 1 5056416 78
// Eine Initiative der nic.at GmbH
// http://www.nic.at/ - Firmenbuchnummer 172568b, LG Salzburg

signature.asc (819 Bytes)

4

I’ve used all of the suggested indexes and can confirm that shredding is far much faster than before.