Mysql Query Cache Description _mysql

The MySQL query cache is analyzed in detail from five angles: The working principle of query cache, how to configure, how to maintain, how to judge the performance of query cache, and the suitable business scenario analysis.

Working principle

The working principle of query caching can be summarized as follows:
A result set and SQL statement that caches a select operation or preprocessing query (note: 5.1.17 begins to support);
The new SELECT statement or preprocessing query statement, first to query the cache, to determine whether there is a set of records available, to determine the standard: and cached SQL statements are exactly the same, case-sensitive;

The query cache is not able to cache its recordset for what query statements, roughly the following categories:
The Sql_no_cache parameter is added to the query statement;
The query statement contains functions that obtain values, including custom functions such as: Curdate (), Get_lock (), RAND (), Convert_tz, etc.
Query to System database: MySQL, information_schema
Use a session-level variable or a local variable in a stored procedure in a query statement;
The statement with lock in SHARE MODE and for update is used in the query statement
Similar to select in query statement ... Into the statement of the exported data;
Transaction ISOLATION LEVEL is: Serializable case, all query statements can not be cached;
Query operations on temporary tables;
A query statement that has a warning message;
A query statement that does not involve any table or view;
A user only has Column Level permission query statement;

Advantages and disadvantages of query caching:

Do not need to do any parsing and execution of SQL statements, of course, parsing must be through the first, directly from query cache to obtain the results;
The judgment rules of query caching are not intelligent enough, which increases the threshold of query caching and reduces its efficiency.
Query cache will increase the cost of checking and cleaning the recordset in query cache, and there is a table with SQL statement caching, each table has only one corresponding global lock;

Configuration

Whether to enable the MySQL query cache, you can pass 2 parameters: Query_cache_type and query_cache_size, any one of the parameters set to 0 means that the query caching function is turned off, but the correct settings recommend query_cache_type=0.

Query_cache_type
Range of: 0-– does not enable query caching;
Range is: 1-– enable query caching, as long as the query cache requirements are met, the client's query statements and record buckets can be
Cached, and used by other clients altogether;
Range is: 2-– enable query caching, as long as the query statement added parameters: Sql_cache, and meet the requirements of the query cache, the client's query statements and recordset, you can cache, a total of other clients to use;

Query_cache_size
Allows setting the value of the query_cache_size minimum of 40K, for the maximum can be almost unlimited, the application of the actual production environment tells us that the value is not the larger, the query cache hit rate is higher, not to the server load drop contribution, but may offset its benefits, Even increase the load of the server, as to how to set up, the following chapters, the recommended setting is: 64M;

Query_cache_limit
Limit query cache to the maximum cached query Recordset, you can avoid a large query recordset to take up a large number of memory areas, and often small query Recordset is the most efficient cache recordset, the default setting of 1M, proposed to modify the range between 16k~1024k, However, the most important thing is based on the actual situation of their application analysis, estimate to set;

Query_cache_min_res_unit
Set the minimum unit for the query cache allocation memory, which can be set appropriately to reduce the number of requests and allocations for memory blocks, but setting too large can cause memory fragmentation values to rise. The default value is 4K, the recommended setting is 1k~16k

Query_cache_wlock_invalidate
This parameter mainly involves the MyISAM engine, if a client adds a write lock to a table, a query request is made by another client, and the query statement has a corresponding query cache record, whether it is allowed to read the query cached recordset information directly, or to wait for the release of the write lock. The default setting is 0, which is allowed;

Maintenance

Query for defragmentation of the ease area

After the query cache has been used for a period of time, memory fragmentation will generally occur, which requires monitoring the relevant state values, and periodically defragment the memory, defragment the operation statement: FLUSH query cache;

Empty query-Cached data
Those operations can trigger query caching, empty all cached information to avoid triggering or when needed, know how to do, two classes can trigger query cache data All empty command:
(1). RESET QUERY CACHE;
(2). FLUSH TABLES;

Performance monitoring

Fragmentation Rate
Query cache memory Fragmentation rate =qcache_free_blocks/qcache_total_blocks * 100%

Shooting
Query Cache Hit Ratio = (qcache_hits–qcache_inserts)/qcache_hits * 100%

Memory usage
Query Cache memory Usage = (query_cache_size–qcache_free_memory)/query_cache_size * 100%

Qcache_lowmem_prunes
The value of the parameter is very important for detecting the memory size of the query buffer, and its representative meaning is: The query cache has to delete the query cache information from the query buffer because of insufficient memory, and the deletion algorithm is LRU;
Query_cache_min_res_unit

Memory block allocation of the smallest unit is very important, set too large may increase the probability of memory fragmentation, too small and may increase the memory allocation of the consumption, for this system to run smoothly after a stage, you can refer to the formula calculated value:
Query cache minimum memory block = (query_cache_size–qcache_free_memory)/Qcache_queries_in_cache
Query_cache_size

How do we determine if query_cache_size is set too small, and still only preset a value, recommended as: The area between 32m~128m, the system to run smoothly for a period of time (at least 1 weeks), and observe the relevant state values this week:
(1). Qcache_lowmem_prunes;
(2). Hit rate;
(3). Memory usage rate;

If the entire steady-state monitoring of the information received, the hit rate is higher than 80%, memory utilization of more than 80%, and the value of qcache_lowmem_prunes continuously increased, and the increase in the number is larger, it means that we have to query the buffer allocated too little memory, Can appropriately increase the memory size of the query buffer;

If the entire smooth running period monitoring the information obtained, for the hit rate lower than the value of 40%,qcache_lowmem_prunes also maintain a stable state, it shows that our query buffer memory set too large, or the business scene repeated execution of the same query statement probability is low, At the same time, if you also monitor a certain amount of freeing items, you must consider the memory of the query cache small, or even turn off the query caching function;

Business Scenario

Through the above knowledge carding and analysis, we know at least the following points of query caching:
The query cache can speed up the cached query statement, and can obtain the correct recordset without reparse and execution.
In the query cache, each Table object has a lock of its own global nature.
Table if you do DDL, FLUSH tables and other similar operations, trigger the relevant table query cache information empty;
The DML operation of the Table object, it is necessary to decide whether to clean the record information of the relevant query cache, and the lock wait event will inevitably occur;
Query cache memory allocation problem, inevitably produce some memory fragments;
Whether the query cache is the same query statement is very demanding and not intelligent;

Let's go back to the focus of this section, what kind of business scenario does the query cache fit? As long as it is clear that the query cache the above advantages and disadvantages, it is not difficult to list, business scenario requirements:
The whole system to read the main business, such as portal, news, statements, forums and other sites;
The Table object of the query statement operation, which is not frequently DML, can be used in query_cache_type=2 mode, and then the SQL statement is specified with the Sql_cache parameter;