[rt-users] RT 3.4.5 slow loading/large query for RT: At a Glance page

[Tim]

Hi All,

I was recently tasked with upgrading RT from 3.0.11 to 3.4.5 for my 
company, basicaly we use RT to handle all incoming requests from 
customers as well as internal requests between branches, so the database 
is very large 650k + tickets from a large number of individual users - 
pretty much anyone that emails the company on the specified support 
address.

We have a front end box with SearchBuilder 1.38, Apache 2.0.54, mod_perl 
2,RT 3.4.5 (p4 2.8 ghz 1.5 gig ram) and a backend box, running the mysql 
RT database Fedora Core 4, Mysql 4.1.16 (p4 3 ghz 4 gig ram 10k rpm WD 
raptor drive. The upgrade process was all very smooth ( I dumped the old 
DB - transferred it to the new server, configured the appropriate my.cnf 
settings, imported the database and then ran the schema upgrades) and in 
initial testing everything seemed fine, however when all the support 
staff started logging on this particular query grinds the server to a 
halt by maxing out the CPU on the rt-sql server.

The offending query is:

SELECT DISTINCT main.* FROM (((Tickets main  JOIN Groups Groups_1  ON ( 
Groups_1.Instance = main.id))  LEFT JOIN CachedGroupMembers 
CachedGroupMembers_2  ON ( CachedGroupMembers_2.GroupId = Groups_1.id) 
AND( (CachedGroupMembers_2.GroupId != CachedGroupMembers_2.MemberId)))  
LEFT JOIN Users Users_3  ON ( Users_3.id = 
CachedGroupMembers_2.MemberId))   WHERE ((Groups_1.Domain = 
'RT::Ticket-Role')) AND ((Groups_1.Type = 'Requestor')) AND 
((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND 
((main.Type = 'ticket')) AND ( ( (main.Status = 'new')OR(main.Status = 
'open')OR(main.Status = 'stalled') ) AND ( (Users_3.EmailAddress = 
'blah at blah.com.au') ) )

When this is run by multiple people logging on to RT (seems to be run 
when loading the Rt:At a glance page) the CPU hits 99.9 percent and just 
grinds away until it can eventually complete the query. 3~5 minutes later

With maybe 5 people using RT it takes roughly 3 minutes to run that 
query get the more people logging on symaltaneously the worse it gets. 
At present we are looking at running shredder on the database and nuking 
tickets over 2 years old and their associated users Although the same 
database was being used with 3.0.11 (prior to upgrading schema etc) and 
it did not seem to suffer from the same problem. It was just generally 
slow overall. Hence the reason for upgrading in the first place.

If anyone has any suggestions or if there is any more info that is 
needed to help determine the problem im more than happy to help out =]

Cheers

Tim