MySQL Open cache

MySQL open query cache can have two ways to open the use of the set command to open, and the other is to directly modify the My.ini file directly set is very simple oh.

Open the cache, set the cache size, specifically implemented as follows:

1, modify the configuration file, under Windows is my.ini,linux under the my.cnf;

On the last append of the configuration file:

The code is as follows	Copy Code
Query_cache_type = 1 Query_cache_size = 600000

Need to restart MySQL to take effect;

Then adopt the second way;

b) Open the cache in two ways:

A) Use the MySQL command:

The code is as follows	Copy Code
Set global query_cache_type = 1; Set global query_cache_size = 600000;

If error:

Query cache is disabled; Restart the server with query_cache_type=1 ...

On the MySQL command line input

Show variables like "%query_cache%" to see if the settings are successful, now you can use the cache;
Of course, if you have updates to the data sheet to do, it's okay MySQL default will be related to this table cache deleted, the next query will be read directly to the table and then cached

The following is a simple example:

The code is as follows

Copy Code

[Email protected] ~]$ mysql-u root-p Enter Password: Welcome to the MySQL Monitor. Commands End With; Or/g. Your MySQL Connection ID is 3 Server version:5.0.45-community MySQL Community Edition (GPL) Type ' help; ' or '/h ' for help. Type '/C ' to clear the buffer. mysql> Set Global query_cache_size = 600000; Set Cache memory Query OK, 0 rows Affected (0.00 sec) Mysql> Set Session Query_cache_type = on; Turn on query caching Query OK, 0 rows Affected (0.00 sec) mysql> Use test Reading table information for completion of table and column names you can turn off this feature to Get a quicker startup With-a Database changed mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | Animals | | person | +----------------+ 5 rows in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 6 | +----------+ 1 row in Set (0.00 sec) The qcache_hits represents the cumulative number of hits in the cache by the MySQL cache query, which is the cumulative value. mysql> SHOW STATUS like ' qcache_hits '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 0 | --0 times +---------------+-------+ 8 rows in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 6 | +----------+ 1 row in Set (0.00 sec) mysql> SHOW STATUS like ' qcache% '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 1 | Indicates that SQL results directly in the cache and does not need to be parsed +---------------+-------+ 8 rows in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 6 | +----------+ 1 row in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 6 | +----------+ 1 row in Set (0.00 sec) mysql> SHOW STATUS like ' qcache_hits '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 3 | The above SQL is also a direct fetch from the cache to the result +---------------+-------+ 1 row in Set (0.00 sec) mysql> INSERT into animals Select 9, ' Testsds '; After inserting the data, the SQL cache associated with the table will be emptied out Query OK, 1 row Affected (0.00 sec) Records: 1 duplicates:0 warnings:0 mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 7 | +----------+ 1 row in Set (0.00 sec) mysql> SHOW STATUS like ' qcache_hits '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 3 | is equal to 3, indicating that the previous SQL was not directly obtained from the cache directly +---------------+-------+ 1 row in Set (0.00 sec) Mysql> Select COUNT (*) from animals; +----------+ | COUNT (*) | +----------+ | 7 | +----------+ 1 row in Set (0.00 sec) mysql> SHOW STATUS like ' qcache_hits '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Qcache_hits | 4 | +---------------+-------+ 1 row in Set (0.00 sec)

The above related content is the MySQL cache query and settings introduced, I hope you can gain some.

Add

First: Query_cache_type How to use query caching
In general, we will set the Query_cache_type to on, which should be on by default

The code is as follows	Copy Code
Mysql> SELECT @ @query_cache_type; +--------------------+ | @ @query_cache_type | +--------------------+ | On | +--------------------+

Query_cache_type has 3 values of 0 for closing the query cache off,1 on behalf of on,2 (DEMAND) represents the cache when there are sql_cache keywords in the SQL statement, such as: Select Sql_cache user_name from Users where user_id = ' 100 ';
So when we execute select Id,name from TableName, then the query cache is used.
① Query_cache_type Open, if you do not want to use the cache, you need to specify the
Select Sql_no_cache id,name from TableName;
② when using MySQL functions in SQL,
  is not cached;
Of course you can disable the query cache: mysql> set session Query_cache_type=off;
Second: System variable Have_query_cache sets whether the query cache is available

The code is as follows	Copy Code
Mysql> Show variables like ' Have_query_cache '; +------------------+-------+ | variable_name | Value | +------------------+-------+ | Have_query_cache | YES |

+------------------+-------+
The above display indicates that the set query cache is available.
Third: System variable Query_cache_size
Represents the query cache size, which is the allocation of memory size to the query cache, if you allocate a size of 0,
Then the first and second steps do not work, or there is no effect.

The code is as follows	Copy Code
Mysql> SELECT @ @global. query_cache_size; +---------------------------+ | @ @global. query_cache_size | +---------------------------+ | 16777216 | +---------------------------+

Above is mysql6.0 set default, the previous version as if the default is 0, then it is necessary to set the next.
Sets the SET @ @global. query_cache_size=1000000; Here is the setting of about 1M, more than 900 K.
Look again under

The code is as follows	Copy Code
SELECT @ @global. query_cache_size; +---------------------------+ | @ @global. query_cache_size | +---------------------------+ | 999424 | +---------------------------+

Shows that we set the new size to indicate that the setting was successful.
IV: Query_cache_limit control The maximum value of cached query results
For example: If the query result is very large, also cache???? This is obviously out of the question.
MYSQL can set a maximum cache value, when you query the cache number result data exceeds this value will not
To cache. The default is 1M, which means that more than 1M query results are not cached.

The code is as follows	Copy Code
Mysql> SELECT @ @global. query_cache_limit; +----------------------------+ | @ @global. query_cache_limit | +----------------------------+ | 1048576 | +----------------------------+

This is the default value, if you need to modify it, just like setting the cache size, using Set
Re-specify the size.
OK, in 4 steps you can open the query cache, the size of the specific value and the way the query is different
To specify the situation.
MySQL Query cache related variables

The code is as follows

Copy Code

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 | 16777216 | | Query_cache_type | On | | Query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 6 rows in Set (0.00 sec)

V: View the status of the cache

The code is as follows

Copy Code

Mysql> Show status like '%qcache% '; +-------------------------+----------+ | variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 11 | | Qcache_free_memory | 16610552 | | Qcache_hits | 10 | | Qcache_inserts | 155 | | Qcache_lowmem_prunes | 0 | | qcache_not_cached | 21 | | Qcache_queries_in_cache | 111 | | Qcache_total_blocks | 256 | +-------------------------+----------+ 8 rows in Set (0.00 sec)

MySQL provides a series of Global status to record the current state of the Query Cache, as follows:
Qcache_free_blocks: Number of memory blocks in Query Cache that are currently idle
Qcache_free_memory: Total amount of Query Cache memory currently in idle state
Qcache_hits:query Cache Hit Count
Qcache_inserts: The number of times to insert a new query cache into the query cache, that is, the number of missed hits
Qcache_lowmem_prunes: When query cache memory capacity is insufficient, you need to remove the old query cache from the number of times it will be used for the new cache object
Qcache_not_cached: The number of SQL not being cache, including SQL that cannot be cache, and SQL that will not be cache because of Query_cache_type settings
Qcache_queries_in_cache: Number of SQL currently in Query cache
The total number of blocks in the Qcache_total_blocks:query Cache

Sixth: Check Query cache usage
The simplest way to check whether to benefit from the query cache is to check the cache hit ratio
When the server receives a SELECT statement, the qcache_hits and com_select variables are cached according to the query
The situation is incremented
The calculation formula for query cache hit ratio is: qcache_hits/(qcache_hits + com_select).

The code is as follows	Copy Code
Mysql> Show status like '%com_select% '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Com_select | 1 | +---------------+-------+

Query_cache_min_res_unit configuration is a "double-edged sword", the default is 4KB, set the value of large data query is good, but if your query is small data query, it is easy to create memory fragmentation and waste.

Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use flush query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your queries are small data volumes.

Query Cache utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%

Query cache utilization below 25% indicates that the query_cache_size setting is too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a little bit small, or too much fragmentation.

Query Cache Hit Ratio = (qcache_hits-qcache_inserts)/qcache_hits * 100%

Sample server query Cache Fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache Hit Ratio = 1.94%, hit ratio is poor, probably write more frequently, and possibly some fragments.

MySQL Open cache