The query cache stores result sets of SELECT statements in memory so that identical queries bypass parsing and execution entirely — the result is returned directly from cache. This reduces CPU utilization, lowers IOPS, and shortens query response times for read-heavy, low-write workloads with stable data. For high-write workloads or varied query patterns, the overhead of cache invalidation can reduce overall throughput, so test with your actual workload before enabling in production.
When to use the query cache
The query cache works best when all of the following are true:
-
Table data changes infrequently or is static.
-
The same SELECT statement runs repeatedly.
-
Query result sets are under 1 MB.
The query cache is not always beneficial. For workloads with frequent table writes or varied queries, the overhead of invalidating and repopulating the cache can reduce overall throughput. Test with your actual workload before enabling in production.
How it works
-
ApsaraDB RDS for MySQL computes a hash value for each SELECT statement received from a client.
-
If the hash matches a cached entry, the result is returned immediately — the query is never parsed or executed.
-
If there is no match, the query runs normally and the hash and result are stored in the cache.
-
When any table changes, all cached queries that reference that table are immediately invalidated and removed from the cache.
Limitations
The following queries are not cached:
-
Queries that differ only in letter case, whitespace, database context, protocol version, or character set are treated as distinct queries and cached separately.
-
Subquery result sets — only the final result set of the outer query is cached.
-
Queries executed inside a stored function, stored procedure, trigger, or event.
-
Queries that call any of these non-deterministic functions:
now(),curdate(),last_insert_id(), orrand(). -
Queries that reference tables in the
mysql,information_schema, orperformance_schemadatabase. -
Queries that reference temporary tables.
-
Queries that produce warnings.
-
Queries containing
SELECT … LOCK IN SHARE MODE,SELECT … FOR UPDATE, orSELECT * FROM … WHERE AUTOINCREMENT_COL IS NULL. -
Queries that reference a user-defined variable.
-
Queries using the
SQL_NO_CACHEhint.
Configure the query cache
Set these three parameters in the ApsaraDB RDS console:
| Parameter | Default | Description |
|---|---|---|
query_cache_type |
— | Controls whether the cache is active. 0 = off; 1 = on (queries using SELECT SQL_NO_CACHE are excluded); 2 = on demand only (only SELECT SQL_CACHE queries are cached). |
query_cache_size |
3 MB | Total memory allocated to the cache, in bytes. Must be a multiple of 1024. |
query_cache_limit |
1 MB | Maximum result set size for a single cached query, in bytes. Results larger than this value are not cached. |
In the left-side navigation pane, click Parameters. In the parameter list, find query_cache_type and change its value to the target value. The change takes effect only after the instance restarts. You can also modify related parameters such as query_cache_limit (valid values: 1 to 1048576) and query_cache_size (valid values: 0 to 104857600) as needed. Click the information icon next to a parameter to view its description.
Changing query_cache_type triggers an automatic restart of the RDS instance. If you set query_cache_size to a value that is not a multiple of 1024, the console returns the The specified parameter is invalid error, and the error code is InvalidParameters.Malformed.
Enable and disable the cache
The cache is enabled when query_cache_size > 0 and query_cache_type is 1 or 2.
The cache is disabled when query_cache_size = 0 or query_cache_type = 0.
Recommendations
-
Do not set
query_cache_sizetoo large. An oversized cache not only takes memory away from other memory structures of the instance, but also increases the overhead of cache lookups. Start with a value between 10 MB and 100 MB based on your instance type, then adjust it based on observed usage. -
To turn the query cache on or off, adjust the value of
query_cache_sizerather thanquery_cache_type, because changes toquery_cache_typetake effect only after an instance restart. -
The query cache benefits only specific workloads. Test thoroughly with your actual workload before you enable it, to avoid performance degradation or other issues.
Monitor and verify cache performance
Check cache statistics
Run the following statement to check cache usage:
SHOW GLOBAL STATUS LIKE 'Qcache%';The output includes these status variables:
| Variable | Description |
|---|---|
Qcache_hits |
Number of queries served from the cache |
Qcache_inserts |
Number of queries added to the cache |
Qcache_not_cached |
Number of queries that could not be cached |
Qcache_queries_in_cache |
Number of queries currently in the cache |
Sample output:
mysql>show global status like 'Qca%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 2763 |
| Qcache_free_memory | 10115160 |
| Qcache_hits | 365589713 |
| Qcache_inserts | 612280336 |
| Qcache_lowmem_prunes | 1257159 |
| Qcache_not_cached | 1805250864 |
| Qcache_queries_in_cache | 9318 |
| Qcache_total_blocks | 22409 |
+-------------------------+------------+Verify using the ApsaraDB RDS console
In the left-side navigation pane, choose Autonomy Service (formerly CloudDBA) > Performance Trends. View the MySQL CPU/Memory Utilization chart and confirm that both CPU utilization and memory utilization stay within the normal range.