In this post we learn how Mysql Query cache work , There is lot of information is available on net but basic info is not shared (or may be i am not able to find) on net which might help beginner users like how much
query_cache_size .
The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again. The query cache is shared among sessions, so a result set generated by one client can be sent in response to the same query issued by another client.
The query cache can be useful in an environment where you have tables that do not change very often and for which the server receives many identical queries. This is a typical situation for many Web servers that generate many dynamic pages based on database content.
The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.
Query cache :
- Only cache the select statement
- Do not cache if select statement used as subquery,inline views or parts of the UNION or stored procedure using select.
- Invalidate cache if data is modified
- Do not cache Non-deterministic functions such as UUID(), RAND(), CONNECTION_ID() etc
Lets say you have query which takes approx 20 sec to display 1 million data so 1st time it take 20 sec and 2nd time it take 0.20 ms to display 1 million data but if data is changed the again in 3rd time it take 20 sec.
So database should be properly normalized to make use of this feature ,
Let say you are running article site then you also storing number of hits on same table then every time after selecting data you are updating the hit counter too in that situation Query cache is no use and mysql always return updated data.
As per net any non zero value should work for query_cache_size and no clear info is found in net what should be ideal minimum size in Mysql forum and RDS forums.
If you define too low value then qcache_hits is always 0 with RDS, So it should be minimum 65 kb (65536) to work
For 8 GB Instance 1000 MB of DB size
query_cache_size : 24000000 this means 24 MB Approx
For 16 GB Instance 1000 MB of DB size
query_cache_size : 64000000 this means 64 MB Approx
In RDS You cant define like 24M so you need to convert it to bytes and put that value.
| Parameter Variable | 16 GB | 8 GB |
query_alloc_block_size
(Allocation size of memory blocks that are allocated for objects,
increase this parameter to deal with memory fragmentation) | 8192 | 8192 |
query_cache_limit
(Do not cache results/record-set /result-set that are larger than this
number of bytes.
Default is 1MB [1048576]) | 1048576 | 1048576 |
| query_cache_min_res_unit (4kb Default,Minimum allocation size) | 4096 | 4096 |
query_cache_size
(This is the RAM or memory which need to be allocated) , 0 to disable it
In RDS when we set it to 65536 it start caching the queries | 33554432 | 25165824 |
query_cache_type
0 : Don't cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE | 1 | 1 |
query_cache_wlock_invalidate
0 : Client will get old data when table is locked for write
1 : Client will wait untill loack is opened and recive latest data | OFF | OFF |
query_prealloc_size
(persistent buffer for memory allocation) | 8192 | 8192 |
| Status | 16 GB | 8GB |
| Qcache_free_blocks | 251 | 984 |
| Qcache_free_memory | 770776 | 3766104 |
Qcache_hits
Number of select served by cache | 181587 | 136941 |
Qcache_inserts
Number of cache Queries | 38245 | 84376 |
Qcache_lowmem_prunes
Queries that were deleted from
the cache because of low memoryTip : Increase cache_size | 25391 | 43258 |
Qcache_not_cached
Number of Queires which are excluded due to query_cache_type setting | 61058 | 78150 |
Qcache_queries_in_cache
Number of queries active in cache | 739 | 3197 |
| Qcache_total_blocks | 1985 | 7781 |
Source
http://dev.mysql.com/doc/refman/5.7/en/query-cache.html
https://www.percona.com/blog/2006/07/27/mysql-query-cache/
