Query_cache_limit for MySQL Optimization

Query_cache_limit
Query_cache_limit specifies the buffer size that can be used by a single query. The default value is 1 MB.

Query_cache_size Optimization

MySQL provides a query buffer mechanism starting from 4.0.1. Using the Query Buffer, MySQL stores the SELECT statement and query result in the buffer. In the future, the same SELECT statement (case sensitive) will be read directly from the buffer. According to the MySQL user manual, query buffering can achieve a maximum efficiency of 238%.

Check the status value qcache _ * to check whether the query_cache_size setting is reasonable (the preceding status value can be obtained using show status like 'qcache % ). If the qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the qcache_hits value is also very large, it indicates that the query buffer is frequently used. In this case, you need to increase the buffer size; if the qcache_hits value is small, it indicates that your query repetition rate is very low. In this case, the Query Buffer will affect the efficiency, so you can consider not to use the query buffer. In addition, adding SQL _no_cache to the SELECT statement explicitly indicates that no Query Buffer is used.

Parameters related to query buffering include query_cache_type, query_cache_limit, and query_cache_min_res_unit. Query_cache_type specifies whether to use the Query Buffer. It can be set to 0, 1, and 2. This variable is a session-level variable. Query_cache_limit specifies the buffer size that can be used by a single query. The default value is 1 MB. Query_cache_min_res_unit is introduced after version 4.1. It specifies the minimum unit for allocating the buffer space. The default value is 4 K. Check the status value qcache_free_blocks. If the value is very large, it indicates that there are many fragments in the buffer. This indicates that the query results are relatively small. In this case, reduce query_cache_min_res_unit.