MySQL query randomly hangs on 'sending data' status?

Question

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

Quite often the CPU of the server is working at 100% due to a mysqld process for roughly 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...

The problematic query is:

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 these columns are INDEXED, thus this ain't the problem. Also, when I run the query in phpMyAdmin (with a sufficient LIMIT), it takes 0.05 seconds to complete. Also, it is quite frustrating that I find it to be impossible to reproduce this problem myself, even when executing this query twice simultaneously and spamming it only gives me spikes to 40% CPU.

Prefixing the query with EXPLAIN results in:

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 | 

Unless I cannot see what is right in front of me, I am looking for ways to discover how to debug this kind of problem. I do already log all queries with their time to execute, and their values etc. But since I cannot reproduce it, I am stuck. What should I do to figure out what this problem is about?

Solution 1

Wow, thanks to Masood Alam's comment I found and solved the problem.

Using SHOW ENGINE INNODB STATUS turns out to be very helpful in debugging.

It turns out that with some values for user_id the database handles things differently, hence its unpredictable behaviour and not being able to reproduce the problem. Through the status command I was able to copy and paste the exact query that was running at the moment.

This are the results of EXPLAIN of that particular value for 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

A follow-up question would be now be how this behaviour can be explained. However, to fix the problem for me was just to change the query to:

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

Of which EXPLAIN results in:

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

Now I know, that InnoDB can have different approaches for executing queries given different input values. Why? I still don't know.

Solution 2

SENDING DATA is an indication for a long-running query no matter which engine you use and as such is very bad.

In your case no index is used on table a in both cases and you are lucky that this table only has a couple hundred records. No index or ALL (full table scan) is something to be avoided completely. Remember, things are different when you have thousands or even millions of records in your table.

Create an index modeled after the query in case. Use EXPLAIN again to see if the engine uses this index. If not, you may use a hint like USE INDEX(myindex) or FORCE INDEX (myindex). The result should be stunning.

If not, your index might not be as good as you think. Reiterate the whole process.

Remember, though, if you change the query you work with this index might no longer be appropriate, so again you have to reiterate.