First, confirm that the MySQL version supports query cache (only supported by 5.7 and earlier), and check have_query_cache, query_cache_type and query_cache_size through SHOW VARIABLES to ensure that they are available and enabled; 2. Set query_cache_size reasonably, and it is recommended that the OLTP system use 64MB to 256MB to avoid memory fragmentation or contention due to excessive size; 3. Use query_cache_type = 2 (DEMAND mode), and only cache high-frequency queries that clearly mark SQL_CACHE to avoid resource waste; 4. Adjust query_cache_min_res_unit to reduce fragmentation, monitor Qcache_free_blocks to evaluate fragmentation, and execute FLUSH QUERY if necessary CACHE organizes memory; 5. Understand the limitations of query cache, including lock contention under high concurrency, cache failure caused by table writes, precise matching requirements, etc. If Qcache_lowmem_prunes is too high or the system writes more and reads less, query cache should be considered; it is finally recommended to configure query_cache_type = 2, query_cache_size = 128M, query_cache_min_res_unit = 1024, and combine the ratio between Qcache_hits and Qcache_inserts to evaluate efficiency, and give priority to more efficient performance improvement solutions such as application layer caching or index optimization.

MySQL's query cache can improve performance by storing the text of a SELECT statement along with the result set. If an identical query is received later, MySQL returns the cached result without executing the query again. However, proper configuration is essential to get benefits without causing overhead or memory contention.

Here's how to configure the MySQL query cache effectively for performance:

1. Check if Query Cache is Available and Enabled

First, verify that your MySQL version supports the query cache. Note that MySQL 8.0 removed the query cache entirely , so this only applies to versions 5.7 and earlier.

Run:

 SHOW VARIABLES LIKE 'have_query_cache';

If the value is YES , query cache is available.

Check current status:

 SHOW VARIABLES LIKE 'query_cache_type';
SHOW VARIABLES LIKE 'query_cache_size';

Ensure query_cache_type is set to ON (or 1 ) to enable caching.

2. Set the Query Cache Size Appropriately

The query_cache_size system variable controls how much memory is allocated for cached queries.

Example (in my.cnf or my.ini ):

 query_cache_size = 256M

Important considerations:

• Too small (eg, 0 or 1M): No real benefit.
• Too large (eg, >1G): Can cause memory fragmentation and mutex content, especially under high concurrency.
• Recommended range: 64MB to 256MB for most OLTP systems.

After setting, monitor the actual usage:

 SHOW STATUS LIKE 'Qcache%';

Key metrics:

• Qcache_hits : Number of cache hits.
• Qcache_inserts : Number of queries added to the cache.
• Qcache_lowmem_prunes : Number of times entries were removed due to memory shortage.

If Qcache_lowmem_prunes is high and growing fast, either increase the size or reevaluate usefulness.

3. Control What Gets Cached

Use query_cache_type to define caching behavior:

• 0 or OFF : No caching.
• 1 or ON : Cache all SELECTs except those marked with SQL_NO_CACHE .
• 2 or DEMAND : Only cache queries explicitly marked with SQL_CACHE .

For better control, use:

 query_cache_type = 2

Then explicitly cache only known, frequently used, expensive queries:

 SELECT SQL_CACHE * FROM reports WHERE month = '2024-01';

This avoids wasting memory on one-time or infrequency queries.

4. Tune Query Cache Fragmentation and Cleanup

Over time, the cache can become fragmented, reducing efficiency.

Key variables:

 query_cache_min_res_unit = 512

• This sets the minimum allocation unit for each query result.
• If you have many small queries, lower this (eg, 256 or 512 bytes).
• If you have large result sets, increase it (eg, 2K or 4K) to reduce fragmentation.

Monitor fragmentation:

 SHOW STATUS LIKE 'Qcache_free_blocks';

A high number of free blocks relative to total cache size indicates fragmentation.

To defragment (if needed):

 FLUSH QUERY CACHE;

(This doesn't clear data but reorganizes memory.)

Avoid RESET QUERY CACHE unless necessary—it clears all entries.

5. Understand the Limitations and Trade-offs

The query cache has known limitations:

• It's per-connection mutex locked , so under high concurrency, it can become a bottleneck.
• Any write to a table invalidates all cached queries that involve that table.
• It only works for exact text matches—whitespace or comments make queries different.
• Not useful in write-heavy workloads.

Because of these, even with tuning, the query cache may not help (or can hurt) performance in many real-world scenarios.

Summary: Recommended Configuration (for MySQL 5.7 and earlier)

 query_cache_type = 2
query_cache_size = 128M
query_cache_min_res_unit = 1024

And use SQL_CACHE only on stable, frequently executed, read-heavy queries.

Also:

• Monitor Qcache_hits / (Qcache_hits Qcache_inserts) to gauge efficiency.
• Consider disabling the query cache ( query_cache_type = OFF , query_cache_size = 0 ) if you're on a busy OLTP system or using MySQL 5.7 with high concurrency.

Keep in mind: For modern applications, application-level caching (eg, Redis, Memcached) or index optimization often provides better and more scalable performance than the MySQL query cache.

Basically, configure it carefully if you must use it—but don't rely on it as a primary performance fix.

The above is the detailed content of How to configure the MySQL query cache for performance?. For more information, please follow other related articles on the PHP Chinese website!