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%'; 
image.png
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.

innodb_buffer_pool_dump_pct
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.

innodb_buffer_pool_load_abort
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:

image.png

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'; 
image.png

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%';
image.png

View execution status: no loading

mysql>SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
image.png

Load manually:

mysql> set global innodb_buffer_pool_load_now=1;
image.png

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

Summary:
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