How to adjust the MySQL Query Buffer

The method for adjusting the MySQL Query Buffer is often used. The following describes how to adjust the MySQL Query Buffer for your reference.

QueryCache (QC) is cache 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. SQL text is not processed before QC is applied. That is to say, as long as the difference between the two SQL statements is not the same as a character, for example, case, or space), 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:
Filter all comments and remove spaces and tabs 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 with 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 database, rather than reduce the burden. My 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.

Block. In this section, "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 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 tables involved also require the storage block of a storage block table to be 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.

When * query_cache_min_res_unitsql the block where the text and table information are located occupies a small amount of memory, which can be ignored.) If it is much larger than query_cache_size-Qcache_free_memory, you can try to reduce the value of query_cache.

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 indicates that QC is completely disabled and is not controlled by SQL statements. In addition, even if this function is disabled, the memory size set by the preceding parameter will still be allocated.) 1. Enable QC, you can use SQL _NO_CACHE to disable SQL statements; 2. You can use SQL _CACHE to enable 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: the number of read-only queries not cached since the mysql process was started, including select, show, use, desc, etc)
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 unused memory in these blocks will be released. 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, which is not counted in query_cache_size, the second block is 4KB, the third block is 2KB first allocate4KB, and then the excess 2KB 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.

Show global status like 'com _ select': You can view the number of queries without cache, including read/write queries.

The total number of SELECT queries is equivalent:

Com_select + Qcache_hits + queries with errors found by parser

The value of Com_select is equivalent:

Qcache_inserts + Qcache_not_cached + queries with errors found during columns/rights check
Common calculation formula:

Qcache hit rate: Qcache_hits/(Com_select + Qcache_hits)

Qcache fragmentation rate: Qcache_free_blocks/Qcache_total_blocks

Average Query result set size: (query_cache_size-Qcache_free_memory)/Qcache_queries_in_cache

Explanation for querying cache variables in MySQL

Usage of MySQL conditional query statements

Common MySQL command line tools

Detailed description of how to name a shard using the mysql Command Line

Special usage of the SELECT command in MySQL