Why is MySQL slow to start and close services under normal circumstances!

Once, at the customer site, during tuning, I found that a 128G memory server was configured, but when innodb_buffer_pool_size was set to 4G and 100G, it was found that it was very slow when shutting down and starting? Suddenly, I have never encountered such a scene. When in doubt, there are the following settings for verification.

MySQL provides parameters for shutting down and starting hot data:
show variables where variable_name like 'innodb_buffer_pool_dump%' or variable_name like 'innodb_buffer_pool_load%'; 
A new feature in MySQL 5.6

innodb_buffer_pool_dump_at_shutdown = 1
Explanation: Dump hot data to the local disk when shutting down.

innodb_buffer_pool_dump_now = 1
Explanation: Manually dump the hot data to the local disk.

Explanation: Specify the percentage of page reads and dumps recently used by each buffer pool. The range is 1 to 100. The default value is 25. For example, if there are 4 buffer pools, each buffer pool has 100 pages, and innodb_buffer_pool_dump_pct is set to 25, then dump the 25 most recently used pages in each buffer pool.

whether to abort the buffer pool load operation, the default is closed

innodb_buffer_pool_load_at_startup = 1
Explanation: Load hot data into memory at startup.

innodb_buffer_pool_load_now = 1
Explanation: Manually load the hot data into the memory.

Save buffer file

When MySQL is closed, the hot data in the memory will be saved in the ib_buffer_pool file on the disk, located in the innodb_log_group_home_dir data directory where the redo log is stored.

Check the log, you will find:


After startup, the hot data will be automatically loaded into the Buffer_Pool buffer pool.
View mode: display loading time

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'; 

Manual loading
See configuration parameters (innodb_buffer_pool_load_at_startup)

mysql> SHOW variables WHERE variable_name  like 'innodb_buffer_pool_dump%'     -> or variable_name  like 'innodb_buffer_pool_load%';

View execution status: no loading

mysql>SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

Load manually:

mysql> set global innodb_buffer_pool_load_now=1;

In this way, the hot data is always kept in the memory.

Only when the MySQL service is normally shut down or pkill mysql, the hot data will be dumped to the memory. The machine is down or pkill -9 mysql will not dump.

Official instructions: https://dev.mysql.com/doc/refman/5.7/en/innodb-preload-buffer-pool.html