mysql-innodb-Cache Management

MySQL Memory structure

InnoDB Primary index is clustered index, index and data common table space, for InnoDB, data is index, index is data. The biggest difference between the InnoDB cache mechanism and the MyISAM cache mechanism is that INNODB is not just a cache index, but also caches the data.

InnoDB Cache Pool
InnoDB Cache Pools (InnoDB buffer pool) are the core of InnoDB performance improvements that can cache data and cache indexes, and even other administrative data such as metadata information, row-level lock information.

Use the MySQL command "show variables like ' innodb%pool% ';" To view the relevant parameter information for the INNODB cache pool.

innodb_buffer_pool_size: Used to set the size of the InnoDB cache pool (Innodb_buffer_pool).
The size of the INNODB cache pool has a large impact on InnoDB overall performance, and if the current MySQL server is dedicated to providing MySQL services, increase the size of the innodb_buffer_pool_size and put the frequently accessed data into memory. Minimize InnoDB access to the hard disk, and strive to maximize the InnoDB as an in-memory storage index, and strive to maximize InnoDB as a memory storage engine.

innodb_buffer_pool_instances: The default value is 1, which means that the InnoDB cache pool is partitioned into a zone. Increase the parameter appropriately (for example, set the parameter value to 2), at which point the InnoDB is divided into two regions, which can improve the concurrency performance of InnoDB. If the InnoDB cache pool is divided into multiple regions, it is recommended that each zone be less than 1GB in space.

innodb_additional_mem_pool_size: Specifies the cache size that InnoDB uses to store data dictionaries and other internal structures, with a default value of 2MB. The greater the number of InnoDB tables, the greater the value of the parameter should be set, and when InnoDB runs out of cache space, InnoDB will request memory space and write a warning message to the error log.

InnoDB Cache Pool Internal structure
InnoDB maintains a cache pool in memory for caching data and indexes. The cache pool can be considered a long list of LRU, which is divided into 2 sub-lists, a sub-linked list of old pages (which is stored in a long time not accessed data pages), and another child link to the new pages (which contains the most recently accessed data page). The remainder is occupied by new pages, as shown in. The data page near the head of the LRU list represents the most recently accessed data page near the end of the LRU chain list, which represents a long time of not being accessed, and the two-part intersection becomes midpoint.

When users need access to data, InnoDB first finds data in the InnoDB buffer pool, and if there is no data in the buffer pool, InnoDB inserts chunks of data on the hard disk into the InnoDB cache pool, or if the InnoDB buffer pool is full, InnoDB clearly innodb the individual data blocks in the cache pool through the LRU algorithm. Whenever a new chunk of data needs to be loaded into the InnoDB buffer pool, the data block is strained to the point where the ' data page ' is inserted into the midpoint and declared as an old data page. When will the old data page be moved to the new page list?

(1) When the innodb_old_blocks_time parameter value is set to 0 o'clock. When the old part of the data page is accessed, the data page is promoted to the head of the list and marked as a new data page.
(2) when the parameter value of Innodb_old_blocks_time is greater than 0 o'clock (for example, 1000 milliseconds or 1 seconds). Old part of the data page is inserted into the buffer pool and is accessed after 1 seconds, and the data page is promoted to the head of the list and marked as a new data page. The data page is not moved to the head of the new list, even if the old part of the data page is accessed during the first insertion into a second.

Use the MySQL command show variables like ' innodb_old% '; You can view parameter information for the INNODB buffer pool structure.

innodb_old_blocks_pct: Controls the length of the old page sub-linked list in the LRU list.
innodb_old_blocks_time: The time to control the data page of the old page sub-list to move to the new page sub-list.

InnoDB Cache Pool Preheating
After the MySQL service starts for a period of time, InnoDB will place frequently accessed data (including business data, management data) into the InnoDB buffer pool, where the InnoDB buffer pool holds frequently accessed data (referred to as hot data). How do I reload the hot data from the previous InnoDB buffer pool into the INNODB buffer pool when the InnoDB buffer pool size is dozens of GB or even hundreds of GB, for some reason (such as regular database maintenance) to restart the MySQL service? Simply put: How to warm up the InnoDB buffer pool so that the MySQL server can quickly revert to the performance state before restarting the MySQL service?

Starting with version 5.6, MySQL supports the shutdown of the MySQL service when saving hot data in memory to the hard disk, and MySQL restarts the first time the data in the hard disk is loaded into the InnoDB buffer pool, in order to shorten the warmup process and increase the efficiency when the business is busy and high concurrency. Use the MySQL command show variables like '%innodb%pool% '; You can view the parameter settings for the INNODB buffer pool preheating feature.

innodb_buffer_dump_at_shutdown: The default is off off. If this parameter is turned on, InnoDB saves hot data from the InnoDB buffer pool to the local hard disk when the MySQL service is stopped.
innodb_buffer_pool_load_at_startup: The default is off off. If this parameter is turned on, MySQL loads the local hot data into the InnoDB buffer pool when the MySQL service is started.
innodb_buffer_pool_load_now: The default is off off. If this parameter is turned on, the hot data in the InnoDB cache pool is manually saved to the local hard disk when the MySQL service is stopped.
innodb_buffer_pool_filename: If the InnoDB warm-up function is turned on and the MySQL service is stopped, MySQL saves the hot data from the InnoDB buffer pool to the root of the database, and the default file name is Ib_buffer_ Pool.
Innodb_buffer_pool_load_aborr: The default is off off. If this parameter is turned on, MySQL will not load hot data from the local hard disk into the InnoDB buffer pool even if the InnoDB warm-up function is turned on and the MySQL service room is started.

After you turn on the INNODB buffer pool Preheat, use the MySQL command ' show variables like ' innodb_buffer% '; You can view the status information for the INNODB buffer pool preheating.

mysql-innodb-Cache Management