[rt-users] Performance Increase through Indexing...

[David C. Troy]

OK -- I'll check that out.  Steve Poirier's delete_dead_tickets.pl is a
good seed for a general compact/optimization script that I'm working on
that would do these things daily:

  - Delete dead tickets
  - Delete tickets that have > x (100 in my case) transactions attached to
them (this would prevent buildup of any tickets that are loop or out of
control in some way)
  - Iterate through Attachments to see if there are any "orphan"
attachments; if so, delete them
  - Iterate through other tables for orphaned objects
  - Run mysql "OPTIMIZE TABLE x" on all major tables -- this will compact
and defrag the table files

I brought down a >4GB (admittedly screwed up) rt2 database to about 250MB
total using this technique.

Dave

=====================================================================
David C. Troy   [dave at toad.net]                   410-544-6193 Sales
ToadNet - Want to go fast?                        410-544-1329 FAX
570 Ritchie Highway, Severna Park, MD 21146-2925  www.toad.net

On Thu, 23 Aug 2001, Jesse Vincent wrote:

>
>
>
> On Thu, Aug 23, 2001 at 12:21:52PM -0400, David C. Troy wrote:
> >
> > > Excellent! Thanks.
> > >
> > > I'm really kinda surprised that the Creator indices would have any effect.
> > > What prompted you to add those?
> >
> > Well, that was sort of what I wanted your input on.  I think the ones that
> > made the biggest impact were the Ticket, TransactionId, and EffectiveId
> > indexes on the various tables;  wasn't sure what the creator field was but
> > assumed it was a reference to the Users table.  Any field used as an
> > external reference between tables is usually reasonable to index.
>
> Because the load of Users isn't done a join time, but is a delayed-load,
> if the data is asked for, I suspect this particular thing doesn't buy much.
> But I'll try to take a look at the others and see if there's any reason
> not to add all of them to the default schema going forward.
>
>
> > What would be really cool is if you could type up a moderately detailed
> > description of the RT2 database schema.  Then I'd know for sure what all
> > these fields actually do.
>
> What of that exists is in etc/schema.pm.
>
> >
> > Dave
> >
> >
> > >
> > >
> > > On Thu, Aug 23, 2001 at 10:51:04AM -0400, David C. Troy wrote:
> > > >
> > > > Ugh -- been spending the last 24 hours optimizing my RT data tables, etc,
> > > > trying to clean out junk, etc.
> > > >
> > > > Was running Steve Poirier's delete_dead_tickets.pl script, and it runs
> > > > very slow on large databases;  not his fault.
> > > >
> > > > By creating the following indexes, I was able to increase performance of
> > > > RT2 and Steve's script with no other modifications anywhere.  Jesse, in
> > > > your opinion which of these might provide the biggest gains overall?
> > > >
> > > > alter table Transactions ADD INDEX TicketIndex(Ticket);
> > > > alter table Transactions ADD INDEX EffectiveTicketIndex(EffectiveTicket);
> > > > alter table Transactions ADD INDEX CreatorIndex(Creator);
> > > > alter table Attachments ADD INDEX CreatorIndex(Creator);
> > > > alter table Attachments add index ParentIndex(Parent);
> > > > alter table Attachments ADD INDEX TransactionId(TransactionId);
> > > > alter table Tickets ADD INDEX EffectiveIdIndex(EffectiveId);
> > > > alter table Tickets ADD INDEX OwnerIndex(Owner);
> > > > alter table Tickets ADD INDEX CreatorIndex(Creator);
> > > >
> > > > This has really helped our RT performance.
> > > >
> > > > Dave
> > > >
> > > > =====================================================================
> > > > David C. Troy   [dave at toad.net]                   410-544-6193 Sales
> > > > ToadNet - Want to go fast?                        410-544-1329 FAX
> > > > 570 Ritchie Highway, Severna Park, MD 21146-2925  www.toad.net
> > > >
> > > >
> > > > _______________________________________________
> > > > rt-users mailing list
> > > > rt-users at lists.fsck.com
> > > > http://lists.fsck.com/mailman/listinfo/rt-users
> > > >
> > >
> > > --
> > > http://www.bestpractical.com/products/rt  -- Trouble Ticketing. Free.
> > >
> >
> >
>
> --
> http://www.bestpractical.com/products/rt  -- Trouble Ticketing. Free.
>