Highly optimized queries are sometimes taking 20s to perform?

MySQL & MariaDB Other MySQL® Questions
1

Hi all,
I have a PhP/MySQL app running on Joyent virtual servers that has been humming along nicely for a year, but now is hitting serious problems. Queries that used to run fine are now sometimes taking 10 or 20 seconds to complete (but sometimes <1 second). Table are MyISAM, and locking is not a big issue. There are usually only a couple users at once. The user_book table has about 600k rows and the book table has about 300k rows.

Here is output of a slow log (some info anonymized):

Time: ****

User&#64;Host: [] @ ****

Query_time: 12 Lock_time: 0 Rows_sent: 22 Rows_examined: 44

SELECT id, title, author, count, status, user_book.ranking, asin, locale, image_url, user_book.uid, review.uid as review FROM book LEFT JOIN user_book ON id=bid LEFT JOIN review ON user_book.uid=review.uid AND user_book.bid=review.bid WHERE user_book.uid=25103032;

and here is an explain of the same query.

±—±------------±----------±-------±--------------±-- ------±--------±-------------------------------------±— --±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±----------±-------±--------------±-- ------±--------±-------------------------------------±— --±------------+
| 1 | SIMPLE | user_book | ref | PRIMARY,bid | PRIMARY | 4 | const | 21 | Using where |
| 1 | SIMPLE | review | eq_ref | PRIMARY,bid | PRIMARY | 8 | const,****.user_book.bid | 1 | Using index |
| 1 | SIMPLE | book | eq_ref | PRIMARY | PRIMARY | 4 | ****.user_book.bid | 1 | |
±—±------------±----------±-------±--------------±-- ------±--------±-------------------------------------±— --±------------+

I am at a loss. I am a professional software developer, but I know only the very basics when it comes to databases. I’m guessing there is some configuration problem here, as I see no other reason why this query would take 12 seconds to evaluate.

Any ideas?

Thanks,
Garth

2

It could be hardware problem. You could check RAID and hdd status.

3

So is this not the kind of problem that appears due to MySQL configuration? Has anyone else dealt with this kind of performance issue?

I don’t have control over the hardware. I asked my hosting provider, and they said everything is ok.

Thanks,
Garth

4

Does this occurs randomly ? Or there is pattern, like “slow query every 10 minutes” ?

Constant period will mean that something is wrong with hardware, or that there is something running in the background (cron job).

5

The slowness seems to happen only the first time I run the query. Second and third times it is fast.

Sorry I should have mentioned this. I guess it would suggest a caching problem of some kind?

6

There are two main reasons why it would be faster after the first time. One is the query cache and the other is that indexes have been loaded into the key buffer.

You can prevent MySQL from using the query cache by adding SQL_NO_CACHE to your query like so:

SELECT SQL_NO_CACHE some_column FROM some_table;

If you run your query several times again without the query cache and it still gets faster after the first run, it means that the index pages you need are in the key buffer and its saving you a trip to disk. If that’s the case check the following:

SHOW VARIABLES LIKE ‘key_buffer_size’;
SHOW TABLE STATUS LIKE ‘some_table’;
SHOW STATUS LIKE ‘Key_reads’;
SHOW STATUS LIKE ‘Key_read_requests’;