InnoDB From the Inside: ibdata1 and the Log Files

The ibdata1 file is arguably the most important file in the entire InnoDB infrastructure. Without the file InnoDB simply could not function – we will explore why in this post.

What is ibdata1 and Why Is It So Important?

The ibdata1 file is the system tablespace for the InnoDB infrastructure – the file contains vital information for the InnoDB storage engine. Here’s how the infrastructure of InnoDB looks like (note the ibdata1‘s place on the right):

The ibdata1 contains several classes of information vital for InnoDB to function properly.

What is Stored in ibdata1?

Unfortunately, MySQL does not provide information on what is stored in the ibdata1 by default, but we do know certain things. The file contains:

• Table data pages;
• Table index pages;
• The data dictionary;
• Multiversioning Concurrency Control (MVCC) data;
• Undo space;
• Rollback segments;
• The double write buffer;
• The insert buffer.

Here’s what that means:

• A page is a fixed-size unit of storage;
• The InnoDB data dictionary is comprised of internal system tables to keep track of objects such as tables, indexes and columns of a table;
• Multiversioning Concurrency Control ensures that if someone is reading from a database at the same time as someone else is writing to it, the reader will still see a consistent piece of data. The method is commonly used by database management systems to provide concurrent access to a database;
• An undo log is a collection of undo log records – an undo log record contains information about how to undo the latest change by a transaction to a clustered index record;
• A rollback segment is an object of the database used to track undo information;
• The purpose of a double write buffer is to prevent data corruption from partial page writes;
• The InnoDB insert buffer is a standard InnoDB feature – the engine uses this feature to buffer updates to the same page so they can be performed at once and not one after another.

Working with ibdata1 – Proper Configuration

If you have ever worked with MySQL, you might have noticed that the ibdata1 file grows as you insert data into a table with an InnoDB engine. You might have also noticed that MySQL does not provide you with an easy way to make the size of the file smaller – the ibdata1 file cannot be shrunk unless you delete all databases, remove the associated files and start anew.

In order to shrink ibdata1 perform the following steps:

1. Take a backup of your MySQL databases;
2. Drop all of the databases excluding the mysql and performance_schema databases;
3. Stop MySQL;
4. Delete the ibdata1 and ib_logfile* files (these will be recreated on the next restart of MySQL);
5. Start MySQL and restore your database from the dump you took before.

When you start MySQL after performing the steps outlined above, the ibdata1 and ib_logfile* files will be recreated.

Now you’re good to go! When a new database will be created, its tables will be located in separate ibd* files and not in ibdata1 – ibdata1 will still grow, but it will only contain metadata, not the data itself. In the event of the database getting dropped, the ibd* files will be deleted.

What are the Log Files and What Do They Contain?

The log files (ib_logfile0 and ib_logfile1) contain the redo logs. If MySQL crashes, restarting it will perform a read across the ib_logfile0 and ib_logfile1 files and check for any data changes that were not posted to the double write buffer in ibdata1 and redo those changes. Once the changes are replayed and stored, MySQL is ready for new connections.

In other words, MySQL uses logs to achieve data durability – the engine is able to ensure that data would not be lost in the event of a crash or power loss.

Choosing a good InnoDB log file size is the key to a good InnoDB write performance – as a rule of thumb, the innodb_log_buffer_size parameter should be 25% of the value of the innodb_log_file_size.

What about the Recovery Time?

As previously noted, if MySQL crashes, restarting it will perform a scan across both of the ib_logfile0 and ib_logfile1 files. This is the primary reason why setting innodb_log_file_size to a proper value is very important – the log file size should be set as big as possible, but not bigger than it is absolutely necessary. By increasing the log file size you will get performance benefits, but it does have a drawback: the recovery time after a crash.

Setting the innodb_log_file_size to a proper value is crucial to achieve the balance between reasonably good system performance and fast crash recovery time.

To set both of the innodb_buffer_pool_size and innodb_log_file_size to appropriate values, you can follow this approach:

• Figure out how many RAM your system has by running appropriate commands (for example the “free” command);
• Allocate 75% (or less depending on your workload) of the total RAM size to the innodb_buffer_pool_size parameter;
• Allocate 25% of the value allocated to innodb_buffer_pool_size to the ..._log_file_size parameters.

Summary

The ibdata1 and ib_logfile* files are crucial for the proper performance of InnoDB – although in some cases the contingency of these files might cause problems, those problems can usually be swiftly and efficiently resolved.