Description:
Issuing a somewhat complicated query with a self-join seems to make the thread handling the query hang forever (got bored at about six hours past the start of query and killed mysqld) and as a side-effect cpu usage goes through the roof.
I was originally running mysql 4.1.5, now 4.1.6. Both versions suffer from the same problem. I've tried setting the following variables but they do not seem to have any effect in this case.
max_connections=300
net_read_timeout=60
net_write_timeout=60
connect_timeout=300
interactive_timeout=60
wait_timeout=60
For more information please see:
<http://rt3.fsck.com/Ticket/Display.html?id=6219&user=guest&pass=guest>
I'm happy to assist if you need more information, just let me know. Thanks.
How to repeat:
mysql> SELECT COUNT(DISTINCT main.id) FROM Tickets main , Groups Groups_1, CachedGroupMembers CachedGroupMembers_2, Users Users_3, Groups Groups_4, CachedGroupMembers CachedGroupMembers_5, Users Users_6 WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ( ( (Users_3.EmailAddress LIKE '%r.clasen%')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(main.id = Groups_1.Instance)AND(Groups_1.Type = 'Requestor')AND(Groups_1.id = CachedGroupMembers_2.GroupId)AND(CachedGroupMembers_2.MemberId = Users_3.id) ) OR ( (Users_6.EmailAddress LIKE '%r.clasen%')AND(Groups_4.Domain = 'RT::Ticket-Role')AND(main.id = Groups_4.Instance)AND(Groups_4.Type = 'Cc')AND(Groups_4.id = CachedGroupMembers_5.GroupId)AND(CachedGroupMembers_5.MemberId = Users_6.id) ) ) ;
...
mysql> explain SELECT COUNT(DISTINCT main.id) FROM Tickets main , Groups Groups_1, CachedGroupMembers CachedGroupMembers_2, Users Users_3, Groups Groups_4, CachedGroupMembers CachedGroupMembers_5, Users Users_6 WHERE ((main.EffectiveId = main.id)) AND ((main.Status != 'deleted')) AND ( ( (Users_3.EmailAddress LIKE '%r.clasen%')AND(Groups_1.Domain = 'RT::Ticket-Role')AND(main.id = Groups_1.Instance)AND(Groups_1.Type = 'Requestor')AND(Groups_1.id = CachedGroupMembers_2.GroupId)AND(CachedGroupMembers_2.MemberId = Users_3.id) ) OR ( (Users_6.EmailAddress LIKE '%r.clasen%')AND(Groups_4.Domain = 'RT::Ticket-Role')AND(main.id = Groups_4.Instance)AND(Groups_4.Type = 'Cc')AND(Groups_4.id = CachedGroupMembers_5.GroupId)AND(CachedGroupMembers_5.MemberId = Users_6.id) ) ) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 476
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: Users_3
type: index
possible_keys: PRIMARY
key: Users4
key_len: 121
ref: NULL
rows: 230
Extra: Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: Users_6
type: index
possible_keys: PRIMARY
key: Users4
key_len: 121
ref: NULL
rows: 230
Extra: Using where; Using index
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: CachedGroupMembers_2
type: index
possible_keys: DisGrouMem
key: DisGrouMem
key_len: 12
ref: NULL
rows: 6374
Extra: Using where; Using index
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: CachedGroupMembers_5
type: index
possible_keys: DisGrouMem
key: DisGrouMem
key_len: 12
ref: NULL
rows: 6374
Extra: Using where; Using index
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: Groups_4
type: index
possible_keys: PRIMARY,Groups1,Groups2
key: Groups1
key_len: 139
ref: NULL
rows: 2561
Extra: Using where; Using index
*************************** 7. row ***************************
id: 1
select_type: SIMPLE
table: Groups_1
type: index
possible_keys: PRIMARY,Groups1,Groups2
key: Groups1
key_len: 139
ref: NULL
rows: 2561
Extra: Using where; Using index
7 rows in set (0.03 sec)
