Status values related to MySQL query statement statistics and query Cache

Status values related to MySQL query statement statistics and query Cache

The status values Com_select, Queries, Questions, Qcache_hits, Qcache_inserts, and Qcache_not_cached output by 'show global status' in MySQL have been somewhat confused, now we can learn more accurately and deeply through experiments.

Lab version: 5.5.39

Attach an official explanation of each status

Com_select: The Com_xxx statement counter variables indicate the number of times each xxx statement hasbeen executed. however, if a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select.

The number of times the select statement is executed. If the result is obtained from the query cache, The Qcache_hits is increased without the Com_select

Queries: The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.

All the statements executed by the server, including the number of SQL entries in the Stored Procedure

Questions: The number of statements executed by the server. this primary des only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. this variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

Only the number of SQL statements sent by the client (including the use statement and show statement), excluding the SQL statements in the Stored Procedure (a stored procedure is used as a statement)

Qcache_hits: The number of query cache hits.

Queries the number of cache hits

Qcache_inserts: The number of queries added to the query cache.

Number of times the query cache is added

Qcache_not_cached: The number of noncached queries (not cacheable, or not cached due to the query_cache_type setting ).

The number of queries that cannot be cached. Non-cached (for example, the result set size exceeds query_cache_limit); or because query_cache_type is set (on, off, and demand depends on specified Cache Usage), the number of query caches cannot be used.

Qcache_queries_in_cache: The number of queries registered in the query cache

Number of query statements registered in the query Cache

The following experiment is used to observe the growth of State values:

Restart the database and clear the query cache.

Execute a query statement and observe the status values.

Com_select | 1

Qcache_hits | 0

Qcache_inserts | 1

Status values of the query statements executed above

Com_select remains unchanged

Qcache_hits plus 1

Qcache_inserts unchanged

Explicitly specify not to use the cache (with SQL _NO_CACHE) and execute the query statement just now

Com_select plus 1

Qcache_hits unchanged

Qcache_not_cached plus 1

After figuring out the meanings of the above values, we will discuss the formula for calculating the 'query cache hit rate'. There are different online statements about how to calculate the cache hit rate, here is my understanding:

Query statements can be divided into two types: 'cacheable 'and 'non-cache'.

Cacheable: if it exists in query_cache, Com_select remains unchanged, and Qcache_hits is added to 1.

If it does not exist in query_cache, Com_select adds 1 and Qcache_inserts adds 1.

Non-cached: select plus 1, Qcache_not_cached plus 1

That is, Com_select = Qcache_inserts + Qcache_not_cached

Therefore, the cache hit rate can be calculated as follows:

Qcache_hits/(Qcache_hits + Com_select)

Appendix: Meanings of query cache variables:

Qcache_free_blocks	Currently, the number of memory blocks in the idle Query Cache is large, which indicates that fragments may exist. Flush query cache sorts the fragments in the CACHE to obtain an idle block.
Qcache_free_memory	The total amount of idle memory in the cache.
Qcache_hits	Number of cache hits.
Qcache_inserts	Number of cache failures (the number of QueryCache new to query results)
Qcache_lowmem_prunes	The number of times the cache has insufficient memory and must be cleaned up to free up space for Qcache_inserts actions. It is recommended that this number be used for a long time. If this number increases rapidly, it indicates that the fragmentation may be very serious, or the memory is small. (The free_blocks and free_memory above can tell you what the situation is ).
Qcache_not_cached	The number of queries that are not suitable for caching. This is generally because these queries are not SELECT statements, and because query_cache_type is not set to be queried by the Cache, or the result set exceeds the query_cache_limit variable setting statement.
Qcache_queries_in_cache	Number of select statements for 'registration' in query_cache
Qcache_total_blocks	The total number of blocks in the cache.

This article permanently updates the link address: