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!
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.
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.
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:
[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.
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:
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'?
