[rt-devel] Re: RT SQL 'lockup' (mysql->99% cpu)

[Ian Grant]

> > dunno, i can see this issue in two lights - a)
> > stupid user, who cares, rt does what it does, or b) users can completely
> > halt RT functionality which is a disaster - completely opposing views,
> > Jesse, which approach do you take for RT - user or admin tool?
> 
> I train my users.  If your users have a legitimate need for searching
> for tickets with a combination of multiple requestor addresses, it's
> probably worth looking at the queries RT's generating and seeing what
> can be done indexwise to increase the performance.

The answer is not user-training, surely it is to fix the query builder. It is 
perfectly reasonable to assume a database can handle such a simple request and 
if it can't handle this, what other requests will it choke on?

I have found that a search for two requestor e-mail addresses will take a 
reasonable amount of time when it is first issued, but exponentially more the 
next time and so on ... After adding the two indexes suggested by Iain the 
problematic search returned in about 5 seconds (instead of the ten minutes I 
had seen previously) so I was hopeful of it being fixed. But hitting reload 
caused it to take about a minute. Hitting reload again ... we are now at 17 
minutes of CPU time on the mysqld and no sign of it finishing yet.

This is with RT 3.0.2, MySQL 3.23.55 Apache 1.3.28 and mod_perl 1.28

-- 
Ian Grant, Computer Lab., William Gates Building, JJ Thomson Ave., Cambridge
Phone: +44 1223 334420