MySQL Query cache Hit rate

working principle:

How the query cache works can be broadly summarized as: caching the result set and SQL statements for the select operation or preprocessing query (Note: 5.1.17 start support), a new SELECT statement or a preprocessing query statement, first querying the cache to determine if there is a recordset available, Judging criteria: Whether the SQL statement is exactly the same as the cache, case-sensitive;

The query cache for what query statement cannot cache its recordset, roughly the following categories:

1, the query statement added Sql_no_cache parameters;

2, the query statement contains the function of the obtained value, including the custom function, such as: Curdate (), Get_lock (), RAND (), Convert_tz, etc.;

3, the System database query: MySQL, INFORMATION_SCHEMA query statements using session level variables or local variables stored in the procedure;

4, the query statement using the lock in SHARE MODE, for UPDATE statement query statement similar to select ... Into the statement that exports the data;

5, the query operation of the temporary table; a query statement with warning information; a query statement that does not involve any table or view; A user has only a query statement with column-level permissions;

6, the Transaction isolation level is: Serializable case, all query statements can not be cached;

Configuration

Whether to enable the MySQL query cache, you can pass 2 parameters: Query_cache_type and query_cache_size, any one of the parameters set to 0 means that the query caching function is turned off.

The Query_cache_type domain is:

0 (OFF): Turn off the query cache function and do not use query cache under any circumstances;

1 (ON): Enable query caching, as long as the requirements of the query cache, the client's query statements and the recordset can be cached, a total of other clients to use;

2 (DEMAND): Enable the query cache, as long as the query statement to add parameters: Sql_cache, and meet the requirements of the query cache, the client's query statements and recordsets can be cached, a total of other clients use;

Query_cache_size allows setting the value of the query_cache_size to a minimum of 40K, for the maximum value can be almost considered unrestricted, the actual production environment application experience tells us that the value is not the larger the query cache hit rate is higher, Also not to the server load drop contribution, but may offset its benefits, and even increase the load of the server, as to how to set up, the following chapters, the recommended settings are: 64M; recommended setting do not exceed 256MB

Description of the cache option:

Use show global status like ' qcache% '; view

Mysql> show global status like ' qcache% '; +-------------------------+---------+| Variable_name           | Value   |+-------------------------+---------+| Qcache_free_blocks      | 1       | | Qcache_free_memory      | 1031368 | | Qcache_hits             | 0       | | Qcache_inserts          | 0       | | Qcache_lowmem_prunes    | 0       | | qcache_not_cached |      | Qcache_queries_in_cache | 0       | | Qcache_total_blocks     | 1       |+-------------------------+---------+8 rows in Set (0.02 sec)

Qcache_free_blocks: Number of memory blocks in Query Cache that are currently idle

Qcache_free_memory: Total amount of Query Cache memory currently in idle state

Qcache_hits:query Cache Hit Count

Qcache_inserts: The number of times to insert a new query cache into the query cache, that is, the number of missed hits

Qcache_lowmem_prunes: When query cache memory capacity is insufficient, you need to remove the old query cache from the number of times it will be used for the new cache object

Qcache_not_cached: The number of SQL not being cache, including SQL that cannot be cache, and SQL that will not be cache because of Query_cache_type settings

Qcache_queries_in_cache: Number of SQL currently in Query cache

The total number of blocks in the Qcache_total_blocks:query Cache

The generation of memory fragments. When a piece of allocated memory is not fully used, MySQL will trim the memory and return the unused part back to reuse. For example, the first allocation of 4KB, only 3KB, left 1KB, the second continuous operation, the allocation of 4KB, with 2KB, the remaining 2KB, the two consecutive operations of the remaining 1kb+2kb=3kb, not enough to do a memory unit allocation, this time, the memory fragments will be produced. Using flush query cache, you can eliminate fragmentation

Here are some algorithms for hit rate and memory usage

Estimated value of Query_cache_min_res_unit: (query_cache_size-qcache_free_memory)/Qcache_queries_in_cache

Query cache Hit Ratio ≈ (qcache_hits–qcache_inserts)/qcache_hits * 100%

Query cache memory Usage ≈ (query_cache_size–qcache_free_memory)/query_cache_size * 100%

Buffer pool for InnoDB storage engine

Typically, the hit of the InnoDB storage engine buffer pool should not be less than 99%, such as:

Mysql> show global status like ' innodb%read% ' \g *************************** 1. Row ***************************variable_name:innodb_buffer_pool_read_ahead_rnd value:0************************** * 2. Row ***************************variable_name:innodb_buffer_pool_read_ahead value:0*************************** 3. Row ***************************variable_name:innodb_buffer_pool_read_ahead_evicted value:0********************** 4. Row ***************************variable_name:innodb_buffer_pool_read_requests value:81337*********************** 5. Row ***************************variable_name:innodb_buffer_pool_reads value:432*************************** 6. Row ***************************variable_name:innodb_data_pending_reads value:0*************************** 7. Row ***************************variable_name:innodb_data_read value:9261056*************************** 8. Row ***************************variable_name:innOdb_data_reads value:450*************************** 9. Row ***************************variable_name:innodb_pages_read value:431*************************** 10. Row ***************************variable_name:innodb_rows_read value:29010 rows in Set (0.15 sec)

Parameter description:
Innodb_buffer_pool_reads: Indicates the number of times a page was read from a physical disk

Innodb_buffer_pool_read_ahead: Number of read-ahead

Innodb_buffer_pool_read_ahead_evicted: Read-ahead page, but no read on the number of pages replaced from the buffer pool, generally used to determine the efficiency of the read-ahead

Innodb_buffer_pool_read_requests: Number of pages read from the buffer pool

Innodb_data_read: Number of bytes read in total

Innodb_data_reads: Number of Read requests initiated, each read may require multiple pages to be read

InnoDB buffer pool hit rate calculation:

From the above information can be obtained:

Mysql> Select 81337/(81337+0+432); +---------------------+| 81337/(81337+0+432) |+---------------------+|              0.9947 |+---------------------+1 row in Set (0.00 sec)

MySQL Query cache Hit rate