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

[Iain Price]

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

First thing we did.  Didn't seem to improve much, although i've not tried
(or heard of to date ;) query caching, although effectiveness depends whats
cached i guess, the users rarely execute these queries more than once cos i
have to go kill mysql connections there after and get annoyed to see the
query a second, third even fourth time turn up in the process list.  I guess
the query has to complete once too?

I dont mean to doubt your views or knowledge on mysql etc but given how
little use mysql4 over mysql3 is to me (an uncompletable query still is...)
should i spend a day looking at query caching and performance (remember this
is works time not mine ;) or are you just suggesting any ideas you can think
of rather than knowing that this will solve my issue.

I've become a firm believer the only solution is to better the generated
query or prevent users generating this particular search, since optimising
mysql seems to lead nowhere (except to even more impossible queries) and
having a user base that is 100% competent all the time with every entry
seems a pipe dream... You should see some of the e-mail addresses/typos they
type into RT (omg!), and i get to sort the bounces :S

Iain