Clear MySQL Cache

Question

I took over a project written in Laravel 4. We have MySQL 5.6.21 - PHP 5.4.30 - currently running on Windows 8.1.

Every morning on the first attempt to access the landingpage - which contain about 5 queries on the backend - this site will crash with a php-timeout (over 30 seconds for response).

After using following I got closer to the cause: Laravel 4 - logging SQL queries. One of the queries takes more than 25 seconds on the first call. After that its always < 0.5 seconds.

The query has got 3 joins and 2 subselects wrapped in Cache::remember. I want to go into optimizing this so that on production it won't run into this problem.

So I want to test different SQLs The Problem is that the first time the data gets cached somehow and then I can't see whether my new SQL's are better or not.

Now, since I guess it's a caching issue (on the first attempt it takes long, afterwards not) I did these:

MySQL: FLUSH TABLES;
restart MySQL
restart Apache
php artisan cache:clear

But still, the query works fast. Then after some time I don't access the database at all (can't give an exact time, maybe 4 hours of inactivity) it happens again.

Explain says:

1 | Primary | table1 | ALL | 2 possible keys | NULL | ... | 1010000 | using where; using temporary; using filesort
1 | Primary | table2 | eq_ref | PRIMARY | PRIMARY | ... | 1 | using where; using index
1 | Primary | table3 | eq_ref | PRIMARY | PRIMARY | ... | 1 | using where; using index
1 | Primary | table4 | eq_ref | PRIMARY | PRIMARY | ... | 1 | NULL
3 | Dependent Subquery | table5 | ref | 2 possible keys | table1.id | ... | 17 | using where
2 | Dependent Subquery | table5 | ref | 2 possible keys | table1.id | ... | 17 | using where

So here the questions:

What's the reason for this long time?
How can I reproduce it? and
Is there a way to fix it?

I read mysql slow on first query, then fast for related queries. However that doesn't answer my question on how to reproduce this behaviour.

Update

I changed the SQL and now it is written like:

select 
    count(ec.id) as asdasda

from table1 ec force index for join (PRIMARY)
    left join table2 e force index for join (PRIMARY) on ec.id = e.id
    left join table3 v force index for join (PRIMARY) on e.id = v.id 

where
    v.col1 = 'aaa'
    and v.col2 = 'bbb'
    and v.col3 = 'ccc'
    and e.datecol > curdate()
    and e.col1 != 0

Now explain says:

+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+
| id | select_type | table  | type   | possible_keys | key          | key_len | ref             | rows   | Extra       |
+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+
|  1 | SIMPLE      | table3 | ALL    | PRIMARY       | NULL         | NULL    | NULL            | 114032 | Using where |
|  1 | SIMPLE      | table2 | ref    | PRIMARY       | PRIMARY      | 5       | table3.id       |     11 | Using where |
|  1 | SIMPLE      | table1 | eq_ref | PRIMARY       | PRIMARY      | 4       | table2.id       |      1 | Using index |
+----+-------------+--------+--------+---------------+--------------+---------+-----------------+--------+-------------+

Is that as good as it can get?

Your query is scanning over 1 million rows of data, using a temporary table to do so. Depending on other factors, that's potentially going to be quite slow. Can you re-write the query to make better use of the indexes you have, or remove the temporary table its creating, or optimise by adding an index? — Lee S, Jun 25 '15 at 17:32
Why does it take > 0.5 seconds then. I think that's pretty fast. — hogan, Jun 25 '15 at 17:40
Because it gets cached. Somewhere. The 0.5 second result is probably as a result of using a cache (be that at the disk, RAID, OS, DB, API or PHP level). The fact it takes 25+ seconds when done for the first time says the query is sub-optimal. — Lee S, Jun 25 '15 at 17:44
Without knowing where it is being cached, I can't really answer that one. — Lee S, Jun 26 '15 at 14:24
The way your query is written `LEFT JOIN` is in fact `INNER JOIN`. Any specific reason you use `LEFT JOIN` here? — Quassnoi, Jun 29 '15 at 20:49

score 0 · Accepted Answer · edited May 23 '17 at 12:06

0

The data might be cached in the InnoDB buffer pool or on Windows filesystem cache.

You can't explicitly flush the InnoDB cache but you can set the flushing parameters to more aggressive values:

SET GLOBAL innodb_old_blocks_pct = 5
SET GLOBAL innodb_max_dirty_pages_pct = 0

You can use the solution provided here to clear Windows filesystem cache: Clear file cache to repeat performance testing

But what you really need is an index on table3 (col1, col2, col3)

edited May 23 '17 at 12:06

Community

1
1

answered Jun 29 '15 at 20:49

Quassnoi

413,100
91
616
614

Should that buffer pool not be forgotten when I restart mysql? – hogan Jun 29 '15 at 20:53
@hogan: yes it should – Quassnoi Jun 29 '15 at 20:56
Sorry, then its not stored there. No matter what I restart (haven't tried to reboot the OS, yet. The query is fast. I am on the index, interesting.. I have got an index and despite of that i try to force it, mysql doesn't want it. – hogan Jun 29 '15 at 21:08
@hogan: if you paste your original query and table definitions someone (me included) will probably be able to assist. – Quassnoi Jun 29 '15 at 21:09
@hogan: if you're on Windows you could also try clearing filesystem cache: http://stackoverflow.com/questions/478340/clear-file-cache-to-repeat-performance-testing – Quassnoi Jun 29 '15 at 21:11
I will open a new question since it's actually two different ones. I edited your answer yesterday, do you see this> If you put it through it I'll mark your answer as accepted. – hogan Jun 30 '15 at 23:00
@hogan: no I don't see your edits. Don't edit other people's answers heavily. If you want to reply your own question, just post your own reply and accept it, it's ok. – Quassnoi Jun 30 '15 at 23:04
Hmm, then it got lost.. how did you manage to get all those points? That's crazy! – hogan Jun 30 '15 at 23:26
@hogan: see the question, answer it, rinse, repeat. – Quassnoi Jun 30 '15 at 23:28
Hey Quassnoi, in case you are interested: http://stackoverflow.com/questions/31150650/how-to-improve-this-mysql-query-using-join. – hogan Jul 01 '15 at 00:25

Clear MySQL Cache

So here the questions:

1 Answers1