Hello Ruslan! Thanks for your rapid reply. I have added one of the indices you've suggested (we do not have Custom Fields yet and the ACL table is relatively small and doesn't appear in slow queries log).
I do not think that the delay is caused by the committing transaction. Actually, the gap has to deal with the previous ticket, rather that the next one. I mean that this delay first happens right after when I confirm the "Do you want to proceed? [y/N]" prompt and before the first warning line appears. Could you please explain what happens before the first warning is sent to output? I am no programmer to dig into the source code)) Are the DELETE statement in the Shredder committed one by one or are they committed in a single "package" after all the records in DB tables were found? thanks, Kostya -----Original Message----- From: Ruslan Zakirov [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 14, 2007 7:43 PM To: Konstantin Naryzhniy Cc: [email protected] Subject: Re: [rt-users] RTx::Shredder time gap between tickets wipeout On 3/14/07, Konstantin Naryzhniy <[EMAIL PROTECTED]> wrote: > > Hello, guys! > > First of all, I would like to thank everyone who developed and contributed > to RT. > > This system is really great and the more I dig into it the more I love its > flexibility compared to rivals. > > I have been reading through these lists for quite some time now, and finally > decided to post my question. > > > > So, this one probably goes to Ruslan Zakirov, the author of the Shredder. > > To make a long story short: RT 3.6.1, huge installation, 150000 (one hundred > and fifty thousand) tickets, 30% of them deleted spam, 20 queues, 80 > privileged users. > > Mysql 5.0.18, Postfix, Apache/2.2.0, 2.6.15-1.2054_FC5. > > > > I started to use the Shredder to get rid of at least spam tickets. A single > ticket took about 6 minutes to delete. It took most of the time to wipe out > Cached Group Members. > > After we upgraded to new hardware (C2D, 2G RAM) I went down to 2.5 minutes > per ticket. After that (still with me?) I played around with mysql_slow.log > and indices, as it has been suggested on this list before (if anybody's > interested I'll post exactly what I've done). > > Now, the ticket deletion takes only 10-15 seconds. Great! Yep, there is several queries shredder runs often when RT doesn't, so RT has no indexes for them by default. Here is some critical indexes: # shredder pack CREATE INDEX ruz_OCFV1 ON ObjectCustomFieldValues(ObjectType, ObjectId); CREATE INDEX ruz_ACL5 ON ACL(ObjectType, ObjectId); CREATE UNIQUE INDEX ruz_GM1 ON GroupMembers(MemberId, GroupId); > > However, when I launch rtx-shredder script without -force option from CLI, > so I can see the messages and prompts, there is a delay (a gap) in process > after wiping out previous ticket and starting a new one. On screen > consecutive lines look something like this: > > > > [Wed Mar 14 15:47:19 2007] [warning]: RT::Attachment-158005 wiped out > (/work/opt/rt3/local/lib/RTx/Shredder/Record.pm:198) > > [Wed Mar 14 15:47:19 2007] [warning]: RT::Transaction-330341 wiped out > (/work/opt/rt3/local/lib/RTx/Shredder/Record.pm:198) > > [Wed Mar 14 15:47:19 2007] [warning]: RT::Transaction-334128 wiped out > (/work/opt/rt3/local/lib/RTx/Shredder/Record.pm:198) > > [Wed Mar 14 15:47:19 2007] [warning]: RT::Ticket-31041 wiped out > (/work/opt/rt3/local/lib/RTx/Shredder/Record.pm:198) > > [Wed Mar 14 15:47:43 2007] [warning]: RT::CachedGroupMember-438043 wiped out > (/work/opt/rt3/local/lib/RTx/Shredder/Record.pm:198) > > [Wed Mar 14 15:47:43 2007] [warning]: RT::Transaction-330378 wiped out > (/work/opt/rt3/local/lib/RTx/Shredder/Record.pm:198) > > [Wed Mar 14 15:47:43 2007] [warning]: RT::Group-167572 wiped out > (/work/opt/rt3/local/lib/RTx/Shredder/Record.pm:198) > > > > So, there is this gap of 20-30 seconds, which ends up in approx 17 hours to > delete 1000 tickets. > > I wonder, what causes this gap? a dump writing time? If so, can I run the > Shredder without creating a dump? I think it's DB committing transaction. > > > > I'll appreciate any help. > > > > Thank you very much! > > > > Kostya. > > > > -- 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
