MySQL query Cache

The query cache mechanism of Mysql is QueryCache, which is based on SQL statements. If an SQL query starts with select, the MySQL server will try to use QC for it. Each Cache is saved using SQL text as the key. Before QueryCache is applied, SQL text is not processed. That is to say, as long as the difference between the two SQL statements is one character (for example, case is different; there is one space), the two SQL statements use a different QueryCache.

However, the SQL text may be processed by the client. For example, in the official command line client, before sending SQL to the server, the following processing will be performed:
Filter all comments
Remove spaces, tabs, and other characters before and after SQL text. Note: It is before and after the text. The middle won't be removed.

In the following three SQL statements, because of the Case sensitivity of SELECT, the last one and the other two are used in different storage locations in QC. The difference between Article 1 and Article 2 is that the latter has a comment, and different results may occur on different clients. Therefore, do not use dynamic comments if possible. In the mysql extension of PHP, SQL comments are not removed. That is, three sqls will be stored in three different caches, although their results are the same.
Select * FROM people where name = 'surfchen ';
Select * FROM people where/* hey ~ */Name = 'surfchen ';
SELECT * FROM people where name = 'surfchen ';

Currently, only select statements are cached. Other statements similar to show and use statements are not cached.

Because QC is such a front-end, such a simple cache system, if a table is updated, all QC of the SQL related to the table will be invalidated. Assume that A joint query involves tables A and B. If one of the tables A or B is updated (update or delete), the QC of this query will become invalid.

That is to say, if a table is frequently updated, it is necessary to determine whether QC should be performed on related SQL statements. If QueryCache is applied to a frequently updated table, the burden on the Mysql database may be increased, rather than the burden on the Mysql database. My general practice is to enable QueryCache by default, and disable the CACHE for some SQL statements involving frequently updated tables with the keyword SQL _NO_CACHE. In this way, you can avoid unnecessary memory operations as much as possible and maintain memory continuity as much as possible.

The SQL statements with scattered queries should not be cached. For example, if Mysql is used to query the user and password, select pass from user where name = 'surfchen '". Such a statement may be used only when a user logs on to the system. The queries used by each user for login are different SQL texts, and QueryCache will hardly work here, because the cached data will almost never be used, they are only occupied in memory.
Storage Block
In this section, "Storage block" and "block" are the same meaning.

When QueryCache caches a query result, it generally does not allocate enough memory at a time to cache the result. Instead, the query results are stored in blocks one by one. When a block is filled up, a new block will be created and allocated with memory (allocate ). The memory allocation size of a single block is controlled by the query_cache_min_res_unit parameter. The default value is 4 kb. If the last block cannot be fully used, unused memory will be released. If the cached result is large, the memory allocation operation may be too frequent, and the system performance will also decrease. If the cached result is small, this may cause too many memory fragments. If these fragments are too small, they may not be allocated for use.

In addition to storing the query results, each SQL text also requires a storage block, and the table involved also requires a storage block (the storage block of the table is shared by all threads, each table only needs one storage block ). Total number of storage blocks = number of query results * 2 + number of database tables involved. That is to say, when the first cache is generated, at least three storage blocks are required: The table information storage block, the SQL text storage block, and the query result storage block. If the second query uses the same table, at least two storage blocks are required: SQL text storage block and query result storage block.

By observing Qcache_queries_in_cache and Qcache_total_blocks, you can know the average storage block occupied by each cache result. If their ratio is close to, the current query_cache_min_res_unit parameter is large enough. If Qcache_total_blocks is much larger than Qcache_queries_in_cache, you need to increase the size of query_cache_min_res_unit.

Qcache_queries_in_cache * query_cache_min_res_unit (the block where SQL text and table information are located occupies a small amount of memory that can be ignored) if it is much larger than the query_cache_size-Qcache_free_memory, you can try to reduce the value of query_cache_min_res.
Resize
If Qcache_lowmem_prunes grows rapidly, it means that many caches are released because of insufficient memory, instead of updating related tables. Increase query_cache_size to minimize Qcache_lowmem_prunes growth.
Startup parameters
Show variables like 'query _ cache %.
Query_cache_limit: if a single query result is greater than this value
Query_cache_size: Memory allocated to QC. If it is set to 0, it is equivalent to disabling QC. Note that QC must use approximately 40 kb to store its structure. If it is set to be smaller than 40 kb, it is equivalent to disabling QC. The minimum unit of QC storage is 1024 bytes. Therefore, if you set a value that is not a multiple of 1024, the value is rounded to a value that is equal to or greater than 1024 closest to the current value.
Query_cache_type: 0: the QC is completely forbidden and is not controlled by SQL statements. (It may also be noted that, even if this function is disabled, the memory size set by the preceding parameter will still be allocated ); 1. Enable QC. You can disable it using SQL _NO_CACHE in SQL statements. 2. You can enable it using SQL _CACHE in SQL statements.
Query_cache_min_res_unit: memory size allocated to QC results each time
Status
Show status like 'qcache % 'can see this information.
Qcache_free_blocks: When a table is updated, the cache blocks related to it will be free. However, this block may still exist in the queue unless it is at the end of the queue. The blocks will be counted. You can use the flush query cache statement to clear free blocks.
Qcache_free_memory: available memory. If it is small, add query_cache_size.
Qcache_hits: Number of cache hits since the mysql process was started
Qcache_inserts: the number of qbs that have been added to QC since the mysql process was started.
Qcache_lowmem_prunes: number of items deleted by QC due to a small amount of memory. Increase query_cache_size and try to keep this value increasing by 0.
Qcache_not_cached: Number of read-only queries (including select, show, use, and desc) Not cached since the mysql process was started)
Qcache_queries_in_cache: Number of SQL statements currently cached
Qcache_total_blocks: number of blocks in QC. A query may be stored by multiple blocks, and the last of these blocks will be released if the memory is not used up. For example, a QC result occupies 6 kb of memory. If query_cache_min_res_unit is 4 kb, three blocks are generated. The first block is used to store the SQL statement text, this will not be counted into query + cache_size. The second block is 4 kb, and the third block is 2 kb (allocate4KB first, and then 2 kb is released ). Each table uses a block to store table information when the first SQL query related to it is cached. That is to say, the block will be used in three places: Table information, SQL text, and query results.

Another article:

If the MySQL Server load is high and the processing is very busy, you can start the Query Cache to accelerate the response time. cnf (Linux) or my. ini (Windows) does not include the following items: (Redhat is as follows:/etc/my. cnf; Debian and Ubuntu are in/etc/mysql/my. cnf)

Query_cache_size = 268435456
Query_cache_type = 1
Query_cache_limit = 1048576

In the preceding statement settings, query_cache_size is used to allocate MB of memory to Query Cache; query_cache_type = 1, which is used to Cache all queries; query_cache_limit is used to specify the memory of 1 MB for individual Query statements.

You can change the data as needed. After setting the data, save the document and restart MySQL.

 

Query_cache_type 0 indicates that no buffer is used. 1 indicates that the buffer is used, and 2 indicates that the buffer is used as needed.

Setting 1 indicates that the buffer is always valid. If no buffer is required, use the following statement:

SELECT SQL _NO_CACHE * FROM my_table WHERE...

If it is set to 2, you can use the following statement to enable buffering:

SELECT SQL _CACHE * FROM my_table WHERE...

You can use show status to view the buffer STATUS:

Mysql> show status like 'qca % ';
+ ------------------------- + ---------- +
| Variable_name | Value |
+ ------------------------- + ---------- +
| Qcache_queries_in_cache | 8 |
| Qcache_inserts | 545875 |
| Qcache_hits | 83951 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2343256 |
| Qcache_free_memory | 33508248 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 18 |
+ ------------------------- + ---------- +
8 rows in set (0.00 sec)

To calculate the hit rate, you need to know the number of SELECT statements executed by the server:

Mysql> show status like 'com _ sel % ';
+ --------------- + --------- +
| Variable_name | Value |
+ --------------- + --------- +
| Com_selected | 2889628 |
+ --------------- + --------- +
1 row in set (0.01 sec)

In this example, MySQL hits 2,889,628 of the 83,951 queries, and there are only 545,875 INSERT statements. Therefore, there is a big gap between the two and the total query of 2.8 million. Therefore, we know that the buffer type used in this example is 2.

In the example of type 1, the value of Qcache_hits is much greater than that of Com_select.

Several commands:
Mysql> show status like 'qcache % '; # view the running status of the mysql query Cache
Mysql> reset query cache; # reset the mysql query cache
Mysql> flush query cache # Clear query cache fragments