InnoDB Storage Engine

Introduction

InnoDB is a transactional and ACID compliant MySQL storage engine. It also provides support for row-level locking and is ideal multi-concurrent environments with frequent writes. This section analises in detail the InnoDB storage engine and its configuration.

This page is part of the MySQL administration series:

Configure the Storage Engine

A storage engine manages how the table information is organised, stored and accessed.

MySql uses a pluggable storage engine architecture. MySQL ships with multiple storage engines, such as MyISAM, InnoDB, MEMORY or CSV.

You can change the default storage engine in the MySQL configuration file. InnoDB is the default storage engine after MySQL 6.5. Before that, it was MyISAM.

When you create a table, you can specify the storage engine by adding the ENGINE option to the CREATE TABLE:

You can also convert a table from one storage engine to another using ALTER TABLE:

You can also set the default storage engine for the current session:

InnoDB Tablespace Management

The tablespace is a logical group of one or more files in a database.

The InnoDB tablespace can be configured in two ways:

  • A shared tablespace for all tables
  • A per-table tablespace (default since MySQL 5.6.6)

The innodb_file_per_table property controls the type of InnoDB tablespace.

Shared Tablespace

When innodb_file_per_table=0, InnoDB stores metadata, data and indexes in the shared ibdata files that make up the shared tablespace.

As ibdata files can grow very large, it is common to partition them into several files. The first file is usually called ibdata1. Subsequent files are called ibdata2… By default the ibdata files are located in the data directory.

One feature of the shared ibdata files is that they never shrink, even when we drop or truncate tables. This means the OS can never reclaim unused disk space. When space is released within the tablespace, this is allocated to the internal free space pool, and re-used on demand when additional data is inserted.

Per-Table Tablespace

When innodb_file_per_table=1, InnoDB stores the data and indexes for each table in a separate .IBD file, rather than in the shared tablespace. Unlike shared ibdata files, per-table IBD files can shrink and release disk space when tables are truncated or defragmented.

Per-table mode still uses a shared ibdata file to store metadata.  The shared  ibdata file is however much smaller and manageable.

InnoDB Data Files

Configure the ibdata files

ibdata files are located in the data directory by default.

The following configures two shared ibdata files in the data directory. The first file is a gigabyte in size, and the second is configured to start at a gigabyte but grow in size as needed.

The innodb_data_home_dir can alternatively be used to specify an alternative location.

A third option is to locate each ibdata file in a separate directory. This is useful, for example, when configuring a multi-hard drive setup:

When using per-table tablespace, the innodb file will be much smaller than when using shared tablespace. This is because the ibdata file only contains metadata. The following configuration should be sufficient:

Reduce the size of ibdata files

The shared ibdata file is used to store data and indexes for all databases. One issue with this setup is that MySQL never releases disk space, so the size of the ibdata file never decreases.

These are the steps you need to do to reduce the size of your ibdata files:

  • Back up all databases (logical backup)

  • Shut down mysqld
  • Move all contents from the data directory into a backup location. This includes the existing ibdata files, ib_logfile log files, and .FRM table definition files (binary backup)
  • Change the ibdata file size in the my.ini configuration file. For example:

To roll back the previous version, just shut down mysqld, change the InnoDB configuration, and copy to the original location the old ibdata files, ib_logfile files and the .FRM table files.

Moving from shared tablespace to per-table tablespace

The steps to move from a shared tablespace to a per-table tablespace is similar to reduce the size of the  ibdata files:

  • Back up all databases (logical backup)

  • Shut down mysqld
  • Move all contents from the data directory into a backup location. This will include the existing ibdata files, ib_logfile log files, and .FRM table definition files (binary backup)
  • Change the InnoDB configuration in the my.ini configuration file:

Initialise Data Directory

These are the instructions to initialise the data directory. You might want to initialise the data directory for several reasons. For example, you might want to install a new MySQL instance, or you might want to shrink the size of a shared InnoDB data file. The process to initialise the data directory is different in Windows and Unix.

Windows

To initialise the data directory in Windows, you need to copy the template data directory provided in the MySQL installation.

These steps assume that MySQL is installed in %BASEDIR% and your target data directory is c:\mysql\data

  • Create the data directory by copying the template data directory from the MySQL installation directory

  • Change the datadir value in the ini configuration file

  • Start MySQL server the usual way
  • Log in using root account and no password

  • Check the number of unprotected accounts
  • Protect the root accounts, and remove any other unnecessary accounts

Unix

In Unix the first step above is different. You create the data directory using the mysql_install_db script.

For more information how to use the mysql_install_db script, refer to the MySQL Manual.

The InnoDB Buffer Pool

The InnoDB Buffer Pool caches data and indexes in memory.

When a data or index page is read from the ibdata files, it is also cached into the InnoDB Buffer Pool. When cached data changes, it is updated in the Buffer Pool and the page that contains it is flagged as dirty. The page will eventually be written to the ibdata files, but for performance reasons, MySQL will delay it for as long as possible.

The page will also be written to the InnoDB Log Buffer. This log buffer is typically flushed to disk at transaction commit. These are the redo logs, usually named ib_logfile0 and ib_logfile1.

If MySQL crashes, the InnoDB Pool Cache will be lost given it is an in-memory cache. To recover lost data, MySQL will read the InnoDB Logs files and restores all pages that were flagged as dirty. This recovery happens automatically at start up after a crash

As a rule of thumb, you should make the Buffer Pool as large as possible while still leaving enough memory for the MySQL and other processes running in your system. The innodb_buffer_pool_size is one of the most important parameters that can be fine-tuned.

InnoDB Buffer Pool Cache

Server Crash-Recovery

If MySQL doesn’t shutdown cleanly, the InnoDB storage engine will try to recover automatically next time it starts up. It will try to ensure all InnoDB tables are in a consistent state. In a nutshell, MySQL will complete transactions in the redo logs not yet flushed to disk. Similarly, it will roll back transactions not yet committed.

If it can’t, it will shutdown the server with an appropriate error message.

It is important that the innodb_flush_log_at_trx_commit is set to the default value 1 to maintain full ACID compliance.  This property determines how frequently the log buffer is flushed to disk. With value 1 it is guaranteed that after a transaction commit log files are flushed to disk.

InnoDB Common Configuration Variables

VariablePurpose
innodb_file_per_tableDetermines whether to use a shared or per-table tablespace.

The default is 1 (per-table tablespace)

innodb_data_file_pathDetermins the path, name and size of shared ibdata files.

An example for shared tablespace:

innodb_data_file_path=ibdata1:1024M;ibdata2:1G:autoextend

An example for per-table tablespace:

innodb_data_file_path=ibdata1:256M:autoextend

innodb_data_home_dirThe common directory of all shared ibdata files.

The default is the data directory.

innodb_buffer_pool_sizeThe size of the InnoDB buffer that caches both data and indexes.

The larger this value, the less I/O to access the same data in tables more than once.

innodb_flush_log_at_

trx_commit

Determines how frequently the log buffer is flushed to disk. This property controls balance between strict ACID and performance.

The default is 1 for full ACID compliance.

With value 0, the contents of the log buffer are written to disk approximately once every second.

With value 1, the contents of the log buffer are written out to the log file at each transaction commit, and the log files are flushed to disk.

With value 2, the contents of the log buffer are written out to the log file at each transaction commit, and the log files are flushed to disk approximately once a second.

innodb_log_file_sizeDetermines the size of each InnoDB log file.

The default size is 50MB after MySQL 5.6.8

A larger log file means there is less I/O. It also means if there is a crash the recovery will be slower.  As log files contains changes in the buffer pool that have not yet been written to disk, the total combined size of all log files should not be higher than innodb_buffer_pool_size.

innodb_log_files_in_groupThe number of log files.

Default value is 2.

InnoDB writes to the files in a circular fashion. ib_logfile0 is written first. When full, it then starts writing on ib_logfile1.

Bibliography

The following two tabs change content below.

Eduard Manas

Eduard is a senior IT consultant with over 15 years in the financial sector. He is an experienced developer in Java, C#, Python, Wordpress, Tibco EMS/RV, Oracle, Sybase and MySQL.Outside of work, he likes spending time with family, friends, and watching football.

Latest posts by Eduard Manas (see all)

Leave a Reply