Detailed instructions on how to query and clear MySQL cache commands,

Detailed instructions on how to query and clear MySQL cache commands,

Mysql query Cache
The query cache function is to receive the same query as the previous query, and the server will query the cache results instead of re-analyzing and executing the previous query. This greatly improves the performance and saves time.
1. Configure query Cache
Modify the configuration file and modify query_cache_size and query_cache_type under [mysqld] (if not, add them ). Query_cache_size indicates the cache size, while query_cache_type has three values, indicating the select result set of the cached type. The values of query_cache_type are as follows:
0 or off disable Cache
1. Enable cache on, but do not save the select statement that uses SQL _no_cache. For example, select SQL _no_cache name from wei where id = 2 is not cached.
2. Enable conditional caching for demand. Only select statements with SQL _cache are cached, and select SQL _cache name from wei where id = 4 is cached.
In this example, restart the Mysql server.

query_cache_size=10M query_cache_type=1 

You can run the following command to check whether the function is enabled. have_query_cache indicates whether the function is enabled, and query_cache_limit indicates the buffer size that can be used by a single query. The default value is 1 M. query_cache_min_res_unit indicates the minimum size of Cache, the default value is 4 kb. Setting a large value is good for big data queries. However, if you query small data queries, memory fragmentation and waste may occur; query_cache_size and query_cache_type are our configurations above. query_cache_wlock_invalidate indicates that when other clients are performing write operations on the MyISAM table, if the query is in the query cache, whether to return the cache result or wait until the write operation is complete and then read the 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 execute the command First, select count (*) from wei; and then execute the command again. It can be seen that the time used for the second operation is much lower than that for the first execution, because the select result is read from the cache for the second time.

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) 

Run the following command to view the current cache status:

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 parameters are as follows:

• Qcache_free_blocks: Number of adjacent memory blocks in the cache. A large number of fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block.
• Qcache_free_memory: idle memory in the cache.
• Qcache_hits: increases when a query hits the cache.
• Qcache_inserts: It increases every time a query is inserted. By dividing the number of hits by the number of inserts, This is the ratio of no hits.
• Qcache_lowmem_prunes: the cache has insufficient memory and must be cleaned up to provide more space for queries. It would be better to look at this number for a long time; if this number continues to grow, it may indicate that the fragmentation is very serious, or the memory is very small. (The free_blocks and free_memory above can tell you what the situation is)
• Qcache_not_cached: the number of queries that are not suitable for caching. It is generally because these queries are not SELECT statements or use functions such as now.
• Qcache_queries_in_cache: number of queries (and responses) cached currently.
• Qcache_total_blocks: Number of cached blocks.

Clear Cache
Mysql FLUSH syntax (clear cache)

FLUSH flush_option [,flush_option]

If you want to clear some MySQL instances and use the internal cache, you should use the FLUSH command. To execute FLUSH, you must have the reload permission.
Flush_option can be any of the following:

• HOSTS is used most often. It is mainly used to clear the host cache table. If some of your hosts change the IP number, or if you get the error message Host... isblocked, you should clear the Host table. When connecting to the MySQL server, more than max_connect_errors errors occur continuously on a given host. For security purposes, MySQL will block further connection requests from the host. Clear the host table to allow the host to try again.
• LOGS closes the current binary log file and creates a new file. The new binary log file name is added to the number of the current binary file by 1.
• PRIVILEGES is also frequently used. Every time a new permission is granted, the new permission takes effect immediately just in case. The purpose is to re-load the permission from the database authorization table to the cache.
• Close all opened TABLES in TABLES. This operation also clears the content in the query cache.
• Flush tables with read lock disables all opened tables and adds a read lock to all TABLES in the database until unlock tables is executed explicitly. This operation is often used for data backup.
• STATUS resets most STATUS variables to 0.
• The MASTER node deletes the binary log files in all binary log index files, resets the index file of the binary log file to null, and creates a new binary log file. However, this is not recommended, changed to reset master. As you can imagine, I used to have a lot of soil. I could have done it with a simple command, but I needed several commands. In the past, I first checked out the current binary log file name, use purge.
• Query cache reassembles the Query Cache to eliminate fragments and improve performance, but does not affect the query cache's existing data. This point is Flush table and Reset Query Cache (the query cache content will be cleared) different.
• SLAVE is similar to Resetting replication, so that the SLAVE database forgets the replication location of the Master database, and also deletes the downloaded relay log. Like the Master database, it is not recommended to use it anymore, changed to Reset Slave. This is also useful.

Generally, Flush operations are recorded in binary log files, but flush logs, flush master, flush slave, and flush tables with read lock are not recorded, therefore, if the above operations are recorded in a binary log file, they will affect the slave database. Note: The Reset operation actually plays an enhanced version of the Flush operation.

Articles you may be interested in:

• Analysis on the principle and cache process of using Memcache to cache mysql Databases
• Mysql query cache description
• Mysql sets query Cache
• A feasible method for clearing mysql query Cache