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

[Ruslan Zakirov]

See comments below.

On Wed, Apr 29, 2009 at 2:37 AM, Paul Hirose <pthirose at ucdavis.edu> 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          : 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
> _______________________________________________
> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>
> Community help: http://wiki.bestpractical.com
> Commercial support: sales at bestpractical.com
>
>
> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
> Buy a copy at http://rtbook.bestpractical.com
>



-- 
Best regards, Ruslan.