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

[Jesse Vincent]

On Fri, Jul 04, 2003 at 02:30:50AM -0700, Stanislav Sinyagin wrote:
> Hi all, 
> 
> It was mentioned somewhere before that CachedGroupMembers queries 
> get MySQL busy. 


One of the things I see from below is that you have a number of SELECT
GET_LOCK queries that are waiting for the _same_ sessions. That implies
that someone got impatient and started trying to reload pages while RT
was thinking. Those hits will then wait for 6 minutes while the previous
locks time out.  Incidentally, I've seen some _very_ poor mysql
behaviour on freebsd, due to threading issues, which might also be able
to explain what you're seeing. 

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

Content-Description: RT-Error.txt
> 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                                                                                     |
> +-----+----------+-----------+-------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
> 


-- 
http://www.bestpractical.com/rt  -- Trouble Ticketing. Free.