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

[Konstantin Naryzhniy]

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: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.