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

[Iain Price]

> On Fri, Jul 25, 2003 at 01:10:24PM +0100, Iain Price wrote:
> > > > 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.
>
> > 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.
>
> It's not a magic bullet that will magically make all bogus queries
> perform in negligable time, but its query processor has proven to be
> much more advanced and to NOT DIE in situations where Mysql 3.23 curls
> up into a little ball and refuses to deal with the world. I can't say
> whether you should spend a day of your work time on it. That would
> depend on how much of a problem this issue is for your organization.

Just fyi, already using 4.0.13, first thing we changed to try fix the issue.
Made no noticable difference (except to make my mysql rpm something i have
to manually look after rather than rely on up2date hehe).  Well in all
fairness it might have made some difference, but on a 6 hours query anything
short of several thousand times faster isn't going to get spotted unless I
spend days profiling this (or create a smaller testing RT DB).

Anyway I have to work on other things, strings to decode and process, ya
know, usual stuff, so unless my user base continues to knacker RT (and i'll
probably be the last to know now i've explained mysqladmin kill :P) I dont
think i'll be investigating this further, I may get around to patchign the
search page to remove multiple-requestors.

The only true solution would apppear to be to play with the query generating
logic somehow, but its a 10 line query that comes out and i've not even
touched that part of RT yet (more a back-end coder - mainly because i'm
moving away from using perl to script everything and unifying it all into
one java application rather than several dozen disparate systems -
integration with other languages and systems is important for my job at the
moment).

Is this issue specific to Mysql? I appreciate a vastly superior query parser
on the server side should be able to (in theory) make queries so good theres
no point trying to optimise them but thats clearly not happening here - i
could write a sequential analyser of the tables to find 'multiple
requestors' quicker than this query does (a *LOT* quicker, like within a
couple of seconds, the ideal web app response rate rather than 12, or even
thousands)?  anyone running RT on pgsql/oracle/db2/whatever else there is
(oh yeah ms sql lol) that could try this search at a non-crucial time for
their install and see if it responds in less than 10 minutes? (nb will lock
up your RT totally during the query).

Another approach (probably worse than the current situation) - is it
necessary to lock tables during a query? is this mysql's doing or rt's
doing? its this that causes all other RT's to die eventually - they all wait
for some lock that this query holds, killing the query releases the other
threads... I appreciate the issues of referential integrity, but defensive
coding could avoid these problems since its a search not a write... Although
predicting my users, they would simply end up sending the same query 10
times until the server is just bogged down running a dozen several-hour
queries (which would then presumably take even longer)...

Oh last thing... when it locks up for the several hours mysql is 'copying
data to tmp table'... I dont know /exactly/ what its doing (i.e. compiling
what data in to where) but i guess the join just makes something that is
tremendously large and takes a while to generate (??) - is this going to
chew gigabytes of ram/swap/disk store eventually?  If its nasty-join related
then presumably this gets exponentially worse as the users/tickets increase
(users i'm thinking will hit it worse if i understand the back end DB
structure)

I'll retreat slightly from this list now, i've posted many long essays in my
brief time, hope some of them are useful :) i might be back, so dont get to
comfy *evil laugh*

Summary: dont search for more than one requestors email in one search from
the RTsearch page because the query occupies (at least) mysql for hours
(thousand+ tickets, few hundred users)...

Thanks for your help,
Iain