Fast query cache is a PolarDB-native cache engine that improves queries per second (QPS) for read-heavy workloads. It caches result sets and serves repeated queries directly from memory—without re-parsing, re-optimizing, or re-executing SQL.
How it works
When a query runs, fast query cache stores its result set. If the same query runs again before the underlying data changes, the cached result is returned immediately. The cache is invalidated automatically when data is modified.
Fast query cache solves three core limitations of the MySQL native query cache:
| Limitation | How fast query cache addresses it |
|---|---|
| Concurrency bottleneck: The MySQL native query cache uses a global lock that serializes access under high concurrency, causing performance to degrade further as CPU core count increases. | Fast query cache replaces the global lock with a lock-free synchronization mechanism, letting multiple CPU cores handle queries in parallel without contention. |
| Poor memory reclamation: The MySQL native query cache pre-allocates a fixed memory block and cannot reclaim it efficiently, leaving memory underutilized. | Fast query cache uses dynamic memory allocation to reclaim invalid cache entries immediately, keeping memory usage lean and flexible. |
| Performance degradation at low hit ratios: The MySQL native query cache adds overhead that can slow queries when the cache hit ratio is low. | Fast query cache monitors cache hit ratios in real time and adjusts its caching policy dynamically to maintain stable performance even when hit rates drop or the workload mixes reads and writes. |
The MySQL native query cache was removed in MySQL 8.0. Fast query cache is the replacement built specifically for PolarDB.
Supported versions
Fast query cache is available on the following PolarDB for MySQL revision versions:
| PolarDB version | Minimum revision version |
|---|---|
| PolarDB for MySQL 8.0 | 8.0.1.1.5 |
| PolarDB for MySQL 5.7 | 5.7.1.0.15 |
| PolarDB for MySQL 5.6 | 5.6.1.0.29 |
To check your cluster's revision version, see Query the engine version.
When to use fast query cache
Fast query cache delivers the most benefit when:
Your workload is read-heavy — significantly more reads than writes.
Queries repeat frequently, keeping the cache hit ratio high (above ~60%).
Large numbers of concurrent read requests benefit from in-memory serving.
Avoid enabling fast query cache, or set it to DEMAND mode, when:
Write throughput is high or data changes frequently. Enabling the cache in this scenario may reduce QPS by approximately 2%.
Few queries repeat, so the cache hit ratio stays low.
Enable fast query cache
Each PolarDB cluster specification includes a pre-allocated memory capacity for fast query cache. To enable the feature, set the appropriate parameter in your cluster or node configuration. For instructions, see Specify cluster and node parameters.
| PolarDB version | Parameter | Value to set |
|---|---|---|
| PolarDB for MySQL 8.0 | loose_query_cache_type | ON |
| PolarDB for MySQL 5.6 or 5.7 | query_cache_type | 1 |
Manage fast query cache
Control caching behavior with loose_query_cache_type
Use loose_query_cache_type to control whether fast query cache is always active, optional, or disabled. Set this parameter at the cluster level, or override it at the session level for specific workloads.
| Value | Behavior |
|---|---|
OFF (default) | Fast query cache is disabled. |
ON | Fast query cache is active for all queries. Add SQL_NO_CACHE to a query to exclude it from caching. |
DEMAND | Fast query cache is inactive by default. Add SQL_CACHE to a query to cache it. |
Choose the mode that matches your workload:
High write throughput or frequent data updates — use
OFFto avoid the caching overhead.Many repeated slow queries or high cache hit ratio — use
ONto maximize QPS gains.Mixed workload where only some queries benefit — use
DEMAND, then addSQL_CACHEto the queries that repeat frequently:
SELECT SQL_CACHE id, name FROM customer;Control cache entry lifetime with query_cache_lease_time
Fast query cache reclaims memory dynamically. If a cached result set goes unreferenced for the duration specified by query_cache_lease_time (in seconds), the cache entry is released and its memory is reclaimed.
The default value is 3600 seconds (1 hour).
Performance benchmarks
The following benchmark shows the QPS impact of enabling fast query cache (PolarDB-QC) versus disabling it (QC-OFF).
Test environment:
| Parameter | Value |
|---|---|
| Cluster | PolarDB for MySQL 8.0 Cluster Edition, 8 cores, 64 GB memory |
| Cache memory | 4 GB |
| Test tool | Sysbench |
| Test data | 25 tables × 40,000 rows; 25 tables × 400,000 rows |
| Test cases | oltp_read_only, oltp_point_select, oltp_read_write with rand-type = special or uniform |
All results are based on the primary node only.
Results summary:
| Scenario | Cache hit ratio | QPS change |
|---|---|---|
| High hit ratio (Cases 1, 3, 4, 5, 7) | 63%–99% | +53% to +106% |
| Low hit ratio (Cases 2, 6) | Low | -3% or less |
| Mixed read-write traffic | — | -2% or less |
Case 7 (special oltp_point_select, 400,000 rows) reaches a 99% cache hit ratio and shows the largest QPS improvement.
Test cases and charts:
Case 1: 25 tables × 40,000 rows, rand-type = special, oltp_read_only
Case 2: 25 tables × 40,000 rows, rand-type = uniform, oltp_read_only
Case 3: 25 tables × 40,000 rows, rand-type = special, oltp_point_select
Case 4: 25 tables × 40,000 rows, rand-type = uniform, oltp_point_select
Case 5: 25 tables × 400,000 rows, rand-type = special, oltp_read_only
Case 6: 25 tables × 400,000 rows, rand-type = uniform, oltp_read_only
Case 7: 25 tables × 400,000 rows, rand-type = special, oltp_point_select
Case 8: 25 tables × 400,000 rows, rand-type = uniform, oltp_point_select
Case 9: 25 tables × 400,000 rows, rand-type = special, oltp_read_write
Compatibility
Fast query cache is compatible with the global consistency (high-performance mode) feature. However, if both fast query cache and global consistency (high-performance mode) are active simultaneously and the MTT optimization for global consistency (high-performance mode) is also enabled, the MTT optimization becomes inactive.
For details about global consistency (high-performance mode), see Overview.