How to calculate a good InnoDB log file size

Peter wrote a post a while ago about choosing a good InnoDB log file size. Ânot to pick in Peter, but the post actually kind of talks about a lot of things and then doesn ' t tell you how to choose A good log file size! Âso I thought I ' d clarify it a little.

The basic point is this your log file needs to being 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 does choose that size? I'll show you a rule of thumb to that works pretty well.

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

Mysql>Pager grep SequencepagerSet  to 'grep sequence'MySQL>Show Engine InnoDB Status\gSelectSleep -); Show Engine InnoDB status\gLogSequence Number  - 38364108031Rowinch Set(0.06sec)1Rowinch Set(1 min 0.00sec)LogSequence Number  - 38383346381Rowinch Set(0.05Sec

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 has 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 (see here) from SHOW GLOBAL STATUS, too.)

Mysql> Select(3838334638 - 3836410803)/ 1024x768 / 1024x768  asmb_per_min;+------------+|Mb_per_min|+------------+| 1.83471203 |+------------+

As a rough the rule of thumb, you can make the log big enough that it can hold at a hour or so of logs. That ' s generally plenty of the data for InnoDB and work with; An hour ' s worth are more than enough so it can reorder the writes-use sequential I/O during the flushing and CHECKP Ointing process. At the this rate, the this server could use is about a logs, total. Round it up to good measure. Since There is the log files by default, divide the half, and now you can set

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 are a big one doing a lot of work and 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 the your log file size ought to be many gigabytes More active write workload. Perhaps you ' re inserting a lot of big rows or something. 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 isn't 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 would fit into a given log file size, which'll generally mak e recovery faster than you might expect with a big log.

However, most of the time if I run this calculation, I-end up finding that the log file size needs to is a lot small Er than it ' s configured to be. In part that's because InnoDB ' s log entries is very compact. The other reason is, 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, and judgment and experience are needed. But the "rule of thumb" is generally a good sane place to start.

Reference:

http://www.percona.com/blog/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

How to calculate a good InnoDB log file size