Clear MySQL query cache without restarting server

Question

Is there any way to clear mysql query cache without restarting mySQL server?

John Parker · Accepted Answer · 2016-01-26T07:20:49.303

238

I believe you can use...

RESET QUERY CACHE;

...if the user you're running as has reload rights. Alternatively, you can defragment the query cache via...

FLUSH QUERY CACHE;

See the Query Cache Status and Maintenance section of the MySQL manual for more information.

edited Jan 26 '16 at 07:20

answered Mar 08 '11 at 11:21

John Parker

54,048
11
129
129

28

RESET QUERY CACHE will clear out the query cache, but needs RELOAD privileges. FLUSH QUERY CACHE does NOT clear out the query cache, it simply defrags it leaving the cached query results in place – carpii May 10 '12 at 21:55
This really helped. We are using NodeJS with MySQL using pool with 10 connections. We were facing the issue of data being written by one connection and read by other and it was caching heavily. This one seems to help a lot. Thx, – psuhas Oct 23 '16 at 01:43
3

For some reason, `RESET QUERY CACHE` actually does not clear it for me. Also, MySQL server restart does not help. An explicit `SELECT SQL_NO_CACHE` does the trick, but not `RESET QUERY CACHE`. `sync && echo 3 | sudo tee /proc/sys/vm/drop_caches` from the other answer didn't help, too. – Jānis Elmeris Aug 24 '17 at 23:41
1

And the machine restart didn't help either. – Jānis Elmeris Aug 24 '17 at 23:50

score 50 · Answer 2 · edited Apr 13 '17 at 12:22

50

In my system (Ubuntu 12.04) I found RESET QUERY CACHE and even restarting mysql server not enough. This was due to memory disc caching.
After each query, I clean the disc cache in the terminal:

sync && echo 3 | sudo tee /proc/sys/vm/drop_caches

and then reset the query cache in mysql client:

RESET QUERY CACHE;

edited Apr 13 '17 at 12:22

Community

1
1

answered Aug 30 '13 at 16:01

Leszek

1,290
2
11
21

2

Note that the "Query cache" in MySQL is not a general page/blocks cache. It's a cache of the _results_ of queries. Not always useful - we don't use it. http://dev.mysql.com/doc/refman/5.6/en/query-cache.html – phil_w Oct 11 '13 at 18:47
@phil_w is there a way to reset the cache of pages/blocks mysql uses without restarting mysql and clearing the (linux) OS cache? – matanster Dec 10 '14 at 17:30

score 19 · Answer 3 · answered Mar 08 '11 at 11:18

19

according the documentation, this should do it...

RESET QUERY CACHE

answered Mar 08 '11 at 11:18

Manu Eidenberger

2,076
1
18
23

score 6 · Answer 4 · answered Jul 20 '22 at 13:49

For anyone else, like myself, looking for a way to clear the query cache and finds this question, the query cache is deprecated.

The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

Therefore the commands RESET QUERY CACHE and FLUSH QUERY CACHE have been removed.

Have a look at the answers on Database Administrators for more information: Why did MySQL remove the query cache feature after version 8.0?

Clear MySQL query cache without restarting server

4 Answers4

Linked