Choosing Proper Innodb_log_file_size

If you ' re doing significant amount of writes to Innodb tables decent size of innodb_log_file_size is important fo R MySQL Performance. However setting it too large would increase recovery time, so-in case of MySQL crash or power failure it could take long time Before MySQL Server is operational again.

So what to find the optimal combination?

First let me explain what happens on recovery and why largeinnodb_log_file_sizeSlows down recovery. During startup after crash Innodb scans log files to find log records which only has been applied in memory and does not ex ist in tablespace. Log records for modifications which didn't make it to the tablespace is then applied. This is calledRedo phase of recovery. It can take pretty long time and this time depends on number of variables–how large is rows? (smaller log records mean more records for same sized logs), how random were data modifications (random updates would need Random IO to check if pages is up to date), number of unflushed pages in InnoDB buffer pool and its size as well as Perfo Rmance of IO subsystem. As there-so many factors, it's hard-to-come up with any general guidelines, something-like 1GB per minutes of reco Very time–instead you would need to apply load which are typical for your application, crash MySQL in the middle and WATC H it to recover. Doing this several times you should is able to estimate what long recovery time take and adjust your logs apropriately. The good thing Is–redo phase is close to being proportional to size of log files, so expect 1GB logs to take twice time to Apply compared to 512MB logs.

Redo phase is however only one of the phases of recovery. The other important one is  undo  phase–after Log file was applied and database is in "physically consi Stent "state, Innodb'll need to roll back certain transactions which were not commited, but changes from which already m Ade it to the database. Unlike "Redo" phase "undo" phase can ' t be reduced by sizing your log files. Even more undo phase can is slower with small log files. Undo phase takes considerable time if tranactions is Long–ie if you would delete 10000000 rows in the same transaction And crash in the middle recovery can take quite a long time. The only-to-do can reduce "undo" phase is size your transactions Appropriately–so updates/inserts/deletes can sized To affect limited number of rows.

Good thing about updo phase However is–it can is done with background as in MySQL 5.0. The rows affected by background rollback however might aren't be modified until rollback are complete.

One more thing to consider–how large log files does you need at all? You could run benchmark with 1GB log files and 2GB and see if there are any performance benefit. After certain size increasing log file size does not dramatically increase performance, however this again depends on config Uration and workload.

Note at this poing 4GB are maximum combined size allowed for InnoDB log files, which are however large enough limit for most Onfigurations.

Q:i am a little confused by this line:
Note at this poing 4GB are maximum combined size allowed for INNODB log files

My question is the combined to get this 4GB maximum? Is it Innodb_log_files_in_group * innodb_log_file_size or was there another part of equation I am missing?

A:It is combined size which is limited.

innodb_log_file_size

Command-Line Format	--innodb_log_file_size=#
System Variable	Name	innodb_log_file_size
	Variable Scope	Global
	Dynamic Variable	No
permitted Values (<= 5.6.2)	Type	numeric
	Default	5242880
	Min Value	1048576
	Max Value	4GB / innodb_log_files_in_group
permitted Values (>= 5.6.3, <= 5.6.7)	Type	numeric
	Default	5242880
	Min Value	1048576
	Max Value	512GB / innodb_log_files_in_group
permitted Values (>= 5.6.8)	Type	numeric
	Default	50331648
	Min Value	1048576
	Max Value	512GB / innodb_log_files_in_group

The size in bytes of Each log file in a log Group. The combined size of log files (innodb_log_file_size  *innodb _log_files_in_group ) cannot exceed a maximum value that's slightly less than 512GB. A pair of 255 GB log files, for example, would allow you to approach the limit and not exceed it. The default value is 48MB. Sensible values range from 1MB to 1/ N -th of the size of The buffer pool, WH Ere  N  is The number of the log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. Larger log files A LSO Make crash Recovery slower, although improvements to recovery performance on MySQL 5.5 and higher make the L og file size less of a consideration. For general I/O tuning advice, see section 8.5.7, "Optimizing InnoDB Disk I/o".

Reference:

http://www.percona.com/blog/2006/07/03/choosing-proper-innodb_log_file_size/

Http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_log_file_size

Choosing Proper Innodb_log_file_size