参数介绍
下面介绍, 总结一下 MySQL 的 Redo Log 相关的几个重要参数: innodb_log_buffer_size,innodb_log_file_size,innodb_log_files_in_group
innodb_log_buffer_size
Command-Line Format | --innodb-log-buffer-size=# |
System Variable | innodb_log_buffer_size |
Scope | Global |
Dynamic | Yes |
SET_VARHint Applies | No |
Type | Integer |
Default Value | 16777216 |
Minimum Value | 1048576 |
Maximum Value | 4294967295 |
The size in bytes of the buffer thatInnoDBuses to write to thelog fileson disk. The default is 16MB. A largelog bufferenables largetransactionsto run without the need to write the log to disk before the transactionscommit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For related information, seeMemory Configuration, andSection 8.5.4, "Optimizing InnoDB Redo Logging". For general I/O tuning advice, seeSection 8.5.8, "Optimizing InnoDB Disk I/O".
参数用来设置缓存还未提交的事务的缓冲区的大小, 通俗来说也就是日志缓冲区的大小. 一般默认值 16MB 是够用的, 但如果事务之中含有 blog/text 等大字段, 这个缓冲区会被很快填满会引起额外的 IO 负载. 可通过查看 innodb_log_waits 状态, 如果不为 0 的话, 则需要增加 innodb_log_buffer_size.
- MySQL> show variables like 'innodb_log_buffer_size';
- +------------------------+----------+
- | Variable_name | Value |
- +------------------------+----------+
- | innodb_log_buffer_size | 16777216 |
- +------------------------+----------+
- 1 row in set (0.00 sec)
- MySQL> show status like 'innodb_log_waits';
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | Innodb_log_waits | 0 |
- +------------------+-------+
- 1 row in set (0.00 sec)
- MySQL>
- innodb_log_file_size
参数 innodb_log_file_size 用于设定 MySQL 日志组中每个日志文件的大小. 此参数是一个全局的静态参数, 不能动态修改.
- MySQL> show variables like 'innodb_log_file_size';
- +----------------------+----------+
- | Variable_name | Value |
- +----------------------+----------+
- | innodb_log_file_size | 50331648 |
- +----------------------+----------+
- 1 row in set (0.02 sec)
官方文档关于参数 innodb_log_file_size 的介绍如下:
Command-Line Format | --innodb-log-file-size=# |
System Variable | innodb_log_file_size |
Scope | Global |
Dynamic | No |
SET_VARHint Applies | No |
Type | Integer |
Default Value | 50331648 |
Minimum Value | 4194304 |
Maximum Value | 512GB / innodb_log_files_in_group |
The size in bytes of eachlog filein alog group. The combined size of log files (innodb_log_file_size*innodb_log_files_in_group) cannot exceed a maximum value that is slightly Less than 512GB. A pair of 255 GB log files, for example, approaches the limit but does not exceed it. The default value is 48MB.
Generally, the combined size of the log files should be large enough that the server can smooth out peaks and troughs in workload activity, which often means that there is enough redo log space to handle more than an hour of write activity. The larger the value, the Less checkpoint flush activity is required in the buffer pool, saving disk I/O. Larger log files also makecrash recoveryslower.
- The minimuminnodb_log_file_sizeis 4MB.
- For related information, seeRedo Log File Configuration. For general I/O tuning advice, seeSection 8.5.8, "Optimizing InnoDB Disk I/O".
- Ifinnodb_dedicated_serveris enabled, theinnodb_log_file_sizevalue is automatically configured if it is not explicitly defined. For more information, seeSection 15.8.12, "Enabling Automatic Configuration for a Dedicated MySQL Server".
注意事项:
. 参数 innodb_log_file_size 的单位为字节, 它的默认值 (MySQL 5.6.8 以及之后版本) 默认为 48M, 50331648/1024/1024=48M. 而在之前的 MySQL 版本中(例如 MySQL 5.5), 此参数的默认值为 5M.
. 参数 innodb_log_file_size 的最小值跟 MySQL 版本有关系, MySQL 5.7.11 之前的版本中, 参数 innodb_log_file_size 的最小值为 1MB,MySQL 5.7.11 以后版本, 参数 innodb_log_file_size 的最小值增加到 4MB.
. 参数 innodb_log_file_size 的最大值, 二进制日志文件大小 (innodb_log_file_size*innodb_log_files_in_group) 不能超过 512GB, 所以一般而言, 其大小值为 512GB / innodb_log_files_in_group, 而 innodb_log_files_in_group 最小值为 2, 所以 innodb_log_file_size 最大值不能超过 256GB. 其实这个参数也跟 MySQL 版本有关, MySQL 5.5 和之前的版本中, innodb_log_file_size 最大值为 2GB.
. 如果参数 innodb_log_file_size 设置太小, 就会导致 MySQL 的日志文件 (redo log) 频繁切换, 频繁的触发数据库的检查点 (Checkpoint), 导致刷新脏页(dirty page) 到磁盘的次数增加. 从而影响 IO 性能. 另外, 如果有一个大的事务, 把所有的日志文件写满了, 还没有写完, 这样就会导致日志不能切换 (因为实例恢复还需要, 不能被循环复写, 好比 Oracle 中的 redo log 无法循环覆盖) 这样 MySQL 就 Hang 住了. 如果参数 innodb_log_file_size 设置太大的话, 虽然大大提升了 IO 性能, 但是当 MySQL 由于意外 (断电, OOM-Kill 等) 宕机时, 二进制日志很大, 那么恢复的时间必然很长. 而且这个恢复时间往往不可控, 受多方面因素影响. 所以必须权衡二者进行综合考虑.
innodb_log_files_in_group
参数 innodb_log_files_in_group 指定日志组个数. 默认为 2 个日志组.
- MySQL> show variables like 'innodb_log_files_in_group';
- +---------------------------+-------+
- | Variable_name | Value |
- +---------------------------+-------+
- | innodb_log_files_in_group | 2 |
- +---------------------------+-------+
- 1 row in set (0.00 sec)
官方文档的介绍如下所示:
Command-Line Format | --innodb-log-files-in-group=# |
System Variable | innodb_log_files_in_group |
Scope | Global |
Dynamic | No |
SET_VARHint Applies | No |
Type | Integer |
Default Value | 2 |
Minimum Value | 2 |
Maximum Value | 100 |
The number oflog filesin thelog group.InnoDBwrites to the files in a circular fashion. The default (and recommended) value is 2. The location of the files is specified byinnodb_log_group_home_dir. The combined size of log files (innodb_log_file_size*innodb_log_files_in_group) can be up to 512GB.
此参数的默认值为 2, 最小值为 2, 最大值为 100, 一般可能 2~3 居多. 这个参数其实没有什么可以分析的.
innodb_mirrored_log_groups
innodb_mirrored_log_groups: 指定日志镜像文件组的数量, 默认为 1. 相信熟悉 Oracle 的应该不会陌生. 不过这个参数是一个弃用的参数, MySQL 5.6 中已经提示为启用参数, 在 MySQL 5.7 中已经移除了. 这里不做过多介绍.
参数优化
其它几个参数的优化, 其实没有太多可以说的, 主要是关于参数 innodb_log_file_size 的调优. 参数 innodb_log_file_size 的大小设置或者优化设置有没有什么 guideline 呢? 在 MySQL 8.0 之前, 一般是计算一段时间内生成的事务日志 (redo log) 的大小, 而 MySQL 的日志文件的大小最少应该承载一个小时的业务日志量.
一个 Guideline: 计算, 统计一分钟内生成的事务日志大小, 然后以这个值为均值, 计算一个小时内生成了多少日志量. 参考博客 "How to calculate a good InnoDB log file size"
- MySQL> pager grep sequence;
- PAGER set to 'grep sequence'
- MySQL> show engine innodb status\G select sleep(60); show engine innodb status\G
- Log sequence number 1103198881953
- 1 row in set (0.00 sec)
- 1 row in set (1 min 0.00 sec)
- Log sequence number 1103205163584
- 1 row in set (0.00 sec)
- MySQL> nopager;
- PAGER set to stdout
- MySQL> select (1103205163584-1103198881953)/1024/1024 as MB_per_min;
- +------------+| MB_per_min |+------------+| 5.99063015 |+------------+
- 1 row in set (0.00 sec)
- MySQL> select (1103205163584-1103198881953)/1024/1024*60 as MB_per_hour;
- +--------------+| MB_per_hour |+--------------+| 359.43780899 |+--------------+
- 1 row in set (0.03 sec)
- MySQL>
但是关于这个 Guideline 也有一个问题(个人看法), 就是这一分钟是位于业务繁忙时段? 还是业务空闲时段? MySQL 生成日的志是平均的还是有较大的波动范围? 用一分钟内生成的日志大小做均值推算一个小时内生成的事务日志大小, 这个可能存在较大的误差, 所以, 正确的操作应该是计算半小时或一小时内生成的日志量大小. 这样不仅更接近均值, 而且误差也更小.
- MySQL> pager grep sequence;
- PAGER set to 'grep sequence'
- MySQL> show engine innodb status\G select sleep(60*60);show engine innodb status\G
- Log sequence number 1114192951353
- 1 row in set (0.00 sec)
- 1 row in set (1 hour 0.00 sec)
- Log sequence number 1114578626251
- 1 row in set (0.01 sec)
- MySQL> nopager;
- PAGER set to stdout
- MySQL>
MySQL 8.0 引入了 innodb_dedicated_server 自适应参数, 可基于服务器的内存来动态设置 innodb_buffer_pool_size,innodb_log_file_size 和 innodb_flush_method. 默认情况下, 此参数是关闭的.
如果设置参数 innodb_dedicated_server 为 ON 后, MySQL 会自动探测服务器的内存资源, 确定 innodb_buffer_pool_size, innodb_log_file_size 和 innodb_flush_method 三个参数的取值. 具体取值策略如下
innodb_log_file_size:
在 MySQL 8.0.13 之前的版本 (MySQL 8.*) 中, 根据服务器内存来动态设置 innodb_log_file_size 大小. 规则如下
Detected Server Memory | Log File Size |
< 1GB | 48MiB (the default value) |
<= 4GB | 128M |
<= 8GB | 512M |
<= 16GB | 1024M |
> 16GB | 2048M |
从 MySQL 8.0.14 开始, 根据 buffer pool size 的大小进行配置 innodb_log_file_size 参数
buffer pool size | log file size |
<=8GB | 512mb |
8GB--128GB | 1024mb |
大于 128GB | 2048mb |
innodb_log_files_in_group
MySQL 8.0.14 中, 根据 buffer pool size 的大小进行自动配置(单位是 GB).
buffer pool size | log file number |
小于 8GB | ROUND(buffer pool size) |
8GB--128GB | ROUND(buffer pool size * 0.75) |
大于 128GB | 64 |
如果 buffer pool size 小于 2GB,innodb_log_files_in_group 最小值是 2.
注意: 官方只是建议在可以使用全部的系统资源的专用服务器上配置开启该参数. 如果 MySQL 和其它应用 (例如 Tomcat,Apach 等) 共享资源的话, 是不建议开启该参数的.
另外, 参数 innodb_dedicated_server 是一个只读参数, 需要在配置文件 my.cnf 中设置.
- MySQL> set global innodb_dedicated_server=ON;
- ERROR 1238 (HY000): Variable 'innodb_dedicated_server' is a read only variable
参考资料:
- https://www.percona.com/blog/2017/10/18/chose-mysql-innodb_log_file_size/
- https://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/
- https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_file_size
来源: https://www.cnblogs.com/kerrycode/p/13814012.html