disable mysql cache

Question

i need to disable mysql query cache for testing the speed of query. i have already tried the following but without success

1. select SQL_NO_CACHE col1, col2 from table
2. made config variable query_cache_size to 0 and 'query_cache_type' to OFF so all the qcache variables show zero SHOW STATUS LIKE '%qcache%';
   Qcache_free_blocks 0 Qcache_free_memory 0 Qcache_hits 0 Qcache_inserts 0 Qcache_lowmem_prunes 0 Qcache_not_cached 0 Qcache_queries_in_cache 0 Qcache_total_blocks 0

3.used 'reset query cache', 'flush query cache', flush tables; too but still my query runs quicker than the first time which means it is caching.

4.all the qcache variables remain zero all the time. what am i missing? please help.

Answer 1

Your observation that a subsequent execution of the same query is running quicker does not necessarily mean the query results are being cached, or that a subsequent execution of the query is being returned from the query cache.

It may be that on the first execution of the query, the index and/or data blocks that had to be accessed were not in the InnoDB buffer pool or the MyISAM key cache, and had to be retrieved from disk. Those blocks will remain in the InnoDB buffer pool and the MyISAM key cache wholly independent of the MySQL query cache. Subsequent accesses of those blocks could return more quickly if they are still in memory.

---

Typically, you do NOT want to flush blocks from the InnoDB buffer pool or the MyISAM key cache to measure performance. Rather, you want to run the query multiple times, and throw out the time for the first execution.

If you want to start with no blocks in the InnoDB buffer pool or the MyISAM key cache, then shutdown and restart the MySQL server.

But even if those blocks are not in the InnoDB buffer pool or the MyISAM key cache, it's likely those blocks may still be in "memory" in the OS file system cache. So to ensure you are reading blocks from disk, you'd also need to flush OS file system cache as well.

---

The following is an excerpt from a Tom Kyte (AskTom) article pertaining to Oracle, but the same reasoning holds true for other RDBMS systems (including MySQL).

http://www.oracle.com/technetwork/issue-archive/o43asktom-094944.html

Flush the Cache

Q: Is there a way to flush [the buffer cache]? ... This is important for a tuning exercise where several methods are tried one after the other, but we would like to reduce the effect of a preexisting block in the buffer without doing a database restart.

A: Actually, it is important that a tuning tool not do that. It is important to run the test, ignore the results, and then run it two or three times and average out those results. In the real world, the buffer cache will never be devoid of results. Never. When you tune, your goal is to reduce the logical I/O (LIO), because then the physical I/O (PIO) will take care of itself.

Consider this: Flushing the shared pool and buffer cache is even more artificial than not flushing them. Most people seem skeptical of this, I suspect, because it flies in the face of conventional wisdom. I'll show you how to do this, but not so you can use it for testing. Rather, I'll use it to demonstrate why it is an exercise in futility and totally artificial (and therefore leads to wrong assumptions). I've just started my PC, and I've run this query against a big table. I "flush" the buffer cache and run it again:

Answer 2

Is this on Linux? Also try flushing the OS disk cache:

$ sync && sysctl -w vm.drop_caches = 3

See this post