PolarDB for MySQL:Fast query cache

Limits

Issues and solutions

A query cache is a cache policy that allows you to reduce the response time of queries and optimize queries by saving CPU resources. Query cache is designed to cache the result sets obtained from each query statement that meets conditions. If the result sets are hit by queries again, the cached result sets are read and returned from the query cache. This eliminates the need to analyze, optimize, and execute the statement again.

The native query cache of MySQL has the following issues in design and implementation:

• The native query cache of MySQL cannot process a large number of concurrent queries at a high speed. In high-concurrency scenarios, the processing speed may be further reduced if multiple CPU cores are configured.
• The native query cache of MySQL cannot properly manage memory resources or reclaim memory resources in time. This results in low memory usage.
• If the cache hit ratio is low, query performance cannot be improved and may be reduced.

Therefore, the native query cache of MySQL is not widely used. This feature is no longer provided in MySQL 8.0. Fast query cache is developed and optimized for PolarDB based on the native query cache of MySQL. It has the following benefits:

• Optimized concurrency control

  The global locking mechanism used in MySQL query cache is deprecated. Fast query cache uses a new lock-free mechanism to synchronize data. This ensures the capabilities of multiple CPU cores and the performance in high concurrency scenarios.

• Optimized memory management

  The memory pre-allocation mechanism used in the native MySQL query cache is deprecated. Fast query cache uses a dynamic memory allocation mechanism that is more flexible. This mechanism allows you to quickly reclaim invalid memory resources and ensures the effective usage of memory resources.

• Optimized caching

  Fast query cache dynamically detects cache usage and adjusts the cache policy in real time. This ensures stable query performance in scenarios when the cache hit ratio is low or the cluster processes both read and write requests.

You can enable fast query cache of PolarDB to improve query performance based on your business requirements.

Enable fast query cache

Performance comparison

When other conditions are the same, check the queries per second (QPS) when fast query cache is enabled (PolarDB-QC scenario) or disabled (QC-OFF scenario).

• Test environment
  • A PolarDB for MySQL 8.0 Cluster Edition Edition cluster that has 8 CPU cores and 64 GB memory is used.
  • The memory capacity for fast query cache is 4 GB.
• Test tool

  Sysbench

• Test data
  • 25 tables with 40,000 rows in each table.
  • 25 tables with 400,000 rows in each table.
• Test cases

  Use the following built-in sysbench cases to test the QPS when the rand-type parameter is set to special or uniform.

  • oltp_read_only
  • oltp_point_select
  • oltp_read_write
• Test results and description

  When the cache hit ratio is high and the number of concurrent queries is large, the QPS of the cluster significantly increases. After fast query cache is enabled, the cache hit ratio ranges from 63% to 99% and the QPS of the cluster increases by 53% to 106% in Cases 1, 3, 4, 5, and 7. The memory usage is high when fast query cache is enabled. In Case 7, the cache hit ratio reaches 99% when the amount of test data is large in the special oltp_point_select test. In this case, the QPS significantly increases. When the cache hit ratio is low, the QPS of the cluster decreases by 3% or less in Cases 2 and 6. This indicates that fast query cache has little impact on the QPS in concurrency scenarios. When the cluster processes a large number of concurrent read and write requests, the QPS of the cluster decreases by 2% or less.

  Note The following test data is related to only the primary node of the cluster.
  • 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

Practice guidelines

• Applicable scenarios
  • Fast query cache is used to increase the QPS for reads. We recommend that you enable fast query cache in scenarios that handle more reads than writes. You can also use the SQL_CACHE keyword to enable fast query cache for tables that receive more read requests than write requests. If your PolarDB cluster processes a small number of read requests but a large number of write requests, the data of your PolarDB cluster is frequently updated. In this case, if you enable fast query cache, the query performance of your cluster may decrease by about 2%.
  • The memory usage is high when fast query cache is enabled. This applies to scenarios that require fewer updates and receive large amounts of concurrent reads. Fast query cache can significantly improve the query performance for concurrent reads.
• Manage fast query cache by using the loose_query_cache_type parameter

  MySQL query cache is managed based on the loose_query_cache_type parameter. You can also use this parameter to manage fast query cache.

  Parameter	Value	Description
  loose_query_cache_type	OFF	Fast query cache is disabled. This value is the default value.
  	ON	By default, fast query cache is used for data queries. However, you can specify the SQL_NO_CACHE keyword to allow the cluster not to retrieve results from fast query cache.
  	DEMAND	By default, fast query cache is not used for data queries. However, you can use the SQL_CACHE keyword to enable fast query cache for specified statements.

  Note

  You can specify the loose_query_cache_type parameter for a specific session based on your business requirements.

  • If your PolarDB cluster processes a small number of read requests but a large number of write requests or the cluster data is frequently updated, set loose_query_cache_type to OFF.
  • If your PolarDB cluster processes a large number of repeated slow queries or the cache hit ratio is high, set loose_query_cache_type to ON.
  • If your PolarDB cluster processes a small number of repeated queries, set loose_query_cache_type to DEMAND. You can use the SQL_CACHE keyword to enable fast query cache for specific statements.
• Manage the lease time of fast query cache by using the query_cache_lease_time parameter

  Fast query cache dynamically reclaims the query cache memory. This reduces the amount of memory resources occupied by the cache mechanism. If an unexpired query cache is not hit by queries during the time period (in seconds) that is specified by query_cache_lease_time, the cache is released after the lease time is reached. In this case, the memory resources of the query cache are reclaimed. The default value is 3600, which is equivalent to one hour.