Fast query cache

Prerequisites

• The instance must run MySQL 5.7 with minor engine version 20200331 or later.

• The dedicated proxy service must be disabled on the instance.

Background information

The query cache improves performance by caching query result sets. Its core principles are as follows:

• Caches result sets: For eligible queries, the results are cached directly. This avoids re-running SQL analysis, optimization, and execution, reducing CPU overhead.

• Accelerates queries: By reducing the use of computing resources, it significantly improves response times for frequent, simple queries.

Drawbacks of native MySQL query cache

The native MySQL query cache performs poorly in high-concurrency scenarios due to design flaws. Specific issues include:

• Poor concurrency handling: In multi-core environments, performance can degrade significantly as concurrency increases.

• Inefficient memory management: It suffers from low memory utilization and delayed reclamation, wasting memory.

• Performance degradation: When the cache hit ratio is low, performance stagnates or even degrades significantly.

Due to these issues, MySQL 8.0 completely removed the native query cache, and it is disabled by default in earlier versions.

Innovations in Alibaba Cloud Fast Query Cache

To address the drawbacks of the native query cache, the Alibaba Cloud database team redesigned and implemented Fast Query Cache with the following core optimizations:

Unlike the native query cache, you can safely enable Fast Query Cache in various scenarios to improve query performance.

Enable fast query cache

Enable the fast query cache by configuring the query_cache_type and query_cache_size parameters in the RDS console.

The fast query cache requires additional memory. When you enable this feature, we recommend that you also adjust the innodb_buffer_pool_size parameter. Follow these steps:

1. Set innodb_buffer_pool_size to 90% of its original value and allocate the freed 10% of space to query_cache_size. For example, if the original value is {DBInstanceClassMemory*7/10}, change it to {DBInstanceClassMemory*63/100}. For more information, see Change the buffer pool size of an instance.

2. Set the query_cache_size parameter. For more information, see Set instance parameters.

   • If you can estimate the result set size, set the query_cache_size parameter to 20% * result set size.

   • If you cannot accurately estimate the result set size, set the query_cache_size parameter to 10% * innodb_buffer_pool_size.

   Note

   Changing instance specifications does not automatically adjust the query_cache_size parameter. You must update this parameter manually.

3. Set the query_cache_type parameter to 1 to enable the fast query cache. For more information, see Set instance parameters.

Performance comparison

This benchmark compares the queries per second (QPS) of three configurations under identical conditions: QC-OFF (with the query cache disabled), MySQL-QC (with the native MySQL query cache enabled), and Fast-QC (with the Fast Query Cache enabled).

• Test environment: A 4-core, 8 GB dedicated instance

• Benchmark tool: Sysbench

• Dataset: 250 MB (25 tables, each with 40,000 records)

• Scenario 1: 100% cache hit ratio (read-only)

  This scenario uses the Sysbench oltp_point_select test, which consists of only point select queries on a primary key. The query cache size is set to 512 MB, which is larger than the dataset, ensuring that all queries hit the cache. This test measures the performance gains at various concurrency levels.

  Table 1. QPS for read-only workload with 100% cache hit ratio

  Concurrent queries	QC-OFF	MySQL-QC (improvement over QC-OFF)	Fast-QC (improvement over QC-OFF)
  1	8,093	8,771 (8.38%)	9,261 (14.43%)
  8	62,262	65,686 (5.50%)	75,313 (20.96%)
  16	97,083	73,027 (-24.78%)	139,323 (43.51%)
  32	97,337	60,567 (-37.78%)	200,978 (106.48%)
  64	106,283	60,216 (-43.34%)	221,659 (108.56%)
  128	107,781	62,844 (-41.69%)	231,409 (114.70%)
  256	106,694	63,832 (-40.17%)	222,187 (108.25%)
  512	101,733	64,866 (-36.24%)	203,789 (100.32%)
  1024	89,548	62,291 (-30.44%)	203,542 (127.30%)

  

  Note

  The results show that under high concurrency, the performance of the native MySQL query cache degrades significantly. In contrast, Fast Query Cache shows no performance degradation and more than doubles the QPS at its peak.

• Scenario 2: High cache hit ratio (read-only)

  The test uses the Sysbench oltp_read_only workload, which includes range queries that return multiple records. The query cache is set to 512 MB to ensure sufficient memory, achieving a cache hit ratio of over 80%. This scenario measures performance gains at different concurrency levels.

  Table 2. QPS for read-only workload with a high cache hit ratio

  Concurrent queries	QC-OFF	MySQL-QC (improvement over QC-OFF)	Fast-QC (improvement over QC-OFF)
  1	5,099	6,467 (26.83%)	7,022 (37.71%)
  8	28,782	28,651 (-0.46%)	45,017 (56.41%)
  16	35,333	31,099 (-11.98%)	66,770 (88.97%)
  32	34,864	27,610 (-20.81%)	67,623 (93.96%)
  64	35,503	27,518 (-22.49%)	75,981 (114.01%)
  128	35,744	27,733 (-22.41%)	80,396 (124.92%)
  256	35,685	27,738 (-22.27%)	80,925 (126.78%)
  512	35,308	27,398 (-22.40%)	79,323 (124.66%)
  1024	34,044	26,861 (-22.10%)	75,742 (122.48%)

  

  Note

  The results show that as concurrency increases, the performance of the native MySQL query cache degrades significantly. In contrast, Fast Query Cache scales with concurrency, more than doubling the QPS at its peak.

• Scenario 3: Low cache hit ratio (read-only)

  The test uses the Sysbench oltp_read_only workload, which includes range queries. The query cache is set to only 16 MB, creating a memory-constrained environment. This results in a low cache hit ratio of around 10% and frequent cache eviction. This scenario measures the performance degradation under these conditions.

  Table 3. QPS for read-only workload with a low cache hit ratio

  Concurrent queries	QC-OFF	MySQL-QC (improvement over QC-OFF)	Fast-QC (improvement over QC-OFF)
  1	5,004	4,727 (-5.54%)	5,199 (3.90%)
  8	28,795	22,542 (-21.72%)	28,578 (-0.75%)
  16	35,455	24,064 (-32.13%)	35,682 (0.64%)
  32	34,526	21,330 (-38.22%)	35,871 (3.90%)
  64	35,514	19,791 (-44.27%)	36,051 (1.51%)
  128	35,983	19,519 (-45.75%)	36,253 (0.75%)
  256	35,695	19,168 (-46.30%)	36,337 (1.80%)
  512	35,182	18,420 (-47.64%)	35,972 (2.25%)
  1024	33,915	20,168 (-40.53%)	34,546 (1.86%)

  

  Note

  The results show that the native MySQL query cache's performance degrades by nearly 50% at its worst. In contrast, Fast Query Cache is optimized for low-hit-ratio scenarios and introduces almost no performance overhead.

• Scenario 4: Mixed read/write workload

  The test uses the Sysbench oltp_read_write workload. Each transaction includes update operations, which frequently invalidate the cache and trigger cache eviction. This scenario measures performance degradation in a mixed read/write workload.

  Table 4. QPS for a mixed read/write workload

  Concurrent queries	QC-OFF	Fast-QC (improvement over QC-OFF)
  1	4,152	4,098 (-1.30%)
  8	21,359	21,195 (-0.77%)
  16	26,020	25,548 (-1.81%)
  32	27,595	26,996 (-2.17%)
  64	29,229	28,733 (-1.70%)
  128	29,265	28,828 (-1.49%)
  256	29,911	29,616 (-0.99%)
  512	29,148	28,816 (-1.14%)
  1024	29,204	28,824 (-1.30%)

  

  Note

  The results show that Fast Query Cache causes minimal performance degradation in mixed read/write scenarios.

Best practices

Use cases

Fast Query Cache is designed to improve performance for read-intensive workloads. We recommend enabling it in the following scenarios:

• Read-heavy workloads with infrequent write operations, such as e-commerce product detail pages or reporting queries.

• Use theSQL_CACHE hint to explicitly enable caching for specific tables with a high read-to-write ratio. You can identify these tables by checking their read and write statistics in the TABLE_STATISTICS table. For details on querying the TABLE_STATISTICS table, see Performance Insight.

We do not recommend enabling Fast Query Cache in the following scenarios:

• Write-heavy workloads, such as high-frequency transaction systems. Frequent cache invalidations can lead to performance degradation.

• Applications that require real-time data, such as stock tickers, because cached data can become stale.

• Before you enable the feature globally, check the InnoDB buffer pool hit ratio. We do not recommend enabling it if the hit ratio is below 80%. The hit ratio is calculated using the following formula: hit ratio = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests.

Cache modes (query_cache_type)

The query_cache_type parameter can be modified at the session-level, enabling flexible configuration based on your workload. Consider the following recommendations:

Cache size (query_cache_size)

The optimal value for the query_cache_size parameter depends on your SQL workload. If you cache queries that return large result sets, the required cache size might be several times the data size. If your queries do not include range queries, you can use the following test to evaluate the relationship between data size and the query_cache_size parameter.

• Test environment: A 4-core, 8 GB dedicated instance (innodb_buffer_pool_size = 6 GB)

• Benchmark tool: Sysbench

• Data size: 10 GB (100 tables, 400,000 records each)

The test scenario uses the Sysbench oltp_point_select workload with 64 concurrent threads and a special distribution that includes 20% hot data. This test measures the performance impact of different query_cache_size values. For this dataset, the total size of the full result set is 2.5 GB.

Fast Query Cache does not cause performance degradation regardless of the query_cache_size setting. For primary key lookups, performance improves at all hit ratios, with significant gains when the hit ratio exceeds 90%. For range queries or sorting queries with an Order By clause, even a hit ratio below 90% saves substantial CPU resources and significantly boosts performance.