Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to achieve query buffering in MySQL. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

How to adjust the MySQL query buffer

QueryCache (hereinafter referred to as QC) is cache according to the SQL statement. If a SQL query starts with select, then the MySQL server will try to use QC against it. Each Cache is stored as SQL text as a key. SQL text is not processed until QC is applied. In other words, as long as there is a difference of even one character between two SQL statements (for example, different case; an extra space, etc.), the two SQL will use a different CACHE.

However, the SQL text may be processed by the client. For example, in an official command line client, the following processing will be done before sending SQL to the server:

Filter all comments and remove spaces, TAB and other characters before and after SQL text. Notice that it is in front of and after the text. The middle one will not be removed.

Of the following three SQL, because of the case of SELECT, the last one must be stored in a different location from the other two in QC. The difference between the first and the second is that the latter has a comment that will have different results on different clients. So, just to be on the safe side, try not to use dynamic comments. In PHP's mysql extension, SQL's comments will not be removed. That is, three SQL will be stored in three different caches, although their results are all the same.

Select*FROMpeoplewherename='surfchen'

Select*FROMpeoplewhere/*hey~*/name='surfchen'

SELECT*FROMpeoplewherename='surfchen'

Currently, only select statements will be cache, while other show,use-like statements will not be cache.

Because QC is such a front-end and such a simple caching system, if a table is updated, all QC of the SQL associated with that table will be invalidated. Suppose table An and table B are involved in a federated query, and if one of table An or table B is updated (update or delete), the QC of this query will be invalidated.

That is, if a table is updated frequently, consider whether some of the relevant SQL should be QC. If QC is applied to a table that is updated frequently, it may increase the burden on the database rather than lighten it. My general practice is to turn on QC by default and disable CACHE by adding the SQL_NO_CACHE keyword to some SQL statements that involve frequently updated tables. In this way, unnecessary memory operations can be avoided as much as possible and memory continuity can be maintained as much as possible.

SQL statements that have scattered queries should not use QC either. For example, the statement used to query users and passwords-"selectpassfromuserwherename='surfchen'". Such a statement, in a system, is likely to be used only when a user logs in. The query used by each user to log in is a different SQL text, and QC hardly works here, because the cached data is almost not used, they only take up space in memory.

Storage block, in this section, "storage block" and "block" have the same meaning.

How to adjust the MySQL query buffer

When QC caches the results of a query, it usually does not allocate enough memory at once to cache the results. Instead, it is stored block by block in the process of obtaining the query results. When a storage block is filled, a new storage block is created and memory is allocated (allocate). The memory allocation size of a single storage block is controlled by the query_cache_min_res_unit parameter, which defaults to 4KB. If the last storage block cannot be fully utilized, unused memory will be freed. If the cached results are large, it may result in frequent memory allocation operations and a decline in system performance; if the cached results are small, it may result in too many memory fragments, if these fragments are too small, it is likely that they can no longer be allocated for use.

In addition to the storage block required for query results, each SQL text also needs a storage block, and the tables involved also need a storage block (the storage block of the table is shared by all threads, and only one storage block is needed for each table). Total number of storage blocks = number of query results * 2 + number of database tables involved. That is, at least three storage blocks are required when the first cache is generated: the table information storage block, the SQL text storage block, and the query results storage block. If the second query uses the same table, then at least two storage blocks are needed: the SQL text block and the query result block.

By looking at Qcache_queries_in_cache and Qcache_total_blocks, you can see the average block of storage consumed by each cache result. If their ratio is close to 1:2, the current query_cache_min_res_unit parameter is large enough. If you have a lot more Qcache_total_blocks than Qcache_queries_in_cache, you need to increase the size of the query_cache_min_res_unit.

Qcache_queries_in_cache*query_cache_min_res_unit (the block where the sql text and table information is located is so small that it 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_min_res_unit.

Resize

If the Qcache_lowmem_prunes grows rapidly, it means that many caches are freed because there is not enough memory, rather than because the related tables are updated. Try to increase query_cache_size and make Qcache_lowmem_ prunes zero growth as much as possible.

Startup parameters

What showvariableslike'query_cache%' sees is this information.

Query_cache_limit: if the result of a single query is greater than this value, do not Cache

Query_cache_size: memory allocated to QC. If set to 0, it is equivalent to disabling QC. Note that QC must use approximately 40KB to store its structure, and if the setting is less than 40KB, it is equivalent to disabling QC. The smallest unit stored in QC is 1024byte, so if you set a value that is not a multiple of 1024, the value will be rounded to a multiple of 1024 that is closest to the current value.

Query_cache_type:0 completely prohibits QC and is not controlled by the SQL statement (it may also be noted that even if it is disabled here, the memory size set by the above parameter will still be allocated); 1 enable QC, which can be disabled using SQL_NO_CACHE in the SQL statement; 2 can be enabled in the SQL statement using SQL_CACHE.

Query_cache_min_res_unit: the amount of memory allocated to the QC result each time

Status

What showstatuslike'Qcache%' sees is this information.

Qcache_free_blocks: when a table is updated, the cacheblocks associated with it is free. But the block may still exist in the queue, except at the end of the queue. These blocks will be counted to this value. You can use the FLUSHQUERYCACHE statement to clear the freeblocks.

Qcache_free_memory: free memory. If it is small, consider adding query_cache_size.

Qcache_hits: the number of cache hits since the mysql process was started

Qcache_inserts: the number of QC added since the mysql process was started

Qcache_lowmem_prunes: the number of QC deleted due to insufficient memory. Increase the query_cache_size and keep this value growing at zero as much as possible.

Qcache_not_cached: the number of read-only queries that have not been made by cache since the mysql process was started (including select,show,use,desc, etc.)

Qcache_queries_in_cache: the number of SQL currently being cache

Qcache_total_blocks: the number of blocks in QC. A query may be stored by multiple blocks, and the last unused memory of these blocks will be freed. For example, a QC result takes up 6KB memory. If query_cache_min_res_unit is 4KB, three blocks will be generated at last. The first block is used to store the text of sql statements, which will not be counted in query_cache_size. The second block is 4KB, and the third block is 2KB (first allocate4KB, then release the excess 2KB). Each table, when the first SQL query associated with it is CACHE, uses a block to store table information. In other words, block will be used in three places: table information, SQL text, and query results.

What showglobalstatuslike'Com_select' sees is the number of queries that missed cache, including read and write queries.

The total number of SELECT queries is equivalent to:

Com_select+Qcache_hits+querieswitherrorsfoundbyparser

The value of Com_select is equivalent to:

Qcache_inserts+Qcache_not_cached+querieswitherrorsfoundduringcolumns/rightscheck

Commonly used calculation formulas:

Qcache hit rate: Qcache_hits/ (Com_select+Qcache_hits)

Qcache fragmentation rate: Qcache_free_blocks/Qcache_total_blocks

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

After reading the above, do you have any further understanding of how to implement query buffering in MySQL? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.