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@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