High-performance MySQL reading notes-query cache

1 , MySQL query cache

Many database products are able to cache the execution plan of a query, and the SQL parsing and execution plan generation phases can be skipped for the same type of SQL. MySQL also has a different type of cache: Cache the full select query result, which is the query cache.

The query cache system keeps track of each table involved in the query, and if those tables change, all cached data associated with the table will be invalidated.

The query cache is completely transparent to the application. The application does not need to care whether MySQL is returning results by querying the cache or actually performing the returned results.

In addition, as today's universal servers become more powerful, query caching can be a factor that affects server scalability. It may become a resource-competitive single point for the entire server and may cause the server to zombie on a multi-core server.

1.1 working mechanism of query caching

Mysql determines whether to hit the cache is very simple, first will be cached results in the reference table, and then use the query statement, database name, the version of the client protocol and other factors to calculate a hash value, the hash value is associated with the results in the reference table. If the hash value calculated based on some related criteria can be associated with the data in the reference table when the query is executed, the query hit

Indicates whether the query cache is available through the Have_query_cache server system variable:

SHOW VARIABLES like ' Have_query_cache ';

+------------------+-------+

| variable_name | Value |

+------------------+-------+

| Have_query_cache | YES |

+------------------+-------+

To monitor query cache performance, use Show status to view cache state variables:

SHOW STATUS like ' qcache% ';

+-------------------------+--------+

| variable name | value |

+-------------------------+--------+

| Qcache_free_blocks | 36 |

| Qcache_free_memory | 138488 |

| Qcache_hits | 79570 |

| Qcache_inserts | 27087 |

| Qcache_lowmem_prunes | 3114 |

| qcache_not_cached | 22989 |

| Qcache_queries_in_cache | 415 |

| Qcache_total_blocks | 912 |

+-------------------------+--------+

any character, such as a space or comment, will cause a cache miss. If the query contains any user-defined functions, stored functions, user variables, temporary tables, system tables, or any tables that contain column-level permissions, they will not be cached.

Before checking the query cache, MySQL only does one thing, through a case-insensitive check to see if the SQL statement begins with the SEL.

If the query statement contains any indeterminate functions, it is not possible to find the cached results in the query cache.

1.2 Additional consumption
If query caching is used, additional resource consumption is generated during read and write operations, and consumption is mainly reflected in the following areas:
· The query will check if the cache is hit, this is relatively small consumption
· If the query cache is not hit, MySQL will determine whether the query can be cached, and if the system does not have a corresponding cache, the results will be written to the query cache.
· If a table is changed, all buffered queries that use that table are no longer valid and are removed from the buffer. This includes queries that map to a changed table using the merge table. A table can be changed by many types of statements, such as INSERT, UPDATE, DELETE, TRUNCATE, ALTER table, drop table, or drop DATABASE.
For InnoDB, some features of a transaction also limit the use of query caching. When the B table is modified in transaction A, the modification of table B before the transaction commits is not visible to other transactions. In order to ensure the correctness of the cached results, InnoDB takes steps to make all queries involving the B table non-cacheable before transaction a commits. If a transaction is running for a long time, it can severely affect the hit rate of the query cache.

Do not set the query cache space too large.
Because the query cache is protected by a global lock operation, if the query cache configuration memory is relatively large and contains a large number of query results, when the query cache fails, the long time to hold this global lock. Because the query cache's hit detection operations and cache invalidation detection also depend on this global lock, this can lead to a system zombie situation.

1.3 How the query cache uses memory

The query cache is completely stored in memory, so before we can configure and use it, we need to understand how it uses memory. In addition to the query results, it requires some memory specifically to determine what memory is currently available, what has been used, what mappings are used to store data tables and query results, and what is used to store query strings and query results. These basic management maintenance data structures probably need 40KB of memory resources, in addition, MySQL used to query the cache memory is divided into a block of data, the data block is longer.

When the server starts, it initializes the memory required for the query cache. This memory pool is initially a complete free block. When a query result needs to be cached, MySQL first requests a block of data from a large space block to store the results. This data block needs to be greater than the parameter Query_cache_min_re_unit

Allocating memory is a very slow operation because you need to lock the space block first and then find the appropriate size data block. MySQL tries to avoid this operation as often as possible. When a query result needs to be cached, it selects a memory block as small as possible and then stores the result in the qcache_inserts. If the data blocks are all used up, but there is still data remaining to be stored, then MySQL will request a new chunk (still the smallest possible chunk)-to continue storing the resulting data. When the query is complete, if the requested memory remains, MySQL frees it and puts it in the free memory section. (The allocation of memory blocks here, not through the malloc function to the operating system to request memory, but MySQL first create a query cache when a one-time application, and by themselves to manage a chunk of memory)

1.4 What happens when the query cache works

1, query cache can reduce the time of query execution, but can not reduce the network consumption of query results transmission, if this consumption is the main bottleneck of the entire query process, then the role of query caching is also very small.

2, for those who need to consume a lot of resources are usually very good for caching, for complex SELECT statements can use the query cache, but it should be noted that the table on the update, DELETE, insert operation compared to select is very few.

3, query Cache hit ratio: qcache_hits/(qcahce_hits+com_select), query cache hit rate is a good hit rate, needs specific analysis. As long as the query cache is more efficient than the additional consumption of the query cache, even a 30% hit rate is worthwhile. In addition, it is important to cache which queries are cached, and even if the cache hit ratio is low, it is still beneficial to improve the system performance.

4. Any SELECT statement that is not returned from the query cache is referred to as a "cache miss" to the following possible:

• Query statements cannot be cached, either because the query contains an indeterminate function, or the query result is too large to be cached.
• MySQL never handles this query, so the results have never been cached.
• Although the query results were previously cached, MySQL needed to delete some caches because the query cache ran out of memory, or the cache was invalidated because the data table was modified.

If there are a large number of cache cache misses on the server, but in fact the large queries are cached, there must be something like this:

The query cache has not yet been warmed up, i.e. MySQL has not yet had a chance to cache the query results.

The query statement has never been executed before. If the application does not repeatedly execute a query statement, there will still be a lot of cache misses even when the warmup is complete.

Too many cache failures, cache fragmentation, low memory, and data modification can invalidate the cache. Parameter com_* can be used to view data modifications (including Com_update,com_delete, etc.), and the qcache_lowmem_prunes can be used to see how many failures are caused by insufficient memory.

5, there is an intuitive way to reflect whether the query cache is good for the system, recommend an indicator: "Hit and write" ratio, that is, the ratio of qcache_hits and qcache_inserts. In experience, when this ratio is greater than 3:1, the query cache is usually valid, if it can reach 10:1 the best.

6. It is usually possible to determine whether the query cache needs to be scaled down or expanded by observing the actual usage of the query cache memory qcache_free_memory.

1.5 Configuring and maintaining the query cache

MySQL provides some parameters to control the behavior of the query cache, with the following parameters

· Query_cache_limit

The maximum query result that MySQL can cache, which is not cached when the query result is greater than this value. The default value is 1048576 (1MB)

If the result of a query exceeds this value, the value of qcache_not_cached is added to 1, and if an operation is always exceeded, you can consider adding sql_no_cache to the SQL to avoid additional consumption

· Query_cache_min_res_unit

The size (in bytes) of the smallest block allocated by the query cache. The default value is 4096 (4KB).

· query_cache_size

The amount of memory allocated for the cached query result, in bytes, and the value must be an integer multiple of 1024. The default value is 0, which disables query caching. Note that this amount of memory will be allocated even if Query_cache_type is set to 0.

· Query_cache_type

Sets the query cache type. Set the global value to set the type of all subsequent client connections. Clients can set session values to affect their own use of the query cache. The following table shows the possible values:

options
0 or off	do not cache or query the results. Note that this does not cancel the allocated query buffers. To cancel, you should set the Query_cache_size to 0.
1 or on	cache all query results except those that start with select Sql_no_cache.
2 or demand	Only query results that begin with select Sql_no_cache are cached.

The variable is set to on by default.

· query_cache_wlock_invalidate

In general, when a client makes a write lock on a MyISAM table, if the query results are in the query cache, other clients are not locked out and can query the table. Setting the variable to 1 allows the table to be write locked, making all queries to that table in the query cache illegal. This can force other clients attempting to access the table to wait while the lock is in effect.

1.6 Reduce fragmentation

There is no way to completely avoid fragmentation, but choosing the right query_cache_min_res_unit can help you reduce the wasted memory space caused by fragmentation. This value is too small to waste less space, but leads to frequent memory block requests, and if set too large, fragmentation can be a lot. Adjusting the appropriate values is actually a balancing of memory waste and CPU consumption. The average cache size for a single query can be computed by dividing the actual memory consumption (query_cache_size_qcache_free_memory) by QCACHE_QUERIES_IN_CAHCE. Fragments can be observed by qcahce_free_blocks.

With FLUSH_QUERY_CAHCE, this command re-sorts all the query caches and focuses all the free space on a chunk of the query cache.

A flowchart for analyzing and configuring the query cache:

1.7 Alternatives to query caching

The principle of MySQL query caching is: The fastest way to execute queries is not to execute, but queries still need to be sent to the server side, the server also needs to do a little bit of work, if for some queries do not need to communicate with the server effect will be? At this point, the client cache can greatly share the pressure on the MySQL server.

MySQL Focus: "High performance MySQL" Reading notes-index : http://blog.csdn.net/xifeijian/article/details/20312557

High-performance MySQL reading notes-query cache