Instant InnoDB

InnoDB is more than a fast disk-based relational database engine. It offers, at its core, the following features that separate it from other disk-based engines:

These features are responsible for providing what is known as Referential integrity; a core requirement for enterprise database applications.

InnoDB can be used on all platforms on which MySQL can be installed. These include:

There are also custom ports of MySQL from the open source community for running MySQL on various embedded platforms and non-standard operating systems.

Hardware-wise, MySQL and correspondingly InnoDB, will run on a wide variety of hardware, which at the time of this writing includes:

Keep in mind when installing and configuring InnoDB, depending on the architecture in which it is installed, it will have certain options available and enabled that are not available on all platforms. In addition to the underlying hardware, the operating system will also determine whether certain configuration options are available and the range to which some variables can be set. One of the more decisively important differences to be considered while choosing an operating system for your database server is the manner in which the operating system and underlying filesystem handles write caching and write flushes to the disk storage subsystem. These operating system abilities can cause a dramatic difference in the performance of InnoDB, often to the order of 10 times the concurrency ability. These options will be explained in detail in Chapter 3, Advanced Configuration Parameters.

MySQL should, unless you have a specific need for an older version, be downloaded directly from the MySQL website. Some operating systems maintain the latest version of MySQL in their package repositories while some do not, thus offering outdated and potentially insecure or unstable versions of the database application. Since end users do not have control over the operating system package repositories, and thus cannot upgrade the version of MySQL in the repository, it is professionally recommended that any server destined for production use be installed via the General Availability version of MySQL Community or Enterprise from the MySQL corporation website. The latest downloads can be found at http://www.mysql.com/downloads/mysql/.

As of MySQL 3.23, InnoDB has been included with the MySQL GA releases, and has been the primary choice for transactional engine support since. One of the more visible major changes came about in early versions of MySQL 5.1, when the InnoDB engine was shipped slightly different than before, where it was available as a pluggable storage engine. This new architecture, which allowed users to enable or disable storage engines on the fly, as opposed to compiling in support for different engines, made MySQL very flexible to different use cases. As such, using the plugin version of InnoDB allows the user to enable additional settings that were not available in the default included version of the engine.

As of MySQL 5.5, the default version of InnoDB is also the latest version of the plugin which negates the necessity of installing and enabling the formal plugin directly from InnoBase. From the MySQL website, the specific version differences are defined as follows:

mysql> show engines\G
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+
1 row in set (0.00 sec)

[mysql://root@localhost/(none)]> SHOW GLOBAL VARIABLES LIKE 'tx_isolation';                                                                                                                                           
+---------------+-----------------+                                                                                                                                                                                             
| Variable_name | Value           |                                                                                                                                                                                             
+---------------+-----------------+                                                                                                                                                                                             
| tx_isolation  | REPEATABLE-READ |                                                                                                                                                                                             
+---------------+-----------------+                                                                                                                                                                                             
1 row in set (0.00 sec)

Once InnoDB support has been verified, you can start using the engine in your table definitions. A simple test to interact with InnoDB can be run as follows, where we will create a table with the engine specified explicitly along with a primary key, which InnoDB uses to index data.

If you leave off the engine definition at the end of the table create statement then MySQL will create the table with the system default engine, which is defined by the startup variables: default-storage-engine or, alternately, storage_engine, both accomplish the same purpose.

If you plan to use InnoDB exclusively for your table engine definitions, it generally makes sense to ensure that tables are created, explicitly and implicitly, by changing one or both of those startup variables to InnoDB. As of MySQL Version 5.5, the default has been changed to InnoDB so depending on your version you may not need to explicitly define the variable.

mysql> create database test; use test;
Query OK, 1 row affected (0.00 sec)
Database changed
mysql> CREATE TABLE `test` (
    ->   `id` int(8) NOT NULL auto_increment,
    ->   `data` varchar(255),
    ->   `date` datetime,
    ->  PRIMARY KEY  (`id`),
    ->  INDEX `date_ix` (`date`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)

In the preceding table create statement, we created one table with three columns. These columns are described as follows:

Besides edge cases, these columns can be considered the bare minimum columns for relational data. The automatically incremented id field will allow InnoDB to efficiently index our data or rapid lookups, and the date field will allow us to easily sort and search our data based on the time it was created in the table.

These two columns are specifically important for InnoDB based tables, as other table engines can operate relatively quick and efficient full table scans without a primary key column if running a typical query such as SELECT COUNT(*) FROM test;. However, InnoDB requires a PRIMARY KEY column or other INDEX to read in order for the query results to remain expedient; thus our id column provides that performance gain. Instead of writing the inefficient latter query, we can now write SELECT COUNT(id) FROM test; and InnoDB will access the id column PRIMARY KEY index which results in a very fast result versus a full table scan. Similarly, we can sort our table data via the date column and get quickly-returned table data.

If you'd like to experiment with InnoDB features more easily or see how different versions of MySQL work with InnoDB, you can install a sandboxing tool such as MySQL Sandbox.

At this point you should know the hardware and operating system requirements for installing MySQL, as well as the basic process for downloading and enabling support for the InnoDB engine. We've covered the requirements for ACID compliance and shown you how to set up basic indexing, as well as the process for creating a sample InnoDB table. Next we'll get into the basics of tuning InnoDB for better performance.