See comments below. On Wed, Apr 29, 2009 at 2:37 AM, Paul Hirose <[email protected]> wrote: > 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?)
Hurt performance of update/create. May hurt optimizer that is not ideal. > 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. There are better ways to analyze mysql's slow log. Try googling "analyze mysql slow log". http://hackmysql.com/mysqlsla, http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/. There is no silver bullet. Each DB is unique as well as its load. Queries you show below just useless with explains. > 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 : [email protected] : 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 > _______________________________________________ > http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users > > Community help: http://wiki.bestpractical.com > Commercial support: [email protected] > > > Discover RT's hidden secrets with RT Essentials from O'Reilly Media. > Buy a copy at http://rtbook.bestpractical.com > -- Best regards, Ruslan. _______________________________________________ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [email protected] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
