How to calculate a good InnoDB log file size

The basic point is that your log file needs to be big enough to let InnoDB optimize its I/O, but not so big that recovery takes a long time. That much Peter covered really well. But how do you choose that size? I'll show you a rule of thumb that works pretty well.


In most cases, when people give you a formula for choosing a configuration setting, you should look at it with skepticism. But in this case you can calculate a reasonable value, believe it or not. Run these queries at your server's peak usage time:

PLAIN TEXT
SQL:
mysql> pager grep sequence
PAGER SET TO 'grep sequence'
mysql> SHOW engine innodb STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G
Log sequence number 84 3836410803
1 row IN SET (0.06 sec)

1 row IN SET (1 min 0.00 sec)

Log sequence number 84 3838334638
1 row IN SET (0.05 sec)
Notice the log sequence number. That's the total number of bytes written to the transaction log. So, now you can see how many MB have been written to the log in one minute. (The technique I showed here works on all versions of MySQL. In 5.0 and newer, you can just watch Innodb_os_log_written from SHOW GLOBAL STATUS, too.)

PLAIN TEXT
SQL:
mysql> SELECT (3838334638 - 3836410803) / 1024 / 1024 AS MB_per_min;
+------------+
| MB_per_min |
+------------+
| 1.83471203 |
+------------+
As a rough rule of thumb, you can make the log big enough that it can hold at most an hour or so of logs. That's generally plenty of data for InnoDB to work with; an hour's worth is more than enough so that it can reorder the writes to use sequential I/O during the flushing and checkpointing process. At this rate, this server could use about 110 MB of logs, total. Round it up to 128 for good measure. Since there are two log files by default, divide that in half, and now you can set

PLAIN TEXT
CODE:
innodb_log_file_size=64M
Does that look surprisingly small? It might. I commonly see log file sizes in the gigabyte ranges. But that's generally a mistake. The server I used for the measurements above is a big one doing a lot of work, not a toy. Log file sizes can't be left at the default 5MB for any real workload, but they often don't need to be as big as you might think, either.

If this rule-of-thumb calculation ends up showing you that your log file size ought to be many gigabytes, well, you have a more active write workload. Perhaps you're inserting a lot of big rows or something. In this case you might want to make the log smaller so you don't end up with GB of logs. But also realize this: the recovery time depends not only on the total log file size, but the number of entries in it. If you're writing huge entries to the log, fewer log entries will fit into a given log file size, which will generally make recovery faster than you might expect with a big log.

However, most of the time when I run this calculation, I end up finding that the log file size needs to be a lot smaller than it's configured to be. In part that's because InnoDB's log entries are very compact. The other reason is that the common advice to size the logs as a fraction of the buffer pool size is just wrong.

One final note: huge buffer pools or really unusual workloads may require bigger (or smaller!) log sizes. This is where formulas break down and judgment and experience are needed. But this "rule of thumb" is generally a good sane place to start.