Sizing InnoDB Log Files

size-innodb-log-files.sh

currentSize=`mysql $DSN -BNe "SELECT ROUND(@@innodb_log_file_size/1024/1024, 0)"` \
currentNum=`mysql $DSN -BNe "SELECT @@innodb_log_files_in_group"` \
totalCurSize=`mysql $DSN -BNe "SELECT ROUND((@@innodb_log_file_size * @@innodb_log_files_in_group)/1024/1024, 2)"` \
startSeq=`mysql $DSN -BNe "SHOW ENGINE InnoDB STATUS\G" | grep sequence | grep -o "[0-9]\+$"`; sleep 60; \
endSeq=`mysql $DSN -BNe "SHOW ENGINE InnoDB STATUS\G" | grep sequence | grep -o "[0-9]\+$"`; \
logSizeReq=`echo "scale=2; ((($endSeq-$startSeq)/1024/1024)*60)/$currentNum" | bc`; \
echo; echo "innodb_log_file_size is ${currentSize}MB * ${currentNum} log files = ${totalCurSize}MB"; \
echo "innodb_log_file_size setting should be at least ${logSizeReq}MB for ${currentNum} log files."; echo;

sizing-innodb-redo.sql

SELECT innodb_os_log_written_per_minute*60 AS estimated_innodb_os_log_written_per_hour, 
       CONCAT(ROUND(innodb_os_log_written_per_minute*60/1024/1024, 1), 'MB') AS estimated_innodb_os_log_written_per_hour_mb
FROM
 (SELECT SUM(value) AS innodb_os_log_written_per_minute FROM (
   SELECT VARIABLE_VALUE AS value
     FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'innodb_os_log_written'
   UNION ALL
   SELECT SLEEP(60) FROM DUAL
   UNION ALL
   SELECT VARIABLE_VALUE AS value
     FROM performance_schema.global_status
     WHERE VARIABLE_NAME = 'innodb_os_log_written'
  ) s1
) s2;