MySQL Query Cache principle analysis
- 2020-05-06 11:47:59
- OfStack
The principle of
QueryCache(hereinafter referred to as QC) cache according to the SQL statement. If an SQL query starts with select, the MySQL server will attempt to use QC on it. Each Cache is stored as SQL text as key. SQL text is not processed until QC is applied. That is, two SQL statements, as long as the difference of even one character (for example, different case; One more space, etc.), then the two SQL will use a different CACHE.
However, the SQL text may be processed by the client. For example, in the official command line client, before sending SQL to the server,
will do the following
Filter all comments
Remove the SQL text before and after the space,TAB characters. Notice that this is before and after the text. The middle one won't be taken out.
In the following three SQL, because of the case of SELECT, the last one must have a different storage location from the other two in QC. The difference between the first one and the second one is that the second one has a comment, and it's going to look different on different clients. So, to be on the safe side, try not to use dynamic comments. In PHP's mysql extension, SQL's comments are not removed. That is, three SQLs will be stored in three different caches, even though the 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 will be cache, other statements like show and use will not be cache.
Because QC is such a front-end, such a simple caching system, if a table is updated, all QC of SQL associated with that table will be invalidated. Suppose a joint query involves tables A and B. If one of the tables A or B is updated (update or delete), the QC of the query will be invalidated.
In other words, if a table is being updated frequently, it is important to consider whether QC should be applied to some of the SQL involved. A frequently updated table, if applied with QC, may burden the database rather than reduce it. My general approach is to open QC by default and disable CACHE for some SQL statements involving frequently updated tables by adding SQL_NO_CACHE keywords. In this way, unnecessary memory operations can be avoided and memory continuity can be maintained as much as possible.
SQL statements, where queries are scattered, should not use QC. For example, the statement used to query the user and password - "select pass from user where name='surfchen'". Such statements, in a system, are likely to be used only when a user logs in. The query used for each user's login is different SQL text, QC is almost useless here, because the cached data is almost never used, it just takes up space in memory.
Storage blocks
In this section "storage block" and "block" mean the same thing
When QC caches a query result, it is not normal to allocate enough memory at once to cache the result. Instead, they are stored block by block as the query results are obtained. When a storage block is filled, a new storage block is created and memory is allocated (allocate). The memory allocation for a single block is controlled by the query_cache_min_res_unit parameter, which defaults to 4KB. The last memory block, if not fully utilized, will free up unused memory. If the cached result is large, it may lead to too many memory allocation operations, and the system system energy will also decline; If the cached results are small, then there may be too much memory fragmentation, which is likely to be too small to be allocated.
In addition to the storage blocks required for the query results, one storage block is required for each SQL text, and one storage block is required for the tables involved (the storage blocks of the tables are Shared by all threads, and only one storage block is required per table). Total number of storage blocks = number of query results *2+ number of database tables involved. That is, 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. The second query, if the same table is used, requires at least two storage blocks: the SQL text storage block and the query result storage block.
By looking at Qcache_queries_in_cache and Qcache_total_blocks, you can see how many blocks are occupied by each cache result on average. If their ratio is close to 1:2, 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_res_unit Then you can try to reduce the value of query_cache_min_res_unit.
Resize
If Qcache_lowmem_prunes grows rapidly, it means that many caches are being freed because of insufficient memory, not because related tables are being updated. Try to increase query_cache_size and try to make Qcache_lowmem_prunes zero.
Start parameter
show variables like 'query_cache%' can see this information.
query_cache_limit: if the result of a single query is greater than this value, Cache
is not Cache
query_cache_size: memory allocated to QC. If set to 0, it is equivalent to disabling QC. Note that QC must use approximately 40KB to store its structure, and if the setting is less than 40KB, it is equivalent to disabling QC. The smallest unit of QC is 1024 byte, so if you set a value that is not a multiple of 1024, the value will be rounded to the nearest multiple of 1024.
query_cache_type:0 completely prohibits QC and is not controlled by SQL statements (also note that the memory size set by the above parameter is allocated even if it is disabled here); 1 enable QC, you can disable SQL_NO_CACHE in SQL statement; 2 can be enabled in SQL statements using SQL_CACHE.
query_cache_min_res_unit: the size of the memory allocated for each QC result State
show status like 'Qcache%' can see this information.
Qcache_free_blocks: when a table is updated, the cache blocks associated with it will be free. But this block might still be in the queue, except at the end of the queue. These blocks will be counted to this value. free blocks can be cleared with FLUSH QUERY CACHE statement.
Qcache_free_memory: available memory, if small, consider increasing query_cache_size
Qcache_hits: the number of hits by cache since the mysql process started
Qcache_inserts: since the mysql process started, the number of QC has been increased to
Qcache_lowmem_prunes: the number of bars that were deleted from QC due to too little memory. Increase query_cache_size as much as possible.
Qcache_not_cached: since mysql process started, the number of read-only queries are not cache (including select, show use, desc etc)
Qcache_queries_in_cache: the number of SQL currently
cache Qcache_total_blocks: the number of blocks in QC. An query may be stored by more than one blocks, and the last of these blocks, unused memory will be freed. If query_cache_min_res_unit is 4KB, then three blocks will be generated. The first block is used to store sql statement text. This will not be counted into query+cache_size. The third block is 2KB (first allocate4KB, then release the extra 2KB). For each table, when the first SQL query associated with it is CACHE, an block is used to store the table information. That is, block is used in three places: table information, SQL text, and query results.
QueryCache(hereinafter referred to as QC) cache according to the SQL statement. If an SQL query starts with select, the MySQL server will attempt to use QC on it. Each Cache is stored as SQL text as key. SQL text is not processed until QC is applied. That is, two SQL statements, as long as the difference of even one character (for example, different case; One more space, etc.), then the two SQL will use a different CACHE.
However, the SQL text may be processed by the client. For example, in the official command line client, before sending SQL to the server,
will do the following
Filter all comments
Remove the SQL text before and after the space,TAB characters. Notice that this is before and after the text. The middle one won't be taken out.
In the following three SQL, because of the case of SELECT, the last one must have a different storage location from the other two in QC. The difference between the first one and the second one is that the second one has a comment, and it's going to look different on different clients. So, to be on the safe side, try not to use dynamic comments. In PHP's mysql extension, SQL's comments are not removed. That is, three SQLs will be stored in three different caches, even though the 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 will be cache, other statements like show and use will not be cache.
Because QC is such a front-end, such a simple caching system, if a table is updated, all QC of SQL associated with that table will be invalidated. Suppose a joint query involves tables A and B. If one of the tables A or B is updated (update or delete), the QC of the query will be invalidated.
In other words, if a table is being updated frequently, it is important to consider whether QC should be applied to some of the SQL involved. A frequently updated table, if applied with QC, may burden the database rather than reduce it. My general approach is to open QC by default and disable CACHE for some SQL statements involving frequently updated tables by adding SQL_NO_CACHE keywords. In this way, unnecessary memory operations can be avoided and memory continuity can be maintained as much as possible.
SQL statements, where queries are scattered, should not use QC. For example, the statement used to query the user and password - "select pass from user where name='surfchen'". Such statements, in a system, are likely to be used only when a user logs in. The query used for each user's login is different SQL text, QC is almost useless here, because the cached data is almost never used, it just takes up space in memory.
Storage blocks
In this section "storage block" and "block" mean the same thing
When QC caches a query result, it is not normal to allocate enough memory at once to cache the result. Instead, they are stored block by block as the query results are obtained. When a storage block is filled, a new storage block is created and memory is allocated (allocate). The memory allocation for a single block is controlled by the query_cache_min_res_unit parameter, which defaults to 4KB. The last memory block, if not fully utilized, will free up unused memory. If the cached result is large, it may lead to too many memory allocation operations, and the system system energy will also decline; If the cached results are small, then there may be too much memory fragmentation, which is likely to be too small to be allocated.
In addition to the storage blocks required for the query results, one storage block is required for each SQL text, and one storage block is required for the tables involved (the storage blocks of the tables are Shared by all threads, and only one storage block is required per table). Total number of storage blocks = number of query results *2+ number of database tables involved. That is, 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. The second query, if the same table is used, requires at least two storage blocks: the SQL text storage block and the query result storage block.
By looking at Qcache_queries_in_cache and Qcache_total_blocks, you can see how many blocks are occupied by each cache result on average. If their ratio is close to 1:2, 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_res_unit Then you can try to reduce the value of query_cache_min_res_unit.
Resize
If Qcache_lowmem_prunes grows rapidly, it means that many caches are being freed because of insufficient memory, not because related tables are being updated. Try to increase query_cache_size and try to make Qcache_lowmem_prunes zero.
Start parameter
show variables like 'query_cache%' can see this information.
query_cache_limit: if the result of a single query is greater than this value, Cache
is not Cache
query_cache_size: memory allocated to QC. If set to 0, it is equivalent to disabling QC. Note that QC must use approximately 40KB to store its structure, and if the setting is less than 40KB, it is equivalent to disabling QC. The smallest unit of QC is 1024 byte, so if you set a value that is not a multiple of 1024, the value will be rounded to the nearest multiple of 1024.
query_cache_type:0 completely prohibits QC and is not controlled by SQL statements (also note that the memory size set by the above parameter is allocated even if it is disabled here); 1 enable QC, you can disable SQL_NO_CACHE in SQL statement; 2 can be enabled in SQL statements using SQL_CACHE.
query_cache_min_res_unit: the size of the memory allocated for each QC result State
show status like 'Qcache%' can see this information.
Qcache_free_blocks: when a table is updated, the cache blocks associated with it will be free. But this block might still be in the queue, except at the end of the queue. These blocks will be counted to this value. free blocks can be cleared with FLUSH QUERY CACHE statement.
Qcache_free_memory: available memory, if small, consider increasing query_cache_size
Qcache_hits: the number of hits by cache since the mysql process started
Qcache_inserts: since the mysql process started, the number of QC has been increased to
Qcache_lowmem_prunes: the number of bars that were deleted from QC due to too little memory. Increase query_cache_size as much as possible.
Qcache_not_cached: since mysql process started, the number of read-only queries are not cache (including select, show use, desc etc)
Qcache_queries_in_cache: the number of SQL currently
cache Qcache_total_blocks: the number of blocks in QC. An query may be stored by more than one blocks, and the last of these blocks, unused memory will be freed. If query_cache_min_res_unit is 4KB, then three blocks will be generated. The first block is used to store sql statement text. This will not be counted into query+cache_size. The third block is 2KB (first allocate4KB, then release the extra 2KB). For each table, when the first SQL query associated with it is CACHE, an block is used to store the table information. That is, block is used in three places: table information, SQL text, and query results.