Databases Reference
In-Depth Information
• Having multiple smaller tables instead of one huge one can help the query cache.
This design effectively makes the invalidation strategy work at a finer level of
granularity. Don't let this unduly influence your schema design, though, as other
factors can easily outweigh the benefit.
• It's more efficient to batch writes than to do them singly, because this method
invalidates cached cache entries only once. (Be careful not to delay and batch so
much that the invalidations caused by the writes will stall the server for too long,
however.)
• We've noticed that the server can stall for a long time while invalidating entries in
or pruning a very large query cache. A possible solution is to not make
query_
cache_size
very large, but in some cases you simply have to disable it altogether,
because nothing is small enough.
• You cannot control the query cache on a per-database or per-table basis, but you
can include or exclude individual queries with the
SQL_CACHE
and
SQL_NO_CACHE
modifiers in the
SELECT
statement. You can also enable or disable the query cache
on a per-connection basis by setting the session-level
query_cache_type
server vari-
able to the appropriate value.
• For a write-heavy application, disabling the query cache completely might improve
performance. Doing so eliminates the overhead of caching queries that would be
invalidated soon anyway. Remember to set
query_cache_size
to
0
when you disable
it, so it doesn't consume any memory.
• Disabling the query cache might be beneficial for a read-heavy application, too,
because of contention on the single query cache mutex. If you need good perfor-
mance at high concurrency, be sure to validate it with high-concurrency tests,
because enabling the query cache and testing at low concurrency can be very
misleading.
If you want to avoid the query cache for most queries, but you know that some will
benefit significantly from caching, you can set the global
query_cache_type
to
DEMAND
and then add the
SQL_CACHE
hint to those queries you want to cache. Although this
requires you to do more work, it gives you very fine-grained control over the cache.
Conversely, if you want to cache most queries and exclude just a few, you can add
SQL_NO_CACHE
to them.
Alternatives to the Query Cache
The MySQL query cache works on the principle that the fastest query is the one you
don't have to execute, but you still have to issue the query, and the server still needs to
do a little bit of work. What if you really didn't have to talk to the database server at
all for particular queries? Client-side caching can help ease the workload on your
MySQL server even more. We explain caching more in
Chapter 14
.
