Some exchanges about MySQL Query Cache

A memory usage alarm occurred in MySQL online today, so I checked the parameters of mysql memory usage, focusing on caching, about the use of innodb_buffer_pool_size and query cache.

query_cache_type is turned on by default, and the default size of the cache area query_cache_size is 32M. It is usually recommended not to exceed 256M. You can use the query cache parameters to see the specific value:

mysql> show variables like '%cache%';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| binlog_cache_size            | 32768                |
| binlog_stmt_cache_size       | 32768                |
| have_query_cache             | YES                  |
| key_cache_age_threshold      | 300                  |
| key_cache_block_size         | 1024                 |
| key_cache_pision_limit     | 100                  |
| max_binlog_cache_size        | 18446744073709547520 |
| max_binlog_stmt_cache_size   | 18446744073709547520 |
| metadata_locks_cache_size    | 1024                 |
| query_cache_limit            | 1048576              |
| query_cache_min_res_unit     | 4096                 |
| query_cache_size             | 33554432             |
| query_cache_type             | ON                   |
| query_cache_wlock_invalidate | OFF                  |
| stored_program_cache         | 256                  |
| table_definition_cache       | 400                  |
| table_open_cache             | 512                  |
| thread_cache_size            | 8                    |
+------------------------------+----------------------+
18 rows in set (0.00 sec)


mysql>

"Qcache_free_blocks": How many remaining blocks are currently in Query Cache. If the value is larger,
, it means there are more memory fragments in the Query Cache, and you may need to find a suitable opportunity to defragment ().
● “Qcache_free_memory”: The current remaining memory size in Query Cache. Through this parameter, we can more accurately observe whether the Query Cache memory size in the current system is sufficient, whether it needs to be increased or is too much;
● " Qcache_hits": How many hits. Through this parameter we can see the basic effect of Query Cache;
● "Qcache_inserts": how many misses and then insert. Through the two
parameters of "Qcache_hits" and "Qcache_inserts" we can calculate the hit rate of Query Cache:
Query Cache hit rate = Qcache_hits / ( Qcache_hits + Qcache_inserts );
● "Qcache_lowmem_prunes": How many Query were cleared out of the Query Cache due to insufficient memory. By
"Qcache_lowmem_prunes" and "Qcache_free_memory" are combined with each other, we can more clearly understand whether the memory size of Query Cache in our system is really sufficient and whether Very frequently, Query is replaced due to insufficient memory

● "Qcache_not_cached": due to the setting of query_cache_type or the number of Query that cannot be cached;
● "Qcache_queries_in_cache": The number of Query in the cache in the current Query Cache;
● "Qcache_total_blocks": The number of blocks in the current Query Cache;
Limitations of Query Cache
Query Cache stores logically structured Result Sets instead of physical data pages, so while performance is improved

will also be subject to some specific restrictions.
a) Versions before 5.1.17 cannot Cache Query that helps set variables, but starting from version 5.1.17, Query Cache has
began to support Query that helps set variables;
b) The external query SQL in all subqueries cannot be cached;
c) The Query in Procedure, Function and Trigger cannot be cached;
d) Query that contains many other functions that may get different results every time it is executed cannot be cached.
In view of the above limitations, when using Query Cache, it is recommended to use it through precise settings, so that only the data of appropriate tables can be used Enter Query Cache to only allow certain Query query results to be cached.

In addition, if the
Qcache_free_blocks value is a bit high, you can use flush query cache to clean it up.

A friend’s suggestion:

The first one:

If there are many read operations, look at the ratio. To put it simply, if it is a user list table, or the data ratio is relatively fixed, such as a product list, it can be opened. The premise is that these libraries are relatively concentrated and the practices in the database are relatively small. .

The second one:

When we "cheat", for example, when we stress test during bidding, we can still receive data by turning on the query cache. To achieve the effect of qps surge, of course the front-end connection pool and other configurations are the same. In most cases, if there are a lot of writes but not many visits, then don’t open it. For example, on social networking sites, 10% of people generate content and the remaining 90% are consuming. Opening it will still have a good effect, but you If it is QQ message or chat, it will be very fatal.

The third one:

It doesn’t matter if the website is small or does not have high concurrency. Under high concurrency, you will see a lot of qcache locks waiting, so Generally, under high concurrency, it is not recommended to open query cache

The above is some exchange of experience about MySQL Query Cache. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!