MySQL Performance Blog

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?
[read more...]

Monty Taylor posted interesting investigation of the fact read_buffer_size variable affects connection speed.

This is not something you would expect right ? me too. Not only global user data is expected to be cached on startup but even if it is not why would you do full table scan to fetch single user information ? Something is fishy here and perhaps bug needs to be filed either resulting in fixes or docummentation updates.
[read more...]