Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of query cache in mysql, which is very detailed and has certain reference value. Friends who are interested must finish it!

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 analysis of suitable business scenarios.

working principle

How query caching works can be summarized as follows:

Caching result sets and SQL statements for SELECT operations or preprocessing queries (Note: supported since 5.1.17)

For a new SELECT statement or preprocessing query statement, query the cache first to determine whether there is an available recordset. The criteria: whether it is exactly the same as the cached SQL statement, case-sensitive

For what kind of query statements cannot be cached by the query cache, there are 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 SESSION-level variables in query statements or local variables in stored procedures

LOCK IN SHARE MODE and FOR UPDATE statements are used in the query statement.

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

Transaction isolation level is: in the case of Serializable, all query statements cannot be cached

Query operation on temporary table

Query statements with warning messages

Query statements that do not involve any tables or views

A query statement in which a user only has column-level permissions

Advantages and disadvantages of query caching:

There is no need to parse and execute the SQL statement. Of course, syntax parsing must first obtain the query result directly from the Query Cache.

The judgment rules of query cache are not intelligent enough, that is, it increases the threshold for the use of query cache and reduces its efficiency.

The use of Query Cache increases the overhead of checking and cleaning recordsets in Query Cache, and there are tables cached by SQL statements, and each table has only one corresponding global lock.

Configuration

Whether or not to enable mysql query caching can be achieved through two parameters: query_cache_type and query_cache_size. Any parameter set to 0 means that query caching is turned off, but the correct setting recommends query_cache_type=0.

Query_cache_type

Value range: 0-query caching is not enabled

The value range is: 1-enable query caching. As long as the query cache meets the requirements of the query cache, the query statement and recordset bucket of the client can

Cached and used by other clients

Value range: 2-enable query caching. As long as the parameter: sql_cache is added to the query statement and meets the requirements of the query cache, the client's query statements and recordsets can be cached and used by other clients.

Query_cache_size

The minimum value of query_cache_size allowed to be set is 40K, while the maximum value can be regarded as almost unlimited. The application experience of the actual production environment tells us that the higher the value, the higher the hit rate of the query cache, nor the greater contribution to the reduction of server load, but may offset its benefits, or even increase the load on the server. As for how to set it, the following chapters describe Recommended setting: 64m

Query_cache_limit

Limiting the maximum cached query recordset in the query cache can prevent a large query recordset from taking up a large area of memory, and often the small query recordset is the most effective cache recordset, which is set to 1m by default. It is recommended to change it to the range between 16k~1024k, but the most important thing is to analyze and estimate the setting according to the actual situation of your application.

Query_cache_min_res_unit

Set the minimum unit of memory allocated by the query cache. To set this parameter properly, you can reduce the number of requests and allocations for memory blocks, but too large a setting may lead to an increase in the value of memory fragments. The default value is 4K, and it is recommended to set it to 1k~16K

Query_cache_wlock_invalidate

This parameter is mainly related to the MyISAM engine, if a client adds a write lock to a table, the query request initiated by other clients, and the query statement has a corresponding query cache record, whether it is allowed to read the record set information of the query cache directly, or wait for the write lock to be released. The default setting is 0, which allows

Maintain

Query the defragmentation of buffer area

After the query cache has been used for a period of time, memory fragmentation usually occurs. For this reason, it is necessary to monitor the relevant status values and defragment the memory regularly. Defragment operation statement: FLUSH QUERY CACHE

Clear the query cached data

Those actions may trigger the query cache and empty all the cached information to avoid knowing what to do when triggered or needed. The second category can trigger the query cache data emptying command:

(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 ratio = (Qcache_hits-Qcache_inserts) / Qcache_hits * 100%

Memory utilization

Query cache memory usage = (query_cache_size-Qcache_free_memory) / query_cache_size * 100%

Qcache_lowmem_prunes

This parameter value plays a very important role in detecting whether the memory size of the query cache is set, and its meaning is: the query cache removes the query cache information that has to be deleted from the query cache because of insufficient memory, and the deletion algorithm is LRU.

Query_cache_min_res_unit

The minimum unit of memory block allocation is very important. Setting too large may increase the probability of memory fragmentation, and too small may increase the consumption of memory allocation. After the system runs smoothly for a period, you can refer to the calculated value of the formula:

Query cache minimum memory block = (query_cache_size-Qcache_free_memory) / Qcache_queries_in_cache

Query_cache_size

How to determine whether the query_cache_size setting is too small, we can only preset a value first, which is recommended as: the area between 32M~128M, wait for the system to run smoothly for a period of time (at least 1 week), and observe the relevant status value during this week:

(1) Qcache_lowmem_prunes

(2)。 Hit rate

(3)。 Memory utilization

If the information obtained by the monitoring during the whole smooth running period is that the hit rate is more than 80%, the memory utilization rate is more than 80%, and the value of Qcache_lowmem_prunes keeps increasing, and the increasing value is also large, it means that the memory allocated to the query buffer is too small, and the memory size of the query cache can be increased appropriately.

If the information obtained by the monitoring during the whole smooth running period keeps a steady state when the hit rate is less than 40%, then the memory setting of our query buffer is too large, or the probability of repeatedly executing the same query statement in the business scenario is low. At the same time, if a certain amount of freeing items is detected, we must consider reducing the memory bar of the query cache or even turning off the query cache function.

Business scenario

Through the above knowledge carding and analysis, we know at least the following points about query caching:

Query caching can accelerate the speed of already cached query statements, and the correct recordset can be obtained without reparsing and executing

For the tables involved in the query cache, each table object has its own global lock

If the table performs similar operations such as DDL or FLUSH TABLES, it triggers the query cache information of the related table to be emptied.

The DML operation of a table object must give priority to determining whether it is necessary to clean up the record information of the relevant query cache, and lock waiting events will inevitably occur.

The problem of memory allocation of query cache will inevitably lead to some memory fragments.

Query cache is very demanding and unintelligent for whether it is the same query statement.

Let's go back to the focus of this section, what kind of business scenarios is query caching suitable for? As long as you have a clear understanding of the above advantages and disadvantages of query cache, it is not difficult to list the business scenario requirements:

The whole system is based on reading business, such as portal, news, newspaper phenotype, forum and other websites.

The table object operated by the query statement, which performs DML operations infrequently. You can use query_cache_type=2 mode, and then the SQL statement specifies the SQL_CACHE parameter.

The above is all the contents of the article "sample Analysis of query caching in mysql". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.