Hello Ronald, 1) I suspect missing index on CachedGroupMembers table. Show indexes for that table.
2) Also one of the following indexes on tickets would help with queries you showed: * (Status) * (Owner, Status) * (Status, Owner) I don't know which one you should use as I don't know distribution of Status and Owner columns, as well, I don't know if there are more queries with different signatures in the slow log. 3) If this system is not 64bit then mysql may have problems allocating more than 2G when you have plenty of room. 4) Major variable to tune for InnoDB is innodb_buffer_pool_size. However, read below. Considering all of the above you should take the following steps: 1) Improve performance of slow queries using indexes. For example you have queries that return "Rows_sent: 1", but to do that look at "Rows_examined: 839772", so this query pollute buffers with info from 1M records to recieve only one. 2) Once you have no slow queries in the log or ration between examined and sent records is small enough, at this moment you can move to tunning mysql's options. You restart mysql server or flush stats, waits some time to gather fresh stats and uses mysqltuner.pl at first and then advance tutorials on optimizing mysql/InnoDB. On Mon, Apr 19, 2010 at 3:38 PM, ronald.higgins <[email protected]> wrote: > > Greetings fellow list members. > > I'm hoping some more experienced members might be able to shed some light on > some performance issues > we have been having with Request Tracker 3.8.7, it really is terribly slow > loading anything from the DB side without the Server itself being > constrained for resources. > > The RT instance is running under VMWare VSphere (ESX4.0) with the following > resources assigned > > 8 vCPU's > 24GB RAM > 500GB disk on SAN (the SAN is idling so it's definately not disk I/O) > > O.S is Centos 5.4 > > The database itself (ibdata1) is 213GB in size. The database stores a lot of > images (faxes) sent from customers, > hence the size of the DB. The Tickets table contains about 1.2 million > records. > > Once logged into RT the (RT @ a Glance & queues takes about 10->15 seconds > too load. > Pages like Configuration loads instantaneously leading me to believe it's > anything being queried out of the DB. > > So any guidance on InnoDB tweaks to try would be appreciated as well. -- Best regards, Ruslan. Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
