Basic Details
Innodb_log_file_size dictates the size in bytes of the commit log files stored by MySQL.
Remember that these log files will protect database data during power loss events or crashes. For servers, with write-intensive workloads, the configuration of innodb_log_file_size is very important to performance. But in most cases, your innodb_log_file_size does not need to be larger than your buffer pool.
Releem automatically tunes innodb_log_file_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
Remember that these log files will protect database data during power loss events or crashes. For servers, with write-intensive workloads, the configuration of innodb_log_file_size is very important to performance. But in most cases, your innodb_log_file_size does not need to be larger than your buffer pool.
Releem automatically tunes innodb_log_file_size and 44 other variables to improve MySQL performance. Try Releem for Free, or deepen your understanding by reading our detailed article.
innodb_log_file_size – Usage
If you need to adjust innodb_log_file_size you can do so with one of the following methods:
In Linux you enter, setting the bolded value to any value that meets your requirements:
$>mysql –innodb_log_file_size=64M
You can also change the value of innodb_log_file_size using a configuration file. To do so, you will use mysqld:
[mysqld]
innodb_log_file_size=64M
Again, you can adjust the bolded value to suit the needs of your servers.
In Linux you enter, setting the bolded value to any value that meets your requirements:
$>mysql –innodb_log_file_size=64M
You can also change the value of innodb_log_file_size using a configuration file. To do so, you will use mysqld:
[mysqld]
innodb_log_file_size=64M
Again, you can adjust the bolded value to suit the needs of your servers.
When should innodb_log_file_size be changed?
Ideally, innodb_log_file_size should be to a high enough value to accommodate an increase in server workload activity and optimize write I/O, while not being so large as to inhibit a speedy crash recovery. When recovering from a crash, you can expect roughly 5 minutes per 1GB of innodb_log_file_size. Meaning if your innodb_log_file_size is 3GB, your MySQL server would fully recover in about 15 minutes.
There is an associated variable, innodb_log_files_in_group, but this variable has a default value that is not often changed.
When changing the innodb_log_file_size parameter in MySQL versions befor 5.7, be sure to shut down the server first and move the log files. This will force the server to create new log files. If this is not done, an error concerning log file size mismatch will be thrown:
ERROR 1033 (HY000): Incorrect information in file....
There is an associated variable, innodb_log_files_in_group, but this variable has a default value that is not often changed.
When changing the innodb_log_file_size parameter in MySQL versions befor 5.7, be sure to shut down the server first and move the log files. This will force the server to create new log files. If this is not done, an error concerning log file size mismatch will be thrown:
ERROR 1033 (HY000): Incorrect information in file....