The fast query cache is a query cache that is developed by Alibaba Cloud based on the native query cache of MySQL. The fast query cache uses a new design and an implementation mechanism to improve the query performance of your PolarDB clusters.

Prerequisites

The version of the PolarDB cluster is ApsaraDB PolarDB MySQL-compatible edition 8.0 and the revision version of the cluster is 8.0.1.1.5 and later. For how to confirm the cluster version, see Query the kernel version number.

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. It caches each statement that meets the conditions and its result set. When the statement is hit by the next query, the cached result set is returned from the query cache. This eliminates the need to analyze, optimize, and execute the statement again. Fast query cache saves CPU resources and accelerates queries.

The native query cache of MySQL has the following issues in the 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 and a waste of memory.
  • 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. The fast query cache is developed and optimized by PolarDB based on the native query cache of MySQL. It has the following benefits:

  • Optimized concurrency control

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

  • Optimized caching

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

Enable the fast query cache

The fast query cache memory capacities that are provided by PolarDB vary based on different cluster specifications. To enable the fast query cache, you need to specify only the loose_query_cache_type parameter. For more information about how to specify this parameter, see Specify cluster parameters.

Performance comparison

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

  • Test environment
    • The PolarDB cluster is of ApsaraDB PolarDB MySQL-compatible edition 8.0 Cluster Edition and has 8 CPU cores and 64 GB memory.
    • The memory capacity for the fast query cache is 4 GB.
  • Test tool

    Sysbench

  • Test data amount
    • 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 is enabled, the cache hit ratio ranges from 63% to 99% and the highest QPS of the cluster increases by 53% to 106% in the test cases. The test cases include case 1, case 3, case 4, case 5, case 6, and case 7. The memory usage is high when the 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. This indicates that the fast query cache has little impact on the QPS in concurrency scenarios. When the cluster processes a large number of concurrent reads and writes, 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_only1
    • Case 2: 25 tables x 40,000 rows, rand-type = uniform oltp_read_only2
    • Case 3: 25 tables × 40,000 rows, rand-type = special oltp_point_select3
    • Case 4: 25 tables × 40,000 rows, rand-type = uniform oltp_point_select4
    • Case 5: 25 tables × 400,000 rows, rand-type = special oltp_read_only5
    • Case 6: 25 tables × 400,000 rows, rand-type = uniform oltp_read_only6
    • Case 7: 25 tables × 400,000 rows, rand-type = special oltp_point_select7
    • Case 8: 25 tables × 400,000 rows, rand-type = uniform oltp_point_select8
    • Case 9: 25 tables × 400,000 rows, rand-type = special oltp_read_write9

Practice guidelines

  • Applicable scenarios
    • The fast query cache is used to increase the QPS for reads. We recommend that you enable the fast query cache in the scenarios that require more reads than writes. You can also use the SQL_CACHE keyword to enable the fast query cache for the table that receives 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, the QPS of the cluster may decrease by 2% or less.
    • The memory usage is high when the fast query cache is enabled. This applies to the scenarios that require fewer updates and receive large amounts of concurrent reads. This significantly improves the performance for concurrent reads.
  • Manage the fast query cache by using the loose_query_cache_type parameter

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

    Parameter Value Description
    loose_query_cache_type OFF The fast query cache is disabled. This value is the default value.
    ON By default, the 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 the fast query cache.
    DEMAND By default, the fast query cache is not used for data queries. However, you can use the SQL_CACHE keyword to enable the 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 the fast query cache for specific statements.
  • Manage the lease time of the fast query cache by using the query_cache_lease_time parameter

    Fast query cache dynamically reclaims the query cache memory. This reduces the used memory. If a query cache that has not expired 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 of the query cache is reclaimed. The default value is 3600, which specifies one hour.