Mysql cached query and purge commands using the detailed _mysql

Mysql Query Cache
the effect of query caching is that when a query receives a query that is the same as before, the server retrieves the results from the query cache, rather than parsing and executing the last query again. This greatly improves performance and saves time.
1. Configure Query Caching
Modify the configuration file to 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 the Query_cache_type has 3 values, representing the cache of that type of select result set, Query_cache_type each value as follows:
0 or off to turn off caching
1 or on opens the cache, but does not save the SELECT statement using Sql_no_cache, such as not caching the Select Sql_no_cache name from Wei where id=2
2 or demand open conditional caching, caching only SELECT statements with Sql_cache, caching select Sql_cache name from Wei where id=4
The example is configured to restart the MySQL server with the configuration complete.

query_cache_size=10m 
query_cache_type=1 

You can use the following command to see if it is open, where Have_query_cache is open, query_cache_limit specifies the size of the buffer that a single query can use, by default 1m;query_cache_min_res_ Unit for the system to allocate the smallest 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 cause memory fragmentation and waste; Query_cache_size and Query_cache_type are the top of our configuration Query_cache_wlock_invalidate indicates that when there are other clients writing to the MyISAM table, if a query is in query cache, whether to return the cache result or the wait-write operation completes the reread table to obtain the result.

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. Test
We perform the first time, select COUNT (*) from Wei, and then execute it again to see that the second time is much less than the first execution, because the second time the select result was 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 by following the 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 fragments. FLUSH QUERY Cache will defragment the cache to get a free block.
• Qcache_free_memory: Free memory in cache.
• Qcache_hits: Increases every time a query hits in the cache
• Qcache_inserts: Increases each time you insert a query. The hit count divided by the number of inserts is an out of proportion.
• Qcache_lowmem_prunes: The number of times the cache appears to be out of memory and must be cleaned up to provide space for more queries. This number is best for long periods of time; If the number is growing, it means that it may be very fragmented or that there is little memory. (The Free_blocks and free_memory above can tell you what kind of situation it belongs to)
• Qcache_not_cached: The number of queries that are not suitable for caching, usually because these queries are not SELECT statements or 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.

Clear Cache
MySQL's flush syntax (clear cache)

FLUSH flush_option [, Flush_option]

If you want to clear some MySQL using the internal cache, you should use the Flush command. In order to perform flush, you must have reload privileges.
Flush_option can be any of the following things:

• HOSTS This use most, often met. It is mainly used to flush the host cache table. If some of your hosts change IP numbers, or if you get the error message host ... isblocked, you should clear the host table. When the MySQL server is connected to a given host more than max_connect_errors error occurs continuously, MySQL for security needs will block the host further connection requests. Emptying the host table allows the host to try the connection again.
• LOGS closes the current binary log file and creates a new file, with the name of the new binary log file added 1 to the current binary file number.
• Privileges This is also often used, whenever the right after the weight, in order to just in case, let the new authority take effect immediately, generally do one, the target is from the Database authorization table reload permissions to the cache.
• Tables closes all open tables, and the operation clears the contents of the query cache.
• FLUSH tables with read lock closes all open tables and adds a read lock to all tables in the database until the unlock tables are displayed, which is often used for data backup.
• The status resets most state variables to 0.
• MASTER deletes the binary log files in all binary log index files, resets the index file for the binary log file to empty, and creates a new binary log file, but this is deprecated and changed to reset Master. Can imagine, before oneself is many dirt, originally a simple command can be done, but better several commands, the previous practice is to first find out the current binary log file name, and then use purge operation.
• Query cache to restructure queries, eliminate fragmentation, improve performance, but does not affect existing data in the query cache, which is not the same as flush table and reset query cache, which will empty query caching.
• SLAVE is similar to resetting replication, let the database forget the copy location of the main database, but also delete the downloaded relay log, as with Master, has not recommended use, changed to reset SLAVE. This is also very useful.

Generally, FLUSH operations are recorded in binary log files, but FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, FLUSH TABLES with READ lock are not logged, so if the above actions are recorded in the binary log file, will have an impact on the database. Note: The reset operation actually acts as an enhanced version of the flush operation.