Comments on: How to Choose the MySQL innodb_log_file_size

By: Kristian Köhntopp

Kristian Köhntopp — Sun, 15 Jul 2018 11:49:44 +0000

In reply to Fadi El-Eter (itoctopus). Recovery time is governed by the log file size at all, it is governed by the amount of data "in flight", that is, the amount of log file in use. The amount of log file in use is limited by the total size of the log (upper limit), but could be zero (lower limit) on an idle server. So it is important to fill up the log file with heavy write load (or at least, typical write log for this server) and then crash the server. That being said, on our servers we are more likely to see your timing that Peter's timing, but it varies widely. Modern MySQL has gotten quite efficient at Redo Log recovery.

By: Peter Zaitsev

Peter Zaitsev — Sat, 28 Oct 2017 18:26:45 +0000

In reply to Fadi El-Eter (itoctopus). Clean Restart or Restart with crash recovery ?

By: Fadi El-Eter (itoctopus)

Fadi El-Eter (itoctopus) — Mon, 23 Oct 2017 10:26:56 +0000

On a 20 core server with 64 GB of RAM an an SSD drive, it takes exactly 1 minute/1 GB (it takes 4 minutes for the full restart of MySQL, and the total size of the log files is 4 GB)

By: Zafar Malik

Zafar Malik — Fri, 20 Oct 2017 13:08:28 +0000

@Peter: Thanks for this great article. Just adding how to calculate per hour write In case of innodb engine by below command- USER='root';PASS='mypass' #put root password here- a=`mysql -u$USER -p$PASS -e"show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'`;mysql -u$USER -p$PASS -e"select sleep(60);";b=`mysql -u$USER -p$PASS -e"show engine innodb status\G" | grep "Log sequence number" | awk '{print $4}'`;mysql -u$USER -p$PASS -e"SELECT ($b - $a)*60/1024/1024 AS MB_per_hour;" Note: Please share if there is any way to calculate for myisam also.