MySQL's Innodb_buffer_pool

After the 1>.mysqld reboot, the Innodb_buffer_pool is almost empty and there is no cached data. With the execution of the SQL statement, the data in the table and the index are gradually populated into the buffer pool, after which the query statement only needs to operate in memory (ideally), greatly improving the performance of MySQL. This gradual filling process may take up to 1-2 hours, or even longer. In this process, MySQL performance is general, because a large number of hard disk read operations are required

2>.INNODB maintains a buffer pool in memory to cache data and indexes, and the cache pool manages a list of data blocks that are divided into 2 columns, one child column for new blocks, and the other a child column for the old blocks. Old blocks defaults to 3/8 of the entire column size (the default value can be changed by innodb_old_blocks_pct, which is a percentage of 5-95), and the remaining size is new blocks occupied

If you export it manually, you can use this command:

Mysql> SET Innodb_buffer_pool_dump_now=on;

MySQL then generates a file in the InnoDB Data directory: Ib_buffer_pool

When MySQL is turned off, it is automatically exported:

Mysql> SET Innodb_buffer_pool_dump_at_shutdown=on;

Adding Innodb_buffer_pool_load_at_startup=on to the MY.CNF will reload the buffer pool after the mysqld is started.

3>. innodb_buffer_pool_instances: Mainly used to divide the Innodb_buffer_pool, by dividing innodb_buffer_pool into multiple instances, can improve the concurrency capability, It also reduces the buffer pages caused by different threads, but does not have to be set too large, this value does not improve MySQL performance, it needs to be tested;

Off Topic:

MySQL allocates memory for the cache: the operating system provides caching for MyISAM data, MyISAM key cache, InnoDB cache pool, query cache

MySQL's Innodb_buffer_pool