Shulou(Shulou.com)06/01 Report--

Query Cache stores SELECT statements and their resulting data results, especially for scenarios where table data does not change very frequently, such as some static pages

Or some piece of information on the page that doesn't change very often. If any write operations occur on this table, all caches associated with this table will be invalidated.

Because Query Cache needs to cache the latest data results, any changes to the table data (INSERT, UPDATE, DELETE, or others are possible

The operation of data change) will cause the Query Cache to be refreshed. For databases with high update pressure, the hit rate of the query cache will also be very low.

But we can set the parameter query_cache_type to DEMAND (on demand and use) so that query caching is not used for the default SQL statement

For a SQL statement that determines that you want to use query cache, you can specify it using the method of sql_cache, for example:

Select sql_cache * from table_name

Or select sql_cache count (*) from table_name

The following are the meanings of the three parameters of query_cache_type:

Query_cache_type=0 (OFF) shutdown

Query_cache_type=1 (ON) caches all results unless the select statement uses SQL_NO_CACHE to disable query caching

Query_cache_type=2 (DEMAND), caching only the queries that need to be cached through SQL_CACHE in the select statement

Modify it to DEMAND mode:

Vi / etc/my.cnf, add the following line:

Query_cache_type = 2

Save and restart mysql

If you modify the mysql5.7 version directly, you may get an error:

Mysql > set global query_cache_type=2

ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

Check whether the DEMAND parameter is enabled:

Mysql > show variables like'% query_cache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | have_query_cache | YES |

| | query_cache_limit | 1048576 | |

| | query_cache_min_res_unit | 4096 | |

| | query_cache_size | 1048576 | |

| | query_cache_type | DEMAND |

| | query_cache_wlock_invalidate | OFF |

+-+ +

6 rows in set (0.44 sec)

Mysql > > show global status like'% Qcache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Qcache_free_blocks | 1 | |

| | Qcache_free_memory | 1031832 | |

| | Qcache_hits | 0 | |

| | Qcache_inserts | 0 | |

| | Qcache_lowmem_prunes | 0 | |

| | Qcache_not_cached | 3 | |

| | Qcache_queries_in_cache | 0 | |

| | Qcache_total_blocks | 1 | |

+-+ +

8 rows in set (0.14 sec)

The relevant parameters are explained as follows:

Qcache_free_blocks: indicates how much blocks is left in the query cache. If the value is large, there are too many memory fragments in the query cache.

Qcache_free_memory: represents the current amount of memory left in the Query Cache.

Qcache_hits: indicates how many times the cache has been hit.

Qcache_inserts: indicates how many times the cache is missed and then inserted, which means that if the new SQL request is not found in the cache, it has to perform query processing, and then insert the results into the query cache.

Qcache_lowmem_prunes: this parameter records how many queries have been removed from the query cache due to insufficient memory.

Qcache_not_cached: represents the number of queries that are not cached because of the setting of query_cache_type.

Qcache_queries_in_cache: the number of queries cached in the current cache.

Qcache_total_blocks: the number of block currently cached.

Execute a query:

> select count (*) from test

+-+

| | count (*) |

+-+

| | 36675 |

+-+

1 row in set (0.41 sec)

Then execute show status like'% Qcache%', to see what changes:

Mysql > show global status like'% Qcache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Qcache_free_blocks | 1 | |

| | Qcache_free_memory | 1031832 | |

| | Qcache_hits | 0 | |

| | Qcache_inserts | 0 | |

| | Qcache_lowmem_prunes | 0 | |

| | Qcache_not_cached | 4 |

| | Qcache_queries_in_cache | 0 | |

| | Qcache_total_blocks | 1 | |

+-+ +

8 rows in set (0.01 sec)

Qcache_hits is 0. 0.

Execute select count (*) from test again

Mysql > select count (*) from test

+-+

| | count (*) |

+-+

| | 36675 |

+-+

1 row in set (0.02 sec)

Mysql > show global status like'% Qcache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Qcache_free_blocks | 1 | |

| | Qcache_free_memory | 1031832 | |

| | Qcache_hits | 0 | |

| | Qcache_inserts | 0 | |

| | Qcache_lowmem_prunes | 0 | |

| | Qcache_not_cached | 5 | |

| | Qcache_queries_in_cache | 0 | |

| | Qcache_total_blocks | 1 | |

+-+ +

8 rows in set (0.00 sec)

Qcache_hits is still 0, indicating that Query Cache is not used.

Add sql_cache to execute the statement and see what changes:

Mysql > select sql_cache count (*) from test

+-+

| | count (*) |

+-+

| | 36675 |

+-+

1 row in set, 1 warning (0.00 sec)

Mysql > show global status like'% Qcache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Qcache_free_blocks | 1 | |

| | Qcache_free_memory | 1030296 | |

| | Qcache_hits | 1 | |

| | Qcache_inserts | 1 | |

| | Qcache_lowmem_prunes | 0 | |

| | Qcache_not_cached | 5 | |

| | Qcache_queries_in_cache | 1 | |

| | Qcache_total_blocks | 4 |

+-+ +

8 rows in set (0.00 sec)

You can see that the value of Qcache_hits changes to 1, execute again:

Mysql > select sql_cache count (*) from test

+-+

| | count (*) |

+-+

| | 36675 |

+-+

1 row in set, 1 warning (0.00 sec)

Mysql > show global status like'% Qcache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Qcache_free_blocks | 1 | |

| | Qcache_free_memory | 1030296 | |

| | Qcache_hits | 2 | |

| | Qcache_inserts | 1 | |

| | Qcache_lowmem_prunes | 0 | |

| | Qcache_not_cached | 5 | |

| | Qcache_queries_in_cache | 1 | |

| | Qcache_total_blocks | 4 |

+-+ +

8 rows in set (0.01 sec)

You can see that the value of Qcache_hits becomes 2, accumulating 1 for each execution, indicating that query cache is used.

Note: since MySQL version 8.0, the whole block function of query cache has been cancelled directly.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.