Source: http://www.xymyeah.com/153.htmlprinciples
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, if the difference between the two SQL statements is even one character (for example, case is different; if there is a 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
- 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 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 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.
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 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 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, the value is 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
More please go to http://www.xymyeah.com/153.html