Mysql Query cache _ MySQL

When talking to friends or colleagues about the mysql cache query function, I like to compare QueryCache to lychee, 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, when querying and querying a friend or colleague talk about the mysql Query Cache function, I like to compare Query Cache to lychee, it is a very nutritious thing, but if you eat too much at a time, it will easily lead to a nosebleed. although it is not particularly appropriate, there are many similarities, in addition, Query Cache has special business scenarios. unlike other database products, it caches Query statement execution plans and other information. Instead, it directly caches Query statement record sets and corresponding SQL statements. 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 the query cache can be summarized in two sentences:

Cache the result set and SQL statements of SELECT operations or pre-processing queries (supported at the beginning of 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 statements of the cache cannot be cached. There are roughly the following types:

The SQL _NO_CACHE parameter is added to the query statement;

The query statement contains the functions for obtaining values, including custom functions, such as CURDATE (), GET_LOCK (), RAND (), and CONVERT_TZ;

Query System databases: mysql and information_schema

The query statement uses SESSION-level variables or local variables in the stored procedure;

The lock in share mode and for update statements are used IN the query statement.

The query statement is similar to SELECT... INTO statement for data export;

When the transaction isolation level is Serializable, all query statements cannot be cached;

Query temporary tables;

Query statement with warning information;

Query statements that do not involve any tables or views;

A user can only query statements with column-level permissions;

Advantages and disadvantages of querying cache:

SQL statements do not need to be parsed or executed. of course, syntax parsing must be performed first and Query results can be obtained directly from the Query Cache;

The judgment rules of the query cache are not intelligent enough, which increases the threshold for using the query cache 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;

Configuration

Whether to enable mysql Query cache. Two parameters can be used: query_cache_type and query_cache_size. if any one of the parameters is set to 0, the query cache function is disabled. However, query_cache_type = 0 is recommended for correct settings.

Query_cache_type

Value range: 0 -- Query cache is not enabled;

Value range: 1 -- enable query cache. as long as the query cache requirements are met, the query statements and record set buckets on the client can be cached and 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 can be cached, used by other clients;

Query_cache_size

The minimum value of query_cache_size can be set to 40 kB. The maximum value can be considered almost unlimited. the application experience in the actual production environment tells us that the value is not larger, and the query cache hit rate is higher, it does not contribute much to server load reduction, but may offset the benefits brought by it, or even increase the server load. As for how to set it, we recommend setting it to 64 MB in the following sections;

Query_cache_limit

Limit the maximum number of query records that can be cached in the query cache to prevent a large query record set from occupying a large amount of memory. In addition, a small query record set is usually the most effective cache record set, the default value is 1 M. we recommend that you change it to 16 K ~ 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

Set the minimum unit for memory allocation in the query cache. set this parameter appropriately to reduce the number of requests and allocations for memory blocks. However, Setting this parameter 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 applies to the MyISAM engine. if a client adds a write lock to a table, other clients initiate query requests, and the query statements have corresponding query cache records, 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;

Maintenance

Query the fragment in the cache area

After the query cache is used for a period of time, memory fragments usually occur. To this end, you need to monitor the relevant status values and regularly sort memory fragments. The operation statement for fragment sorting is flush query cache;

Clear query cache data

These operations may trigger the query cache and clear all the cache information to avoid triggering or needing to know how to do it. The second type can trigger the query of all the cache data clearing commands:

(1). reset query cache;

(2). flush tables;

Performance monitoring

Fragmentation rate

Query cache memory fragmentation 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 cache size is set in the query cache. its meaning is as follows: query the cache information that has to be deleted from the query cache due to insufficient memory. The deletion algorithm is LRU;

Query_cache_min_res_unit

The minimum unit for memory block allocation is very important. setting too large may increase the probability of memory fragmentation, which may increase the consumption of memory allocation. after the system runs smoothly for a stage, you can refer to the formula calculation 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 query_cache_size is set to Too Small? we still only need to pre-set a value. The recommended value is 32 M ~ 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 in this week:

(1). Qcache_lowmem_prunes;

(2). hit rate;

(3). memory usage;

If the hit rate is 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 for the query buffer is too small, and the memory size of the query cache can be appropriately increased;

If the hit rate is lower than 40% and the Qcache_lowmem_prunes value remains stable for the monitoring information obtained during the entire stable operation period, it indicates that the memory settings in our query buffer are too large, in other words, the probability of repeated execution of the same query statement in business scenarios is low. if a certain amount of freeing items is also monitored, you must consider a small memory for the query cache, or even disable the query cache function;

Business scenarios

Through the above knowledge sorting and analysis, we should at least know the following aspects of the query cache:

The query cache can accelerate the speed of query statements that already exist in the cache, and obtain the correct record set without re-parsing and execution;

Query the tables involved in the cache. each table object has its own global lock;

If a table performs DDL, flush tables, and other similar operations, the query cache information of the relevant table is cleared;

For DML operations on table objects, you must first determine whether to clear the record information related to the query cache, which will inevitably lead to Lock wait events;

The query cache memory allocation problem inevitably produces some memory fragments;

The query cache has strict requirements on the same query statement and is not intelligent;

Let's go back to the focus of this section again and look at what business scenarios the cache is suitable? As long as the preceding advantages and disadvantages of the query cache are clear, it is not difficult to list them. business scenario requirements:

The entire system's read-oriented business, such as Portal, news, report, Forum, and other 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;

The above is the content of the Mysql Query cache _ MySQL. For more information, please follow the PHP Chinese network (www.php1.cn )!