Last active
October 21, 2019 19:27
-
-
Save utdrmac/6bd75493d738690736d0 to your computer and use it in GitHub Desktop.
Sizing InnoDB Log Files
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment