MySQL query sometimes run slow, sometimes fast

Question

I am having problem with this simple MySQL query:

select sender as id from message where status=1 and recipient=1

where sender table has multi millions of rows.

When I run this on SequelPro, it runs really slow for the first time, ~4 seconds or more, and the next execution it run really fast, ~0.018 seconds. However, if I run again after couple of minutes, it will do the same thing again.

I tried to use SQL_NO_CACHE, and it still gives me the same result.

The DB engine is innoDB, and the DB is MySQL Percona XtraDB cluster. Here is the explain results:

|id|select_type|table  |type|possible_keys         |key |key_len|ref            |row   |Extra
| 1|SIMPLE     |message|ref |recipient,status, sent|sent|12     |const,const    |2989   |NULL

"sent" is an index of multi-column of (recipient, status). Does anyone has any idea to fix this problem?

Thank you.

Added (from comment)

CREATE TABLE 'message' (
    'id' int(20) NOT NULL AUTO_INCREMENT, 
    'sender' bigint(20) NOT NULL, 
    'recipient' bigint(20) NOT NULL, 
    'status' int(5) NOT NULL, 
    'date' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY ('id'), 
    KEY 'id' ('id'), 
    KEY 'recipient' ('recipient'), 
    KEY 'sender' ('sender'), 
    KEY 'date' ('date'), 
    KEY 'status' ('status'), 
    KEY 'sent' ('status','recipient')
) ENGINE=InnoDB AUTO_INCREMENT=90224500 DEFAULT CHARSET=latin1;

You can use the limit in your mysql query. e.g `select sender as id from message where status=1 and recipient=1 LIMIT 10` OR you can also use the offset like `select sender as id from message where status=1 and recipient=1 LIMIT 10 OFFSET 15` — Sanjay Chaudhari, Mar 11 '16 at 04:20
@SanjayChaudhari Thanks for the reply. However, I want to get all of the results from the table. That's why I don't use LIMIT. But I will try to loop the query for every 100 records and see if it makes any difference even though it's not a good solution since in the future the record will grow. — zangetsKid, Mar 11 '16 at 04:36
See if your index is used properly. Also, backup the table and re-create it. Then import the records from the dump once. Scattered records may come together and boos up performance a little. — Bimal Poudel, Mar 11 '16 at 07:03

Rick James · Answer 1 · 2016-03-14T04:14:58.353

2

Those symptoms point to caching issues. I don't mean the "Query cache", but rather the Engine's cache.

How big is the table? How big are all the the active tables?

What is the value of innodb_buffer_pool_size?

I suspect the buffer_pool is a lot smaller than the table(s), and a lot of stuff is going on. Hence, the query's blocks get bumped out of RAM, necessitating a few dozen reads to bring them back in.

innodb_buffer_pool_size should be set to about 70% of available RAM.

More (based on CREATE TABLE)

The "covering" INDEX(status, recipient, sender) will be faster -- it won't have to bounce over to the data; the query can be done entirely in the index.

A PRIMARY KEY is a key, so INDEX(id) is redundant and can be DROPped.

A KEY that is a prefix of another key is redundant. I am referring to (status) in your current CREATE TABLE.

edited Mar 14 '16 at 04:14

answered Mar 11 '16 at 04:45

Rick James

135,179
13
127
222

the table is about 80 million rows. The value of innodb_buffer_pool_size = 2GB, and the RAM size is 8GB. In the past, I have configured the innodb_buffer_pool_size to 5GB, but when I tested a lot of query requests, like about 1000 requests/second, MySQL suddenly died. But when I changed it to 2GB, I never experience MySQL dying on its own. – zangetsKid Mar 11 '16 at 05:00
Do not draw a big list at once. You can often see MySQL server go e away error when querying too fast, multiple times. Second immediate query is likely to run faster possibly due to hard disk performance where the HDD head is very close to the data to read next. – Bimal Poudel Mar 11 '16 at 07:01
@BimalPoudel I tried to use LIMIT in the query like this: `select sender as id from message where status=1 and recipient=1 LIMIT 0,100`, but I am still having the same issue. – zangetsKid Mar 11 '16 at 07:26
Please provide `SHOW CREATE TABLE`; I'm missing some thing subtle. – Rick James Mar 11 '16 at 19:29
@RickJames Here is the "SHOW CREATE TABLE message" `CREATE TABLE 'message' ( 'id' int(20) NOT NULL AUTO_INCREMENT, 'sender' bigint(20) NOT NULL, 'recipient' bigint(20) NOT NULL, 'status' int(5) NOT NULL, 'date' timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY ('id'), KEY 'id' ('id'), KEY 'recipient' ('recipient'), KEY 'sender' ('sender'), KEY 'date' ('date'), KEY 'status' ('status'), KEY 'sent' ('status','recipient') ) ENGINE=InnoDB AUTO_INCREMENT=90224500 DEFAULT CHARSET=latin1;` – zangetsKid Mar 14 '16 at 02:16
Give that 3-column index a try. – Rick James Mar 14 '16 at 04:15
@RickJames What do you mean by "A `KEY` that is a prefix of another key is redundant"? Also, why do I need to index "sender" since it's not in the `WHERE` clause? But I'll give this a try and let you know what happens. – zangetsKid Mar 14 '16 at 05:10
`sender` makes it a "covering" index. – Rick James Mar 14 '16 at 18:50
1

`INDEX(a), INDEX(a,b)` -- there is no need for the former, since the latter can handle, say `WHERE a = 123` even though `b` is not mentioned. – Rick James Mar 14 '16 at 18:51

MySQL query sometimes run slow, sometimes fast

1 Answers1