Settings and use of the RDS for MySQL query cache

Https://help.aliyun.com/knowledge_detail/41717.html?spm=5176.7841698.2.11.aCvOXJRDS for MySQL query cache The settings and use of

• function and scope of application
• Principle
• Limit
• Set up
• Validation effect

1. Function and scope of application

Function:

• Reduce CPU usage

• Reduce IOPS utilization (in some cases)

• Reduce query response time and improve system throughput

Scope of application:

• Table data is not modified frequently, the data is more static

• High repeatability of query (Select)

• Query result set less than 1 MB

Note:

• Query caching does not necessarily bring performance gains, and in some cases (such as large queries, but few duplicate queries) opening the query cache can result in degraded performance.

2. Principle

RDS for MySQL computes the hash value of the query from the client's query (Select), which matches the result of the query through the hash value to the query cache.

If a match is hit, the result set of the query is returned directly to the client, eliminating the need to parse and execute the query.

If there is no match (hit), the Hash value and result set are saved in the query cache for later use.

The data in any one of the tables involved in the query is changed, and RDS for MySQL frees (deletes) all the query result sets associated with that table in the query cache.

3. Restrictions

• Queries must be strictly consistent (case, space, database used, protocol version, character set, etc. must be consistent) to be hit, otherwise considered as different queries.

• The subquery result set in the query is not cached and only the query final result set is cached.

• queries in stored functions (Stored function), stored procedures (Stored Procedure), triggers (Trigger), events (event) are not cached.

• Queries that contain functions that change each execution result are not cached, such as now (), Curdate (), last_insert_id (), Rand (), and so on.

• Queries to MySQL, INFORMATION_SCHEMA, Performance_schema system database tables are not cached.

• Queries that use temporal tables are not cached.

• queries that generate alarms (Warnings) are not cached.

• Do not cache Select ... lock in share mode, select ... for UPDATE, select * From ... where autoincrement_col is a NULL type query.

• Queries that use user-defined variables are not cached.

• Queries that use Hint-sql_no_cache are not cached.

4. Set 4.1 parameters

RDS Console parameter settings

• Query_cache_limit: The maximum result set of a single query that can be stored in the query cache, the default is 1 MB, and a result set that exceeds that size is not cached.

• Query_cache_size: The size of the query cache.

• Query_cache_type: Whether the query caching function is turned on.

Value is 0: Turn off query function

The value is 1: Turn on query caching, but do not cache queries at the beginning of Select Sql_no_cache.

The value is 2: The query caching feature is turned on, but only queries that begin with Select Sql_cache are cached.

Note:

• Modifying the Query_cache_type requires restarting the instance (the instance is automatically restarted after the modification).
• The parameter query_cache_size requires an integer multiple of 1024 to be set, otherwise you will be prompted to "format the parameter incorrectly, re-enter".

4.2 Open

When the parameter query_cache_size is greater than 0 and Query_cache_type is set to 1 or 2, the query cache is turned on.

4.3 Close

Set the parameter query_cache_size to 0 or set Query_cache_type to 0 to close the query cache.

4.4 Recommendations

• Query_cache_size is not recommended to set too large. Excessive space not only crowding out the space of other memory structures in the instance, but also increases the cost of searching in the cache. It is recommended that the initial value be set to a value between 10MB and MB based on the instance specification, and then adjusted according to the usage of the operation.

• We recommend that you turn the query cache on and off by adjusting the value of the query_cache_size, because modifying the Query_cache_type parameter requires that the instance be restarted.

• The query cache is suitable for specific scenarios and is recommended to be fully tested before considering opening, avoiding performance degradation or introducing other issues.

5. Verify Effect 5.1 Console

5.2 SQL Command

show global status like ‘Qca%‘;

You can use the show global status like ' qca% ' to get the usage status of the query cache.

• Qcache_hits: Query cache Hit count.

• Qcache_inserts: The number of times the query and result set are written to the query cache.

• qcache_not_cached: Number of queries that cannot be cached.

• Qcache_queries_in_cache: The amount of queries cached in the query cache.

Settings and use of the RDS for MySQL query cache