When reading online about innodb_log_file_size, it says

The combined size of the log files must be less than 4GB

So what happens if it needs to be larger?

In books and online, it says to set the innodb_log_file_size to at least an hour's worth of writes.

Currently I have 2x 1GB log files that when calculating Innodb_os_log_written for 1 hour, needs at least 10GB. 2GB of log files can only do 20 minutes of writes on our peak load.


  • 1
    +1 for this question because most people are unaware of how to deal with this. Jan 17, 2013 at 13:23
  • 1
    MySQL 5.6 increases the total configurable size to 512GB: dev.mysql.com/doc/refman/5.6/en/… ... although, I'll mention that I've heard it said that 1 hour of traffic is the maximum size you want, not the minimum, since larger log files potentially means long crash recovery times (though not to the same extent as in earlier versions). Jan 17, 2013 at 23:22

1 Answer 1


Years ago, I would set innodb_log_file_size to 2047M. This is the biggest number allowed to create transaction log files without surpassing InnoDB's 4GB limit. I believe it still applies to the current version (MySQL 5.5). Keep in mind that InnoDB was designed to work in a 32-bit environment, thus the 4GB limit.


Percona Server binaries actually surpass this limitation. I believe I have seen 4GB as innodb_log_file_size in one installation. That's right, I said a 4GB ib_logfile0 and a 4GB ib_logfile1, ** totaling 8GB**. Here is a current listing from the DB Server of one of my employer's clients:

[root@*** ~]$ ls -l /var/lib/mysql/ib*
-rw-rw---- 1 mysql mysql 2283798528 Jan 16 22:53 /var/lib/mysql/ibdata1
-rw-rw---- 1 mysql mysql 4294967296 Jan 16 22:52 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 4294967296 Jan 16 22:53 /var/lib/mysql/ib_logfile1

When I login to mysql, I see this Welcome message:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 177401833
Server version: 5.1.45-51-log Percona SQL Server (GPL), XtraDB 10

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


So, Percona Server 5.1.45 lets you make huge InnoDB Transaction Logs. SO, I am very sure Percona Server 5.5 (which uses XtraDB) will let you create InnoDB Log File > 2GB.


If you really need log files that big (> 2G), please upgrade to Percona Server with the XtraDB storage engine. It is an excellent drop-in replacement for InnoDB.


The larger the log files, the longer the shutdown process may become. I would recommend the following:


That way, any uncommitted transactions left in ibdata1 get committed upon shutdown rather than upon MySQL's crash recovery during startup.

UPDATE 2013-01-17 11:40 EDT

From your last two comments, your question to me is

What is the downside of having innodb_log_file_size too low for the load - less than 1 hours worth?

I would say frequent checkpoints having to be written to disk. That could make it very annoying for InnoDB to handle MVCC for Transaction Isolation.

You could properly size the InnoDB Log files for whatever transaction rate you peak. I wrote about this before : Proper tuning for 30GB InnoDB table on server with 48GB RAM (under the Section Log File Size). Here is an excerpt from my earlier post:

Log File Size

5MB is the default size for innodb_log_file_size. Percona's mysqlperformanceblog.com gave two good articles on computing the right size for your particular MySQL instance:

Basically, the blog recommends measuring how many bytes are written to the InnoDB Log Files in one hour. This is what I run to figure that out

SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;

Whatever number comes back for @MB_WL_HR, take half of it and resize innodb_log_file_size to it : See my post How to safely change MySQL innodb variable 'innodb_log_file_size'?

  • Thanks, what is the downside of having Innodb_os_log_written too low for the load - less than 1 hours worth?
    – Tom Geee
    Jan 17, 2013 at 16:22
  • sorry I meant to say "innodb_log_file_size" not "Innodb_os_log_written" in the above comment.
    – Tom Geee
    Jan 17, 2013 at 16:30
  • Updated my answer... Jan 17, 2013 at 16:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.