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@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
Excellent! Thanks.
I’m really kinda surprised that the Creator indices would have any effect.
What prompted you to add those?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@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@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users
http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.
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.
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.
Dave
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
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@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@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.
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@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.netOn 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@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@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.
Yes its true that it is slow, but my script delete tickets one by one in
case there is a crash in the middle of the process, so a db would’ent get
messed up.
BTW, it really speeds up, i just altered my tables by adding the indexes
you posted. Thanks for the tip, just to delete it was at least 4 times
faster, and also improved performance in RT WEBUI, we also maitain a rather
large db (=~ 200MB) and it was very useful.
Regards,
Steve Poirier
Directeur d�veloppement / Project Manager
Inet-Technologies inc.From: “David C. Troy” dave@toad.net
To: “Jesse Vincent” jesse@bestpractical.com
Cc: rt-users@lists.fsck.com
Sent: Thursday, August 23, 2001 12:21 PM
Subject: Re: [rt-users] Performance Increase through Indexing…
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.
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.
Dave
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@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@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users
–
http://www.bestpractical.com/products/rt – Trouble Ticketing. Free.
rt-users mailing list
rt-users@lists.fsck.com
http://lists.fsck.com/mailman/listinfo/rt-users
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,
-jOn 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@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@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@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.
“DCT” == David C Troy dave@toad.net writes:
DCT> - Iterate through Attachments to see if there are any “orphan”
DCT> attachments; if so, delete them
DCT> - Iterate through other tables for orphaned objects
Ideally, these issues are avoided via foreign keys. But the MySQL
authors know better than us users that this feature is unnecessary ;-(
Really, the only way to find out what keys are necessary is to
evaluate all the queries you do, and determine which ones are most
often done. Not all queries need to have an index on the search keys,
especially if it is not done often. This is your traditional space
vs. time tradeoff.