MySQL启停耗时久是什么原因
一、问题背景
基础环境:
-
主机类型: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。