MySQL Cached Query Cache

Filter all comments

Remove the space before the SQL text, tab, and so on. Note that the front and rear of the text. The middle will not be removed.

In the following three SQL, because of the Select Case relationship, the last one and the other two are definitely different storage locations in the QC. And the first and second, the difference is that the latter have a comment, in different clients, there will be the same result. So, to be on the safe side, please try not to use dynamic annotations. In PHP's MySQL tutorial extensions, SQL annotations are not removed. That is, three of SQL is 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 will be cache, other similar show,use statements will not be cache.

Because the QC is so front-end, so simple a caching system, so if a table is updated, then the table related to the SQL of all the QC will be invalidated. Suppose a union query involves table A and table B, and if either table A or one of the table B is updated (update or delete), the QC of the query will be invalidated.

In other words, if a table is frequently updated, consider whether or not you should be QC the relevant SQL. A table that is frequently updated if the QC is applied, may increase the burden on the database rather than lighten the burden. My general practice is to turn on the QC by default, and disable CACHE for SQL statements that involve frequently updated tables, plus sql_no_cache keywords. This avoids unnecessary memory operations as much as possible and keeps the memory as continuous as possible.

You should also not use QC for SQL statements that are very decentralized queries. 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 on. The query used for each user's login is a different SQL text, and the QC is almost useless here because the cached data is almost never used, and they only occupy the place in memory.

Storage block

In this section, "Storage Block" and "blocks" are the same meaning

When QC caches a query result, it is not normally necessary to allocate enough memory to cache the results. But in the process of obtaining the results of the query, block storage. When a block of storage is filled, a new block of storage is created and allocated memory (allocate). The memory allocation size of a single storage block is controlled by the Query_cache_min_res_unit parameter, and the default is 4KB. The last storage block, if not fully utilized, the unused memory will be released. If the result of the cache is large, this may result in allocating memory operations too frequently and system systems to degrade, and if the cached results are small, it can cause excessive memory fragmentation that, if too small, is likely to be no longer allocated.

In addition to the query results that require storage blocks, each SQL text also requires a block of storage, and the table involved requires a storage block (the table's storage block is shared by all threads, and each table requires only one storage block). Total storage block = Query Result quantity *2+ The number of database tutorial tables involved. That is, when the first cache is generated, it requires at least three blocks of storage: The table Information store block, the SQL text storage block, and the query result storage block. The second query, if it uses the same table, requires at least two blocks of storage: The SQL text block, the query result storage block.

By observing Qcache_queries_in_cache and qcache_total_blocks, you can know the average storage block that each cache result occupies. Their proportions, if they are close to 1:2, indicate that the current query_cache_min_res_unit parameter is large enough. If there are many more qcache_total_blocks than Qcache_queries_in_cache, you need to increase the query_cache_min_res_unit size.

Qcache_queries_in_cache*query_cache_min_res_unit (The block of SQL text and table information occupies a small 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_unit.

Adjust size

If the qcache_lowmem_prunes grows fast, it means that many caches are freed because of insufficient memory, rather than because the related tables are updated. Try to increase query_cache_size and try to make qcache_lowmem_prunes zero.

Startup parameters

Show variables like ' query_cache% ' can see this information.

Query_cache_limit

If a single query result is greater than this value, no cache

Query_cache_size

The memory assigned to the QC. If set to 0, the equivalent is to disable QC. Note that the QC must use about 40KB to store its structure, and if the setting is less than 40KB, it is equivalent to disabling the QC. The minimum unit for QC storage is 1024 byte, so if you set a value that is not a multiple of 1024, this value is rounded to the nearest value equal to the current value of 1024.

Query_cache_type

0 completely prohibit QC, not under SQL statement control (in addition, it may be noted that even if this is disabled, the memory size set by the above parameter will still be allocated); 1 Enable QC, which can be disabled in SQL statements, and 2 can be enabled using Sql_cache in SQL statements.

Query_cache_min_res_unit

Allocate the memory size to the QC results each time

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. However, the block may still exist in the queue unless it is at the end of the queue. These blocks will be counted to this value. You can use the Flush QUERY cache statement to empty the free blocks.

Qcache_free_memory

Available memory, if very small, consider adding query_cache_size

Qcache_hits

Number of cache hits since the MySQL process started

Qcache_inserts

Number of QC additions since the MySQL process started

Qcache_lowmem_prunes

The number of bars that the QC was deleted due to too little memory. Increase query_cache_size and keep this value as much as 0 growth.

Qcache_not_cached

Number of read-only queries without cache (including SELECT,SHOW,USE,DESC, etc.) since the MySQL process started

Qcache_queries_in_cache

The number of SQL currently being cache

Qcache_total_blocks

Number of blocks in QC. A query may be stored by multiple blocks, and the last of these blocks, unused memory will be released. For example, a QC results to 6KB memory, if the Query_cache_min_res_unit is 4KB, then will generate 3 blocks, the first block to store the SQL statement text, this will not be counted into the query+cache_size, The second block is 4KB, and the third block is 2KB (allocate4kb first and then releasing the excess 2KB). Each table uses a block to store table information when the first SQL query associated with it is cache. In other words, block will be used in three places: table information, SQL text, query results.

Sort buffer

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

If the sort_buffer_size is not large enough, the result of the sort will be segmented into the temporary file. After each end, the sorting results in the file are merged and sorted again until the final result is reached. The smaller the sort_buffer_size, the more times it will be merged. The number of merges can be obtained by sort_merge_passes the state variable. Theoretically, the smaller the sort_merge_passes, the quicker the sorting. But this may not be the case in practical applications. Sort_buffer_size How to set up a test that needs to be tested according to the actual operating environment. If you really don't know how to test, then set the sort_merge_passes to 0.

Read_buffer_size read_rnd_buffer_size join_buffer_size Thread_cache

Field Selection

Consider from a binary perspective

Select Col1,col2 from table PROCEDURE analyse (), which provides a recommendation for a field type based on the contents of the current table.

MyISAM

Key_buffer_size

CACHE INDEX Syntax

InnoDB

Innodb_buffer_pool_size

This is one of the most important parameters associated with InnoDB. This parameter specifies the size of the InnoDB cache pool. This cache pool is used to store

Innodb_file_per_table innodb_additional_mem_pool_size=80m innodb_log_file_size=1g Innodb_log_buffer_size=16M 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 related

Linux:

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

Huge page

Memlock

Optimization Tools

Mysqltuner