Treat Query_cache_size with Care

Wu Bingxi Source: http://www.mysqlsupport.cn/ Contact Information: wubingxi#gmail.com reprint Please specify as / translators and sources, and can not be used for commercial purposes, offenders must investigate.

For users who use MySQL, you will not be unfamiliar with this variable. In previous years of MyISAM engine optimization, this parameter was also an important optimization parameter. But with the development, this parameter also reveals some problems.

The memory of the machine is getting bigger and larger, and people are accustomed to allocating the previously useful parameters more and more. This increase in parameters also raises a number of questions. Let's start by analyzing how Query_cache_size works:

After a select query is working in DB, the DB caches the statement, and when the same SQL is called again in DB, the DB returns the result from the cache to the client when the table is not changed.

There is a shut-down point, that is, when DB is working with Query_cache, it requires that the table involved in the statement not be changed during this time period. So what happens to the data in Query_cache if the table is changed? The first thing to do is to invalidate the Query_cache and the table-related statements, and then write the update. So if the query_cache is very large, the query structure of the table is more, the query statement invalidation is slow, an update or insert will be very slow, so see is update or insert how slow.

Therefore, in the database write volume or update volume is also relatively large system, this parameter is not suitable for allocation too large. And in the high concurrency, write a large-scale system, built to disable the function.

Treat Query_cache_size with Care