[rt-users] RTx-shredder with a very large database

[Todd Chapman]

On Tue, Feb 28, 2006 at 06:46:38PM -0500, Jim Faulkner wrote:
> 
> On Wed, 1 Mar 2006, Luke Vanderfluit wrote:
> 
> > I'm afraid I have a similar story, no spam queue (spam is filtered 
> > before it reaches RT), but large amounts of redundant data.
> > I have a similar size RT installation.
> > I've installed rtx-shredder and tried that.
> > See my earlier post of 27Feb2006.
> > 
> > I thought that doing the deletes to mysql directly (that is, not using 
> > the RT api, might be faster) but I'm afraid that isn't necessarily so.
> > 
> > I have created a script that does this and it shows considerably long 
> > processing times.
> > 
> > What needs to be done  to delete a ticket is this:
> > 
> > /~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > select * from Tickets where id = <ticketID>
> > <1>select id from Transactions where ObjectId = <ticketID>;
> > <4>select id from ObjectCustomFieldValues where ObjectId = <ticketID>;
> > <3>select id from Links where LocalTarget = <ticketID>;
> > <2>select id from Attachments where TransactionId = ( for each 
> > transactionid retrieved with <1> )
> > 
> > -delete from Attachments where id in ( for each Attachment retrieved 
> > with <2> )
> > -delete from Links where id = ( for each link in <3> )
> > -delete from ObjectCustomFieldValues where id in ( for each 
> > ObjectCustomFieldValue in <4> )
> > -delete from Transactions where id in ( for each Transaction in <1> )
> > -delete from Tickets where id = <ticketID>;
> > 
> > Additionally Stuff needs to be deleted from
> > principals, cachedgroupmembers, groupmembers, groups.
> > 
> > For each ticket:
> > -delete from principals where
> >     id=groups.id and groups.instance=tickets.id as int
> > -delete from cachedgroupmembers where groupid=groups.id
> >     and groups.instance=tickets.id
> > -delete from groupmembers where groupid=groups.id and
> >     groups.instance=tickets.id
> > -delete from groups where instance=tickets.id
> > \____________________________________________
> > 
> > There may be more that could be deleted, namely Users(requestors) who 
> > created the ticket etc.
> > 
> > I'm toying with the idea of a script that runs each night and cleans out 
> > 1000 tickets or so, I have 90000 tickets to delete in first instance, 
> > after that a regular cleanup of resolved tickets.
> > 
> > Hope this contributes.
> > 
> > Kind regards.
> > Luke.
> 
> That helps a lot!  I'm in the midst of writing my own tool to clean out 
> the database, and I was just trying to figure out that exact information.  
> Thank you very much!
> 

Why? When your done you will have RTx::Shredder, except not
as well tested and likely to break on some future version of RT.

-Todd