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

[Iain Price]

On Tue, 15 Jul 2003, Jesse Vincent wrote:

> > We eventually found we could replicate this by searching for requestor 
> > email address containing (anything) and then another 'contains' search to 
> > the search query produced is a good dozen lines long according to mysql 
> > query log (i spent too long thinking this was a mysql error).  Anyway the 
> > query takes over 10 minutes to respond :P during which time all the other 
> > query threads lock, and RT appears to have 'died'.
>  
> There was a post to, iirc, rt-users, yesterday discussing this
> performance issue with a recommended index to add to the database. That
> may fix it for you.

Had a look around for this and found an index on cachedgroupmembers was 
recommended.  Have done a lot of investigation into this, (thanks to mysql 
explain command phew), and while that index does help, its not enough for 
our setup.

I performed a lot of tests on a query that takes 9256 seconds on our RT
database (2.5 hours :P) and eventually came up with these two:

create index user_MemberIdGroupId on CachedGroupMembers(MemberId,GroupId);

create index user_PrincipalType on Principals(PrincipalType);

Neither one alone speeds the query up faster than one minute (which is all 
i tested when ensuring one index wasn't doing all the work) and separate 
indexes on memberid and groupid produced no extra speed increase.

However these two indexes together sped the query up to 12 seconds, about
770 times faster for me.

I've spent about 2 weeks tracking this down now :) I originally thought it 
was a bug in mysql, or something related to the dual processor machine 
we'd moved to for the 'release' version.

Some stats on the size of our DB for reference:
mysql> select count(*) from CachedGroupMembers;
|    12772 |
mysql> select count(*) from Groups;
|     5264 |
mysql> select count(*) from Users;
|      527 |
mysql> select count(*) from Tickets; 
|     1170 |
mysql> select count(*) from Principals;
|     5793 |

Our RT is version 3.0.2 at this time.

Iain