MySQL Sql_cache cache usage

With the following rules, if the data table is changed, all caches associated with the data table will be invalidated and deleted. "Data table Changes" Here include: INSERT, UPDATE, DELETE, TRUNCATE, ALTER table, drop table, or drop database. For example, if the data table posts access frequently, it means that a lot of its data will be the QC cache, but every time the posts data table updates, whether the update is not affected by the cache data, will be all and posts table related cache cleanup. If your data table is updated frequently, then the query cache will be a burden on the system. Experiments have shown that, in the worst case, QC will reduce the processing power of the system 13%[1].

Query Cache Related parameters:

Query_cache_size QC takes up space by setting it to 0 to turn off the QC feature
Query_cache_type 0 means shutdown qc;1 indicates normal cache; 2 means Sql_cache is cached
Query_cache_limit Maximum cache result set
The Query_cache_min_res_unit manual says that QC assigns the size of the cache block according to this value.
Qcache_lowmem_prunes This is a state variable (show status) when the cache space is not needed

The value is self-increasing when the old cache is released.

qcache_hits– indicates that SQL results directly in the cache and does not need to be parsed

have_query_cache– whether the query cache is available
query_cache_limit– can cache the maximum value of a specific query result
query_cache_size– Size of query cache
query_cache_type– block or support query caching
Set global query_cache_size = 600000; – Set Cache memory
Set session Query_cache_type = on; – Turn on query caching
The number of contiguous memory blocks in the Qcache_free_blocks cache. A large number indicates that there may be fragmentation.

FLUSH QUERY Cache organizes the fragments in the cache to get a free block.
Qcache_free_memory free memory in the cache.
Qcache_hits is incremented each time a query is hit in the cache.
Qcache_inserts is incremented each time a query is inserted. The number of hits divided by the number of inserts is not the middle ratio;

Subtracting this value by 1 is the hit rate. In the above example,

About 87% of queries are hit in the cache.
Qcache_lowmem_prunes Cache is out of memory and must be cleaned up to provide null for more queries

The number of times between. This number is best seen for a long time, and if the number is growing,

Indicates possible fragmentation is very serious, or memory is small. (Above the Free_blocks and

Free_memory can tell you which kind of situation you belong to).
Qcache_not_cached the number of queries that are not appropriate for caching, usually because these queries are not

SELECT statement.
Qcache_queries_in_cache the number of queries (and responses) that are currently cached.
The number of blocks in the qcache_total_blocks cache.

1 through configuration implementation: Only the "Data changes less, select Multi" table to open the cache function. (This can be implemented through configuration, also need to investigate, in the MySQL manual does not seem to mention)

2 Configure the Query_cache_type, while rewriting the program.

Query_cache_type 0 means not using buffering, 1 is using buffering, and 2 is used as needed.

Setting 1 for buffering is always valid, and if buffering is not required, use the following statement:

SELECT Sql_no_cache * from my_table WHERE ...

If set to 2, you need to turn on buffering, you can use the following statement:

SELECT Sql_cache * from my_table WHERE ...

So, just set the Query_cache_type to 2 and then, where you need to increase the select speed, use:

SELECT Sql_cache * FROM ...

"MySQL Cache debug Notes"

1 You can use the following command to turn on the MySQL select Cache feature:

SET GLOBAL query_cache_size = 102400000;

Because the cache function is not turned on when query_cache_size defaults to 0 o'clock.

2 Commissioning:

To view the settings for the cache:

Show variables like '%query_cache% ';

Performance monitoring:

Show status like '%qcache% ';

3 MySQL Cache cleanup:

You can use the Flush query cache statement to clean up query buffer fragmentation to improve memory usage performance. The statement does not remove any queries from the cache.

The RESET query cache statement removes all queries from the query buffer. The FLUSH Tables statement also performs the same work.

MySQL Sql_cache cache usage