innodb_log_file_size調整redo log 大小官方 guidelines

Stop the MySQL server and make sure that it shuts down without errors.

Edit my.cnf to change the log file configuration. To change the log file size, configure innodb_log_file_size. To increase the number of log files, configure innodb_log_files_in_group.

Start the MySQL server again.

If InnoDB detects that the innodb_log_file_size differs from the redo log file size, it writes a log checkpoint, closes and removes the old log files, creates new log files at the requested size, and opens the new log file.

上面這段話取自mysql5.7官方手冊。

確定幹淨的關閉資料庫

修改my.cnf 檔案innodb_log_file_size，innodb_log_files_in_group

啟動資料庫

Make your redo log files big, even as big as the buffer pool. When InnoDB has written the redo log files full, it must write the modified contents of the buffer pool to disk in a checkpoint. Small redo log files cause many unnecessary disk writes. Although historically big redo log files caused lengthy recovery times, recovery is now much faster and you can confidently use large redo log files.

Consider increasing the size of the log buffer. A large log buffer enables large transactions to run without a 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. Log buffer size is configured using the innodb_log_buffer_size configuration option.

這是官方給出的兩條guidelines，大意是redo log越大越好，甚至可以配置到和buffer pool一樣的大小。過小的redo log會帶來許多不必要磁盤寫入。事實上來看，确實redo log files大有許多的好處，帶來的壞處就是系統一旦崩潰，恢複的時間比較長。同時考慮調整innodb_log_buffer_size參數來支援處理大事物的能力。

官方的說法比較模糊，現實中也不可能調整redo log files至 buff pool的大小，我們實際上需要更為精确的計算方法。

選取業務高峰期計算産生的日志大小，redo log 的大小最少應該承載一個小時的業務日志。

一個小時的日志量是1500M，預設innodb_log_files_in_group =2，則一個innodb_log_file_size =750M。我們一般取2的整數倍，是以

innodb_log_files_in_group =2

innodb_log_file_size =1024M

對于寫入很頻繁的資料庫，設定比較大redo log file 是一個比較明智的措施。

網上有很多說法可以去計算 Innodb_os_log_written（ Innodb_os_log_written is the number of bytes written to the redo logs）的大小，實際上 Innodb_os_log_written一般都是lsn的幾倍，建議還是使用lsn計算，具體資訊可以參考連結：

<a href="https://www.percona.com/blog/2012/10/08/measuring-the-amount-of-writes-in-innodb-redo-logs/">link</a>

有個有趣的現象innodb_buffer_pool_instances這個值比較大時，實際上會産生比較多的log，這時可以調高logsize大小。

參考連結：

<a href="https://www.percona.com/blog/2012/10/08/mysql-5-6-7-rc-in-tpcc-mysql-benchmark/">link</a>