Mysql query caching mechanism Basic Learning tutorial _mysql

The MySQL caching mechanism is simply caching SQL text and query results, and if you run the same SQL, the server takes the results directly from the cache without having to parse and execute the SQL. If the table changes, all buffered queries that use this table will no longer be valid and the relevant entries for the query cache value are emptied. Changes refer to any data or structure changes in the table, including insert, UPDATE, DELETE, TRUNCATE, ALTER table, drop table, or drop database, It also includes those queries that map to the changed tables using the merge table. Obviously, this is not appropriate for frequently updated tables, and query caching can save a lot of performance for tables that do not often change data and have a large number of identical SQL queries.

Hit condition

The cache exists in a hash table, querying SQL, querying the database, client protocols, and so on as key. Before deciding whether to hit, MySQL does not parse SQL, but instead directly uses SQL to query the cache, and any differences in SQL characters, such as spaces, comments, can result in cache misses.

If there are indeterminate data in the query, such as the Current_date () and now () functions, then the query is not cached after completion. So, queries that contain indeterminate data are definitely not going to find the available cache

Work flow

1. server receives SQL, SQL and some other conditions for key Lookup cache table (additional performance consumption)

2. If the cache is found, return the cache directly (performance boost)

3. If the cache is not found, execute the SQL query, including the original SQL parsing, optimization, and so on.

4. After the execution of the SQL query results, the SQL query results are stored in the cache table (additional performance consumption)

Cache Invalidation

When a table is writing data, the cache of this table (hit check, Cache writes, etc.) will be in a failed state. In InnoDB, if a transaction modifies a table, the table's cache is invalidated before the transaction commits, and the related query for the table cannot be cached until the transaction is committed.

Memory Management for caching

Caching creates a chunk of memory in memory (Query_cache_size) to maintain cached data, with about 40K of space being used to maintain cached metadata, such as space memory, mapping of data tables and query results, mapping of SQL and query results, and so on.

MySQL divides this large chunk of memory into small chunks of memory (query_cache_min_res_unit), each of which stores its own type, size and query result data, and pointers to memory blocks before and after.

MySQL needs to set the size of a single small storage block and apply for a space when the SQL query starts (not yet the result), so even if your cached data doesn't reach that size, you need to use this size block of data to save it (this is the same as the Linux file system blocks). If the result is greater than the size of this block of memory, you will need to request a memory block. When the query is completed found that the requested memory block has a surplus, it will be freed up the space, which will cause memory fragmentation problem, see the following figure

Here Query 1 and Query 2 is a blank part of the memory fragmentation, this part of the free memory is a query 1 after the release of query, assuming that this space large and small in MySQL set the size of the memory block, you can no longer be used, causing debris problems

Request allocation memory block at the beginning of the query the allocation of memory blocks is very resource-intensive, so it is necessary to lock up the entire free memory area. Note that the allocated memory is allocated on the memory that was developed during MySQL initialization.

Time to use caching

Measuring whether opening a cache is a performance boost to the system is a difficult topic

1. Cache Hit rate = Cache hits (qcache_hits)/number of queries (Com_select)

2. By cache write rate, write rate = cache write count (qcache_inserts)/number of queries (Qcache_inserts)

3. By hit-write rate, ratio = hit count (qcache_hits)/write times (qcache_inserts), high-performance MySQL is called the comparison can reflect performance improvement index, generally 3:1 is the query cache is valid, and the best to achieve 10:1

Cache configuration Parameters

1. Query_cache_type: Whether to open the cache

Available options

1) off: Close

2) on: Always Open

3) DEMAND: only explicitly written Sql_cache query will be inhaled cache

2. Query_cache_size: The total amount of memory space used by the cache, in bytes, this value must be 1024 integer times, otherwise the MySQL actual allocation may be different from this value (feel this should be related to the file system's Blcok size)

3. Query_cache_min_res_unit: Minimum unit size when allocating memory blocks

4. Query_cache_limit:mysql can cache the maximum results, if exceeded, then increase the qcache_not_cached value, and delete the query results

5. Query_cache_wlock_invalidate: If a data table is locked, whether the data is still returned from the cache, the default is off, which means that you can still return

Parameter explanations for caching in the GLOBAL Staus:

Qcache_free_blocks: Number of free blocks in the cache pool

Qcache_free_memory: Amount of free memory in cache

Qcache_hits: Cache Hit count

Qcache_inserts: Number of Cache writes

Qcache_lowmen_prunes: Remove cache count due to insufficient memory

Qcache_not_cached: Query not cached number of times, such as query result exceeds cache block size, query contains variable functions, etc.

Qcache_queries_in_cache: The number of SQL cached in the current cache

Qcache_total_blocks: Cache Total block number

Reduce fragmentation policies

1. Select the appropriate block size

2. Use the FLUSH query cache command to defragment. This command causes other connections to be unable to use the query cache while the cache is being sorted

PS: Empty Cached command RESET QUERY cache

Query Caching Problem analysis

InnoDB and query caching

InnoDB sets a transaction counter for each table that stores the current maximum transaction ID. When a transaction commits, InnoDB uses the transaction ID with the largest system transaction ID in MVCC to the counter of the new current table.

Only transactions that are larger than this maximum ID can use the query cache, and other transactions that are smaller than this ID cannot use the query cache.

In addition, in InnoDB, all transactions that have a lock operation do not use any query caching

The query must be exactly the same (bytes by byte) to be considered the same. In addition, the same query string may be considered different for other reasons. Queries that use different databases, different protocol versions, or different default character sets are considered to be different queries and are cached separately.

The following SQL query caching is considered different:

SELECT * FROM Tbl_name 
select * from Tbl_name

Query Cache related Parameters

Mysql> show VARIABLES like '%query_cache% '; 

+------------------------------+---------+ 
| Variable_name        | Value  | 
+------------------------------+---------+ 
| Have_query_cache       | YES   |--Query cache availability 
| Query_cache_limit      | 1048576 |-cache The maximum value of a specific query result 
| Query_cache_min_res_unit   | 4096  |  
| query_cache_size       | 599040 |--Query cache size 
| Query_cache_type       | On   |--block or support query caching 
| query_cache_wlock_invalidate | Off   |  
+------------------------------+---------+ 

Here is an example of a simple MySQL query caching mechanism:

[mysql@csdba1850 ~]$ 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; --Open Query cache 

Query OK, 0 rows Affected (0.00 sec) 

 
mysql> Use test 

Reading table information for completion of table and column names you can turn out this feature to get 
a quicker sta Rtup with-a 
 
Database changed 

Mysql> Show tables; 

+----------------+ 
| Tables_in_test | 
+----------------+ 
| animals    |  
|  
+----------------+ 
5 rows in Set (0.00 sec) 
 
mysql> Select COUNT (*) from animals; 
+----------+ 
| count (*) | 
+----------+ 
|    6 |  
+----------+ 
1 row in Set (0.00 sec) 
 
--qcache_hits represents the cumulative number of times a SQL query hits the cache, which is an additive 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) 

+---------------+-------+ 
| variable_name | Value | 
+---------------+-------+ 
| Qcache_hits  | 1   |--means SQL gets results directly in cache, no need to parse 
+---------------+-------+ 
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 from the cache directly to the result 
+---------------+-------+ 
1 row in Set (0.00 sec) 

 
mysql> INSERT INTO Animals Select 9, ' Testsds '; --After inserting the data, all the SQL caches associated with the table are 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  |-or equal to 3, indicates that the previous SQL was not directly from the cache 
+---------------+-------+ 
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)