[rt-users] RTx::Shredder time gap between tickets wipeout

[Ruslan Zakirov]

On 3/16/07, Konstantin Naryzhniy <konstantinn at parallels.com> wrote:
> 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.
Huh, that's kinda a ringing bell that's I've screwed up something and
that's actually problem in the shredder.

>
> 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?
I commit things once per ticket or per user, depends on what top level
objects are. Top level objects are objects you select with a plugin.

>
> thanks,
> Kostya
>
> -----Original Message-----
> From: Ruslan Zakirov [mailto:ruslan.zakirov at gmail.com]
> Sent: Wednesday, March 14, 2007 7:43 PM
> To: Konstantin Naryzhniy
> Cc: rt-users at lists.bestpractical.com
> Subject: Re: [rt-users] RTx::Shredder time gap between tickets wipeout
>
> On 3/14/07, Konstantin Naryzhniy <konstantinn at parallels.com> 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.
>
>


-- 
Best regards, Ruslan.