InnoDB logfiles

Oct 1, 2008 / By Sheeri Cabral

Tags:

The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery possible.

Database administrators of other DBMS may be familiar with the concept of a “redo” log. When data is changed, affected data pages are changed in the innodb_buffer_pool. Then, the change is written to the redo log, which in MySQL is the InnoDB logfile (ib_logfile0 and ib_logfile1). The pages are marked as “dirty”, and eventually get flushed and written to disk.

If MySQL crashes, there may be data that is changed that has not been written to disk. Those data pages were marked as “dirty” in the innodb_buffer_pool, but after a MySQL crash the innodb_buffer_pool no longer exists. However, they were written to the redo log. On crash recovery, MySQL can read the redo log (InnoDB log files) and apply any changes that were not written to disk.

That is the basic functionality of the InnoDB log files. Given this, let’s look at some of the different parameters and their ramifications.

innodb_log_files_in_group is set with a default of 2. The logfiles are written in a circular manner — ib_logfile0 is written first, and when it has reached its maximum size, then ib_logfile1 will be written to.

innodb_log_file_size is the size of each log file in the log group. The total, combined size of all the log files has to be less than 4 Gb (according to the MySQL manual). Because the logfiles contain changes in the buffer pool that have not been written to disk, the total, combined size of all the log files should not be more than the innodb_buffer_pool_size.

If all the log files in the group are full of changes that have not been written to disk, MySQL will start to flush dirty pages from the InnoDB buffer pool, writing the changes to disk. If the log files are small, changes will be written to disk more often, which can cause more disk I/O.

When InnoDB does a crash recovery, it reads the log files. If the log files are large, it will take longer to recover from a crash. If innodb_fast_shutdown is set to 0, the log files are purged when MySQL shuts down — larger files mean a longer shutdown time. The default for innodb_fast_shutdown is 1, which means that the log files are not purged before a shutdown. Starting in MySQL 5.0.5, you can set it to 2, which simulates a crash, and at the next startup InnoDB will do a crash recovery.

innodb_flush_log_at_trx_commit controls how often the log files are written to. A value of 0 causes the log files to be written and flushed to disk once per second. The default is 1, which causes the log buffer to be written and flushed to disk after every transaction commit. The value can also be set to 2, which causes the log buffer to be written after every transaction commit and flushes the log files to disk once per second. A value of 2 means that MySQL might think that some changes are written to the log file, but do not persist in the log file after an operating system crash, because the log file was not flushed to disk before a crash.

Note that some filesystems are not honest about flushing to disk, so even though you may have the default value of 1, your system may be acting as if it has a value of 2. Setting this parameter to 2 means that there will be less I/O, at the cost of not being able to recover data from a crash.

innodb_flush_method changes how InnoDB opens and flushes data and log files. See the manual for details; the end result is a tradeoff in I/O performance versus whether or not an operating system crash would leave the InnoDB log files in an inconsistent state.

innodb_log_buffer_size is the write buffer for InnoDB log files. The larger the buffer is, the less often the log files are written to. This can save I/O.

13 Responses to “InnoDB logfiles”

  • Arjen Lentz says:

    “The logfiles are written in a circular manner — ib_logfile0 is written first, and when it has reached its maximum size, then ib_logfile1 will be written to.”

    They’re actually initialised to their full length, so they don’t grow over time.
    The easy way to tell which logfile is currently used and how often it flips over between the two (if # of logfiles = 2) is looking at their mtime on disk.

  • Sheeri Cabral says:

    Arjen — good point. It should read “when it has been filled with changed data, then ib_logfile1 will be written to.”

  • newbie says:

    Hi Sheeri,
    How about the undo log what are they ? Where are they written? Can we see exactly what is the content in the ib_logfile ? Thanks.

  • “Note that some filesystems are not honest about flushing to disk, so even though you may have the default value of 1, your system may be acting as if it has a value of 2. Setting this parameter to 2 means that there will be less I/O, at the cost of not being able to recover data from a crash.”

    The most ‘dis-honest’ filesystems are the ones across a network. The issue is that every software, firmware and hardware layer has physical limitation when it comes to speed, so everyone caches. The DB, the OS, the hard drive, the network card and every one layer tells the rest it’s ready, when actually the data is still in memory.

    Storage manufacturers have been aware of this for a long time, so now you may find that some storage appliances have ‘battery backed up memory’, which means that even if the power goes down, the cache will still be there to be applied for a some period of time. This way, when the hardware gets powered again, it can complete the write. Kind of how the pagers retain the messages for a while to give you time to change the battery.

    Keep this in mind when you choose whether 0, 1 or 2 is the right value for your your “innodb_flush_log_at_trx_commit”. Without forgetting that you’ll have to compromise security vs performance. My $.02.

  • Sheeri Cabral says:

    Newbie:

    According to:
    http://forge.mysql.com/wiki/MySQL_Internals_Files_In_InnoDB_Sources, the undo logs are inside the tablespace.

    And sadly there isn’t an easy way to see what’s in the logfile, though it is possible (I don’t know how, but Heikki can do it!)

  • sandeep says:

    how we can call these files as redo log files?

    i think the best word to describe these files is undo log files ,redo log files are one which we are calling as bin log files because “redo” log files reapplies the changes

  • @Sandeep.

    Using these terms, I’d call the innodb log files the redo logs, and the binary log archive redo logs.

  • Amit says:

    i’m faced with a situation which is not a crash but is as bad. Some wrong action on part of our customer triggered some delete statements that resulted in some undesired data loss (the records from that table and all referenced tables got deleted). All the tables in question are InnoDB tables. Is there a way I can get to see those records (possibly to refill them manually on my own)? Wouldn’t the innodb log files have a record of what was inserted in the tables originally? I’m not sure if these log files just contain the data that’s still to be flushed to disk, or do they even keep some historical data. If it’s the later case, I think I should give reading these files a try. So, next question is how to read them? I’ll appreciate any help/pointers on this.

  • Sheeri Cabral says:

    Amit,

    The InnoDB log files do not contain a lot of historical information.

    What you want is the binary logs — if turned on, they will log every change.

    An even better solution would be to restore the data from a backup — I recommend having a consistent physical backup for disaster recovery (file copy of some kind, whether it’s using XtraBackup, InnoDB Hot Backup, having a slave where MySQL is stopped and the files are copied, LVM, etc) AND having a logical backup, doesn’t need to be consistent, for problems like this.

    (ie, using mysqldump –skip-extended-insert)

    To read the binary logs, use at the mysqlbinlog tool. http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html

  • […] configuration change which can prevent MySQL from starting involves the InnoDB log file size. The InnoDB log file is the ‘redo’ or recovery log for the InnoDB storage engine. It contains transactions […]

  • […] had become corrupted and were not recognised, the engine couldn’t start. These files are the crash recovery logs: The unsung heroes of InnoDB are the logfiles. They are what makes InnoDB automatic crash recovery […]

  • vishnu rao says:

    very nice article – well written -thanks

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>