Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about how to query the cache in the MySQL database. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

I. caching conditions and principles

MySQL Query Cache is used to cache the SELECT statement we execute and the result set of the statement. MySql is similar to the typical KV storage in the specific technical details of implementing Query Cache, that is, the SELECT statement and the result set of the query statement are mapped by HASH and saved in a certain memory area. When the client initiates a SQL query, the search logic of Query Cache is to first verify the corresponding permissions on the SQL, and then find the results through Query Cache (note that it must be exactly the same, even if one more space or different case is considered different, even if the exact same SQL is used, different character sets, different protocols, etc., will be regarded as different queries and cached separately). It does not need to be analyzed and optimized by the Optimizer module, much less need to interact with any storage engine, reducing a large number of disk IO and CPU operations, so sometimes it is very efficient.

The workflow of the query cache is as follows:

1: hit condition

Cache is stored in a hash table, by querying SQL, querying database, client protocol, etc., as key. Before judging whether to hit or not, MySQL will not parse SQL, but directly use SQL to query the cache. Differences in any characters in SQL, such as spaces and comments, will cause the cache to miss.

If there is uncertain data in the query, such as the CURRENT_DATE () and NOW () functions, it will not be cached after the query is completed. Therefore, queries that contain uncertain data will certainly not find available caches.

2: workflow

The server receives SQL and looks up cache tables for key with SQL and some other conditions (additional performance consumption)

If the cache is found, the cache is returned directly (performance improvement)

If no cache is found, the SQL query is executed, including the original SQL parsing, optimization, etc.

After executing the SQL query results, store the SQL query results in the cache table (additional performance consumption)

II. Related SQL statements

2.1.View SQL cache parameters: show variables like'% query_cache%'

The meanings of each parameter are as follows:

Qcache_free_blocks: the number of adjacent memory blocks in the cache. A large number means there may be fragments. FLUSH QUERY CACHE will defragment the cache to get a free block.

Qcache_free_memory: free memory in the cache.

Qcache_hits: increments every time a query hits in the cache

Qcache_inserts: increments each time a query is inserted. The miss ratio is the number of hits divided by the number of inserts.

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. (the free_blocks and free_memory above can tell you which case it is.)

Qcache_not_cached: the number of queries that are not suitable for caching, usually because they are not SELECT statements or use functions such as now ().

Qcache_queries_in_cache: the number of queries (and responses) currently cached.

Qcache_total_blocks: the number of blocks in the cache.

2.2. Enable SQL cache: set global query_cache_type = 1

2.3. Turn off SQL cache: set global query_cache_type = 0

2.4.Setting cache space: set global query_cache_size = 1024m 102464m

2.5. Fixed SQL statement states that caching is not applicable: select sql_no_cache * from table name

Note: if you change the case of the SQL statement or if there is a data change in the data table, the cache will not be called.

2.6. Configure query cache

Vi / etc/my.cnf query_cache_size=300M query_cache_type=1

3. Clear the cache

FLUSH syntax of mysql (clear cache)

FLUSH flush_option [, flush_option]

If you want to clear some MySQL using internal caching, you should use the FLUSH command. In order to execute FLUSH, you must have reload permission.

A flush_option can be anything of the following:

HOSTS is the most frequently used and often encountered. It is mainly used to empty the host cache table. If some of your hosts change the IP number, or if you get an error message Host. Isblocked, you should clear the host table. When connecting to a MySQL server, more than max_connect_errors errors occur continuously for a given host, and MySQL will prevent further connection requests from the host for security reasons. Clearing the host table allows the host to try to connect again.

LOGS closes the current binary log file and creates a new file. The name of the new binary log file adds 1 to the number of the current binary file.

PRIVILEGES is also often used. Whenever a new permission is regranted, it is generally executed to reload the permission from the database authorization table to the cache, just in case, to make the new permission take effect immediately.

TABLES closes all open tables, and this 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 unlock tables is executed explicitly, which is often used when backing up data.

STATUS resets most state variables to 0.

MASTER deletes all binary log files in the binary log index file, resets the index file of the binary log file to empty, and creates a new binary log file, but this is no longer recommended and changed to reset master. You can imagine how corny you used to be. You could have done it with a simple command, but you need several commands. The previous practice is to find out the current binary log file name first, and then use purge operation.

QUERY CACHE restructures the query cache to remove fragmentation and improve performance, but does not affect the existing data in the query cache, unlike Flush table and Reset Query Cache (which will empty the contents of the query cache).

SLAVE is similar to reset replication, making the slave database forget the replication location of the master database, and also delete the downloaded relay log. Like Master, it is no longer recommended and changed to Reset Slave. This is also very useful.

Generally speaking, Flush operations are recorded in binary log files, but FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not recorded, so if the above operations are recorded in binary log files, it will affect the slave database. Note: the Reset operation actually acts as an enhanced version of the Flush operation.

IV. Memory management of cache

Cache will open up a query_cache_size in memory to maintain cached data, of which about 40K is used to maintain cached metadata, such as spatial memory, mapping of data tables and query results, mapping of SQL and query results, and so on.

MySQL divides this large memory block into small memory blocks (query_cache_min_res_unit), each storing its own type, size and query result data, as well as pointers to front and back memory blocks.

MySQL needs to set the size of a single small storage block and apply for a piece of space at the beginning of the SQL query (before the results are obtained), so even if your cache data does not reach this size, you need to use this size to store it (this is the same as the Block of the Linux file system). If the result exceeds the size of this memory block, you need to apply for another memory block. When the query is completed and it is found that the requested memory block has a surplus, the excess space will be released, which will cause the problem of memory fragmentation, as shown in the figure below.

Query cache the remaining query fragments after storing the query results

The blank space between query 1 and query 2 here is memory fragment. This free memory is released after query 1. Assuming that the space size is less than the memory block size set by MySQL, it can no longer be used, resulting in fragmentation problem.

Applying for memory allocation Block at the beginning of a query requires locking the entire free memory area, so allocating memory blocks is very resource-consuming. Note that the allocated memory mentioned here is allocated on the piece of memory that was opened up during MySQL initialization.

Fifth, the timing of cache use & performance

It is a difficult topic to measure whether opening the cache will improve the performance of the system.

1. According to cache hit ratio, cache hit ratio = cache hit times (Qcache_hits) / query times (Com_select)

two。 Through cache write rate, write rate = cache writes (Qcache_inserts) / queries (Qcache_inserts)

3. Judging by the hit-write rate, the ratio = Qcache_hits / Qcache_inserts, which is called the index that reflects the performance improvement in high-performance MySQL. Generally speaking, 3:1 is considered to be effective for the query cache, and it is best to reach 10:1.

Anything goes too far, especially for some systems that write frequently, turning on Query Cache may not improve system performance, but sometimes degrade it. The reason is that in order to ensure that the content of the Query Cache cache is absolutely consistent with the actual data, when a data table is updated, deleted and inserted, MySql will force the Query Cache of all queries SQL referenced to the table to be invalidated. For intensive write operations, enabling query caching is likely to cause frequent cache invalidation, indirectly lead to memory surge and CPU surge, which is a great burden on the already very busy database system.

6. Analysis of query cache problem

The above is how to query the cache in the MySQL database shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are 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.