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

[Jesse Vincent]

What I'd recommend, if you do this is to explicitly log every row 
that gets pulled out of the database, so that one time that a ticket with > 100
transactions that you happen to _want_ gets purged, you don't get screwed.

If you can build the script to allow folks to easily toggle the various 
options, that would be really cool.

Adding functionality to do nightly compressed table dumps, perhaps just 
before the optimization would be a good bet. (since everyone needs a nightly
RT backup ;)

Making sure the optimize table is an optional step will help out the folks
who are running RT on Oracle or Pg.

	Just some random thoughts,

	-j


On Thu, Aug 23, 2001 at 12:45:21PM -0400, David C. Troy wrote:
> 
> 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.
> >
> 
> 

-- 
http://www.bestpractical.com/products/rt  -- Trouble Ticketing. Free.