MySQL Query Cache Related issues

I've been asked a lot of questions about MySQL query cache recently, just sort out the contents of MySQL query cache for reference.

As the name implies, MySQL query cache is used for caching and query-related data. Specifically, Query cache caches the SELECT statement that our client submits to MySQL and the result set of the statement. Presumably, the result of the SELECT statement and statement is a HASH mapping relationship and then stored in a certain area of memory.

In most MySQL distributions, the Query Cache feature is turned on by default and can be turned on by adjusting the parameters option of MySQL Server. The following 5 parameters are mainly composed of:

• Query_cache_limit: The maximum capacity of a single query result set that allows the cache, by default, is 1MB, and the query result set that exceeds this parameter setting will not be cache
• Query_cache_min_res _unit: Sets the minimum amount of memory allocated for each allocation in query cache, which is the minimum amount of memory space consumed by the cache for each query.
• query_cache_size: Sets the memory size used by the query cache, the default value For 0, the size must be an integer multiple of 1024, and if it is not an integer multiple, MySQL automatically adjusts the minimum amount to reach a multiple of 1024
• Query_cache_type: The switch that controls the query cache function, which can be set to 0 (OFF), 1 (on ) and 2 (DEMAND) Three, the meanings are as follows:
  • 0 (off): Turn off the query cache feature, and do not use query cache
  • 1 (on) in any case: The query cache function is turned on, but When the Sql_no_cache hint is used in the SELECT statement, query cache
  • 2 (DEMAND) is not used: The query cache function is turned on, but only if the Sql_cache hint is used in the SELECT statement , use query cache
• query_cache_wlock_invalidate: Controls whether a write lock occurs at a time on the table if the query cache associated with the table is invalidated first, if set to 1 (TRUE) , all query caches associated with the table will be invalidated while the lock is written, and if set to 0 (FALSE), the query cache associated with the table is still allowed to be read at the time of the lock.

How does the query Cache handle subqueries?
This is one of the most common problems I have encountered. In fact, the query Cache is a client request submitted by the query for the object processing, as long as the client is requesting a query, whether the query is a simple single-table query or a multi-table Join, or a complex SQL with a subquery, is considered as a query, not will be split into multiple Query to Cache. Therefore, a complex query that has subqueries will only produce a cache object, and the subquery will not produce a separate cache content. The same is true for statements of type Union[all].

Query Cache is a block of data stored in the way?
No, the cached content in query cache contains only the result data needed for that query, which is the result set. Of course, it is not just the result data, but also contains other information related to the result, such as the character set of the client connection that generated the Cache, the character set of the data, the Default database of the client connection, and so on.

Why is the query cache very efficient, even if all the data can be cache into memory, sometimes it is not as efficient as using the query cache?
The lookup for query Cache is before SQL resolves after MySQL has accepted the client request after permission validation on query. In other words, when MySQL receives the client's SQL, it only needs to verify the corresponding permission to find the result through Query Cache, even do not need to go through the Optimizer module to perform the analysis optimization of the plan, let alone any storage engine interaction. A lot of disk IO and CPU operations are reduced, so the efficiency is very high.

Does the SQL statement case submitted by the client affect the Query Cache?
Yes, because the query cache in memory is a hash structure to map, the hash algorithm is composed of the character of the SQL statement, so the entire SQL statement must be exactly the same character level in order to hit in the Query Cache, even if more than one space.

What happens to the contents of an SQL statement in the Query Cache?
To ensure that the content in the query cache is exactly the same as the actual data, any changes to the data in the table, including additions, modifications, deletions, and so on, will invalidate all SQL query caches referencing the table.

Why does the overall performance of my system drop after I open the Query Cache?
When query cache is turned on, especially when our Query_cache_type parameter is set to 1, MySQL will Query the cache for each SELECT statement, while the lookup operation is relatively simple, but still consumes some CPU The resource is counted. Because of the failure mechanism of the query cache, it is possible that because the data on the table is changing more frequently, a large number of query caches are frequently invalidated, so the hit rate of the query cache may be low. So in some scenarios, the Query Cache not only does not improve efficiency, but it can negatively impact.

How to verify that the Query Cache of a system is running healthy, how hit, and is the amount of setup sufficient?
MySQL provides a series of Global status to record the current state of the Query Cache, as follows:

• Qcache_free_blocks: Number of memory blocks in Query Cache that are currently idle
• Qcache_free_memory: Total amount of Query Cache memory currently in idle state
• Qcache_hits:query Cache Hit Count
• Qcache_inserts: The number of times to insert a new query cache into the query cache, that is, the number of missed hits
• Qcache_lowmem_prunes: When query cache memory capacity is insufficient, you need to remove the old query cache from the number of times it will be used for the new cache object
• Qcache_not_cached: The number of SQL not being cache, including SQL that cannot be cache, and SQL that will not be cache because of Query_cache_type settings
• Qcache_queries_in_cache: Number of SQL currently in Query cache
• The total number of blocks in the Qcache_total_blocks:query Cache

You can calculate the cache hit rate based on these states, calculate the query cache size settings is sufficient, overall, I personally do not recommend the size of the query cache more than 256MB, which is the industry's more common practice.

Can MySQL Cluster use Query Cache?
In fact, we don't use MySQL Cluster in our production environment, so I don't have the actual experience of using query Cache in the MySQL Cluster environment, but the MySQL documentation shows that you can actually use query C in MySQL Cluster. Ache From the MySQL Cluster principle to analyze, but also think should be able to use, after all, SQL node and data node independent, the respective, but the Cache invalidation mechanism will be slightly more complicated.

Http://isky000.com/database/mysql-query-cache-summary

MySQL Query Cache Related issues