I think that I must have something seriously wrong with the database after an
upgrade of rt3.2.3 to 3.4.4. In particular how my CachedGoupMembers table has
an index length of 225001472 for 2471666 rows… I think that mysqld is just
stalling on a table copy because the mysqld cpu ( cache hits ) go to %99 and
frequently abort client conections. If I kill the mysql process the
‘Tickets_Overlay.pm’ related web UI pages work as they ‘normally should’.
Name | Engine | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length
CachedGroupMembers | InnoDB | Fixed | 2471666 | 53 |
132808704 | NULL | 225001472
Users | InnoDB | Dynamic | 141084 | 249 |
35192832 | NULL | 32669696
Principals | InnoDB | Dynamic | 1094481 | 43 |
47792128 | NULL | 18399232
Groups | InnoDB | Dynamic | 443875 | 65 |
28901376 | NULL | 38961152
ACL | InnoDB | Dynamic | 11450 | 138 |
1589248 | NULL | 1589248
These two queries are causing the most troupble:…
SELECT DISTINCT main.* FROM Users main , Principals Principals_1,
CachedGroupMembers CachedGroupMembers_2 WHERE ((CachedGroupMembers_2.GroupId =
‘1102001’)) AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND
((Principals_1.Disabled = ‘0’)) AND ((main.id = Principals_1.id)) ORDER BY
main.Name ASC
SELECT DISTINCT main.* FROM Users main , Principals Principals_1, ACL ACL_2,
Groups Groups_3, CachedGroupMembers CachedGroupMembers_4 WHERE
((ACL_2.RightName = ‘OwnTicket’)) AND ((CachedGroupMembers_4.MemberId =
Principals_1.id)) AND ((Groups_3.id = CachedGroupMembers_4.GroupId)) AND
((Principals_1.Disabled = ‘0’)or(Principals_1.Disabled = ‘0’)) AND
((Principals_1.id != ‘1’)) AND ((main.id = Principals_1.id)) AND ( (
ACL_2.PrincipalId = Groups_3.id AND ACL_2.PrincipalType = ‘Group’
AND ( Groups_3.Domain = ‘SystemInternal’ OR Groups_3.Domain =
‘UserDefined’ OR Groups_3.Domain = ‘ACLEquivalence’)
mysql> show processlist;
| Id | User | Host | db | Command | Time | State | Info
|
| 838 | root | localhost | rt3 | Query | 0 | NULL | show
processlist
|
| 852 | root | localhost | rt3 | Query | 54 | Copying to tmp table | SELECT
DISTINCT main.* FROM Users main , Principals Principals_1, ACL ACL_2, Groups
Groups_3, Cached |
2 rows in set (0.00 sec)
The process will stay stalled as ‘Copying to tmp table’ which would make sense
as the tmp table would be pretty large and could be a choke point.
mysql> kill 852;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
| Id | User | Host | db | Command | Time | State | Info |
| 838 | root | localhost | rt3 | Query | 0 | NULL | show processlist |
| 856 | root | localhost | rt3 | Sleep | 1 | | NULL |
2 rows in set (0.00 sec)
Also the ‘Comment’ on ticket also gets slowed down int a tmp table copy when it
queries:
SELECT ACL.id from ACL, Groups, Principals, CachedGroupMembers WHERE
(ACL.RightName = ‘SuperUser’ OR ACL.RightName = ‘ShowTicket’) AND
Principals.Disabled = 0 AND CachedGroupMembers.Disabled = 0 AND Principals.id =
Groups.id AND Principals.id = CachedGroupMembers.GroupId AND
CachedGroupMembers.MemberId = ‘1100506’ AND ( ACL.ObjectType = ‘RT::System’ OR
(ACL.ObjectType = ‘RT::Ticket’ AND ACL.ObjectId = ‘203381’) OR (ACL.ObjectType =
‘RT::Queue’ AND ACL.ObjectId = ‘28’)) AND ( ((Groups.Domain = ‘RT::Ticket-Role’
AND Groups.Instance = 203381) OR (Groups.Domain = ‘RT::Queue-Role’ AND
Groups.Instance = 28) ) AND Groups.Type = ACL.PrincipalType AND Groups.Id =
Principals.id AND Principals.PrincipalType = ‘Group’) LIMIT 1
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
| 1 | SIMPLE | Groups | range | PRIMARY,Groups1,Groups2 |
Groups1 | 70 | NULL | 8 | Using where; Using
index |
| 1 | SIMPLE | Principals | eq_ref | PRIMARY |
PRIMARY | 4 | rt3.Groups.id | 1 | Using where
|
| 1 | SIMPLE | CachedGroupMembers | ref | DisGrouMem,GrouMem |
DisGrouMem | 12 | rt3.Principals.id,const,const | 1 | Using where; Using
index |
| 1 | SIMPLE | ACL | range | ACL1 |
ACL1 | 54 | NULL | 22 | Using where; Using
index |
4 rows in set (0.00 sec)
SELECT DISTINCT main.* FROM Users main , Principals Principals_1,
CachedGroupMembers CachedGroupMembers_2 WHERE ((CachedGroupMembers_2.GroupId =
NULL)) AND ((CachedGroupMembers_2.MemberId = Principals_1.id)) AND
((Principals_1.Disabled = ‘0’)) AND ((main.id = Principals_1.id)) ORDER BY
main.Name ASC
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
| 1 | SIMPLE | CachedGroupMembers_2 | ref | DisGrouMem,GrouMem |
DisGrouMem | 5 | const | 1 | Using where;
Using index; Using temporary; Using filesort |
| 1 | SIMPLE | Principals_1 | eq_ref | PRIMARY |
PRIMARY | 4 | rt3.CachedGroupMembers_2.MemberId | 1 | Using where
|
| 1 | SIMPLE | main | eq_ref | PRIMARY,Users3 |
PRIMARY | 4 | rt3.Principals_1.id | 1 |
|
3 rows in set (0.00 sec)
Suggestions? Is this a mysql table access problem/bug? Should you be using a
GROUP BY instead of ORDER BY? Technically the requests work but the database
looks hosed…
-jp