Rtx-shredder mysql optimization

RT Users
1

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

2

Another bit of information about the issue that may help is the following
"top" display, during a rtx-shredder running and after it finished:

The next “top” output was captured with a running rtx-shredder (removing a
single ticket):
last pid: 51170; load averages: 0.80, 0.54, 0.38 up 20+21:41:21
22:20:35
69 processes: 2 running, 67 sleeping
CPU states: 28.5% user, 0.0% nice, 2.0% system, 0.0% interrupt, 69.6%idle
Mem: 1796M Active, 782M Inact, 262M Wired, 149M Cache, 112M Buf, 522M Free
Swap: 4070M Total, 1100K Used, 4069M Free

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
48990 mysql 10 20 0 447M 60432K kserel 0 4:18 93.60% mysqld
2245 www 1 4 0 163M 156M accept 2 30:08 0.00% perl
2131 www 1 4 0 185M 170M accept 0 28:34 0.00% perl
2174 www 1 4 0 180M 169M accept 2 28:30 0.00% perl
2058 www 1 4 0 174M 163M accept 0 27:23 0.00% perl

The next ‘top’ output was captured two minutes after rtx-shredder finished
removing a single ticket:

last pid: 51575; load averages: 0.19, 0.41, 0.35 up 20+21:43:19
22:22:33
61 processes: 1 running, 60 sleeping
CPU states: % user, % nice, % system, % interrupt, %
idle
Mem: 1769M Active, 783M Inact, 260M Wired, 149M Cache, 112M Buf, 550M Free
Swap: 4070M Total, 1096K Used, 4069M Free

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
2245 www 1 4 0 163M 156M accept 2 30:08 0.00% perl
2131 www 1 4 0 185M 170M accept 0 28:34 0.00% perl
2174 www 1 4 0 180M 169M accept 2 28:30 0.00% perl
2058 www 1 4 0 174M 163M accept 0 27:23 0.00% perl
2066 www 1 4 0 162M 155M accept 0 26:29 0.00% perl
48990 mysql 10 20 0 443M 58028K kserel 0 4:24 0.00% mysqld
2067 nobody 1 96 0 14420K 13536K select 0 0:47 0.00% perl
1919 root 1 96 0 8612K 5876K select 0 0:41 0.00% httpd

Thanks,

Alex

3

RTx::Shredder - Cleanup RT database - metacpan.org Wed, Apr 2, 2008 at 5:02 AM, Alex Moura alexsm@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

The rt-users Archives

Community help: http://wiki.bestpractical.com
Commercial support: sales@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.