MySQL中Redo Log相關的重要引數總結

引數介紹

下面介紹、總結一下MySQL的Redo Log相關的幾個重要引數：innodb_log_buffer_size、innodb_log_file_size、innodb_log_files_in_group

innodb_log_buffer_size

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default is 16MB. A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For related information, see Memory Configuration, and Section 8.5.4, “Optimizing InnoDB Redo Logging”. For general I/O tuning advice, see Section 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的介紹如下：

The size in bytes of each log file in a log 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 make crash recovery slower.

The minimum innodb_log_file_size is 4MB.

For related information, see Redo Log File Configuration. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

If innodb_dedicated_server is enabled, the innodb_log_file_size value is automatically configured if it is not explicitly defined. For more information, see Section 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)

官方檔案的介紹如下所示：

The number of log files in the log group. InnoDB writes to the files in a circular fashion. The default (and recommended) value is 2. The location of the files is specified by innodb_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大小，規則如下

從MySQL 8.0.14開始，根據buffer pool size的大小進行配置innodb_log_file_size引數

innodb_log_files_in_group

MySQL 8.0.14中，根據buffer pool size的大小進行自動配置（單位是GB），

如果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

標籤：其他

上一篇：關于sql server中特殊空格去除不掉

下一篇：Oracle學習筆記九：表空間與資料檔案管理