An InnoDB tutorial

Is InnoDB enabled in my server?

The SQL command SHOW ENGINES will return a list of known engines and tell you if they are enabled and operational or not. If the engine is included in your server and operational it is shown with "YES". If it is included, but not operational, "DISABLED" is shown. If it is not even part of your servers codebase, a "NO" is shown.

An InnoDB minimum configuration

All other examples in this tutorial assume that you MySQL server has operational InnoDB support. The minimum configuration shown below is not suitable for production, but should be sufficient to go from DISABLED to YES.

• Use SHOW GLOBAL VARIABLES LIKE 'datadir' to locate the data directory of your MySQL instance. Within this directory there may be two files ib_logfile0 and ib_logfile1 and one ibdata1. If not, no problem - they will be created in the next steps.
  
  
  CODE:
  root on mysql.sock [(none)]> show global variables like 'datadir';
  +---------------+------------------------------+
  | Variable_name | Value                        |
  +---------------+------------------------------+
  | datadir       | /export/data/rootforum/data/ |
  +---------------+------------------------------+
  1 row in set (0.00 sec)
  root on mysql.sock [(none)]> quit
  Bye
  linux:/export/data/rootforum # ls -lh /export/data/rootforum/data/ib*
  -rw-rw---- 1 mysql mysql   5M Jan  9 17:51 /export/data/rootforum/data/ib_logfile0
  -rw-rw---- 1 mysql mysql   5M Jan  9 17:51 /export/data/rootforum/data/ib_logfile1
  -rw-rw---- 1 mysql mysql  10M Dec 13 14:34 /export/data/rootforum/data/ibdata1
  
  
  
• Shut down the server.
• Delete the above three files if they are present in your server. WARNING! This will delete all data stored in InnoDB tables in your server. Do this only if your engine was shown as DISABLED before.
• Find your servers my.cnf or my.ini file and look for the configuration statement skip-innodb in the [mysqld] section of your server. If it is in there, remove it.
• In place of skip-innodb put in the following configuration statements. They will be explained later.
  CODE:
  innodb
  innodb_file_per_table = 1
  innodb_flush_log_at_trx_commit = 2
  This is just a minimal setup for testing and is not suitable for performance. See the rest of the tutorial for more informastion on performance.
• Restart your server. It will create new ibdata1, ib_logfile0 an ib_logfile1 files and put appropriate messages in your log. After the server completes startup, SHOW ENGINES lists InnoDB as "YES".
• If this does not work, connect a Freenode IRC server and join the channel #mysql. Ask for help in there.

Creating InnoDB tables

When writing a CREATE TABLE statement you may add an ENGINE clause at the end of the statement. This clause determines which storage engine will be used for this particular table. It can be different for each and every table in your server. The engine used for a table can be changed later at any time using the ALTER TABLE statement without data loss.


You can use SHOW CREATE TABLE or SHOW TABLE STATUS to see what storage engine is being used for a table.

The output of the above SHOW TABLE STATUS will show you a few "kinks" specific to InnoDB. For example, the average row length shown is way off for tables with few records. We know that the average row length in our table is approximately 4 (integer) + 5 (1 length byte + 4 characters) plus some administrative overhead (10-12 byte per row). InnoDB is managing space in pages of 16K, though, and consequently Data_length is shown as 16384. The Average_row_length is not a statistic maintained by InnoDB and so MySQL fakes it by calculating Data_length/Rows. For small numbers of rows this is a bad estimate.

We also see that the Index_length for out table is 0, even if we have defined a primary key. We will address this later in more detail. For the moment it is enough that the primary key in InnoDB is part of the data, and also somewhat magical.

Data_free in InnoDB is always 0, unlike MyISAM.

Changing existing tables to InnoDB

Use the ALTER TABLE command to change an existing table from InnoDB to MyISAM or back. Data will not be lost.

Here for reference the output of SHOW TABLE STATUS for the same table being MyISAM and InnoDB. We can see how MyISAM counts the primary key as an Index and not data, how the MyISAM index is page based, but with smaller pages (1K instead of 16K) and how the data is not page based.

We may change our table back to InnoDB. We even can change an existing InnoDB table to InnoDB again. And this is even useful - it is what OPTIMIZE TABLE does when you apply it to InnoDB tables.

Using ALTER TABLE t ENGINE=... will create a copy of the original table in the new engine as a temporary table. Then the original table will be dropped and the temporary table will we installed in its place. This has a number of consequences:

• The operation cannot lose data. If it fails or is stopped by an operator the temporary table will be dropped and the original table is unchanged.
• At some point in time both versions of the table exist at once. This may use a lot of storage. Note that InnoDB does not pack data as tightly as MyISAM does - it will use 1.6 to 2.2 times the space that MyISAM uses..
• During the ALTER TABLE both tables will be locked. You can avoid that with a sequence of
  • CREATE TABLE b LIKE a;
  • ALTER TABLE b ENGINE=InnoDB
  • INSERT INTO b SELECT * FROM a;
  This will put only a read lock on a for the duration of the copy process. Also, you may use a LIMIT clause on your INSERT ... SELECT to create only a limited subset of data for experimentation.