ApsaraDB RDS:Fast query cache

Why not use the native MySQL query cache?

The native MySQL query cache uses a global lock on all cache operations. Under high concurrency, this lock becomes a bottleneck: adding more CPU cores can actually reduce QPS rather than increase it. Poor memory management compounds the problem — fragmentation accumulates, reclamation is slow, and low cache hit ratios cause significant performance degradation. These issues led MySQL to remove the native query cache entirely in MySQL 8.0 and disable it by default in earlier versions.

How Fast Query Cache addresses these problems

Alibaba Cloud redesigned the query cache from the ground up:

Parameters

Steps

Because Fast Query Cache consumes extra memory, reconfigure innodb_buffer_pool_size first to free up space:

1. Reduce innodb_buffer_pool_size to 90% of its current value. The 10% you free up becomes available for the query cache. For example, if the current value is {DBInstanceClassMemory*7/10}, set it to {DBInstanceClassMemory*63/100}. For details, see Change the size of the InnoDB buffer pool.

2. Set query_cache_size based on your workload. For details on modifying parameters, see Modify instance parameters.

   • If you know the result set size: set query_cache_size to 20% of the result set size.

   • If you don't know the result set size: set query_cache_size to 10% of `innodb_buffer_pool_size`.

   Important

   When you resize your RDS instance, query_cache_size does not scale automatically. Reconfigure it immediately after the specification change takes effect.

3. Set query_cache_type to 1 to enable Fast Query Cache. For details, see Modify instance parameters.

Test at the session level

Before changing the global configuration, test Fast Query Cache on a single connection without affecting other users:

-- Enable Fast Query Cache for this session only
SET SESSION query_cache_type = 1;

-- Disable Fast Query Cache for this session only
SET SESSION query_cache_type = 0;

All-hit read workload (100% cache hit ratio, 512 MB cache)

Uses the oltp_point_select script with POINT SELECT statements on primary keys.

Table 1. QPS for read queries with a cache hit ratio of 100%

As concurrency increases, the native MySQL query cache QPS drops sharply — by up to 43% at 64 threads. Fast Query Cache QPS continues to climb, exceeding QC-OFF by up to 127% at 1,024 threads.

High hit-rate read workload (>80% cache hit ratio, 512 MB cache)

Uses the oltp_read_only script with range queries that return multiple records.

Table 2. QPS for read queries with a cache hit ratio higher than 80%

Fast Query Cache QPS continues to increase with concurrency and peaks at over 124% above QC-OFF. The native MySQL query cache degrades to 22% below QC-OFF at high concurrency.

Low hit-rate read workload (~10% cache hit ratio, 16 MB cache)

Uses the oltp_read_only script. A 16 MB cache is far smaller than the data set, causing frequent evictions and a hit ratio near 10%.

Table 3. QPS for read queries with a cache hit ratio of about 10%

The native MySQL query cache loses up to 48% QPS in low hit-rate conditions. Fast Query Cache stays within 1–4% of QC-OFF, adding virtually no overhead even when the cache is mostly missing.

Mixed read/write workload

Uses the oltp_read_write script with frequent table updates that constantly invalidate cached entries.

Table 4. QPS for read and write queries

Fast Query Cache reduces QPS by at most 2.17% in mixed read/write workloads — the incremental invalidation mechanism keeps cache maintenance overhead low even under constant writes.

Use Fast Query Cache when

• Your workload is read-intensive with low write frequency — for example, e-commerce product detail pages or reporting queries.

• You want to cache specific tables with high read/write ratios. Check the TABLE_STATISTICS table to identify these tables, then use the SQL_CACHE keyword with query_cache_type = 2 to cache only those queries. For details on querying TABLE_STATISTICS, see Performance Insight.

Before enabling Fast Query Cache globally, check your InnoDB Buffer Pool hit ratio:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';

Hit ratio = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

If the InnoDB Buffer Pool hit ratio is below 80%, the buffer pool is already under memory pressure. Enabling Fast Query Cache in this state — which requires reducing innodb_buffer_pool_size — is likely to hurt performance. Increase the instance memory or optimize queries before enabling the cache.

Avoid Fast Query Cache when

• Write-intensive workloads: Frequent writes cause constant cache invalidation, adding overhead with little caching benefit. For high-frequency transaction systems, keep query_cache_type = 0.

• Real-time data requirements: Cached results may lag behind live data. For use cases like financial market data where stale reads are unacceptable, disable the cache or use SQL_NO_CACHE per query.

Choose the right query_cache_type value

query_cache_type supports session-level changes, so you can adjust caching behavior per connection without modifying the global setting:

SET SESSION query_cache_type = 1;  -- enable for this session
SET SESSION query_cache_type = 0;  -- disable for this session