MySQL's unique weapon for accelerating query speed: Query caching

" Guide "

When talking to a friend or colleague about the MySQL query cache function, the individual likes to compare the query cache function to the Litchi , is very nutritious things, but once eat too much, it is easy to get angry and nosebleeds, although not particularly appropriate metaphor, but there are many similar places. In addition, the query cache has its own special business scenario, MySQL is not like other database products, caching query statement execution plan and other information, but directly cache query statement result set and corresponding SQL statement. This article will introduce you to the query cache related knowledge, I hope we can guide you to correctly use the query cache weapon.

The MySQL query cache is analyzed in more detail from five angles: How query cache works, how to configure, how to maintain, how to judge the performance of query caching, and the appropriate business scenario analysis.

n Working principle

Query caching works, basically can be summed up in two words:

Cache the result set and SQL statements for the select operation or preprocessing query (Note: 5.1.17 start support);

L New SELECT statement or preprocessing query statement, first go to query the cache, determine whether there is a recordset available, judging the criteria: with the cached SQL statement, is exactly the same, case-sensitive (note: Storage SQL statement MD5 value);

The query cache for what query statement cannot cache its recordset, roughly the following categories:

L ADD the Sql_no_cache parameter in the query statement;

L Query statement contains the function to obtain the value, including the custom function, such as: Curdate (), Get_lock (), RAND (), Convert_tz, etc.;

L Query the system database: MySQL, information_schema

L Use session-level variables or local variables in stored procedures in query statements;

L Use the lock in SHARE MODE, for UPDATE statement in the query statement;

L Query statement similar to select ... Into the statement that exports the data;

The transaction isolation level is: Serializable case, all query statements can not be cached;

L Query operations on temporary tables;

L Query statement with warning information;

L No query statements involving any tables or views;

L A user has only a column-level permission query statement;

Pros and cons of query caching:

L syntax parsing and conforming to the syntax requirements, do not need to do any parsing and execution of SQL statements, directly from the query cache to obtain the results;

The decision rules of the query cache are not intelligent enough, that is to raise the threshold of using the query cache and reduce its efficiency;

The application of the query cache will increase the cost of checking and cleaning the recordset in query cache, and there is a table with SQL statement cache, and each table has only one corresponding global lock;

N 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 are recommended query_cache_type=0.

L Query_cache_type

The domain value is: 0-no query caching is enabled;

The range is: 1-enable the query cache, as long as the requirements of the query cache, the client's query statements and the recordset can be cached, all other clients shared use;

The range is: 2-enable the query cache, as long as the query statement added parameters: Sql_cache, and meet the requirements of the query cache, the client's query statement and Recordset, it can be cached, a total of other clients shared use;

L Query_cache_size

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

L Query_cache_limit

Limit the query buffer maximum cache query Recordset, you can avoid a large query recordset takes up a large number of memory areas, and often small query Recordset is the most effective cache recordset, the default is set to 1M, it is recommended to modify the value range between 16k~1024k, But the most important is based on the actual situation of their application to analyze, estimate to set;

L Query_cache_min_res_unit

Set the minimum unit of memory allocated for the query cache, and to set this parameter appropriately, you can reduce the number of requests and allocations for memory blocks, but setting too large may cause memory fragmentation to rise. The default value is 4K and the recommended setting is 1k~16k

L Query_cache_wlock_invalidate

This parameter mainly involves the MyISAM engine, if a client has a write lock on a table, other client-initiated query requests, and the query statement has a corresponding query cache record, whether to allow direct reading of the query cached recordset information, or wait for the release of the write lock. The default setting is 0, which is allowed;

N Maintenance

L Query the cache for defragmentation

After the query cache is used for a period of time, memory fragmentation generally occurs, which requires monitoring the relevant state values, and periodically defragment the memory, defragment the operation statement: FLUSH QUERY Cache;

L EMPTY the query cache data

Those operations may trigger the query cache, emptying all cached information to avoid triggering or needing to know what to do, and the two classes of commands that trigger the full emptying of the query cache data:

(1). RESET QUERY CACHE;

(2). FLUSH TABLES;

n Performance Monitoring

L Fragmentation Rate

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

L Hit ratio

Query cache Hit Ratio =qcache_hits/(qcache_hits + qcache_inserts) * 100%

L Memory Utilization

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

L Qcache_lowmem_prunes

The parameter value for detecting the memory size setting of the query buffer is very critical, its representative meaning is: query cache to the memory of the query buffer to delete from the query cache information, delete algorithm for LRU;

L 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 consumption of memory allocation, for this, after a smooth system run a phase, you can refer to the calculation of the formula value:

Query cache minimum memory block = (query_cache_size–qcache_free_memory)/Qcache_queries_in_cache

L Query_cache_size

How do we determine if query_cache_size is set too small, and still only pre-set a value, recommended as: 32m~128m between the region, the system to run a period of time (at least 1 weeks), and observe the relevant state values during the week:

(1). Qcache_lowmem_prunes;

(2). Hit Ratio;

(3). Memory utilization;

If the information obtained during the entire smooth running period is more than 80%, the memory usage exceeds 80%, and the value of the qcache_lowmem_prunes increases continuously, and the added value is larger, then we allocate too little memory for the query buffer, The memory size of the query buffer can be increased appropriately;

If the information obtained during the smooth running period is monitored, the value of the hit rate below 40%,qcache_lowmem_prunes is also maintained in a steady state, indicating that our query buffer is too large in memory settings, or the probability of a query statement being repeated by the business scenario is low, At the same time, if a certain amount of freeing items is also detected, it must be considered that the memory of the query cache is small and even the query caching function is closed.

n Business Scenarios

Through the above knowledge combing and analysis, we know at least the following points of query cache:

The query cache can speed up the already existing cached query statements and obtain the correct recordset without re-parsing and executing;

L Query the table involved in the cache, each Table object has a lock that belongs to its global nature;

L table if DDL, FLUSH TABLES and other similar operations, trigger the related table query cache information emptied;

L Table Object DML operation, must first determine whether to clean up the relevant query cache record information, will inevitably appear the lock wait event;

L Query Cache memory allocation problem, inevitably produce some memory fragments;

L query cache is the same query statement, the requirements are very demanding, but not intelligent;

Let's go back to the point in this section, what kind of business scenario does the query cache fit into? As long as it is clear that the query cache the above advantages and disadvantages, it is not difficult to list out, business scenario requirements:

L The whole system to read the main business, such as portal type, news category, report type, forum and other web sites;

L Query the Table object of the operation of the statement, do not frequent DML operations, you can use the query_cache_type=2 mode, and then the SQL statement plus sql_cache parameter specified;

Reference: http://www.zhdba.com/mysqlops/2011/08/10/mysql-query-cache/

MySQL's unique weapon for accelerating query speed: Query caching