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 PolarDB for MySQL 8.0 and the revision version of the cluster is 8.0.1.1.5 and later. For more information about how to check the cluster version, see Query the kernel version.

Issues and fixes

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 stores each qualified statement and its result set. If an identical statement is received in the future, the database server can retrieve the result set 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 limits in terms of its 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 manage memory resources in a proper way or reclaim memory resources in a timely manner. This reduces resource utilization and wastes memory resources.
  • If the cache hit ratio is low, query performance cannot be improved and may be compromised.

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 of PolarDB is redesigned and implemented based on the native query cache of MySQL. The fast query cache has the following benefits:

  • Optimized concurrency control

    The native query cache of MySQL uses the global locking mechanism to synchronize data. The fast query cache uses a new lock-free synchronization mechanism. The new synchronization mechanism allows you to fully use the capabilities of multiple CPU cores and process a large number of concurrent queries at a fast speed.

  • Optimized memory management

    The memory preallocation mechanism in the native query cache of MySQL is removed. The fast query cache uses a dynamic memory allocation mechanism that is more flexible. This mechanism allows you to reclaim invalid memory resources in a timely manner and increase the utilization of memory resources.

  • Optimized caching

    The fast query cache dynamically detects cache usage and adjusts the cache policy in real time. This ensures stable query performance if the cache hit ratio is low or your PolarDB cluster processes both read and write requests.

You can enable the fast query cache of PolarDB in different business scenarios to improve query performance.

Enable the fast query cache

The memory space that is configured for the fast query cache of PolarDB varies based on cluster specifications. You need only to specify the loose_query_cache_type parameter to enable the fast query cache. For more information about how to specify this parameter, see Specify cluster parameters.

Performance comparison

In the same conditions, the queries per second (QPS) of a cluster are tested in QC-OFF and PolarDB-QC scenarios. 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 a standard PolarDB for MySQL 8.0 cluster that has 8 CPU cores and 64 GB memory.
    • The memory space for the fast query cache is 4 GB.
  • Test tool

    Sysbench

  • Test data amount
    • 25 tables and 40,000 rows in each table
    • 25 tables and 400,000 rows per table.
  • Test cases

    Use the following 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

    In the following test cases, when the cache hit ratio for fast queries 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. For the fast query cache, the utilization of memory resources 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 increased QPS for the cluster is high. When the cache hit ratio for fast queries is low, the QPS of the cluster decreases by 3% or less. This means that the 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_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

  • Enable the fast query cache in various scenarios
    • The fast query cache aims to increase the QPS for read requests. We recommend that you enable the fast query cache if your PolarDB cluster processes a large number of read requests but a small number of write requests. Assume that a large number of requests are sent to read data from a table and only a small number of requests are sent to write data to the table. In this case, you can use the SQL_CACHE keyword to enable the fast query cache for the table. 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.
    • For the fast query cache, the utilization of memory resources is high. The fast query cache is applicable to the oltp_point_select scenarios where only a few updates are performed but a large number of queries are run. In these scenarios, the fast query cache improves the database performance in a significant way.
  • 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. You can specify the SQL_NO_CACHE keyword so that your PolarDB cluster does not retrieve result sets from the fast query cache.
    DEMAND By default, the fast query cache is not used for data queries. You can enable the fast query cache later by using the SQL_CACHE keyword.
    Note

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

    • 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 for the cluster.
    • If your PolarDB cluster processes a small number of repeated queries, set loose_query_cache_type to DEMAND for the cluster. You can use the SQL_CACHE keyword to enable the fast query cache for only specific statements.