Geek Answers Handbook

Does MySQL query accidentally freeze on "send data" status?

I am using MySQL 5.5.25 server and InnoDB for my databases.

Quite often, the server processor runs at 100% due to the mysqld process for about a minute. Using SHOW PROCESSLIST:

Command | Time | State        | Info
Query   |  100 | Sending data | SELECT a.prefix, a...
Query   |  107 | Sending data | SELECT a.prefix, a...
Query   |   50 | Sending data | SELECT a.prefix, a...

Problem request:

SELECT a.prefix, a.test_id, a.method_id, b.test_id
FROM a
LEFT JOIN b ON b.test_id = a.test_id
AND user_id = ?
AND year = ?

All of these columns are INDEXED, so this is not a problem. Also, when I run the request in phpMyAdmin (with enough LIMIT), it takes 0.05 seconds. In addition, it is quite difficult that I find it impossible to reproduce this problem myself, even when you execute this request twice at the same time, and spamming it only gives me peaks of up to 40% of the CPU.

Query prefix with EXPLAIN results:

Id | select_type | table | type | possible_keys        | key     | key_len | ref          | rows | Extra
 1 | SIMPLE      | a     | ALL  | NULL                 | NULL    | NULL    | NULL         | 1169 | 
 1 | SIMPLE      | b     | ref  | user_id,year,test_id | test_id | 4       | db.a.test_id |   58 |

, , , . , .. , . , , ?

+4
1

, .

SHOW ENGINE INNODB STATUS .

, user_id -, , . status , .

EXPLAIN user_id:

Id | select_type | table | type         | possible_keys        | key          | key_len | ref  | rows | Extra
 1 | SIMPLE      | a     | ALL          | NULL                 | NULL         | NULL    | NULL |  841 | 
 1 | SIMPLE      | b     | index_merge  | user_id,year,test_id | user_id,year | 4,4     | NULL |   13 | Using intersect(user_id,year); Using where

, . :

SELECT a.prefix, a.test_id, a.method_id, b.test_id
FROM a
LEFT JOIN b ON b.test_id = a.test_id
WHERE
b.id IS NULL
OR user_id = ?
AND year = ?

EXPLAIN :

Id | select_type | table | type | possible_keys | key     | key_len | ref          | rows | Extra
 1 | SIMPLE      | a     | ALL  | NULL          | NULL    | NULL    | NULL         |  671 | 
 1 | SIMPLE      | b     | ref  | test_id       | test_id | 4       | db.a.test_id |   49 | Using where

, InnoDB . ? .

+5

More articles:


All Articles