Mysql query Cache

Mysql query Cache
When talking to friends or colleagues about the mysql Cache Query function, I like to compare Query Cache to litchi, which is a very nutritious thing. However, if I eat too much at a time, it will easily lead to a nose attack, although it is not a particularly appropriate analogy, there are many similarities. In addition, Query Cache has special business scenarios, unlike other database products, such as Cache Query statement execution plans, instead, the record set and corresponding SQL statement of the query statement are directly cached. This article will introduce you to the Query Cache knowledge, hoping to guide you through the correct use of the Query Cache. Perform a detailed analysis on mysql Query Cache from five perspectives: Query Cache working principle, how to configure, how to maintain, how to determine the Query Cache performance, and suitable business scenario analysis. Working principle the working principle of cache query can be summarized in two sentences: The result set and SQL statement of the SELECT operation or pre-processing query (supported at 5.1.17; the new SELECT statement or pre-processing query statement first queries the cache to determine whether there is an available record set. The criteria are: whether it is exactly the same as the cached SQL statement, case sensitive; the query statement of the cache cannot cache its record set. There are roughly the following types: The SQL _NO_CACHE parameter is added to the query statement, and the query statement contains the function for obtaining the value, include custom functions, such as CURDATE (), GET_LOCK (), RAND (), and CONVERT_TZ. Query System databases: mysql and information_schema query statements use SESSION-level variables or local variables IN stored procedures. The query statements that use lock in share mode and for update are similar to SELECT... INTO data export statement; transaction isolation level: Serializable, all query statements cannot be cached; query operations on temporary tables; query statements with warning information; query statements that do not involve any tables or views. A user has only column-level permission query statements. Advantages and Disadvantages of query cache: No parsing or execution of SQL statements is required, of course, Syntax Parsing must first obtain the Query results from the Query Cache. The judgment rules of the Query Cache are not intelligent enough, which increases the Query Cache Usage threshold and reduces its efficiency; when Query Cache is used, the overhead of checking and clearing Record Sets in Query Cache is increased, and tables cached by SQL statements exist. Each table has only one corresponding global lock; configure whether to enable mysql query cache by using two parameters: query_cache_type and query_cache_size. setting any parameter to 0 means disabling the query cache function. However, we recommend that you set query_cache_type to 0. Query_cache_type: 0 -- Query cache is not enabled; Value: 1 -- Query cache is enabled. As long as the query cache requirements are met, the query statements and record set buckets on the client can be cached, used by other clients; value range: 2 -- enable query cache. As long as the parameter SQL _cache is added to the query statement and meets the query cache requirements, the client's query statement and record set, it can be cached and used by other clients. query_cache_size allows you to set the query_cache_size value to a minimum of 40 K. For the maximum value, you can almost think of it as unlimited. The application experience in the actual production environment tells us that, this value is not a large value. The higher the query cache hit rate, or greater contribution to server load reduction, it may offset the benefits brought by it, or even increase the server load, the following section describes how to set this parameter. We recommend that you set this parameter to 64 mb. query_cache_limit restricts the maximum number of query records that can be cached in the cache, this prevents a large query record set from occupying a large amount of memory areas, Generally, a small query record set is the most effective cache record set. The default value is 1 MB. We recommend that you change it to 16 KB ~ Value range between K, but the most important thing is to set it based on the actual situation of your application. query_cache_min_res_unit sets the minimum unit of memory allocated to the query cache. Set this parameter appropriately, you can apply for and allocate memory blocks to reduce the number of times, but setting too large may increase the memory fragmentation value. The default value is 4 K. We recommend that you set it to 1 K ~ 16 K query_cache_wlock_invalidate this parameter mainly involves the MyISAM engine. If a client adds a write lock to a table, other clients initiate query requests, and the query statement has a corresponding query cache record, whether to allow direct reading of the query cache record set information or wait for the release of the write lock. The default value is 0, that is, allow. After a period of time is used to maintain the fragment sorting and query cache in the query cache, memory fragments usually occur. Therefore, you need to monitor the relevant status values, in addition, the memory fragment is organized regularly. The fragment operation statement is flush query cache. Clearing the data in the query cache may trigger the query cache and clear all the cached information, to avoid triggering or needing to know how to do this, the second class can trigger the query of all cache data clearing commands: (1 ). reset query cache; (2 ). flush tables; performance monitoring fragment rate query cache memory fragment rate = Qcache_free_blocks/Qcache_total_blocks * 100% hit rate query cache hit rate = (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 this parameter value plays a key role in checking whether the memory size of the cache area is set. Its meaning is: query the cache information that has to be deleted from the query cache due to insufficient memory. The deletion algorithm is LRU. The minimum unit allocated by query_cache_min_res_unit memory block is very important, setting too large may increase the probability of memory fragmentation, and too small may increase the consumption of memory allocation. Therefore, after the system runs smoothly for a stage, you can refer to the formula for calculating the value: query the minimum cache memory block = (query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache query_cache_size how can we determine whether the query_cache_size is too small and only one pre-set value is required? We recommend that ~ In the area between MB, wait for the system to run smoothly for a period of time (at least 1 week), and observe the relevant status values for this week: (1 ). qcache_lowmem_prunes; (2 ). hit rate; (3 ). memory usage. If the monitoring information obtained during the entire stable operation period is hit rate higher than 80%, the memory usage exceeds 80%, and the Qcache_lowmem_prunes value increases continuously, and the increase value is large, it indicates that the memory allocated to the query buffer is too small. You can increase the memory size of the query cache. If the monitoring results for the entire stable operation period are less than 40% hit rate, the value of Qcache_lowmem_prunes is also in a stable state, which indicates that the memory settings of our Query Buffer are too large, or the probability of repeated execution of the same query statement in business scenarios is low, at the same time, if a certain amount of freeing items are also monitored, you must consider a small memory size for the query cache, or even disable the query cache function. In business scenarios, you can sort and analyze the knowledge above, we should at least know the following aspects of the query cache: Slow Query The cache can accelerate the speed of query statements that already exist in the cache. You can obtain the correct record set without re-parsing and executing the statements. You can query the tables involved in the cache, each table object has its own global lock. If a table is used for DDL, flush tables, and other similar operations, the query cache information of the relevant table is cleared; DML operations of the table object, priority must be given to determine whether to clear the relevant query cache records, which will inevitably lead to lock wait events; Memory Allocation Problems in the query cache will inevitably generate some memory fragments; whether the query cache is the same query statement is demanding and not intelligent. Let's go back to the focus of this section. What business scenarios is the query cache suitable? As long as you understand the advantages and disadvantages of the query cache, it is not difficult to list it. business scenarios require that the entire system read-oriented business, such as portal, news, report, and Forum websites; the table object operated by the query statement. The DML operation is not performed frequently. You can use query_cache_type = 2 mode, and then specify the SQL statement with the SQL _CACHE parameter;