Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm basically concerned about the following two settings, these are my default settings :

innodb_log_buffer_size  1048576
innodb_log_file_size    5242880

I haven't changed them for 2 reasons : one, I don't know what they are, and two changing this is risky, according to articales I've read

My application data-structure is write-intensive and I use only innodb tables. Currently I've set innodb_buffer_pool_size to 5GB and my database size is around 7GB and increases at the rate of a 400MB per day. Also note, this 400MB insert is done within a period of only about 30 minutes and some might refer to this as "bulk insert"

As far as i understand, log files are for keeping a record of all the insert/update operations, why do I need this?

What are these log files and why should I increase their size?

And lastly, if I don not want to keep a log, how should I fine tune my database then?

share|improve this question

migrated from stackoverflow.com Jun 17 '13 at 14:04

This question came from our site for professional and enthusiast programmers.

2 Answers 2

innodb_log_file_size determines the size of the redo log. In short, this is a temporary storage zone on disk where data changes are buffered before being inserted in the actual table data files. It also stores temporary data that may be generated during the course of a transaction.

The first, most obvious advantage of this buffer is that it mitigates the problem of random writes, which is what would happen otherwise in a typical real-life situation. Writes in the redo log are done sequentially, which is quicker. Randomly located changes may be committed to the actual data tables at a later time, when, hopefully, the activity decreases.

The second advantage is it provides protection against data corruption in case of crash. Since a coherent version of the data (almost) always exists, either in the data table, or in the redo log, the risk of data corruption is lowered, since it should alwys be possible to restore the database to a coherent state. Other mechanisms exist on top of this one for the same purpose (eg. double-buffering)

As the manual puts it:

Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of 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 also make crash recovery slower.

innodb_log_buffer_size defines the size of the memory buffer where data is stored before being flushed to the redo log.

The larger the better, but make sure to leave enough memory for other buffers. Also, I do not think there is a point in allocating more than the total redo log size (ie. innodb_log_file_size x innodb_log_files_in_group.

Books the size of a dictionary can be written about database tuning, this cannot be addressed here. This manual page is a good starting point. If you have identified a specific bottleneck, please come back with relevant information and I (we) will be happy to help you.

share|improve this answer
+1, but one thing that would make this answer better is to give a slightly fuller description of the redo log so that it would answer the OP's question As far as i understand, log files are for keeping a record of all the insert/update operations, why do I need this? –  Derek Downey Jun 17 '13 at 14:08
1 more question, if i allocate say 2GBs to log_file_size , will it affect the memory usage in any way? or just file storage spacE? –  Peeyush Kushwaha Jun 17 '13 at 14:18
Thanks Derek Downey, I have detailed the role of the redo log. @PeeyushKushwaha log_file_size won't have an impact on memory usage, the redo log is stored on the disk (in innodb_data_home_dir). innodb_log_buffer_size will. –  RandomSeed Jun 18 '13 at 10:02
@PeeyushKushwaha Oh and in case you want to resize your redo log, please make sure to follow this official procedure, and not the dubious shortcuts that you may stumble upon elsewhere. –  RandomSeed Jun 18 '13 at 10:16

Please Look at this Pictorial Representation of the InnoDB Architecture. Click Here to Get it as a PDF.


Notice the place of the log buffer and the redo logs. The log buffer queues up transactional information before flush to the redo logs. However, you would want to delay these writes to reduce disk I/O with proper tuning of the Logs and the Log Buffer. You may also want to SET autocommit = 0 in your session to speed up writing transactions.

See My Post on Tuning InnoDB : How to tune mysql to be inmemory-like.

I discuss how to tune

Just to keep things in perspective, data changes are actually written in the Double Write Buffer inside ibdata1 (the system tablespace) to bypass caching it in the OS.

You may not need to allocate 2GB for log files. In fact, you cannot do so because MySQL's InnoDB does not allow the log files to total 4GB. The highest value allowed for innodb_log_file_size is 2047M. Notwithstanding, this may be too high depending on the write activity. How can you determine the best size for innodb_log_file ?

Rather than reinventing the wheel, I wrote two posts that explain carefully how to measure the the amount of writes to the logs (as set by the change in the Status Variable Innodb_os_log_written) and sizing the logs in bytes per hour:

Here is an excerpt on the code to do just that during your write peaks

SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;

Use half of the @MB_WL_HR to set the log file size. Please refer to my other post How to safely change MySQL innodb variable 'innodb_log_file_size'? in order to safely resize the logs and not lose any transactional data.

CAVEAT : If the @GB_WL_HR is greater than 4GB, then you must do one of two things:

  • Set innodb_log_file_size to 2047M
  • Switch to Percona Server. It allows for innodb_log_file_size to go to 2T (2 Terabytes)
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.