Basic tutorial on querying the cache mechanism of MySQL,

Basic tutorial on querying the cache mechanism of MySQL,

The MySQL cache mechanism simply caches the SQL text and query results. If the same SQL statement is run, the server directly obtains the results from the cache without parsing and executing the SQL statement. If the table is changed, all buffered queries using this table will no longer be valid, and the items related to the query cache value will be cleared. Changes refer to any data or structure changes in the TABLE, including INSERT, UPDATE, DELETE, TRUNCATE, alter table, drop table, or drop database, this also includes queries for tables mapped to changed tables that use MERGE tables. Obviously, the query cache is not suitable for frequently updated tables. For tables that do not often change data and have a large number of identical SQL queries, the query cache will save a lot of performance.

Hit Condition

The cache contains a hash table, which uses SQL queries, database queries, and client protocols as keys. before determining whether a hit is hit, MySQL does not parse the SQL statement, but directly uses the SQL statement to query the cache. Any difference in SQL characters, such as spaces and comments, will lead to cache miss.

If there are uncertain data in the query, such as the CURRENT_DATE () And NOW () functions, the query will not be cached after the query is completed. Therefore, the query containing uncertain data will certainly not find the available cache.

 

Workflow

1. The server receives the SQL statement and uses the SQL statement and other conditions as the key to find the cache table (additional performance consumption)

2. If the cache is found, the cache is directly returned (performance improvement)

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

4. After the SQL query results are executed, save the SQL query results to the cache table (additional performance consumption)

Cache failure

When a table is writing data, the cache (hit check, cache write, etc.) of the table will be invalid. in Innodb, if a transaction modifies a table, the cache of the table is invalid before the transaction is committed. before the transaction is committed, related queries of this table cannot be cached.

 

Cache memory management

The cache will open up a piece of memory (query_cache_size) in the memory to maintain the cache data. About 40 kb of space is used to maintain the cache metadata, such as the Space Memory, ing between data tables and query results, and ing between SQL and query results.

MySQL divides this large memory block into small memory blocks (query_cache_min_res_unit). Each small block stores its own type, size, query result data, and pointers to the front and back memory blocks.

MySQL needs to set the size of a single small storage block. When the SQL query starts (the result is not yet obtained), it applies for a space, so even if your cache data does not reach this size, you also need to use this size of data Block for storage (this is the same as the Linux File System Block ). if the result exceeds the size of the memory block, you need to apply for another memory block. when the requested memory block is found to be surplus after the query is completed, the excess space will be released, which will cause memory fragmentation problems. See

The blank part between Query 1 and query 2 is the memory fragmentation, and the idle memory is released after query 1. Assume that the space is smaller than the memory block size set by MySQL, it cannot be used again, causing fragmentation problems.

When you request to allocate a memory Block at the beginning of the query, the entire idle memory zone needs to be locked. Therefore, allocating a memory Block consumes a lot of resources. note that the memory allocated here is allocated on the memory opened during MySQL initialization.

 

Cache Usage time

It is difficult to determine whether cache is enabled to improve the system performance.

1. Based on the cache hit rate, the cache hit rate = cache hits (Qcache_hits)/queries (Com_select)

2. cache write rate, write rate = cache write count (Qcache_inserts)/Query Count (Qcache_inserts)

3. based on the hit-write rate judgment, the ratio = hit Times (Qcache_hits)/Write times (Qcache_inserts). High-Performance MySQL is called a comparison index that reflects performance improvement, generally, the query cache is effective when the value reaches, and it is better to reach.

 

Cache configuration parameters

1. query_cache_type: whether to enable the cache

Optional

1) OFF: Close

2) ON: always open

3) DEMAND: only queries with SQL _CACHE explicitly written will be sucked into the cache.

 

2. query_cache_size: The total memory space used by the cache, in bytes. The value must be an integer multiple of 1024, otherwise, the actual allocation of MySQL may be different from this value (I think this should be related to the blcok size of the file system)

 

3. query_cache_min_res_unit: minimum unit size for memory block allocation

 

4. query_cache_limit: Maximum number of results that can be cached by MySQL. If the value exceeds the upper limit, the value of Qcache_not_cached is increased and the query result is deleted.

 

5. query_cache_wlock_invalidate: If a data table is locked and the data is still returned from the cache, the default value is OFF, indicating that the data can still be returned.

 

Description of cache parameters in global staus:

Qcache_free_blocks: Number of idle blocks in the cache pool

Qcache_free_memory: The amount of idle memory in the cache

Qcache_hits: cache hits

Qcache_inserts: Number of cache writes

Qcache_lowmen_prunes: Number of times the cache is deleted due to insufficient memory

Qcache_not_cached: Number of times the query has not been cached. For example, the query result exceeds the cache block size, and the query contains variable functions.

Qcache_queries_in_cache: Number of SQL statements cached in the current Cache

Qcache_total_blocks: Total number of cache Blocks

 

Fragment reduction policy

1. select an appropriate block size

2. Use the flush query cache command to clear fragments. This command will cause other connections to fail to use the query cache during CACHE sorting.

PS: Clear the CACHE's imperative RESET QUERY CACHE

 

Query cache Problem Analysis

InnoDB and query Cache

Innodb sets a transaction counter for each table, which stores the largest transaction ID currently. when a transaction is committed, InnoDB uses the transaction ID with the largest system transaction ID in MVCC and the counter of the new current table.

Only transactions with a larger ID can use the query cache. Other transactions with a smaller ID cannot use the query cache.

In addition, in InnoDB, no query cache is used for all transactions with lock operations.

The query must be exactly the same (in bytes) to be considered the same. In addition, the same query string may be considered different for other reasons. Queries using different databases, different protocol versions, or different default character sets are considered to be different queries and cache them separately.

The following SQL query cache is considered different:

SELECT * FROM tbl_name Select * from tbl_name

Query cache Parameters

mysql> SHOW VARIABLES LIKE '%query_cache%'; 

+ Cached + --------- + | Variable_name | Value | + cached + --------- + | have_query_cache | YES | -- query whether the cache is available | query_cache_limit | 1048576 | -- maximum Value of the cache query result | query_cache_min_res_unit | 4096 | query_cache_size | 599040 | -- Query cache size | query_cache_type | ON | -- block or support query cache | query_cache_wlock_invalidate | OFF | + ------------------------ + --------- +

The following is an example of a simple MySQL query cache 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; -- sets the cache memory.

Query OK, 0 rows affected (0.00 sec) 

 

Mysql> set session query_cache_type = ON; -- enable 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 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) -- Qcache_hits indicates the cumulative number of times that the SQL query hits in the cache, is the cumulative value.

mysql> SHOW STATUS LIKE 'Qcache_hits'; 

+ --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Qcache_hits | 0 | -- 0 + --------------- + ------- + 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 the SQL statement is directly obtained in the 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 preceding SQL statement is also obtained directly from the cache + --------------- + ------- + 1 row in set (0.00 sec)

Mysql> insert into animals select 9, 'testsds '; -- after data is inserted, all SQL caches related to the table will be cleared.

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, note that the previous SQL statement is not directly obtained 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) 

Articles you may be interested in:

• A feasible method for clearing mysql query Cache
• Mysql query cache description
• Mysql sets query Cache