MySQL query cache configuration and viewing

Mysql Query Cache
The purpose of the query cache is that when a query receives the same query as before, the server will retrieve the results from the query cache instead of parsing and executing the last query again. This greatly improves performance and saves time.
1. Configure the query cache
Modify the configuration file and modify the Query_cache_size and Query_cache_type under [mysqld] (if not added). Where query_cache_size represents the size of the cache, and Query_cache_type has 3 values that represent the cache of that type of select result set, Query_cache_type each value as follows:
0 or off to close the cache
1 or on opens the cache, but does not save the SELECT statement using Sql_no_cache, such as the Select Sql_no_cache name from Wei where id=2 is not cached
2 or demand open the conditional cache, cache only SELECT statements with Sql_cache, cache select Sql_cache name from Wei where id=4
The configuration of the example is configured to restart the MySQL server after the configuration is complete.

query_cache_size=10m  query_cache_type=1

You can use the following command to see if it is turned on, where Have_query_cache is on, query_cache_limit specifies the buffer size that can be used by a single query, and defaults to 1m;query_cache_min_res_ Unit for the system allocation of the minimum cache block size, the default is 4KB, set the value of large data query is good, but if your query is small data query, it is easy to create memory fragmentation and waste; Query_cache_size and query_cache_type are our configurations. Query_cache_wlock_invalidate means that when another client is writing to the MyISAM table, whether the query returns the cache result or waits for the write operation to complete before the table gets the result, if it is in the queries cache.

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 |             10485760 |  | Query_cache_type             | On       |  | query_cache_wlock_invalidate | OFF      |  +------------------------------+----------+  6 rows in Set (0.00 sec)  

2. Testing
We do this one time, select COUNT (*) from Wei, and then execute again, and you can see that the second time is much less than the first execution because the second time the select result is read from the cache.

Mysql> Select  Count (*) from Wei;  +----------+  | count (*) |  +----------+  |  4194304 |  +----------+  1 row in Set (3.92 sec)    mysql> Select  Count (*) from Wei;  +----------+  | count (*) |  +----------+  |  4194304 |  +----------+  1 row in Set (0.00 sec)  

We can view the current cache situation with the following command:

mysql> show status Like ' qcache% '; +-------------------------+----------+  | variable_name |  Value | +-------------------------+----------+  | Qcache_free_blocks |  1 | | Qcache_free_memory |  10475424 | | Qcache_hits |  1 | | Qcache_inserts |  1 | | Qcache_lowmem_prunes |  0 | | qcache_not_cached |  0 | | Qcache_queries_in_cache |  1 | | Qcache_total_blocks |  4 | +-------------------------+----------+ 8 rows in Set (0.00 sec) 

The meanings of each of these parameters are as follows:
Qcache_free_blocks: The number of contiguous memory blocks in the cache. A large number indicates that there may be fragmentation. FLUSH QUERY Cache organizes the fragments in the cache to get a free block.
Qcache_free_memory: Free memory in the cache.
Qcache_hits: Increases each time the query hits the cache
Qcache_inserts: Increases each time a query is inserted. The number of hits divided by the number of inserts is not the ratio.
Qcache_lowmem_prunes: The number of times that the cache is out of memory and must be cleaned up to provide space for more queries. This number is best seen over a long period of time, and if the number is growing, it can mean that fragmentation is very serious, or that there is little memory. (The above free_blocks and free_memory can tell you which kind of situation)
Qcache_not_cached: The number of queries that are not appropriate for caching, usually because these queries are not a SELECT statement or are using functions such as now ().
Qcache_queries_in_cache: The number of queries (and responses) that are currently cached.
Qcache_total_blocks: The number of blocks in the cache.

MySQL query cache configuration and viewing