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

[Jim Faulkner]

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!

Jim Faulkner