Analyze MySQL cache startup method and parameters (query_cache_size)

MySQL query cache has been provided since version 4.1, but it is worth studying it today. Under the default configuration, this function of MySQL is not enabled. You may find that the value of have_query_cache is yes through show variables like '%query_cache%'; for MYSQL beginners It is easy to think that if this parameter is YES, it means that QueryCache is turned on. This is actually wrong. This parameter indicates whether the current version of MYSQL supports Query Cache. In fact, whether to enable the query cache depends on the value of another parameter: query_cache_size. The value is 0, which means the query cache is disabled, and the default configuration is 0.

Find the following content in the
configuration file
my.ini or my.cnf of MYSQL: # Query cache is used to cache SELECT results and later return them

# without actually executing the same query once again. Having the query

# cache enabled may result in significant speed improvements , if your

# have a lot of identical queries and rarely changing tables. See the

# “Qcache_lowmem_prunes” status variable to check if the current value

# is high enough for your load.

# Note: In case your tables change very often or if your queries are

# textually different every time, the query cache may result in a

# slowdown instead of a performance improvement.
query_cache_size=0

The above information is the default configuration, and its
comment
means that the query cache of MYSQL is used to cache select query results, and When the same query request is received next time, the actual query processing will no longer be performed and the results will be returned directly. Such a query cache can increase the speed of the query and optimize the query performance. The prerequisite is that you have a large number of the same or similar queries. , and rarely change the data in the table, otherwise there is no need to use this function. You can check whether the current value meets your current system load through the value of the Qcache_lowmem_prunes variable. Note: If the table you query is updated frequently and rarely has the same query, it is best not to use the query cache. Specific configuration method:

1. Set query_cache_size to a specific size. The specific size depends on the actual situation of the query, but it is best to set it to a multiple of 1024, with a reference value of 32M.

2. Add a line: query_cache_type=1

The query_cache_type parameter is used to control the type of cache. Note that this value cannot be set casually and must be set to a number. The optional items and descriptions are as follows:


If it is set to 0, then it can be said that your cache is useless at all, which is equivalent to being disabled. But in this case, does the system need to allocate the size set by query_cache_size? This question needs to be tested?

If set to 1, all results will be cached unless your select statement uses SQL_NO_CACHE to disable query caching.

If set to 2, only the queries that need to be cached through SQL_CACHE in the select statement will be cached.

OK, some of the files after configuration are as follows:

query_cache_size=128M

query_cache_type=1
Save the file, restart the MYSQL service, and then query as follows Verify whether it is really turned on:

mysql> show variables like ‘%query_cache%';

+——————————+———–+

| Variable_name                | Value     |

+——————————+———–+

| have_query_cache             | YES       |

| query_cache_limit            | 1048576   |

| query_cache_min_res_unit     | 4096      |

| query_cache_size             | 134217728 |

| query_cache_type             | ON        |

| query_cache_wlock_invalidate | OFF       |

+——————————+———–+

6 rows in set (0.00 sec)

The value of query_cache_size here is 134217728, we The setting is 128M, which is actually the same, but the units are different. You can convert it yourself: 134217728 = 128*1024*1024.

query_cache_type is set to 1 and displayed as ON. This has been mentioned before.

In short, seeing the above display indicates that the settings are correct, but whether the query can be cached in the actual query still needs to be tested manually. We can test it through the show status like '%Qcache%'; statement. Now we have enabled the query cache function. Before executing the query, let’s first look at the values ​​of the relevant parameters:

mysql> show status like ‘%Qcache%';

+————————-+———–+

| Variable_name           | Value     |

+————————-+———–+

| Qcache_free_blocks      | 1         |

| Qcache_free_memory      | 134208800 |

| Qcache_hits             | 0         |

| Qcache_inserts          | 0         |

| Qcache_lowmem_prunes    | 0         |

| Qcache_not_cached       | 2         |

| Qcache_queries_in_cache | 0         |

| Qcache_total_blocks     | 1         |

+————————-+———–+

8 rows in set (0.00 sec)

这里顺便解释下这个几个参数的作用：

Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理。

Qcache_free_memory:查询缓存的内存大小，通过这个参数可以很清晰的知道当前系统的查询内存是否够用，是多了，还是不够用，DBA可以根据实际情况做出调整。

Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。

Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数，次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。

Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小。

Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量。

Qcache_queries_in_cache:当前缓存中缓存的查询数量。

Qcache_total_blocks:当前缓存的block数量。
下边我们测试下：

比如执行如下查询语句

mysql> select * from user where id = 2;

+—-+——-+

| id | name  |

+—-+——-+

|  2 | test2 |

+—-+——-+

1 row in set (0.02 sec)

然后执行show status like ‘%Qcache%'，看看有什么变化：

mysql> show status like ‘%Qcache%';

+————————-+———–+

| Variable_name           | Value     |

+————————-+———–+

| Qcache_free_blocks      | 1         |

| Qcache_free_memory      | 134207264 |

| Qcache_hits             | 0         |

| Qcache_inserts          | 1         |

| Qcache_lowmem_prunes    | 0         |

| Qcache_not_cached       | 3         |

| Qcache_queries_in_cache | 1         |

| Qcache_total_blocks     | 4         |

+————————-+———–+

8 rows in set (0.00 sec)

对比前面的参数值，我们发现Qcache_inserts变化了。Qcache_hits没有变，下边我们在执行同样的查询

select * from user where id = 2，按照前面的理论分析：Qcache_hits应该等于1，而Qcache_inserts应该值不变（其他参数的值变化暂时不关注，读者可以自行测试），再次执行：
show status like ‘%Qcache%'，看看有什么变化：

mysql> show status like ‘%Qcache%';

+————————-+———–+

| Variable_name           | Value     |

+————————-+———–+

| Qcache_free_blocks      | 1         |

| Qcache_free_memory      | 134207264 |

| Qcache_hits             | 1         |

| Qcache_inserts          | 1         |

| Qcache_lowmem_prunes    | 0         |

| Qcache_not_cached       | 4         |

| Qcache_queries_in_cache | 1         |

| Qcache_total_blocks     | 4         |

+————————-+———–+

8 rows in set (0.00 sec)

OK，果然跟我们分析的完全一致。

The above is the detailed content of Analyze MySQL cache startup method and parameters (query_cache_size). For more information, please follow other related articles on the PHP Chinese website!