MySQL Query Cache Variable: query_cache_size
The query cache in MySQL stores the text of SELECT statement as well as its result set. If an identical query is sent to the server later, the server retrieves the result set from the query cache instead parsing and exciting the query again. Therefore, it reduces time and resource that need to execute the query. However, please note that Query Cache only works for identical queries (exactly the same). For example, the following two queries are not considered identical:
Select city, state, zip from address where city = ‘new york’
Vs.
Select city, state, zip from address where city = ‘NEW YORK’
These two queries are also not considered identical:
Select city, state, zip from address where city = ‘NEW YORK’
Vs.
SELECT city, state, zip from address WHERE city= NEW YORK’
Because one use lower case clause word (select and where) and the other use upper case clause (SELECT and WHERE). Also, the cache is not used for queries of the following types:
· Queries that are a subquery of an outer query
· Queries executed within the body of a stored function, trigger, or event
(1.) Query Cache Variables
The following are server variables need to be set to make use of the query cache.
1. Have_query_cache: this variable indicates where or not query cache is available. The default is ‘Yes’ for standard installation.
2. Query_cahce_type: this variable sets the query cache type. It can be set in global and session level. The values for this variable can be 0,1 or 2:
- 0 - tells the server not to cache results or retrieve results from the query cache. However, this setting dose not deallocate the query cache buffer. Refer to query_cache_size section for how to allocate the query cache buffer.
- 1 – tells the server to cache all queries except for those that begin with select sql_no_cache. The default for Query_cahce_type is 1.
- 2 – tells the server to cache query results only for queries that begin with select sql_cache.
3. Query_cache_size: this variable sets the amount of memory allocated for caching query result sets. To disable or deallocate the query cache, set this variable value to 0, which is also the default. Please note that memory are allocated for query cache regardless if Query_cahce_type is set to 0 or not if query_cache_size variable is set to 40960 bytes or more. If the value is set to less than 40960, the server will reset the query_cache_size variable to 0 and place a warning message in the error log.
4. query_cache_min_res_unit: this variable sets the minimum size (in bytes) for) blocks allocated by the query cache. The default value is 4KB. According to the MySQL Manual, this should be adequate for most case. The query cache allocates blocks for storing query results by allocating memory blocks set by the query_cache_min_res_unit variable. If there are many queries with small result sets, the default block size may lead to memory fragmentation; moreover, it can force the query cache to prune (delete) queries from the cache due to lack of memory. In this case, you should decrease the value of the query_cache_min_res_unit variable.
5. Query_cache_limit: this variable sets the maximum size result set that the query cache will store. The default is 1MB.
(2.)
Query Cache Status Variables and Monitoring
- Qcache_free_memory: this status variable shows that amount of free memory for query cache. If the amount is low in comparing to the amount of the Query_cache_size, memory may be added to the Query_cache_size.
- Qcache_not_cached: this status displays the number of queries that were not cached. Not all the queries are cacheable such as a non-select query.
- Qchache_queries_in_cache: this status display the number of queries registered in the query cache.
- Qcache_total_blocks: this status displays the total number of blocks in the query cache.
- Qcache_hits: this status variable displays the number of query cache hits
- Qcache_inserts: this status variable displays the number of queries added to the query cache.
- Com_select: this status display number of times a select statement has been executed. If a query result is returned from query cache, the server increments the Qcache_hits status variable, not Com_select.
Com_select = (Qcache_inserts + Qcache_not_cached + queries with errors found during the
column-privileges check)
The total number of SELECT queries = ( Com_select + Qcache_hits + queries with
errors found by parser)
The total number of successfully executed SELECT queries = ( Com_select + Qcache_hits )
Query cache utilization = (Qcache_hits/ total number of successfully executed SELECT queries)
Or
Query cache utilization = (Qcache_hits/( Com_select + Qcache_hits ))
Please note that it all depends on the kind of applications, the % Query cache utilization will be
different.
8.
Qcache_free_blocks: this variable display the number of free memory blocks in the query cache.
9.
Qcache_lowmem_prunes: this status displays the number of queries that were deleted from the query
cache because of low memory. This status variable value also increases if the cache is not big enough to
hold the query statements. Also, this variable increase if there were many small query results sets which
indicates memory fragmentation. If the qcache_lowmem_prunes status variable is greater than 0,
consider increasing the query_cache_size dynamically and monitor the Qcache_lowmem_prunes status
variable to make sure that it stop increase.
(3.) MySQL Commands
To determine if or not the query cache has become fragmented, you can execute the FLUSH QUERY CACHE statement to defragment the cache... After issuing the statement, only a single free block remains. This command dose not removes queries from the cache.
To remove all the queries from the cache, use the RESET QUERY CACHE command.
Some other considerations need to be given when setting up Query Cache. As the size of the query cache grows, it will take longer to scan for matching queries, take longer to retrieve the result sets, and increase mutex contention.
References:
Related Posts:
Posts
