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

Limits

Your PolarDB cluster must be one of the following versions:
  • A PolarDB for MySQL 8.0 cluster whose revision version is 8.0.1.1.5 or later
  • A PolarDB for MySQL 5.6 cluster whose revision version is 5.6.1.0.29 or later. For information about 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.

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.

  • 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

PolarDB provides different Fast Query Cache memory capacities for different cluster specifications. To enable 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

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
    • The PolarDB cluster is of PolarDB for MySQL 8.0 Cluster Edition and has 8 CPU cores and 64 GB memory.
    • The memory capacity for Fast Query Cache is 4 GB.
  • Test tools

    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 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 test cases 1–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. This indicates that 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
    • Fast Query Cache is used to increase the QPS for reads. We recommend that you enable Fast Query Cache in scenarios that require 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 QPS of your cluster may decrease by 2% or less.
    • 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 Type 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 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 of the query cache is reclaimed. The default value is 3600, which is equivalent to one hour.