0

I have a MySQL 5.6 DB running in AWS RDS. I'm in the process of refining a few of my indexes. I use FLUSH QUERY CACHE on my local instance to clear the query cache when doing this kind of development.

I've got a query that takes 10 seconds the first time I call it and 0.2 seconds every time thereafter. I was expecting a post FLUSH QUERY CACHE call of the query would take 10 seconds but it still only takes 0.2 seconds.

This stackoverflow question points out that on Ubuntu I need to clear the memory disc caching prior to clearing the query cache. AWS RDS unfortunately does not allow terminal access to RDS instances.

How do I clear the query cache on a MySQL 5.6 DB running in AWS RDS?

Community
  • 1
  • 1
Frank
  • 590
  • 1
  • 8
  • 23
  • 1
    Can you define "does not seem to be working"? Error? Unexpected behaviour? Just a guess? – ceejayoz Feb 11 '15 at 20:20
  • @Ceejayoz, I've edited the question. It now more clearly states my expectation vs. the reality. _Thanks for keeping me honest!_ – Frank Feb 12 '15 at 01:39

1 Answers1

8

FLUSH QUERY CACHE doesn't clear the query cache in MySQL.

You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.

The RESET QUERY CACHE statement removes all query results from the query cache. The FLUSH TABLES statement also does this.

— http://dev.mysql.com/doc/refman/5.6/en/query-cache-status-and-maintenance.html

Also, the linked question is not related to the query cache in any way. The memory used by the query cache is allocated at startup and never returned to the system. The query cache is not backed by a file, and so isn't impacted by flushing to OS filesystem cache.

Finally, I'm not sure why, in the process of index maintenance, clearing the query cache would typically be needed. When benchmarking queries, using SELECT SQL_NO_CACHE ... will cause the query not to be serviced by the query cache.

Community
  • 1
  • 1
Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • @micheal-sqlbot: Thank you very for the clarification regarding MySQL's ```QUERY CACHE```. It seems I've been living under a misconception regarding the role of MySQL's ```QUERY CACHE```. I now realize I should have clarified my expectation regarding ```FLUSH QUERY CACHE``` more clearly. I've edited my question to more clearly reflect my intent. I've also up voted your answer as it clearly addressed my misconception. – Frank Feb 14 '15 at 07:03
  • Also, @Frank, any time you modify (insert into/update/delete from) a table with cached queries, all of the cached queries that involed that table are evicted from the cache, so a table that is actively being used by your application other than for selects isn't likely to have queries in the cache for very long. You clear the query cache in RDS the same as in standard MySQL Server. I'm unclear what else you are asking. Clearing the OS cache isn't possible in RDS but also isn't needed. – Michael - sqlbot Feb 14 '15 at 21:17