14.1.2 InnoDB as the Default MySQL Storage Engine

MySQL has a well-earned reputation for being easy-to-use and delivering performance and scalability. In previous versions of MySQL, MyISAM was the default storage engine. In our experience, most users never changed the default settings. With MySQL 5.5, InnoDB becomes the default storage engine. Again, we expect most users will not change the default settings. But, because of InnoDB, the default settings deliver the benefits users expect from their RDBMS: ACID Transactions, Referential Integrity, and Crash Recovery. Let's explore how using InnoDB tables improves your life as a MySQL user, DBA, or developer.

Trends in Storage Engine Usage

In the first years of MySQL growth, early web-based applications didn't push the limits of concurrency and availability. In recent years, hard drive and memory capacity and the performance/price ratio have all gone through the roof. Users pushing the performance boundaries of MySQL care a lot about reliability and crash recovery. MySQL databases are big, busy, robust, distributed, and important.

InnoDB addresses these top user priorities. The trend of storage engine usage has shifted in favor of the more scalable InnoDB. Thus MySQL 5.5 is the logical transition release to make InnoDB the default storage engine.

Consequences of InnoDB as Default MySQL Storage Engine

Starting from MySQL 5.5.5, the default storage engine for new tables is InnoDB. This change applies to newly created tables that don't specify a storage engine with a clause such as ENGINE=MyISAM. Given this change of default behavior, MySQL 5.5 might be a logical point to evaluate whether your tables that do use MyISAM could benefit from switching to InnoDB.

The mysql and information_schema databases, that implement some of the MySQL internals, still use MyISAM. In particular, you cannot switch the grant tables to use InnoDB.

Benefits of InnoDB Tables

If you use MyISAM tables but aren't tied to them for technical reasons, you'll find many things more convenient when you use InnoDB tables in MySQL 5.5:

Best Practices for InnoDB Tables

If you have been using InnoDB for a long time, you already know about features like transactions and foreign keys. If not, read about them throughout this chapter. To make a long story short:

Recent Improvements for InnoDB Tables (from the Plugin Era)

Testing and Benchmarking with InnoDB as Default Storage Engine

Even before completing your upgrade to MySQL 5.5, you can preview whether your database server or application works correctly with InnoDB as the default storage engine. To set up InnoDB as the default storage engine with an earlier MySQL release, either specify on the command line --default-storage-engine=InnoDB, or add to your my.cnf file default-storage-engine=innodb in the [mysqld] section, then restart the server.

Since changing the default storage engine only affects new tables as they are created, run all your application installation and setup steps to confirm that everything installs properly. Then exercise all the application features to make sure all the data loading, editing, and querying features work. If a table relies on some MyISAM-specific feature, you'll receive an error; add the ENGINE=MyISAM clause to the CREATE TABLE statement to avoid the error (for example, tables that rely on full-text search must be MyISAM tables rather than InnoDB ones).

If you did not make a deliberate decision about the storage engine, and you just want to preview how certain tables work when they're created under InnoDB, issue the command ALTER TABLE table_name ENGINE=InnoDB; for each table. Or, to run test queries and other statements without disturbing the original table, make a copy like so:

CREATE TABLE InnoDB_Table (...) ENGINE=InnoDB AS SELECT * FROM MyISAM_Table;
        

Since there are so many performance enhancements in the InnoDB that is part of MySQL 5.5, to get a true idea of the performance with a full application under a realistic workload, install the real MySQL 5.5 and run benchmarks.

Test the full application lifecycle, from installation, through heavy usage, and server restart. Kill the server process while the database is busy to simulate a power failure, and verify that the data is recovered successfully when you restart the server.

Test any replication configurations, especially if you use different MySQL versions and options on the master and the slaves.

Verifying that InnoDB is the Default Storage Engine

To know what the status of InnoDB is, whether you're doing what-if testing with an older MySQL or comprehensive testing with MySQL 5.5: