MySQL high-speed cache startup method and parameters (query_cache_size)-mysql tutorial

MySQLquerycache has been provided since MySQL 4.1. However, we are only able to study it today. By default, MySQL does not start this function. you may find that the value of its variable have_query_cache is yes through showvariableslike '% query_cache %, for beginners of MYSQL, it is easy to think that this parameter is "YES", which indicates that it is enabled.

MySQL query cache has been provided since MySQL 4.1. However, we are only able to study it today. By default, MySQL does not start this function. you may find that the value of its variable have_query_cache is yes through show variables like '% query_cache %, for beginners of MYSQL, it is easy to think that this parameter is "YES", which indicates that it is enabled.

MySQL query cache has been provided since MySQL 4.1. However, we are only able to study it today. By default, MySQL does not start this function. you may find that the value of its variable have_query_cache is yes through show variables like '% query_cache %, for beginners of MYSQL, it is easy to think that if this parameter is set to YES, it indicates that the Query Cache is enabled. In fact, this parameter indicates whether the current version of MYSQL supports Query Cache, in fact, whether to enable the query cache depends on the value of another parameter: query_cache_size. The value 0 indicates that the query cache is disabled, and the default configuration is set to 0.

Configuration method:

Find the following content in the MYSQL configuration file my. ini or my. cnf:

# Query cache is used to cache SELECT results and later return them
# Without actual executing the same query once again. Having the query
# Cache enabled may result in significant speed improvements, if your
# Have a lot of identical queries and rarely changing tables. See
# "Qcache_lowmem_prunes" status variable to check if the current value
# Is high enough for your load.
# Note: In case your tables change very often or if your queries are
# Textually different every time, the query cache may result in
# Slowdown instead of a performance improvement.

Query_cache_size = 0

The above information is the default configuration. the comment means that the MYSQL Query cache is used to cache the select query results and the next time the same query request is received, results are directly returned instead of performing actual query processing. the query cache can speed up the query and optimize the query performance, provided that you have a large number of identical or similar queries, this function is rarely used to change the data in the table. You can use the value of the Qcache_lowmem_prunes variable to check whether the current value meets the load of your current system. Note: If the tables you query are updated frequently and there are few identical queries, it is best not to use the query cache.

Specific Configuration method:

1. set query_cache_size to a specific size. the specific size depends on the actual situation of the query, but it is best to set it to a multiple of 1024, with a reference value of 32 M.

2. add a row: query_cache_type = 1

The query_cache_type parameter is used to control the cache type. Note that this value cannot be set randomly and must be set to a number. optional items and descriptions are as follows:

If it is set to 0, you can say that your cache is useless at all, which is equivalent to disabling it. In this case, does the system allocate the size set by query_cache_size? is this problem to be tested?

If it is set to 1, all results will be cached unless your select statement uses SQL _NO_CACHE to disable the query cache.

If set to 2, the query to be cached is only cached in the select statement using SQL _CACHE.

OK. some files after configuration are as follows:

Query_cache_size = 128 M
Query_cache_type = 1

Save the file, restart the MYSQL service, and check whether the MYSQL service is enabled by the following query:

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 | 134217728 |
| Query_cache_type | ON |
| Query_cache_wlock_invalidate | OFF |
+ ---------- + ---- +
6 rows in set (0.00 sec)

It mainly depends on whether the values of query_cache_size and query_cache_type are consistent with those we set:

Here, the query_cache_size value is 134217728, and we set 128 M, which is actually the same, but the unit is different. you can convert it to 134217728 = 128*1024*1024.

Query_cache_type is set to 1 and displayed as ON, as mentioned earlier.

In short, the above display indicates that the settings are correct, but whether the query can be cached in the actual query still needs to be manually tested. we can use show status like '% Qcache % '; now we have enabled the query cache function. before executing the query, let's take a look at the values of related parameters:

Mysql> show status like '% Qcache % ';
+ --------- + ---- +
| Variable_name | Value |
+ --------- + ---- +
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 134208800 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+ --------- + ---- +
8 rows in set (0.00 sec)

Here, by the way, we will explain the functions of these parameters:
Qcache_free_blocks: indicates the number of blocks remaining in the query cache. if this value is displayed as large, it indicates that there are too many memory fragments in the query cache and may be sorted in a certain period of time.
Qcache_free_memory: queries the cache memory size. with this parameter, you can clearly see whether the current system's query memory is sufficient. if so, the DBA can make adjustments based on the actual situation.
Qcache_hits: indicates the number of cache hits. We can use this value to verify the cache performance of our queries. The larger the number, the better the cache effect.
Qcache_inserts: indicates the number of missed and inserted queries. This indicates that new SQL requests are not found in the cache and have to be queried. after the query is processed, the results are inserted into the query cache. In this case, the more times the query cache is applied, the less effective the query cache is. Of course, after the system is started, the query cache is empty, which is normal.
Qcache_lowmem_prunes: number of queries in this parameter record that are removed from the query cache due to insufficient memory. With this value, you can adjust the cache size appropriately.
Qcache_not_cached: indicates the number of queries not cached due to query_cache_type settings.
Qcache_queries_in_cache: number of queries cached in the current cache.
Qcache_total_blocks: number of blocks in the current cache.

Below we test:

For example, execute the following query statement:

Mysql> select * from user where id = 2;
+ -- + --- +
| Id | name |
+ -- + --- +
| 2 | test2 |
+ -- + --- +
1 row in set (0.02 sec)

Run show status like '% Qcache %' to check the changes:

Mysql> show status like '% Qcache % ';
+ --------- + ---- +
| Variable_name | Value |
+ --------- + ---- +
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 134207264 |
| Qcache_hits | 0 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 3 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+ --------- + ---- +
8 rows in set (0.00 sec)

Compared with the previous parameter values, we found that Qcache_inserts has changed. Qcache_hits has not changed. the same query is executed below.
Select * from user where id = 2, according to the previous theoretical analysis: Qcache_hits should be equal to 1, while Qcache_inserts should be the same (changes in values of other parameters are not concerned for the moment, you can test it on your own.) run the following command again:

Show status like '% Qcache %' to see the changes:

Mysql> show status like '% Qcache % ';
+ --------- + ---- +
| Variable_name | Value |
+ --------- + ---- +
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 134207264 |
| Qcache_hits | 1 |
| Qcache_inserts | 1 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 4 |
| Qcache_queries_in_cache | 1 |
| Qcache_total_blocks | 4 |
+ --------- + ---- +
8 rows in set (0.00 sec)

OK. it is exactly the same as what we analyzed.