http://search.cpan.org/~ruz/RTx-Shredder-0.07/lib/RTx/Shredder.pm#Database_indexes
On Wed, Apr 2, 2008 at 5:02 AM, Alex Moura <[EMAIL PROTECTED]> wrote: > Hello, > > I'd like to ask the mysql experts for any tips that would help improving > rtx-shredder performance. > > There database are already has indexes in an attempt to improve the > performence: > > CREATE INDEX objectcf_type_id on ObjectCustomFieldValues(Objectid, > ObjectType); > CREATE INDEX acl_type_id on ACL(Objectid, ObjectType); > CREATE INDEX acl_princid on ACL(PrincipalId); > > Right now, the removal of only one ticket takes between 1:33min. and > 1:40min., in the lastest measurements. > > $ /usr/bin/time -h sudo ./rtx-shredder --plugin > 'Tickets=status,deleted;queue,general;limit,1' > Next objects would be deleted: > RT::Ticket-37203 object > Do you want to proceed? [y/N] y > 1m33,81s real 1,13s user 0,17s sys > ------------------ > > Last weekend, removing 2.000 tickets took two days: > > $ /usr/bin/time -h sudo ./rtx-shredder --plugin > 'Tickets=status,deleted;queue,general;limit,2000' > 2d1h9m24,94s real 11m35,93s user 47,71s sys > > > The strange part is that four months ago, removing 7.000 tickets too half of > this time in another mysql instance running in the very same hardware: > > # /usr/bin/time -h ./rtx-shredder --plugin > 'Tickets=status,deleted;queue,general;limit,7000' > 21h59m55,19s real 53m14,32s user 1m22,57s sys > > Relevant info: > O.S.: FreeBSD-6.2-RELEASE / RT Version: 3.6.5 (installed via FreeBSD ports) > / MySQL version: 5.0.51 / Mason: 1.35 / Apache: 1.3.37 (w/ mod_ssl-2.8.28) / > Hardware: Dell PowerEdge 2850, CPU: Intel Xeon 3GHz, RAM: 2GB > > ------------------------- > # mysql-duplicate-key-checker --databases rt3 > > DATABASE TABLE ENGINE OBJECT TYPE STRUCT PARENT COLUMNS > rt3 Attachments MyISAM Attachments1 KEY BTREE NULL > `Parent` > rt3 Attachments MyISAM Attachments3 KEY BTREE NULL > `Parent`,`TransactionId` > rt3 CachedGroupMembers MyISAM DisGrouMem KEY BTREE NULL > `GroupId`,`MemberId`,`Disabled` > rt3 CachedGroupMembers MyISAM GrouMem KEY BTREE NULL > `GroupId`,`MemberId` > rt3 ObjectCustomFieldValues MyISAM TicketCustomFieldValues1 KEY > BTREE NULL `CustomField`,`ObjectId`,`Content` > rt3 ObjectCustomFieldValues MyISAM TicketCustomFieldValues2 KEY > BTREE NULL `CustomField`,`ObjectId` > rt3 Tickets MyISAM PRIMARY KEY BTREE NULL `id` > rt3 Tickets MyISAM Tickets4 KEY BTREE NULL > `id`,`Status` > rt3 Tickets MyISAM Tickets5 KEY BTREE NULL > `id`,`EffectiveId` > rt3 Tickets MyISAM Tickets3 KEY BTREE NULL > `EffectiveId` > rt3 Tickets MyISAM Tickets6 KEY BTREE NULL > `EffectiveId`,`Type` > rt3 Users MyISAM PRIMARY KEY BTREE NULL `id` > rt3 Users MyISAM Users3 KEY BTREE NULL `id`,`EmailAddress` > rt3 Users MyISAM Users1 KEY BTREE NULL `Name` > rt3 Users MyISAM Users2 KEY BTREE NULL `Name` > ------------------------- > > The database still have more than 72.278 tickets to be removed. And I'd > appreciate any tips that can help improving the removal performance, so we > won't have to wait weeks before this cleanup ends. > > Thanks in advance, > > Alex > > _______________________________________________ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users > > Community help: http://wiki.bestpractical.com > Commercial support: [EMAIL PROTECTED] > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com > -- Best regards, Ruslan. _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
