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

[Luke Vanderfluit]

Todd Chapman wrote:

>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.
>  
>
Not sure whether web-interface invoked actions and RT API processed 
requests will be equal in performance to straigh SQL from perl script.
Would have to test both to give objective reply.
I have tried both API and direct sql, both _are_ slow.

BTW.
The stuff that I put in my script comes from the WIKI.
http://wiki.bestpractical.com/index.cgi?DatabaseAdmin

Kind regards.
Luke

>-Todd
>  
>


-- 
Luke

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20060301/76ac2db5/attachment.htm>