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

[Jesse Vincent]

On Thu, Jul 24, 2003 at 12:36:52PM +0100, Ian Grant wrote:
> > > 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?

In this case, yes, there is an optimization we're looking at for this
case that should dramatically improve simple searches on requestor
addresses , but it's always going to be possible for users to string together 
stupidly broken queries. 

> 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

First up, move up to mysql 4.0.13 or newer. really. It's internal query
processor is WORLDS better. It won't cure cancer, but it should
definitely help performance. Especially if you turn on query caching.



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

-- 
http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.