MySQL Cache query_cache parameter tuning

By default, MySQL does not have the Query_cache feature turned on.

MariaDB [(None)]> show variables like '%query_cache% ';

+------------------------------+---------+

| variable_name | Value |

+------------------------------+---------+

| Have_query_cache |    YES | -----indicates that the current MySQL version supports the query cache feature

| Query_cache_limit |    1048576 | -----

| Query_cache_min_res_unit | 4096 |

| Query_cache_size |    0 | -----means disable query cache, default value

| query_cache_strip_comments | OFF |

| Query_cache_type | On |

| Query_cache_wlock_invalidate | OFF |

+------------------------------+---------+

Query_cache_size according to the actual situation, it is better to set a multiple of 1024, reference value 32M

In the/ETC/MY.CNF configuration document, modify:

Query_cache_type=0 (off) off

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 SELECT statement by Sql_cache specifying the query that needs to be cached

To configure the experiment:

OK, after the configuration of the following sections of the file:

query_cache_size=128m

Query_cache_type=1

Save the file, restart the MySQL service, and verify that it is actually turned on 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)

Mainly see whether the values of query_cache_size and query_cache_type are consistent with our set:

Here the value of the query_cache_size is 134217728, we set the 128M, the actual is the same, but the unit is different, you can convert the next: 134217728 = 128*1024*1024.

The Query_cache_type is set to 1, which is shown as on, as mentioned earlier.

In short, see the above display indicates that the settings are correct, but in the actual query can cache the query, but also need to manually test, we can use the show status like '%qcache% ', the statement to test, now we open the query cache function, before executing the query, Let's take a look at the values of the relevant 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, explain the effect of these parameters:

Qcache_free_blocks: Indicates how many remaining blocks are currently in the query cache, and if the value is larger, it means that there is too much memory fragmentation in the query cache and may be collated at some time.

Qcache_free_memory: The memory size of the query cache, through this parameter can be very clear to know whether the current system query memory is sufficient, is more, or is not enough, the DBA can make adjustments according to the actual situation.

Qcache_hits: Indicates how many times the cache has been hit. We can mainly use this value to verify the effect of our query cache. The larger the number, the better the caching effect.

Qcache_inserts: Indicates how many misses were missed and then inserted, meaning that the new SQL request was not found in the cache, had to perform query processing, and then insert the results into the query cache after executing the query processing. Such a situation, the more times, the query cache is applied to the less, the effect is not ideal. Of course, the query cache is empty after the system has just started, which is normal.

Qcache_lowmem_prunes: This parameter records how many queries have been removed from the query cache because of insufficient memory. With this value, the user can adjust the cache size appropriately.

Qcache_not_cached: Represents the number of queries that are not cached because of the Query_cache_type settings.

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

Qcache_total_blocks: The number of blocks currently cached.

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)

Then execute show status like '%qcache% ' to see what's changed:

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)

Comparing the previous parameter values, we found that the qcache_inserts changed. Qcache_hits is not changed, we are doing the same query below
SELECT * from user where id = 2, according to the previous theoretical analysis: qcache_hits should be equal to 1, and qcache_inserts should be the same value (other parameters of the value change temporarily not concerned, the reader can self-test), re-execution:

Show status like '%qcache% ' to see what's changed:

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, sure enough, with our analysis of the exact same.

MySQL Cache query_cache parameter tuning