[rt-users] Creating Index(es) for RT and other optimizations

[Paul Hirose]

RT-3.8.2 and MySQL 5.0.77.  I've seen many messages mention "create an index..." that would help performance.  I was wondering if anyone has recommendations on what index(es) to create (and if you could add how to do so as well.)  It's hard to tell which apply for what (some referring to RT-Shredder, others to RT 3.6.x, etc.)  Does creating a bunch of indexes that never get used hurt (other than disk space?)

I started MySQL with "--log-queries-not-using-indexes" just out of curiosity, and see quite a few over the past 24 hours.  A "grep SELECT rt-slow.log | sort | uniq -c | sort -n" shows most of them aren't repeated often.  Of course, I restarted so it's only had about 14 hours worth of operation so far.  A couple random SELECT statements from the above are shown below.

Also ran mysqlreport (saw that reference in a recent email message on this list, thank you!)  It's mostly doing MyISAM analysis, but the InnoDB section is showing some stuff too.  Again, w/less than a day's worth of info it's not much to go on.

I asked previously about my output from mysqltuner.pl and that mostly boiled down to "can't really fix these with just RT" (and obviously stuff about adding RAM, or changing some particular variable I'm trying as best I can.)

My Apache (2.2.11 with Mod-perl 2.04) is solely dedicated to RT.  If anyone has performance tuning suggestions that helps it run RT better, I'd be happy to hear those too.

I've looked at http://wiki.bestpractical.com/view/PerformanceTuning and tried a few things as appropriate (latest version of DBIx::SearchBuilder, etc.)  I'm going to try the HTML::Mason suggestions there next (one change at a time!)

Thank you,
PH

PS
SELECT main.* FROM Tickets main  WHERE (main.Status != 'deleted') AND ( ( main.Owner = '6' OR main.Owner = '22' )  AND main.Type = 'reminder' AND  ( main.Status = 'new' OR main.Status = 'open' ) ) AND (main.EffectiveId = main.id)  ORDER BY main.Due DESC;

SELECT main.* FROM Tickets main  WHERE (main.Status != 'deleted') AND ( ( main.Owner = '6' OR main.Owner = '220' )  AND main.Type = 'reminder' AND  ( main. Status = 'new' OR main.Status = 'open' )  AND  (  ( main.Queue = '3' OR main.Queue = '4' OR main.Queue = '5' OR main.Queue = '6' OR main.Queue = '7' )  ) ) AND (main.EffectiveId = main.id)  ORDER BY main.Due DESC;

--
Paul Hirose          : pthirose at ucdavis.edu : Sysadm Motto: rm -fr /MyLife
1034 Academic Surge  : Programmer/Analyst   : Backup Motto : rm -fr /
One Shields Avenue   : Voice (530) 752-7181 : Robot, n.: Univ. Admin
Davis, CA 95616-8770 : Fax   (530) 752-4465 : rec.pets.cat.anecdotes