Shulou(Shulou.com)06/03 Report--

What is the purpose of the query cache Query_cache in Mysql? I believe that most people do not understand, in order to let you better understand, the editor summed up the following content, do not say much, let's look down together.

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 | 16106127360 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +-+-| -+ 6 rows in set (0.01 sec)

Where:

Query_cache_type: whether to enable caching. Values include ON, OFF and DEMAND. Default is ON.

-query caching is turned off when the value is OFF or 0

-if the value is ON or 1, the query caching function is enabled, and the result of SELECT will be cached if it meets the caching condition. Otherwise, it will not be cached, and SQL_NO_CACHE will not be cached explicitly.

-when the value is DEMAND or 2, the query caching function is performed as needed, and only the SELECT statements that explicitly specify SQL_CACHE will be cached; others are not cached.

Query_cache_wlock_invalidate: when another client is writing to the MyISAM table, if the query is in query cache, whether to return the cache result or wait for the write operation to complete and then read the table to get the result.

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

The minimum cache block size allocated by query_cache_min_res_unit to the system is 4KB by default. Setting a large value is good for big data queries, but if your queries are small data queries, it is easy to cause memory fragmentation and waste; query_cache_size: indicates the size of the cache.

After understanding the above metrics, we can set them in the configuration file my.cnf of mysql. Then restart the mysl server. Add a parameter under [mysqld]. Generally, query_cache_size and query_cache_type are set.

2. The above view shows the configuration of our cache, which usually reads values from the configuration file, but sometimes we need to check the data cache size in the current mysql in real time.

Mysql > show status like'% qcache%' +-+-+ | Variable_name | Value | +-+-+ | Qcache_free_blocks | 1 | Qcache_free_memory | 1031832 | Qcache_hits | 0 | | Qcache_inserts | 0 | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 16489053 | Qcache_queries_in_cache | 0 | Qcache_total_blocks | 1 | +-+-+ 8 rows in set (sec)

Explanation:

Qcache_free_memory: free memory in the cache.

Qcache_total_blocks: the number of blocks in the cache.

Qcache_lowmem_prunes: the number of times the cache runs out of memory and must be cleaned to provide space for more queries. This number is best seen over a long period of time; if it is growing, it means that the fragmentation may be very serious, or there may be very little memory. How to determine, you need to look at Qcache_free_blocks and Qcache_free_memory two indicators.

3. Clear the cache:

Flush query cache command

Increasing the cache will help us improve the efficiency of our queries:

Here's an example:

Mysql > use mob_adnDatabase changedmysql > select count (*) from creative_output;+-+ | count (*) | +-+ | 87151154 | +-+ 1 row in set (3 min 18.29 sec) mysql > select count (*) from creative_output +-+ | count (*) | +-+ | 87151154 | +-1 row in set (0.00 sec) mysql > select count (*) from creative_output;+-+ | count (*) | +-+ | 87151154 | +-+ 1 row in set (0.00 sec)

The above is a brief introduction to the role of Query_cache, the detailed use of the need for you to use their own hands in order to understand. If you want to know more about it, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.