Query buffering mechanism of MySQL database

Query buffering mechanism for MySQL database 2011-08-10 11:07 unknown fire Demon Netfont Size:T | T

Using the query buffering mechanism can greatly improve the efficiency of MySQL database query and save the time spent in querying. So how is the query buffering mechanism set up? In this article we will introduce this part of the content, we hope to be able to help you.

AD:

The MySQL database provides a query buffering mechanism. Using this query buffering mechanism, MySQL stores the SELECT statement and query results in a buffer, and later, for the same SELECT statement (case sensitive), the result is read directly from the buffer. To save query time and improve the efficiency of SQL query. In this article we mainly introduce the MySQL database query buffer mechanism, then let us come together to understand this part of the content.

By adjusting the following parameters you can know whether the query_cache_size is set properly:

Qcache Inserts

Qcache Hits

Qcache Lowmem prunes

Qcache Free Blocks

Qcache Total Blocks

The value of Qcache_lowmem_prunes is very large, it indicates that buffering is not enough, and the value of qcache_hits is very large, it indicates that the query buffer is used very frequently, when the buffer size needs to be increased qcache_hits the value is not big, Indicates that your query repetition rate is very low, in which case the use of query buffering will affect efficiency, then you can consider not querying the buffer. In addition, adding sql_no_cache in the SELECT statement can make it clear that query buffering is not used.

Qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer query_cache_type specifies whether to use query buffering.

If set to:

Query_cache_size = 32M

Query_cache_type= 1

The following status values are obtained:

Qcache queries in cache 12737//indicates the number of currently cached bars.

Qcache Inserts 20649006

Qcache hits 79060095//It seems that the repeat query rate is quite high.

Qcache Lowmem prunes 617913//There are so many occurrences of cache too low.

Qcache Not Cached 189896

Qcache free memory 18573912//current remaining cache space.

Qcache Free Blocks 5328//This number seems a bit big and fragmented.

Qcache Total Blocks 30953

If memory permits, you can also set the query_cache_size a bit larger.

About MySQL database query caching mechanism is here, if you want to learn more about MySQL database knowledge, you can go here to see: http://database.51cto.com/mysql/, I believe it will bring you the harvest!

Query buffering mechanism of MySQL database