ApsaraDB RDS:How do I configure and use the query cache feature of ApsaraDB RDS for MySQL?

Benefits and scenarios

Benefits:

• CPU utilization is reduced.
• IOPS usage is reduced in specific cases.
• The response time of queries is shortened, and the system throughput is increased.

Scenarios:

• Table data is not frequently updated or is static.
• The same SELECT statement is frequently executed.
• The query result set is less than 1 MB.

Implementation

ApsaraDB RDS for MySQL calculates the hash value of a SELECT query that is sent from a client. Then, ApsaraDB RDS for MySQL uses the hash value to obtain the query result from the query cache.

If the hash value matches a query that is stored in the cache, the result is directly returned to the client. ApsaraDB RDS for MySQL does not translate or execute the query.

If the hash value does not match any queries that are stored in the cache, the hash value and the corresponding result are stored in the cache for future use.

If a table changes, all cached queries that reference the table become invalid, and the related query result sets are removed from the cache.

Limits

• Queries must be exactly the same. Queries that use different letter case types, different databases, different protocol versions, or different character sets are considered different queries and are cached separately.
• The result set of a subquery is not cached. Only the final result set of a query is cached.
• A query is not cached if it is executed within the body of a stored function, a stored procedure, a trigger, or an event.
• A query is not cached if it uses any of the following functions: now(), curdate(), last_insert_id(), and rand().
• A query is not cached if it references tables in the mysql, information_schema, or performance_schema database.
• A query is not cached if it references temporary tables.
• A query is not cached if it triggers warnings.
• A query is not cached if it contains SELECT … LOCK IN SHARE MODE, SELECT … FOR UPDATE, or SELECT * FROM … WHERE AUTOINCREMENT_COL IS NULL.
• A query is not cached if it references a custom variable.
• A query is not cached if it uses HINT - SQL_NO_CACHE.

Settings

• Parameters settings

  This section describes the parameter settings in the ApsaraDB RDS console.

  • query_cache_limit: specifies the maximum cached result set of a single query. The default value is 1 MB. Result sets that exceed the specified value are not cached. Unit: bytes.

  • query_cache_size (byte): specifies the size of the query cache. The default value is 3 MB. Unit: bytes.

  • query_cache_type: specifies whether to enable the query cache feature. Valid values:

    0: disables the feature.

    1: enables the feature. Queries that start with SELECT SQL_NO_CACHE are not cached.

    2: disables the feature. Only queries that start with SELECT SQL_CACHE are cached.

    
  Important

  • After you modify the query_cache_type parameter of an RDS instance, the RDS instance is automatically restarted for the modification to take effect.
  • You must set the query_cache_size parameter to an integer that is a multiple of 1024. Otherwise, the error message indicating that the specified parameter is invalid is displayed.
• Feature enabled

  If the value of the query_cache_size parameter is greater than 0 and the query_cache_type parameter is set to 1 or 2, the query cache feature is enabled.

• Feature disabled

  If the query_cache_size parameter is set to 0 or the query_cache_type parameter is set to 0, the query cache feature is disabled.

• Suggestions
  • We recommend that you do not set the query_cache_size parameter to a large value. A large cache occupies the memory capacity of other processes and increases the overhead when you use the query cache feature. We recommend that you set the initial value of this parameter to a value ranging from 10 MB to 100 MB based on the instance type, and then adjust the value based on your business requirements.
  • You can change the value of the query_cache_size parameter to enable or disable the query cache feature.
  • The query cache feature applies to specific scenarios. You must run tests before you enable it to prevent the system performance from being compromised or prevent other issues.

Verify results

• ApsaraDB RDS console
• SQL statements

  You can execute the following statement to query the usage status of the query cache feature:

  show global status like ‘Qca%’;

  • Qcache_hits: the number of query cache hits

  • Qcache_inserts: the number of queries and query results added to the query cache

  • Qcache_not_cached: the number of queries that cannot be cached

  • Qcache_queries_in_cache: the number of queries in the query cache