MySQL Database performance optimization-Query caching

Query cache

The caching mechanism is simply to cache the SQL text and query results, if you run the same SQL, the server directly from the cache to take the results, without the need to parse and execute SQL, if the table changes, then all cached queries using this table will no longer be valid. The associated entry for the query cache value is emptied. Changes refer to any data or structure changes in the table, including INSERT, UPDATE, DELETE, truncate, ALTER TABLE, DROP TABLE, or drop database. This is not appropriate for tables that are frequently updated, and query caching can save a lot of performance for tables that do not often change data and have a large number of identical SQL queries.

queries must be identical in order to be considered the same . In addition, the same query string may be considered different for other reasons, and queries that use different databases, different protocol versions, or different default character sets are considered different queries and are cached separately.

1. Query Cache Open method

Edit the MySQL my.cnf, add the following, and then restart MySQL:

• Query_cache_size = 268435456--Sets the amount of memory used by the query cache, which defaults to 0 and the size must be 1024 of the entire number of parts
• Query_cache_type = 1-Cache all queries
• Query_cache_limit = 1048576--The maximum capacity of the cache's single query result set is allowed, by default it is 1MB, and the query result set beyond this parameter will not be set by the cache

2. Query cache Running Status analysis

Show status like ' qcache% ';

• Qcache_free_blocks: Large number indicates possible fragmentation
• Qcache_free_memory: Free memory in cache
• Qcache_hits: Increases each time the query hits the cache
• Qcache_insert: Increases each time a query is inserted
• Qcache_lowmem_prunes: The number of times that the cache is out of memory and must be cleaned up to provide space for more queries. This number is best seen over a long period of time, and if it continues to grow, the markings may be very fragmented or have little memory. (Combination of qcache_free_blocks and qcache_free_memory analysis in which case)
• Qcache_total_blocks: Total number of blocks in cache
• Qcache_not_cached: The number of queries that are not appropriate for caching, usually because these queries are not a SELECT statement or are using functions such as now ().

3. Query Cache Setting State analysis

Show variables like '%query_cache% ';

• Query_cache_limit: The maximum capacity of a single query result set that allows the cache, by default, is 1MB, and the query result set that exceeds this parameter is not set by the cache
• Query_cache_size: Sets the memory size used by the query cache, which defaults to 0 and the size must be an integer multiple of 1024
• Query_cache_type: The switch to control the query cache function can be set to: 0 (off, turn off the query cache function, how to do not use the query cache), 1 (on, open the query cache function, The query cache function is enabled as long as the SELECT statement does not use the Sql_no_cache hint, with query cache, 2 (DEMAND), but only if the Sql_cache hint is used in the SELECT statement Cache
• Query_cache_min_res_unit: The minimum size of the cache block. is a "double-edged sword", the default is 4KB, set the value of large data query is good, but if your query is small data query, it is easy to create memory fragmentation and waste.

4. Several rates

Query Cache Hit rate =qcache_hits/(qcache_hits+qcache_inserts)

Query Cache Fragmentation Rate =qcache_free_blocks/qcache_total_blocks * 100%

--If the query cache fragmentation rate exceeds 20%, you can use flush query cache; Organizing Cache Fragmentation

Query Cache Utilization = (query_cache_size-query_free_memory)/query_cache_size * 100%

--Query cache utilization below 25% indicates that the query_cache_size setting is too large to be properly reduced; query cache utilization is above 80% and Query_lowmem_prunes > 50, query_cache_ Size may be a little bit small, or too much fragmentation.

MySQL Database performance optimization-Query caching