ApsaraDB RDS:ApsaraDB RDS for MySQL query cache

Benefits and usage notes

Benefits:

• Reduces CPU utilization.
• Reduces IOPS in certain scenarios.
• Decreases query response time and increases system throughput.

Usage notes:

• Infrequently modified tables or tables with static data.
• Highly repetitive queries (SELECT statements).
• Result sets smaller than 1 MB.

How it works

ApsaraDB RDS for MySQL computes a hash value for each SELECT query from a client. It then uses this hash value to find a matching result in the query cache.

If a match (a hit) is found, the result set is returned directly to the client, bypassing the need to parse and execute the query.

If no match is found, the query is executed, and its hash value and result set are stored in the query cache for future use.

If data in any table involved in a query changes, ApsaraDB RDS for MySQL invalidates and removes all cached result sets related to that table.

Limitations

• Queries must be strictly identical to achieve a hit. Any difference in character case, spacing, the current database, protocol version, or character set is treated as a different query.
• The result sets of subqueries are not cached. Only the final result set of the outer query is cached.
• Queries within a stored function, stored procedure, trigger, or event are not cached.
• Queries that contain non-deterministic functions, such as now(), curdate(), last_insert_id(), and rand(), are not cached because their results change with each execution.
• Queries on tables in the mysql, information_schema, or performance_schema system databases are not cached.
• Queries that use temporary tables are not cached.
• Queries that generate warnings are not cached.
• Queries that contain SELECT ... LOCK IN SHARE MODE, SELECT ... FOR UPDATE, or SELECT * FROM ... WHERE autoincrement_col IS NULL are not cached.
• Queries that use user-defined variables are not cached.
• Queries that include the SQL_NO_CACHE hint are not cached.

Query cache settings

• Parameter settings

  Configure the following parameters in the console.

  • query_cache_limit (Unit: bytes): The maximum size of a single query result set that can be stored in the query cache. The default value is 1 MB. Result sets larger than this limit are not cached.

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

  • query_cache_type: Specifies whether to enable the query cache.

    A value of 0 disables the query cache.

    A value of 1 enables the query cache for all queries except those that start with SELECT SQL_NO_CACHE.

    A value of 2 enables the query cache only for queries that start with SELECT SQL_CACHE.

    
  Important

  • Modifying the query_cache_type parameter requires an automatic instance restart.
  • The value of the query_cache_size parameter must be a multiple of 1024. Otherwise, the message "the specified parameter is invalid" is returned.
• Enabled

  The query cache is enabled when the query_cache_size parameter is greater than 0 and the query_cache_type parameter is set to 1 or 2.

• Disabled

  To disable the query cache, set the query_cache_size parameter to 0 or set the query_cache_type parameter to 0.

• Recommendations
  • Do not set the query_cache_size parameter to an excessively large value. A large cache not only consumes memory resources that could be used by other instance components but also increases the overhead of searching the cache. Set an initial value between 10 MB and 100 MB based on your instance specifications and then adjust it based on runtime usage.
  • Enable or disable the query cache by adjusting the value of the query_cache_size parameter. Modifying the query_cache_type parameter requires an instance restart.
  • The query cache is suitable only for specific use cases. Perform thorough testing before enabling it to avoid performance degradation or other issues.

Verify the effect

• Console
• SQL command

  Run the following command to check the status of the query cache.

  show global status like 'Qca%';

  • Qcache_hits: Number of query cache hits.

  • Qcache_inserts: Number of queries inserted into the cache.

  • Qcache_not_cached: The number of non-cacheable queries.

  • Qcache_queries_in_cache: Number of queries currently in the cache.