Shulou(Shulou.com)06/01 Report--

This article mainly introduces the query mysql cache optimization details, I hope you can add and update some knowledge, if there are other issues to understand, you can continue to pay attention to my updated articles in the industry information.

Query caching has been deprecated since MySQL 5.7.20 and removed in MySQL 8.0.

Query cache works on the whole MySQL, mainly used to cache the query result set in MySQL, that is, the result set of an SQL statement execution, so it can only be used for select statements. When we turn on the Query Cache function, MySQL receives a select statement request, if it hits the cache, that is to say, the required result set is already in the cache, and the following series of steps do not need to be executed. The result set is returned directly from the cache to the client, which can greatly improve query performance!

SQL query database execution steps are as follows:

As can be seen from the above figure, when select hits the cache, a series of sql parsing, preprocessing, and optimizer processing are not required!

Database cache open mode:

The query_cache_size parameter can be adjusted according to the running status of your database query. The adjustment principle will be described later!

query_cache_type can be set to 0, 1, or 2(DEMOND), respectively, indicating that query cache is not used at all, all selects except for explicit requirements do not use query cache (use sql_no_cache) use query cache, only display requirements use query cache (use sql_cache)

You can use the following command show variables like '%have_query_cache %'; check whether it is enabled, where have_query_cache is enabled, query_cache_limit specifies the buffer size that can be used by a single query, the default is 1M;query_cache_min_res_unit is the minimum cache block size allocated by the system, the default is 4 KB, setting a large value is good for large data queries, but if your queries are small data queries, it is easy to cause memory fragmentation and waste;

View cache-dependent variables with the following command show variables like '%query_cache %';

Check query cache status with the following command show status like '%Qcache %';

Qcache_free_blocks: Number of memory blocks in Query Cache currently idle

Qcache_free_memory: Total Query Cache memory currently free

Qcache_hits: Number of Query Cache hits

Qcache_inserts: The number of times a new Query Cache is inserted into Query Cache, i.e. the number of misses.

Qcache_lowmem_prunes: When the Query Cache memory capacity is insufficient, the old Query Cache needs to be deleted from it to make use of the new Cache object.

Qcache_not_cached: Number of SQL not cached, including SQL that cannot be cached and SQL that will not be cached due to query_cache_type setting

Qcache_queries_in_cache: Number of SQL currently in Query Cache

Qcache_total_blocks: Total number of blocks in Query Cache

Check for hits.

The query cache hit ratio is calculated as Qcache_hits/(Qcache_hits+Com_select).

query_cache_min_res_unit defaults to 4KB. Setting a large value is good for large data queries, but if your queries are small data queries, it is easy to cause memory fragmentation and waste.

Query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can defragment the cache with FLUSH QUERYCACHE, or try reducing query_cache_min_res_unit if your queries are small.

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

Query cache utilization below 25% indicates that query_cache_size is too large and can be appropriately reduced; query cache utilization above 80% and Qcache_lowmem_prunes >50 indicates that query_cache_size may be a bit small or there are too many fragments.

Query cache hit ratio = (Qcache_hits - Qcache_inserts) / Qcache_hits * 100%

Optimization tips:

If the Qcache_lowmem_prunes value is large, it means that the query cache size setting is too small and needs to be increased.

If there are many Qcache_free_blocks, it means that there are many memory fragments and need to be cleaned up, flush query cache

query_cache_min_res_unit = (query_cache_size - Qcache_free_memory)/ Qcache_queries_in_cache

mysql in determining whether to hit the cache, any character difference, uncertain function will lead to cache hits, sub-query, stored procedures will also lead to query cache hits;

mysql query cache brings query performance improvement, but also brings other problems: first, open query cache, read to write will bring extra consumption

1) Before reading the query, you must first check whether it hits the cache.

2) If the cache hits, the result set is returned directly to the user without performing a series of subsequent operations. If the cache does not hit, the result set needs to be stored in the query cache after the query is completed, which is also a consumption.

3) It also has a great impact on write operations, because when we perform on the table, we also need to set the query cache of the table to invalid first. If the cache of this table is very large, then this is also a considerable consumption.

Both write and set invalidation operations to the query cache are exclusive lock operations

1) For innodb, transaction characteristics will limit the use of queries. If a table is modified in a transaction, then the query cache of this table needs to be set to invalid. In RR, RC mode, transaction characteristics will isolate this modification from other transactions! Before this transaction is submitted, other related queries of this table cannot be cached. Only after this table modifies the things submitted, the related queries of this table can be cached. Therefore, a large update transaction for a long time will lead to a significant decrease in query caching efficiency!

2) If we turn on a large query cache, it may cause performance problems in the entire database system during cache invalidation operations, because cache invalidation operations are global locks.

All queries requiring cache invalidation wait for this lock, and both cache hit detection and cache invalidation detection wait for this lock. When the cache is too large, this can lead to severe performance bottlenecks.

Therefore, when we open the query cache, we must open a reasonably sized query cache according to business needs to avoid large transactions online!

Read the above on the query mysql cache optimization detailed explanation, I hope to give you some help in the actual application. Due to the limited space of this article, it is inevitable that there will be deficiencies and needs to be supplemented. If you need more professional answers, you can contact our 24-hour pre-sales service on the official website to help you answer your questions at any time.

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.