[rt-devel] RT 3.0.4pre1: still some problems with CachedGroupMembers

[Stanislav Sinyagin]

Hi all, 

It was mentioned somewhere before that CachedGroupMembers queries 
get MySQL busy. 

I've upgraded our RT to 3.0.4pre1 from 3.0.3 release, and caught this problem again:
Mysql server takes 99% of the CPU, and mysqladmin -p proc output gives:
Locked       | DELETE FROM CachedGroupMembers WHERE id='7977'
See the whole output in the attachment. Ignore  
"select count(id) from CachedGroupMembers", this is where we tried to troubleshoot it.

It happened couple of times before the upgrade that mysql process is 99% busy, and 
only Mysql server restart helps.
Something was mentioned about the indexes, but I didn't read that thread 
carefully. Did something change in the database schema? 

Mysql version 4.0.13-log on FreeBSD 4.8-STABLE

Thanks,
Stan

-------------- next part --------------
ticket 9#mysqladmin -p proc
Enter password: 
+-----+----------+-----------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id  | User     | Host      | db    | Command | Time | State        | Info                                                                                                 |
+-----+----------+-----------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 1   | snortman | localhost | snort | Sleep   | 0    |              |                                                                                                      |
| 262 | rt_user  | localhost | rt3   | Query   | 221  | User lock    | SELECT GET_LOCK('Apache-Session-0c7777147baf0004b46fed5fef74de0e', 3600)                             |
| 263 | rt_user  | localhost | rt3   | Query   | 241  | User lock    | SELECT GET_LOCK('Apache-Session-0c7777147baf0004b46fed5fef74de0e', 3600)                             |
| 264 | rt_user  | localhost | rt3   | Sleep   | 208  |              |                                                                                                      |
| 265 | rt_user  | localhost | rt3   | Sleep   | 208  |              |                                                                                                      |
| 267 | rt_user  | localhost | rt3   | Query   | 201  | User lock    | SELECT GET_LOCK('Apache-Session-0c7777147baf0004b46fed5fef74de0e', 3600)                             |
| 268 | rt_user  | localhost | rt3   | Query   | 272  | Sending data | SELECT count(DISTINCT main.id) FROM Tickets main, Groups Groups_1, Principals Principals_2, CachedGr |
| 269 | rt_user  | localhost | rt3   | Query   | 209  | User lock    | SELECT GET_LOCK('Apache-Session-0c7777147baf0004b46fed5fef74de0e', 3600)                             |
| 270 | rt_user  | localhost | rt3   | Query   | 207  | Locked       | DELETE FROM CachedGroupMembers WHERE id='7977'                                                       |
| 271 | rt_user  | localhost | rt3   | Query   | 208  | User lock    | SELECT GET_LOCK('Apache-Session-0c7777147baf0004b46fed5fef74de0e', 3600)                             |
| 272 | rt_user  | localhost | rt3   | Query   | 183  | User lock    | SELECT GET_LOCK('Apache-Session-b2a45354b7281a5d1495e5349785de55', 3600)                             |
| 275 | rt_user  | localhost | rt3   | Query   | 113  | User lock    | SELECT GET_LOCK('Apache-Session-b2a45354b7281a5d1495e5349785de55', 3600)                             |
| 279 | root     | localhost | rt3   | Query   | 23   | Locked       | select count(id) from CachedGroupMembers                                                             |
| 280 | root     | localhost |       | Query   | 0    |              | show processlist                                                                                     |
+-----+----------+-----------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------+