So How can you bypass these caches?

Query Profiling with MySQL: Bypassing caches

Quite frequently I run into question like this “I’m using SQL_NO_CACHE but my query is still much faster second time I run it, why is that ?

The answer to this question is simple - because SQL_NO_CACHE only bypasses query cache but it has no change on other caches, which are
MySQL Caches - Innodb Buffer Pool and Key Buffer are best example though Falcon, PBXT and other storage engines have similar buffers. There is also table_cache both MySQL side and Internal Innodb one which can affect query execution speed.
OS Caches Operation Systems typically cache file IO unless you explicitely bypass it by using O_DIRECT flag or mounting file system in direct IO mode.
Hardware Caches State of CPU cache may affect query execution speed but only lightly, the hardware IO cache may however cause dramatic difference. Hardware RAID cache is the one but more important SAN caches which can be pretty big.

So How can you bypass these caches?

For MySQL Caches you can restart MySQL and this is the only way to clean all of the caches. You can do FLUSH TABLES to clean MySQL table cache (but not Innodb table meta data) or you can do “set global key_buffer_size=0; set global key_buffer_size=DEFAULT” to zero out key buffer but there is no way to clean Innodb Buffer Pool without restart.

For OS Caches on Linux you can use drop caches control available in new Linux Kernels. You could also remount file system in question and the safest thing is of course to reboot.

For Hardware Caches it is more hardware specific. Typically doing some IO will flush cashes but you can’t be sure as you do not know what policies exactly do they employ. For RAID hardware caches reboot of the box is also enough however SAN caches may survive longer. Though few of us have SAN available for performance benchmarking