MYSQL in Query_cache_size Summary

1 principle
The MySQL query cache saves the full result returned by the query. When the query hits the cache, the results are returned immediately, skipping the parsing, optimization, and execution phases.
The query cache tracks each table involved in the query, and if the write table changes, all caches associated with the table will be invalidated.
But with the power of the server, query caching can also become a resource contention point for the entire server.

2 Preliminary Setup
The default switch is off, that is, prohibit the use of Query_cache, query whether to use the following statement:
mysql> SHOW VARIABLES like ' Have_query_cache ';
variable_name | Value |
+------------------+-------+
| Have_query_cache | YES |

Note that this is just a display that supports the Query_cache function, which is turned off by default, through this statement
Query settings:
mysql> SHOW STATUS like ' qcache% ';

If Query_cache_size = 0 is found in the results, it is not set and the method is set to
My.ini, set
query_cache_size=128m
Add one line: query_cache_type=1

3 How MySQL allocates query_cache_size

The cached memory that MySQL uses for querying is broken up into chunks of data that are used to store types, sizes, data, and so on.
When the server starts, it initializes the memory required by the cache and is a complete free block. When the query results need to cache, the first request from the free block of data block is greater than the configuration of the parameter query_cache_min_res_unit, even if the cache data is small, the application data block is this, because the query starts to return the result when the allocation of space, at this time can not predict the results of how large.
Allocating memory blocks requires locking the space block first, so the operation is slow, MySQL will try to avoid this operation, select the smallest possible memory block, if not enough, continue to apply, if the storage is free when the extra.

4 How to determine whether a hit
The cache is stored in a reference table, referenced by a hash value, which includes the query itself, the database, the version of the client protocol, and so on, any character, such as a space, which causes the cache to miss.
When there are some uncertain data in the query, it is not cached, such as now (), Current_date (), custom functions, stored functions, user variables, word queries, etc. So the query will not hit the cache, but also to detect the cache, because the query cache before parsing SQL, so MySQL does not know whether the query contains the class function, but does not cache, nature will not hit.

The specific summary is as follows:

BENCHMARK ()
CONNECTION_ID ()
Curdate ()
Current_date ()
Current_time ()
Current_timestamp ()
Curtime ()
DATABASE ()
Encrypt with one parameter ()
Found_rows ()
Get_lock ()
LAST_INSERT_ID ()
Load_file ()
Master_pos_wait ()
Now ()
RAND ()
Release_lock ()
Sysdate ()
Unix_timestamp with no parameters ()
USER ()


· Reference a custom Function (UDFs).
· Reference a custom variable.
· Refer to the table in the MySQL system database.
· Any one of the following methods:
SELECT ... In SHARE MODE
SELECT ... For UPDATE
SELECT ... Into OUTFILE ...
SELECT ... Into DumpFile ...
SELECT * FROM ... WHERE Autoincrement_col is NULL

· is used as a well-written statement, even if the placeholder is not in use. For example, the following query is used:
Char *my_sql_stmt = "Select a, b from Table_c";
/* ...*/
Mysql_stmt_prepare (Stmt,my_sql_stmt,strlen (my_sql_stmt));
not be cached.
· Use the temporary table.
· Do not use any tables.
· The user has column-level permissions for a table.




Opening the Qcache will bring additional consumption to both read and write:
A. You must check whether the cache is hit before the read query starts.
b, if the read query can be cached, then the cache is written after execution.
C, when writing data to a table, you must set the table all the cache to fail, if the cache space is large, then the consumption will be very large, may cause the system zombie for a period of time, because this operation is protected by the global lock operation.
For the InnoDB table, when a table is modified, the cache invalidation is set, but the multi-version feature temporarily masks this modification to other transactions, and until the transaction commits, all queries cannot use the cache until the transaction is committed, so long transactions can significantly reduce the query cache hit

A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE, ALTER table, drop table, or drop DATABASE.
For InnoDB, some features of things also limit the use of query caching. When the B table is modified in thing a, the modification of table B before the thing is submitted is not visible to other things. In order to ensure the correctness of the cached results, InnoDB takes steps to make all queries involving the B table non-cacheable before thing a commits. If a thing runs for a long time, it can seriously affect the hit rate of the query cache

Do not set the query cache space too large.
Because the query cache is protected by a global lock operation, if the query cache configuration memory is relatively large and contains a large number of query results, when the query cache fails, the long time to hold this global lock. Because the query cache's hit detection operations and cache invalidation detection also depend on this global lock, it can cause the system to zombie


5 Specific parameter meaning:
Show status like '%qcache% ';
variable_name | Value |

+ ————————-+ ——— –+

| Qcache_free_blocks | 1 |

| Qcache_free_memory | 134208800 |

| Qcache_hits | 0 |

| Qcache_inserts | 0 |

| Qcache_lowmem_prunes | 0 |

| qcache_not_cached | 2 |

| Qcache_queries_in_cache | 0 |

| Qcache_total_blocks | 1 |


Analytical:
Qcache_free_blocks: Indicates how many remaining blocks are currently in the query cache, and if the value is larger, it means that there is too much memory fragmentation in the query cache and may be collated at some time.


Reduce fragmentation:
The appropriate query_cache_min_res_unit can reduce fragmentation, and the most appropriate size of this parameter is directly related to the average size of the application query results, which can be consumed by the actual memory (query_cache_size-qcache_free_ Memory) divided by Qcache_queries_in_cache calculates the average cache size.
Fragments can be observed by qcache_free_blocks, which reflects the rest of the free block, if the value is many, but
Qcache_lowmem_prunes is increasing, it means that there are too many fragments. You can use flush query cache to defragment, reorder, but not empty, and the empty command is reset query cache. During defragmentation, the query cache cannot be accessed and may cause the server to zombie for some time, so the query cache should not be too large.



Qcache_free_memory: The memory size of the query cache, through this parameter can be very clear to know whether the current system query memory is sufficient, is more, or is not enough, the DBA can make adjustments according to the actual situation.

Qcache_hits: Indicates how many times the cache has been hit. We can mainly use this value to verify the effect of our query cache. The larger the number, the better the caching effect.

Qcache_inserts: Indicates how many misses were missed and then inserted, meaning that the new SQL request was not found in the cache, had to perform query processing, and then insert the results into the query cache after executing the query processing. Such a situation, the more times, the query cache is applied to the less, the effect is not ideal. Of course, the query cache is empty after the system has just started.

It's normal.

Qcache_lowmem_prunes: This parameter records how many queries have been removed from the query cache because of insufficient memory. With this value, the user can adjust the cache size appropriately.


Qcache_not_cached: Represents the number of queries that are not cached because of the Query_cache_type settings.

Qcache_queries_in_cache: The number of queries cached in the current cache.

Qcache_total_blocks: The number of blocks currently cached.


Increase the usage of the query cache:
If fragmentation is not a problem, the hit rate is very low, possibly out of memory, and you can view unused memory through the Qcache_free_memory parameter.
If the 2 is not a problem and the hit rate is still low, then the cache does not fit your current system. Can be set by setting
query_cache_size = 0 or Query_cache_type to close the query cache.

-------------------------------------------------------------
The following explains the parameters of show variables like '%query_cache% '

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 is not set by the cache
Query_cache_min_res_unit: Sets the minimum amount of memory allocated per allocation in query cache, which is the minimum amount of memory space per query cache
Query_cache_size: Set the memory size used by Query cache, the default value is 0, the size must be an integer multiple of 1024, if not an integer multiple, MySQL will automatically adjust the minimum amount to achieve a multiple of 1024
Query_cache_type: The switch to control the Query cache function can be set to 0 (OFF), 1 (on) and 2 (DEMAND) Three, with the following meanings:
0 (OFF): The query cache function is turned off and the query cache is not used under any circumstances
1 (ON): The query cache function is turned on, but the query cache is not used when the Sql_no_cache hint is used in the SELECT statement
2 (DEMAND): The query cache function is turned on, but the query cache is used only if the Sql_cache hint is used in the SELECT statement
Query_cache_wlock_invalidate: Controls whether the query cache associated with the table is invalidated when there is a write lock occurring on the table, and if set to 1 (TRUE), all query caches related to the table will be invalidated at the same time as the write lock. If set to 0 (FALSE), the Query Cache associated with the table is still allowed to be read at the time of the lock.


Related Questions and answers:
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.

MYSQL in Query_cache_size Summary