Classic description of MySQLQueryCache principles

We all know that MySQLQueryCache (QC) is cache based on the SQL statements actually used. For a related SQL query, if it starts with select, its MySQL server will try to use QC for it. Each Cache is saved using SQL text as the key. SQL text is not processed before QC is applied. That's it.

We all know that MySQLQueryCache (QC) is cache based on the SQL statements actually used. For a related SQL query, if it starts with select, its MySQL server will try to use QC for it. Each Cache is saved using SQL text as the key. SQL text is not processed before QC is applied. That's it.

We all know that MySQL QueryCache (QC) is cache based on the SQL statements actually used. For a related SQL query, if it starts with select, its MySQL server will try to use QC for it. Each Cache is saved using SQL text as the key.

SQL text is not processed before QC is applied.

That is to say, if the difference between the two SQL statements is even one character (for example, the case is different; if there is a space, etc.), the two SQL statements 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, the following processing will be performed:

1. filter all comments.

2. 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 SQL statements 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 QC is applied to a frequently updated table, it may increase the burden on the data base, rather than reduce the burden. The general practice is to enable QC by default, and disable the CACHE for some SQL statements involving frequently updated tables with the SQL _NO_CACHE keyword. In this way, you can avoid unnecessary memory operations as much as possible and maintain memory continuity as much as possible.

QC should not be used for SQL statements with scattered queries. For example, the statement 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 for login by each user are different SQL texts, and QC hardly works here, because the cached data is almost never used, they are only occupied in memory.

Storage Block

In this section, "Storage block" and "block" are the same meaning. When QC 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 slow storage result is large, it may lead to too many memory allocation operations, and the system performance will also decrease. If the cached results are 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 and 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.

MySQL QueryCache principle: 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