[+/-]
From version 4.0.1 on, MySQL Server features a query cache. When
in use, the query cache stores the text of a
SELECT
statement together with
the corresponding result that was sent to the client. If an
identical statement is received later, the server retrieves the
results from the query cache rather than parsing and executing
the statement again. The query cache is shared among sessions,
so a result set generated by one client can be sent in response
to the same query issued by another client.
The query cache is extremely useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.
The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
The query cache does not work in an environment where you have
multiple mysqld servers updating the same
MyISAM
tables.
The query cache is not used for prepared statements. If you are using prepared statements, consider that these statements will not be satisfied by the query cache. See Section 17.6.4, “C API Prepared Statements”.
Some performance data for the query cache follows. These results were generated by running the MySQL benchmark suite on a Linux Alpha 2×500MHz system with 2GB RAM and a 64MB query cache.
If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower.
Searches for a single row in a single-row table are 238% faster with the query cache than without it. This can be regarded as close to the minimum speedup to be expected for a query that is cached.
To disable the query cache at server startup, set the
query_cache_size
system
variable to 0. By disabling the query cache code, there is no
noticeable overhead. If you build MySQL from source, query cache
capabilities can be excluded from the server entirely by
invoking configure with the
--without-query-cache
option.
The query cache offers the potential for substantial performance improvement, but you should not assume that it will do so under all circumstances. With some query cache configurations or server workloads, you might actually see a performance decrease:
Be cautious about sizing the query cache excessively large, which increases the overhead required to maintain the cache, possibly beyond the benefit of enabling it. Sizes in tens of megabytes are usually beneficial. Sizes in the hundreds of megabytes might not be.
Server workload has a significant effect on query cache
efficiency. A query mix consisting almost entirely of a
fixed set of SELECT
statements is much more likely to benefit from enabling the
cache than a mix in which frequent
INSERT
statements cause
continual invalidation of results in the cache. In some
cases, a workaround is to use the
SQL_NO_CACHE
option to prevent results
from even entering the cache for
SELECT
statements that use
frequently modified tables. (See
Section 7.5.4.2, “Query Cache SELECT
Options”.)
To verify that enabling the query cache is beneficial, test the operation of your MySQL server with the cache enabled and disabled. Then retest periodically because query cache efficiency may change as server workload changes.
User Comments
http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-limitations.html
states that:
>
>Unlike the case in MySQL 4.1, the Cluster storage engine in MySQL 5.0 supports MySQL' query cache.
>
Which combined with the statement above of:
>
>Note: The query cache does not work in an environment where you have multiple mysqld servers updating the same MyISAM tables.
>
seems to imply that the Cluster NBD knows how to correctly
flush the query cache for multiple mysqld servers.
Here is one article about the Query Cache
It's better explained what it really does and what it doesn't
do
http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/
Add your own comment.