[rt-users] RTx::Shredder performance improvement (by adding new index)

[drewday at cc.gatech.edu]

In the process of investigating the performance of using Shredder to delete tickets, I found an index that seems to have a considerable improvement on performance, even above the indexes that are suggested in the documentation. I simply added:

CREATE INDEX SHREDDER_CGM3 ON CachedGroupMembers(Val, Id);

Before, when I had no indexes, deleting 500 tickets (there are ~ 100,000 in our database), the process took about 2 hours and 30 minutes. After adding the suggested indexes (which I found in the CPAN documentation), the process took about 1 hour, 15 minutes. After adding the above index, the process took about 16 minutes. I stumbled upon this while using the tool mytop (available at http://jeremy.zawodny.com/mysql/mytop/).

I am pretty certain that, despite the occasionally self-referrential nature of the Val field in the CGM table, this index shouldn't lead to any difference in results, ordering, or completeness in deletion. I haven't had the time to check, though - so obviously I take no responsibility for any huge catastrophes that may occur on your particular database.

Anyway, thanks to Ruslan Zakirov for a great tool.

Cheers,
-Drew Day
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080807/cb379366/attachment.htm>