0

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?

1 Answer 1

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.

4
  • +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? Commented Jun 17, 2013 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? Commented Jun 17, 2013 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
    Commented Jun 18, 2013 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
    Commented Jun 18, 2013 at 10:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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