Monday, April 23, 2012

Choosing the ideal InnoDB log file size

I refer to an incredible post by Baron Schwartz where he wittingly explains a technique to choose the best InnoDB log file size. The idea is so monitor the growth rate of the log file for a minute and then adjust the size such that it can hold at most one hour of changes. Let me explain with an example:
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 1028230
1 row in set (0.06 sec)

1 row in set (1 min 0.00 sec)

Log sequence number 84 2028230
1 row in set (0.05 sec) 

The log sequence number is the total number of bytes written in the transaction log. Therefore the delta should indicate the growth rate per second:

mysql> select (2028230 - 1028230) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 0.95367432 |
+------------+

In an hour, the growth will therefore be 57MB, rounded to 64MB. Since we have two logfiles by default, they can be 32MB each. Sounds small, but in reality that is the optimum size for this particular server. Baron mentions a pretty busy server which only requires two logfiles each being 64MB!

Post a Comment