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

[Iain Price]

Two large postings to introduce myself to the list :P
I noticed elsewhere on the maillists users watching their mysql server 
lock up with various queries going on

(see below for horrible mysqladmin processlist example)

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

Around this time our users hit refresh a ton and hence totally drive 
everything into the ground (*sigh*), forcing us to just restart the mysqld 
a lot.  The users then dont like the various error pages the 
server generates, and resent even more that 'back' in IE doesn't restore 
their posted form data (mozilla does tho...)

My thinking at the moment is not to let my users near the search feature 
in RT as its far too advanced and complicated for them, I appreciate by 
its nature you can create horrendously complicated searches (which is 
nice) however at the moment they are locking the db server up about 20 
times a day.

would it be possible to timelimit the sql queries, or have a more simple 
search page that just takes a 'word' and searches for it in emails, 
usernames, ticket subjects etc (the search in the top right seems to do 
subjects and ticket numbers only?)

Iain

+----+---------+-----------+-----+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User    | Host      | db  | Command | Time | State                | 
Info                                                                                                 
|
+----+---------+-----------+-----+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 1  | rt_user | localhost | rt3 | Query   | 130  | Locked               | 
SELECT DISTINCT main.* FROM Tickets main  WHERE ((main.EffectiveId = 
main.id)) AND ((main.Type = 'ti |
| 2  | rt_user | localhost | rt3 | Query   | 25   | User lock            | 
SELECT GET_LOCK('Apache-Session-d1d4573829558297ddf09545e082d3a9', 3600)                             
|
| 3  | rt_user | localhost | rt3 | Query   | 618  | Copying to tmp table | 
SELECT DISTINCT main.* FROM Tickets main, Groups Groups_1, Principals 
Principals_2, CachedGroupMembe |
| 4  | rt_user | localhost | rt3 | Query   | 81   | Locked               | 
SELECT  * FROM Tickets WHERE id = '573'                                                              
|
| 5  | rt_user | localhost | rt3 | Query   | 44   | Locked               | 
SELECT DISTINCT main.* FROM Tickets main  WHERE ((main.EffectiveId = 
main.id)) AND ((main.Type = 'ti |
| 6  | rt_user | localhost | rt3 | Query   | 110  | User lock            | 
SELECT GET_LOCK('Apache-Session-d1d4573829558297ddf09545e082d3a9', 3600)                             
|
| 7  | rt_user | localhost | rt3 | Query   | 131  | Locked               | 
UPDATE Tickets SET LastUpdated='2003-07-10 09:20:38' WHERE id='812'                                  
|
| 8  | rt_user | localhost | rt3 | Query   | 120  | User lock            | 
SELECT GET_LOCK('Apache-Session-d1d4573829558297ddf09545e082d3a9', 3600)                             
|
| 11 | root    | localhost |     | Sleep   | 231  |                      |                                                                                                      
|
| 12 | rt_user | localhost | rt3 | Query   | 70   | Locked               | 
SELECT DISTINCT main.* FROM Tickets main  WHERE ((main.EffectiveId = 
main.id)) AND ((main.Type = 'ti |
| 13 | rt_user | localhost | rt3 | Query   | 63   | Locked               | 
SELECT DISTINCT main.* FROM Tickets main  WHERE ((main.EffectiveId = 
main.id)) AND ((main.Type = 'ti |