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.

The first one:

The second one:

The third one:

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