Mysql cache Start Method and parameters detailed (query_cache_size) _mysql

MySQL Query cache has been available since version 4.1, but it's worth studying today. The default configuration, the MySQL feature is not started, you may be through show variables like '%query_cache% '; the value of its variable have_query_cache is yes, MySQL beginners can easily assume that this parameter is yes on behalf of the query cache turned on, in fact, it is not true, this parameter indicates whether the current version of MySQL support query cache, actually open query caching is to see the value of another parameter: Query_cache_size, The value is 0, which means that query cache is disabled, and the default configuration is configured to 0.

Configuration method:

Find the following 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 significant speed improvements, if your
# have a lot of identical queries and rarely, changing tables. The
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# note:in Case your tables very often or if your queries are
# textually different every time, the query cache could result in a
# 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 you receive the same query request, no longer perform the actual query processing and directly return the results, there is such a query cache can improve the speed of query, so that query performance is optimized, The prerequisite is that you have a large number of the same or similar queries, and rarely change the table data, otherwise it is not necessary to use this feature. You can check whether the current value satisfies your current system load by qcache_lowmem_prunes the value of the variable. Note: If you are querying a table that is updated more frequently and rarely has the same query, it is best not to use the query cache.

Specific configuration method:

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

2. Add one line: query_cache_type=1

The Query_cache_type parameter is used to control the type of cache, note that this value cannot be set arbitrarily, must be set to a number, optional item, and the description is as follows:

If set to 0, then you can say that your cache is not used at all, equivalent to disabled. But in this case the size system set by Query_cache_size is to be allocated for it, and this question needs to be tested?

If set to 1, all results will be cached unless your SELECT statement disables the query cache with Sql_no_cache.

If set to 2, caching is only specified in the SELECT statement by Sql_cache the query that requires caching.

OK, some of the files after configuration are as follows:

query_cache_size=128m
Query_cache_type=1

Save the file, restart the MySQL service, and then verify that it is really turned on by using the following query:

Copy Code code as follows:

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 value of query_cache_size and Query_cache_type is 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 under: 134217728 = 128*1024*1024.

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

In short, see the display above the correct settings, but in the actual query can be cached query, but also manual testing, we can show the status like '%qcache% '; statement to test, now we open the query caching function, before executing the query, Let's take a look at the value of the relevant parameter:

Copy Code code as follows:

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, incidentally, explain the effect of this several parameters:
Qcache_free_blocks: Indicates how many remaining blocks are currently in the query cache, and if the value is shown to be large, there is too much memory fragmentation in the query cache and may be sorted out at a certain time.
Qcache_free_memory: Query the memory size of the cache, through this parameter can be very clear to know whether the current system query memory is enough, is more, or not enough, the DBA can make adjustments according to the actual situation.
Qcache_hits: Indicates how many hits were cached. We can use this value primarily to verify the effect of our query cache. The larger the number, the more desirable the cache effect.
Qcache_inserts: Indicates how many misses were then inserted, meaning that the new SQL request was not found in the cache, had to perform query processing, and the result was executed by inserting the results into the query cache. Such a number of times, the more times, that the query cache applied to a relatively small, the effect is not ideal. Of course, after the system has just started, the query cache is empty, which is normal.
Qcache_lowmem_prunes: This parameter records how many queries were 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 have not been cached because of 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

Copy Code code as follows:

Mysql> SELECT * from user where id = 2;
+--+ ——-+
| ID | name |
+--+ ——-+
| 2 | Test2 |
+--+ ——-+
1 row in Set (0.02 sec)

Then perform show status like '%qcache% ' to see what changes are:

Copy Code code as follows:

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 hasn't changed, we're doing the same thing down here.
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 of the change is temporarily not concerned, readers can test themselves), again:

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

Copy Code code as follows:

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, exactly the same as our analysis.