[rt-users] rtx-shredder mysql optimization

[Ruslan Zakirov]

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 <alexsm at gmail.com> 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: sales at bestpractical.com
>
>
>  Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>  Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.