[rt-users] rtx-shredder mysql optimization

Alex Moura alexsm at gmail.com
Tue Apr 1 21:02:15 EDT 2008


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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20080401/d162237c/attachment.htm>


More information about the rt-users mailing list