The fast query cache is a query cache that is developed by Alibaba Cloud based on the native MySQL query cache. The fast query cache uses a new design and a new implementation mechanism to increase the query performance of your ApsaraDB RDS instance.

Prerequisites

Background information

A query cache is designed to save CPU resources and accelerate queries. It stores the text of each qualified statement with the returned result set. If an identical SQL statement is received later, the database system directly retrieves the result set from the query cache. This eliminates the need to analyze, optimize, and execute the SQL statement again.

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

  • It cannot process a large number of concurrent queries at high performance. If multiple CPU cores are configured, a larger number of concurrent queries may indicate lower performance.
  • It cannot efficiently utilize memory resources or quickly reclaim memory resources. This causes a waste of memory resources.
  • If the cache hit ratio is low, query performance does not increase and may even significantly decrease.

Due to the preceding drawbacks, the native MySQL query cache is not widely used. It is no longer provided in the latest MySQL version 8.0. In contrast, the fast query cache has the following benefits:

  • Optimized concurrency control

    The global locking mechanism removed. This mechanism is used in the native MySQL query cache to synchronize the running of threads. The fast query cache uses a redesigned synchronization mechanism. This mechanism allows ApsaraDB RDS to exploit the configuration of multiple CPU cores and process a large number of concurrent queries at high performance.

  • Optimized memory management

    The memory preallocation mechanism of the native MySQL query cache is removed. The fast query cache uses a dynamic, more flexible memory allocation mechanism. This mechanism allows ApsaraDB RDS to quickly reclaim invalid memory resources. This increases memory usage.

  • Optimized caching

    The fast query cache dynamically monitors cache usage. Then, the fast query cache adjusts the cache policy based on the obtained cache usage. This prevents performance decreases when the cache hit ratio is low or when your RDS instance processes both read and write requests.

The fast query cache can be used in a wide range of business scenarios to increase query performance. However, the native MySQL query cache does not provide the same support.

Enable the fast query cache

You can enable the fast query cache by configuring the query_cache_type and query_cache_size parameters in the ApsaraDB RDS console.

Parameter Description
query_cache_type The switch that is used to control the fast query cache. Valid values:
  • 0: disables the fast query cache. This is the default value.
  • 1: enables the fast query cache. You can use the SQL_NO_CACHE option to skip the caching for SQL statements.
  • 2: disables the fast query cache. You can use the SQL_CACHE option to cache the texts and result sets of only the specified SQL statements.
query_cache_size The size of the memory space that is allocated to the fast query cache. Valid values: 0 to 10485760000. The value must be a multiple of 1024. Unit: bytes.

The fast query cache occupies extra memory space. When you configure the fast query cache, we recommend that you also reconfigure the innodb_buffer_pool_size parameter:

  1. Set the innodb_buffer_pool_size parameter to 90% of the original value of this parameter. The reduced 10% of memory space is used as the query cache size that is specified by the query_cache_size parameter. For example, if the original value of the innodb_buffer_pool_size parameter is {DBInstanceClassMemory*7/10}, set this parameter to {DBInstanceClassMemory*63/100}. For more information, see Change the size of the InnoDB buffer pool for an ApsaraDB RDS for MySQL instance.
  2. Set the query_cache_size parameter. For more information, see Modify the parameters of an ApsaraDB RDS for MySQL instance.
    • If you can accurately estimate the result set size, you can set the query_cache_size parameter to a value that is equal to 20% of the result set size.
    • If you cannot accurately estimate the result set size, you can set the query_cache_size parameter to a value that is equal to 10% of the value of the innodb_buffer_pool_size parameter.
    Note When you change the specifications of your RDS instance, the value of the query_cache_size parameter does not change based on the new specifications. After the specification change is applied, you must immediately reconfigure this parameter.
  3. Set the query_cache_type parameter to 1. This allows you to enable the fast query cache. For more information, see Modify the parameters of an ApsaraDB RDS for MySQL instance.

Compare the performance of the native MySQL query cache and the fast query cache

Compare the queries per second (QPS) with different query cache configurations under the same conditions in various test cases. These query cache configurations are QC-OFF (no query cache is enabled), MySQL-QC (the native MySQL query cache is enabled), and Fast-QC (the fast query cache is enabled).

  • Test environment: a dedicated RDS instance with 4 CPU cores and 8 GB of memory
  • Test tool: SysBench
  • Test data size: 250 MB (25 tables in total, 40,000 records per table)
  • Test case 1: Test the QPS for read queries with a cache hit ratio of 100%.

    The oltp_point_select script is used. Execute only the POINT SELECT statements that are based on primary keys. Additionally, make sure that you set the query cache size to 512 MB. This size is greater than the test data size and allows the cache hit ratio to reach 100%. In this test case, focus on how much the QPS increases based on the number of concurrent queries.

    Table 1. QPS for read queries with a cache hit ratio of 100%
    Number of concurrent queries QC-OFF MySQL-QC (QPS increase compared with QC-OFF) Fast-QC (QPS increase compared with QC-OFF)
    1 8,093 8,771 (8.38%) 9,261 (14.43%)
    8 62,262 65,686 (5.50%) 75,313 (20.96%)
    16 97,083 73,027 (-24.78%) 139,323 (43.51%)
    32 97,337 60,567 (-37.78%) 200,978 (106.48%)
    64 106,,283 60,216 (-43.34%) 221,659 (108.56%)
    128 107781 62,844 (-41.69%) 231,409 (114.70%)
    256 106,694 63,832 (-40.17%) 222,187 (108.25%)
    512 101,733 64,866 (-36.24%) 203,789 (100.32%)
    1024 89,548 62,291 (-30.44%) 203,542 (127.30%)
    QPS for read queries with a cache hit ratio of 100%
    Note Based on the test result, as the number of concurrent queries increases, the QPS of the native MySQL query cache significantly decreases. However, the QPS of the fast query cache does not decrease and can even increase by up to 100%.
  • Test case 2: Test the QPS for read queries with a cache hit ratio higher than 80%.

    The oltp_read_only script is used. Run queries including range queries that each return multiple records. Additionally, make sure that you set the query cache size to 512 MB. This size ensures sufficient memory space and allows the cache hit ratio to reach more than 80%. In this test case, focus on how much the QPS increases based on the number of concurrent queries.

    Table 2. QPS for read queries with a cache hit ratio higher than 80%
    Number of concurrent queries QC-OFF MySQL-QC (QPS increase compared with QC-OFF) Fast-QC (QPS increase compared with QC-OFF)
    1 5,099 6,467 (26.83%) 7,022 (37.71%)
    8 28,782 28,651 (-0.46%) 45,017 (56.41%)
    16 35,333 31,099 (-11.98%) 66,770 (88.97%)
    32 34,864 27,610 (-20.81%) 67,623 (93.96%)
    64 35,503 27,518 (-22.49%) 75,981 (114.01%)
    128 35,744 27,733 (-22.41%) 80,396 (124.92%)
    256 35,685 27,738 (-22.27%) 80,925 (126.78%)
    512 35,308 27,398 (-22.40%) 79,323 (124.66%)
    1024 34,044 26,861 (-22.10%) 75,742 (122.48%)
    QPS for read queries with a cache hit ratio higher than 80%
    Note Based on the test result, as the number of concurrent queries increases, the QPS of the native MySQL query cache significantly decreases. However, the QPS of the fast query cache can increase by more than 100%.
  • Test case 3: Test the QPS for read queries with a cache hit ratio of about 10%

    The oltp_read_only script is used. Run queries including range queries that each return multiple records. Additionally, make sure that you set the query cache size to 16 MB. This size results in insufficient memory space and the deletion of a large amount of cached data. This allows the cache hit ratio to decrease to about 10%. In this test case, focus on how much the QPS decreases based on the number of concurrent queries.

    Table 3. QPS for read queries with a cache hit ratio of about 10%
    Number of concurrent queries QC-OFF MySQL-QC (QPS increase compared with QC-OFF) Fast-QC (QPS increase compared with QC-OFF)
    1 5,004 4,727 (-5.54%) 5,199 (3.90%)
    8 28,795 22,542 (-21.72%) 28,578 (-0.75%)
    16 35,455 24,064 (-32.13%) 35,682 (0.64%)
    32 34,526 21,330 (-38.22%) 35,871 (3.90%)
    64 35,514 19,791 (-44.27%) 36,051 (1.51%)
    128 35,983 19,519 (-45.75%) 36,253 (0.75%)
    256 35,695 19,168 (-46.30%) 36,337 (1.80%)
    512 35,182 18,420 (-47.64%) 35,972 (2.25%)
    1024 33,915 20,168 (-40.53%) 34,546 (1.86%)
    QPS for read queries with a cache hit ratio of about 10%
    Note Based on the test result, as the number of concurrent queries increases, the QPS of the native MySQL query cache significantly decreases. The decrease nears 50% at most. However, the QPS of the fast query cache decreases only by a small amount.
  • Test case 4: Test the QPS for read and write queries.

    The oltp_read_write script is used. Run transactions that contain updates to tables. These frequent updates result in the deletion of cached data. Consequently, the configured query cache is considered invalid. In this test case, focus on how much the QPS decreases based on the number of concurrent queries.

    Table 4. QPS for read and write queries
    Number of concurrent queries QC-OFF Fast-QC (QPS increase compared with QC-OFF)
    1 4,152 4,098 (-1.30%)
    8 21,359 21,195 (-0.77%)
    16 26,020 25,548 (-1.81%)
    32 27,595 26,996 (-2.17%)
    64 29,229 28,733 (-1.70%)
    128 29,265 28,828 (-1.49%)
    256 29,911 29,616 (-0.99%)
    512 29,148 28,816 (-1.14%)
    1024 29,204 28,824 (-1.30%)
    QPS for read and write queries
    Note Based on the test result, as the number of concurrent read and write queries increases, the QPS of the fast query cache decreases only by a small amount.

Practice guidelines

If you can accurately estimate the result set size, you can evaluate the QPS by using the preceding test cases. This includes when you enable the fast query cache on a specified table by using the SQL_CACHE option. The following tips provide more information about how to use the fast query cache:

  • Enable the fast query cache in various scenarios.
    • The fast query cache aims to increase the QPS for read queries. If your RDS instance processes a large number of read queries but a small number of write queries, we recommend that you enable the fast query cache. Otherwise, we recommend that you use the SQL_CACHE option to enable the fast query cache only for the tables that receive a large number of read queries but a small number of write queries. If your RDS instance processes a small number of read queries but a large number of write queries, the data of your RDS instance is frequently updated. In this case, the fast query cache may cause a small QPS decrease.
    • The QPS increase that is brought by the fast query cache varies based on the cache hit ratio. Before you enable the fast query cache for your RDS instance, we recommend that you obtain the hit ratio of the InnoDB buffer pool. If the hit ratio is lower than 80%, we recommend that you do not enable the fast query cache. The hit ratio of the InnoDB buffer pool is calculated by using the following formula: Hit ratio = (1 - The value of the Innodb_buffer_pool_reads parameter/The value of the Innodb_buffer_pool_read_requests parameter) x 100%. You can also obtain the read/write ratio of each table from the TABLE_STATISTICS table. If a table has a high read/write ratio, you can enable the fast query cache for the table by using the SQL_CACHE option. For more information about how to view the TABLE_STATISTICS table, see Performance Insight.
  • Manage the fast query cache by using the query_cache_type parameter.

    You can set the query_cache_type parameter for a specific session based on your business scenario.

    • If your RDS instance processes a small number of read queries but a large number of write queries, the data of your RDS instance is frequently updated. In this case, we recommend that you globally set the query_cache_type parameter to 0.
    • If your RDS instance has a small data size, fixed query types, and a high hit ratio, we recommend that you globally set the query_cache_type parameter to 1.
    • If your RDS instance has a large data size, changing query types, and an unstable hit ratio, we recommend that you set the query_cache_type parameter to 2. Additionally, we recommend that you use the SQL_CACHE option to enable the fast query cache only for specified SQL statements.
  • Specify a proper query cache size by using the query_cache_size parameter.

    The query_cache_size parameter is closely related to SQL statements. If you want to cache the results of queries that each return multiple records, you may need to specify a query cache size that is a few times greater than the data size. If you do not run range queries, you can perform the following test to evaluate the relationship between the data size and the query_cache_size parameter:

    • Test environment: a dedicated RDS instance with 4 CPU cores and 8 GB of memory (The size of the InnoDB buffer pool is set to 6 GB by using the innodb_buffer_pool_size parameter.)
    • Test tool: SysBench
    • Test data size: 10 GB (100 tables in total, 400,000 records per table)

    Test case: Specify different cache sizes by using the query_cache_size parameter and execute the oltp_point_select script for each cache size. Make sure that 64 threads concurrently run to query data. In this case, the test data includes 20% hot data. This way, you can obtain the impacts of different cache sizes (query_cache_size) on the QPS. The actual result set size is 2.5 GB based on the test data size.

    Table 5. QPS with different cache sizes
    query_cache_size (MB) QC-OFF Fast-QC hit ratio Fast-QC (QPS increase compared with QC-OFF)
    64 98,236 22% 99,440 (1.23%)
    128 98,236 45% 114,155 (16.21%)
    256 98,236 72% 140,668 (43.19%)
    512 98,236 82% 151,260 (53.98%)
    1024 98,,236 84% 153,866 (56.63%)
    2048 98236 87% 159,597 (62.46%)
    4096 98,236 92% 169,412 (72.45%)

    The performance of the fast query cache does not decrease regardless of the value of the query_cache_size parameter. Specifically, the fast query cache provides significantly higher performance than the native MySQL query cache for primary key queries regardless of the cache hit ratio. In some circumstances, the performance can even increase by more than 90%. If the cache hit ratio is less than 90%, the fast query cache provides higher performance than the native MySQL query cache and saves a large number of CPU resources for range queries and for the queries that contain the ORDER BY clause.