MySQL Query statement statistics and query cache-related status values troubleshoot

About MySQL ' Show global status ' output of Com_select, Queries, Questions, Qcache_hits, Qcache_inserts, Qcache_not_ Cached these state values have been a bit confusing, now through the experiment to more accurate and profound understanding.

Experiment version: 5.5.39

The official explanations of each state are attached first.

com_select: The COM_XXX statement counter variables indicate the number of times each XXX statement hasbeen execut Ed. However, if a query result is returned from query cache, the server increments the qcache_hits status variable, not Com_se Lect.

The number of times the SELECT statement executes, and if the results are obtained from the query cache, increase the qcache_hitswithout increasing 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 statements that the server has executed, counting the number of SQL bars stored in the stored procedure (stored procedure)

Questions: The number of statements executed by the server. This includes is 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.

Contains only the number of SQL statements sent by the client (including use statements, show statements, etc.), excluding SQL statements in stored procedures (a stored procedure as a statement)

qcache_hits: The number of the query cache hits.

Number of query cache hits

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

Number of times to join the query cache

qcache_not_cached: The number of noncached queries (not cacheable, or not cached due to the Query_cache_type Setti NG).

Number of non-cacheable queries. Non-cacheable (for example, the result set size exceeds query_cache_limit), or the number of query caches cannot be used because of query_cache_type settings (on allow, off not allowed, demand dependency explicitly specified usage cache).

Qcache_queries_in_cache: The number of queries registered in the query cache

The number of "registered" query statements in the query cache

The following experiment is done to observe the growth of a State value:

Restart the database and empty the query cache

Executes a query statement and then observes a status value

Com_select | 1

Qcache_hits | 0

Qcache_inserts | 1

The execution of the query statement above, a status value

Com_select not change

Qcache_hits plus 1

Qcache_inserts not change

Explicitly specify not to use the cache (plus sql_no_cache) to execute the query statement just now

Com_select plus 1

Qcache_hits not change

Qcache_not_cached plus 1

After figuring out the meanings of the above values, we discuss the formula for calculating the ' query cache hit ratio ', the calculation method of the query cache hit ratio, the online statement is different, here say my understanding:

Query statements are divided into ' cacheable ' and ' non-cacheable ' two types of

cacheable: If stored in Query_cache, then com_select unchanged, Qcache_hits plus 1

does not exist in Query_cache, then Com_select plus 1,qcache_inserts plus 1

Non-cacheable: Select plus 1,qcache_not_cached plus 1

That is Com_select = qcache_inserts+qcache_not_cached

So the hit rate of the query cache can be calculated as follows:

qcache_hits/(Qcache_hits+com_select)

Attached: Query cache variable meaning:

Qcache_free_blocks	currently in an idle state Query Cache Medium Memory Block number , a large number indicates that there may be debris. FLUSH QUERY CACHE fragments in the cache are collated to get a free block.
Qcache_free_memory	The total amount of free memory in the cache.
Qcache_hits	The number of cache hits.
Qcache_inserts	Number of cache failures (query results added to querycache )
Qcache_lowmem_prunes	The number of times that the cache is out of memory and must be cleaned up to make room for the qcache_inserts action is best seen for a long time, and if the number grows faster, it means that fragmentation can be very serious, or memory is scarce. (The free_blocks and free_memory above can tell you which is the case).
Qcache_not_cached	The number of queries that are not appropriate for caching, usually because these queries are not SELECT Statements and are not cached by the cache because of query_cache_type settings, or if the result set exceeds query_cache_ The statement set by the limit variable.
Qcache_queries_in_cache	The number of SELECT statement bars currently ' registered ' in query_cache
Qcache_total_blocks	The total number of blocks in the cache.

MySQL Query statement statistics and query cache-related status values troubleshoot