On MySQL InnoDB cache strategy

On MySQL InnoDB cache policy:

The InnoDB Buffer Pool

Innodb holds a storage area called buffer pool to cache data and indexes in memory, knowing how Innodb Bufferpool works, and using it to read frequently accessed data, is an important aspect of MySQL optimization.

Ideally, adjust the size of the bufferpool to large enough to leave enough memory space for other processes on the server (so that it has no missing pages). The larger the Bufferpool, the InnoDB month is a memory-based database that reads data from the hard disk and then reads the data from memory. Buffer pool Even caches data that is changed by the insert,update operation (insert buffer), so random disk writes can be aggregated in a piece for better performance.

InnoDB the cache pool as a chained management, using the LRU (least recently used) algorithm, when adding a new block to the pool (no space), InnoDB replace (expel) a least recently used block, Then add the new block to the middle of the list, "Midpoint insertion strategy" strategy to see the list of two sub-chains.

1: In the head of the list, is a recently visited strand consisting of some "NEW" (or "young") blocks;

2: At the end of the list, is a sub-chain consisting of some ' old ' blocks that have not recently been accessed (or least visited);

 该算法使大量查询 blocks 保持在  new sublist. old sublist 持有最少使用的 blocks;这些blocks将成为替换或驱逐的候选者。

The size of the 1:3/8 buffer pool is assigned to the old sublist

2: The midpoint (middle insertion point) of the list is where the head of the new sublist tail and old sublist converge

3: When InnoDB reads a block into the buffer pool, it is inserted into the midpoint (old Sublist's head), the block is read in the client operation, eg:sql query, or InnoDB feature readahead (pre-read);

4: When accessing a block in the old sublist, make it ' young ', move it to the head of buffer pool (new Sublist's head), if the block is read because of a client SQL query, the first access occurs immediately, and the block becomes ' young '. If the block is read because of read ahead, the first chant access will not occur and may not occur until the block is replaced;

5: As the database operates, the inaccessible blocks (older) in the buffer pool is moved to the tail of the list. Blocks in old sublist than blocks inserted on midpoint, and eventually, A block that has not been used for a long time will arrive at the end of the old sublist and will be replaced.

By default, the blocks that are read are moved immediately to the head of NEW sublist, which means they stay in the buffer pool for a long time. When a table is scanned (eg, mysqldump operations, or a select operation without a where statement), a large number of blocks push into the buffer pool and expel large amounts of older data, even if the so-called new blocks are not Once again accessed, the same, read ahead background threads load a large number of blocks at a time, which makes the frequently accessed blocks push into the old sublist, and then they become candidates for expulsion.

some InnoDB system variables control the size of the buffer pool and enable you to adjust the LRU algorithm

1:innodb buffer pool size

Specify the size of the buffer pool, if your buffer pool space is small, and there is ample space, so that the pool can reduce the number of disk IO to improve performance;

2:innodb Buffer pool instances: Divided into separate areas, each region is the same, to reduce the competition in concurrent memory read and write operations;

3:innodb old blocks pct: default 3/8;

4:innodb Old blocks Time: Specifies how long in milliseconds (ms) The block is inserted into the Lao Tzu list must stay there for how long after the first visit to move to the new sub-list (to solve the cache pollution problem during pre-reading);

Check to see if the query cache exists on your MySQL server:

Mysql>Show variables like 'Have_query_cache';+------------------+-------+|Variable_name|Value|+------------------+-------+|Have_query_cache|YES|+------------------+-------+1Rowinch Set(0.00Sec

Monitor the performance of the query cache and view the cache state variables using the display state:

Mysql> Show status like ' qcache% '; +-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 1        | | Qcache_free_memory      | 16768376 | | Qcache_hits             | 0        | | Qcache_inserts          | 0        | | Qcache_lowmem_prunes    | 0        | | qcache_not_cached       | 227 |      | Qcache_queries_in_cache | 0        | | Qcache_total_blocks     | 1        |+-------------------------+----------+8 rows in Set (0.00 sec)

　　

Mysql> Select Count(*) fromanimals; +----------+  | Count(*)|  +----------+  |        6 |   +----------+  1Rowinch Set(0.00sec)--Qcache_hits represents the cumulative number of times a SQL query is hit in the cache, which is a cumulative value. Mysql>SHOW STATUS like 'qcache_hits'; +---------------+-------+  |Variable_name|Value|  +---------------+-------+  |Qcache_hits| 0     |  --0 plays+---------------+-------+  8Rowsinch Set(0.00sec) MySQL>  Select Count(*) fromanimals; +----------+  | Count(*)|  +----------+  |        6 |   +----------+  1Rowinch Set(0.00sec) 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+---------------+-------+  8Rowsinch Set(0.00sec) MySQL> Select Count(*) fromanimals; +----------+  | Count(*)|  +----------+  |        6 |   +----------+  1Rowinch Set(0.00sec) MySQL> Select Count(*) fromanimals; +----------+  | Count(*)|  +----------+  |        6 |   +----------+  1Rowinch Set(0.00sec) 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+---------------+-------+  1Rowinch Set(0.00sec) MySQL> Insert  intoAnimalsSelect 9,'Testsds';--after inserting the data, the SQL cache associated with the table will be emptied outQuery OK,1Row affected (0.00sec) Records:1Duplicates:0Warnings:0MySQL> Select Count(*) fromanimals; +----------+  | Count(*)|  +----------+  |        7 |   +----------+  1Rowinch Set(0.00sec) 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+---------------+-------+  1Rowinch Set(0.00sec) MySQL> Select Count(*) fromanimals; +----------+  | Count(*)|  +----------+  |        7 |   +----------+  1Rowinch Set(0.00sec) MySQL>SHOW STATUS like 'qcache_hits'; +---------------+-------+  |Variable_name|Value|  +---------------+-------+  |Qcache_hits| 4     |   +---------------+-------+  1Rowinch Set(0.00Sec

On MySQL InnoDB cache strategy