As you know, during commit InnoDB writes the data at once into data files and records changes in the first innodb_log_file. The fact that write data directly to the table – a much more expensive operation than to record changes in the binary log.
Keeping innodb_log_file allows optimization of I/O: write data to successive large pieces, and more quickly serve customers (customer commits quickly made, and the data in the table space are recorded in the background). Therefore, the larger the file, the more opportunities to optimize InnoDB I/O. Currently, the total size of innodb_log_file limited to 4 GB, which is more than enough for most cases.
At the start of an unexpected shutdown MySQL looks innodb_log_file, rolling away a transaction that did not have time to finish before the crash and noting commits that had (and have been completely recorded in innodb_log_file). And of course, the larger the file, the longer it takes the server to view it. How do we determine the optimal size? Follow these instructions during the most intense load of your server:
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)
Note the number of log sequence. This total number of bytes written to the log. So that we can know how many MB per minute was recorded. (These instructions will work with any version of MySQL, starting with 5.0, you can use the metric of innodb_os_log_written SHOW GLOBAL STATUS.)
mysql> SELECT (3,838,334,638 - 3836410803) / 1024/1024 AS MB_per_min; + ------------ + | MB_per_min | + ------------ + | 1.83471203 | + ------------ +
A good rule is to install such a size that it can fit about an hour logs. Then InnoDB can very effectively plan records in data files, and at the same time it is a good kompross for speed run. We rounded up to 128 MB and as the default file install two innodb_log_file_size = 64M.
Is it too little? Maybe. Often I see the size of transaction logs in a few gigabytes, but it’s usually a mistake. The server I used for measurements – more and do a lot of work, it’s not a toy for tests. The size of log files is not necessary to leave in 5 MB by default, but often do not need to install them as big as you might think.
If you are taking advantage of this parvilom poluchuli figure in several gigabayn, then obviously you’re very active in the database. In this case, you can try to set the size smaller to minimize recovery time. But note: the recovery time depends not only on the size of the log tranztsaktsy, but also on the number of entries in it. If you have a lot of big transactions, you can set the size of a little more. Conversely, if you have many small transactions, is set smaller size.
thanks, http://blog-ru.greenmice.info/2009/02/innodblogfilesize.html