PolarDB:Fast Query Cache

Limits

Issues and solutions

A query cache is a cache policy that helps you 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 another query, 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 related to 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 at the earliest opportunity. 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. Instead, the fast query cache feature is developed and optimized for PolarDB based on the native query cache of MySQL. The feature has the following benefits:

• Optimized concurrency control

  The global locking mechanism used in MySQL query cache is deprecated. The fast query cache feature 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. The fast query cache feature 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

  The fast query cache feature 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 the fast query cache feature of PolarDB to improve query performance based on your business requirements.

Enable the fast query cache feature

Performance comparison

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

• Test environment

  • A PolarDB for MySQL 8.0 Cluster Edition cluster that has 8 cores and 64 GB of memory is used in the test.

  • 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 the fast query cache feature 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. If you enable the fast query cache feature, the memory usage is high. 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 the fast query cache feature 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 only to 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

  • The fast query cache feature is used to increase the QPS for reads. We recommend that you enable the fast query cache feature in scenarios that handle more reads than writes. You can also use the SQL_CACHE keyword to enable the fast query cache feature 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 the fast query cache feature, the query performance of your cluster may decrease by about 2%.

  • If you enable the fast query cache feature, the memory usage is high. This applies to scenarios that require fewer updates and receive large amounts of concurrent reads. The feature can significantly improve the query performance of concurrent reads.

• Manage the fast query cache feature by using the loose_query_cache_type parameter

  You can manage MySQL query cache 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	The fast query cache feature is disabled. This value is the default value.
  	ON	By default, the fast query cache feature is used for data queries. However, you can specify the SQL_NO_CACHE keyword to not allow the cluster to retrieve results from fast query cache.
  	DEMAND	By default, the fast query cache feature is not used for data queries. However, you can use the SQL_CACHE keyword to enable the fast query cache feature 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 the fast query cache feature for specific statements. Sample code:

    SELECT SQL_CACHE id, name FROM customer;

• Manage the lease time of fast query cache by using the query_cache_lease_time parameter

  The fast query cache feature 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 period of time (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 1 hour.

Compatibility

The fast query cache feature is compatible with the global consistency (high-performance mode) feature. If the MTT optimization feature for global consistency (high performance mode) has been enabled and both the fast query cache and global consistency (high performance mode) features are enabled, the MTT optimization feature becomes invalid. For more information about the global consistency (high-performance mode) feature, see Overview.