MySQL Cache Query Cache

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 the first and second is that the latter has a comment, and there will be different results on different clients. Therefore, do not use dynamic comments if possible. In the mysql tutorial extension of PHP, the SQL comments will not be 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 QC is applied to a frequently updated table, it may increase the burden on the data base, 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.

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 storage block is calculated by using 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 tutorial 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.

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, this value will be rounded to a value that is equal to or greater than 1024 closest to the current value.

Query_cache_type

0: QC is completely forbidden and is not controlled by SQL statements. (In addition, even if it is disabled here, 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, its related cache blocks 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, consider adding query_cache_size.

Qcache_hits

Number of cache hits since the mysql process was started

Qcache_inserts

The number of mysql processes added to QC since the mysql process was started.

Qcache_lowmem_prunes

The 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 currently cached SQL statements

Qcache_total_blocks

The 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 at the end, and the first block is used to store the SQL statement text, this will not be counted in query + cache_size. The second block is 4 kb, and the third block is 2 kb (allocate4KB first, and then 2 kb later ). 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.

Sort Buffer

When a query needs to sort the results, MySQL allocates a certain amount of memory for sorting. The memory size is controlled by sort_buffer_size. Remember, this parameter is for each query, rather than the total amount that can be allocated for all queries.

If sort_buffer_size is not large enough, the sorting result will be written into the temporary file in segments. After each end, the sorting results in the file are combined and sorted again until the final result is obtained. The smaller the sort_buffer_size, the more merge times. The merge count can be obtained through the state variable Sort_merge_passes. Theoretically, the smaller Sort_merge_passes, the faster the sorting. However, this may not be the case in actual applications. How to Set sort_buffer_size depends on the actual running environment. If you do not know how to test it, set Sort_merge_passes to 0.

Read_buffer_size read_rnd_buffer_size join_buffer_size thread_cache

Field Selection

From the binary perspective

Select col1, col2 from table procedure analyse ();, this statement can be used to recommend a field type based on the content of the current table.

MyISAM

Key_buffer_size

Cache index Syntax

Innodb

Innodb_buffer_pool_size

This is the most important parameter related to innodb. This parameter specifies the size of the innodb cache pool. This cache pool is used for storage

Innodb_file_per_table innodb_additional_mem_pool_size = 80 M innodb_log_file_size = 1G innodb_log_buffer_size = 16 M innodb_flush_method = O_DIRECT

(Max_connections * (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size) + key_buffer + innodb_bufer_pool_size + query_cache + tmp_table_size

System Problems

Linux:

Echo-n 0>/proc/sys/vm/swappiness

Huge page

Memlock

Optimization tools

Mysqltuner