Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

tia!

share|improve this question
    
+1 for this question because most people are unaware of how to deal with this. –  RolandoMySQLDBA Jan 17 '13 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). –  Michael - sqlbot Jan 17 '13 at 23:22

1 Answer 1

up vote 1 down vote accepted

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.

GOOD NEWS

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.

mysql>

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.

RECOMMENDATION

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.

WARNING

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

[mysqld]
innodb_fast_shutdown=0

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;
SELECT @KB_WL_HR,@MB_WL_HR,@GB_WL_HR;

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'?

share|improve this answer
    
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 '13 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 '13 at 16:30
    
Updated my answer... –  RolandoMySQLDBA Jan 17 '13 at 16:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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