一、问题背景
基础环境：
-
主机类型：x3850 X6
-
操作系统：DB:Red Hat Enterprise Linux 9.1 7.8
-
存储：IBM存储，500GB
-
内存：64 G
-
CPU型号：E7-4830 v3 @ 2.10GHz
-
CPU核数：32CORE
-
数据库环境：8.0.27
问题现象：
测试环境数据库启停耗时较长。
说明：
测试环境有一套MySQL数据库给开发同事使用，某天开发小哥让协助启停一下数据库，发现MySQL启停的时候非常慢。
这场面见得还真不多，带着疑问，一探究竟。
二、一探究竟
MySQL启停加载热数据的相关参数：
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
mysql> show variables like 'innodb_buffer_pool_dump%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
|innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
|innodb_buffer_pool_dump_pct | 25 |
+-------------------------------------+-------+
3 rows in set (0.00 sec)
mysql> show variables like 'innodb_buffer_pool_load%';
+------------------------------------+-------+
| Variable_name |
+------------------------------------+-------+
| innodb_buffer_pool_load_abort | OFF |
|innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
+------------------------------------+-------+
3 rows in set (0.01 sec)
解释：
innodb_buffer_pool_dump_at_shutdown = 1 #在关闭时把热数据dump到本地磁盘。
innodb_buffer_pool_dump_now = 1 #采用手工方式把热数据dump到本地磁盘。
innodb_buffer_pool_dump_pct #指定每个缓冲池最近使用的页面读取和转储的百分比。范围是1到100。默认值是25。例如，如果有4个缓冲池，每个缓冲池有100个page，并且innodb_buffer_pool_dump_pct设置为25，则dump每个缓冲池中最近使用的25个page。
innodb_buffer_pool_load_abort #是否要中止缓冲池加载操作，默认是关闭的
innodb_buffer_pool_load_at_startup = 1 #在启动时把热数据加载到内存。
innodb_buffer_pool_load_now = 1 #采用手工方式把热数据加载到内存。
|
MySQL关闭时，会把内存中的热数据保存在磁盘里ib_buffer_pool文件中，此文件位于redo日志存放的路径innodb_log_group_home_dir数据目录下。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> show variables like '%buffer_pool%';
+-------------------------------------+----------------+
| Variable_name | Value |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool | #此文件
| innodb_buffer_pool_in_core_file | ON |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+
|
MySQL启动时，会自动加载热数据到Buffer_Pool缓冲池里。
| 1 2 3 4 5 6 7 8 |
查看加载时间
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 230227 15:32:13 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)
|
改成手动加载热数据
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
手动进行加载：
mysql> show variables like 'innodb_buffer_pool_dump%';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
+-------------------------------------+-------+
3 rows in set (0.01 sec)
mysql> show variables like 'innodb_buffer_pool_load%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
+------------------------------------+-------+
3 rows in set (0.01 sec)
查看执行状态：没有进行加载
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+------------------------------------+
| Variable_name | Value |
+--------------------------------+------------------------------------+
| Innodb_buffer_pool_load_status | Loading of buffer pool not started |
+--------------------------------+------------------------------------+
1 row in set (0.00 sec)
|
手动进行加载：
| 1 2 3 4 5 6 7 8 9 10 |
mysql> set global innodb_buffer_pool_load_now=1;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 230227 15:59:58 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)
|
这样，始终保持热数据在内存中。
MySQL服务正常关闭，热数据会dump到内存。机器宕机或者kill mysql进程，热数据不会dump。
标签：load,OFF,dump,buffer,耗时,启停,innodb,MySQL,pool From： https://www.cnblogs.com/ataoxz/p/18042826