Search code examples
mysqlmariadbinnodb

Problem improving MySQL. (innodb_log_file_size)

we are trying to improve the efficiency of our database server.

One of the recommendations of MySQLTunner is rising the innodb_log_file_size to 12 GB. As we see this change could improve significantly the speed and the performance of our queries. The problem came when we increase this parameter more than 1 GB, the service wont start, we delete the logs, stop it cleanly, and still cant start with this parameter above 1 GB.

Someinfo:

mysql  Ver 14.14 Distrib 5.5.62, for debian-linux-gnu

innodb_buffer_pool_size = 100G 
innodb_file_per_table   = ON
innodb_buffer_pool_instances = 64 
innodb_stats_on_metadata = OFF
innodb_log_file_size = 1G 
innodb_log_buffer_size  = 8M

Theres algo enough space in partitions for keeping this log size

Thanks!

Solution

  • In MySQL 5.5, you can't increase the innodb log file size over 4GB total. The innodb_log_file_size can only be 4GB / innodb_log_files_in_group (which is 2 by default, and there's no benefit to change that). So you can set the log file to a max of 2GB.

    See https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_log_file_size

    The max combined size of the log files increased to 512GB in 5.6.3. Again, innodb_log_file_size should be the size of one log file, so if you use multiple log files, the total cannot exceed 512GB.

    I agree with Rick James' answer that increasing the log file size is not a magical solution to make queries run faster. It won't do that.

    It's sometimes useful to increase the innodb log file size, if the bottleneck is that you run out of log space faster than dirty pages can be flushed to the tablespace, because you have very high write traffic. That's affected by the rate of writes, not the speed of individual writes.

    For most apps, two 2GB log files is more than enough. If it isn't, it's probably time to run multiple MySQL instances, and distribute your write traffic over them as evenly as you can.