MySQL 5.5: InnoDB as Default Storage Engine
By Calvin Sun on Sep 19, 2010
Note: this article was originally published on http://blogs.innodb.com on Sept 19, 2010 by Calvin Sun.
MySQL has a well-earned reputation for being easy-to-use and delivering performance and scalability. In previous versions, 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. Lets explore how using InnoDB tables improves your life as a MySQL user, DBA, or developer.
In the first years of MySQL growth, early web-based applications didn’t push the limits of concurrency and availability. In 2010, 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 hits the sweet spot of these top user priorities. The trend of storage engine usage has shifted in favor of the more efficient InnoDB. With MySQL 5.5, the time is right to make InnoDB the default storage engine.
The Big Change
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
(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.)
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:
- If your server crashes because of a hardware or software issue, regardless of what was happening in the database at the time, you don’t need to do anything special after restarting the database. InnoDB automatically finalizes any changes that were committed before the time of the crash, and undoes any changes that were in process but not committed. Just restart and continue where you left off.
- The InnoDB buffer pool caches table and index data as the data is accessed. Frequently used data is processed directly from memory. This cache applies to so many types of information and speeds up processing so much that dedicated database servers assign most of their physical memory to it.
- If you split up related data into different tables, you can set up foreign keys that enforce referential integrity. Update or delete data, and the related data in other tables is updated or deleted automatically. Try to insert data into a secondary table without corresponding data in the primary table, and it gets kicked out automatically.
- If data becomes corrupted on disk or in memory, a checksum mechanism alerts you to the bogus data before you use it.
- When you do a little advance planning to decide on primary key
columns for each table, lots of operations involving those columns are
automatically optimized. It is very fast to reference the primary key
GROUP BYclauses, and join operations.
- Inserts, updates, deletes are optimized by an automatic mechanism called change buffering. InnoDB not only allows concurrent read and write access to the same table, it caches changed data to streamline disk I/O.
- Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive Hash Index takes over to make these lookups even faster, as if they came out of a hash table.
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 in the MySQL Reference Manual. To make a long story short:
- Specify a primary key for every table using the most frequently queried column or columns, or an auto-increment value if there isn’t an obvious primary key.
- Embrace the idea of joins, where data is pulled from multiple tables based on identical ID values from those tables. For fast join performance, define foreign keys on the join columns, and declare those columns with the same datatype in each table. The foreign keys also propagate deletes or updates to all affected tables, and prevent insertion of data in a child table if the corresponding IDs are not present in the parent table.
- Turn off autocommit. Committing hundreds of times a second puts a cap on performance (limited by the write speed of your storage device).
- Bracket sets of related changes, “logical units of work”, with
COMMITstatements. While you don’t want to commit too often, you also don’t want to issue huge batches of
DELETEstatements that run for hours without committing.
- Stop using
LOCK TABLEstatements. InnoDB can handle multiple sessions all reading and writing to the same table at once, without sacrificing reliability or high performance.
- Enable the
innodb_file_per_tableoption to put the data and indexes for individual tables into separate files, instead of in a single giant system tablespace. (This setting is required to use some of the other features, such as table compression and fast truncation.)
- Evaluate whether your data and access patterns benefit from the new InnoDB table compression feature (
CREATE TABLEstatement. You can compress InnoDB tables without sacrificing read/write capability.
- Run your server with the option
--sql_mode=NO_ENGINE_SUBSTITUTIONto prevent tables being created with a different storage engine if there is an issue with the one specified in the
Recent Improvements for InnoDB Tables (from the Plugin Era)
If you have experience with InnoDB, but not the recent incarnation known as the InnoDB Plugin, read about those new features in the MySQL Reference Manual too. To make a long story short:
- You can compress tables and associated indexes.
- You can create and drop indexes with much less performance or availability impact than before.
- Truncating a table is very fast, and can free up disk space for the operating system to reuse, rather than freeing up space within the system tablespace that only InnoDB could reuse.
- The storage layout for table data is more efficient for BLOBs and long text fields.
- You can monitor the internal workings of the storage engine by querying
- You can monitor the performance details of the storage engine by querying
- There are many many performance improvements.
Performance Improvements for InnoDB Tables
- Crash recovery, the automatic process that makes all data consistent when the database is restarted, is fast and reliable. (Now much much faster than long-time InnoDB users are used to.) The bigger the database, the more dramatic the speedup.
- Most new performance features are automatic, or at most require setting a value for a configuration option. Read details here. Advanced users can review the InnoDB configuration options.
- Review the InnoDB-specific tuning techniques.
Testing and Benchmarking
It’s never too early to prepare for InnoDB as the default storage
engine. You can preview whether your database server or application
works correctly with MySQL 5.1 or even earlier. To set up InnoDB as the
default storage engine, either specify on the command line
--default-storage-engine=InnoDB, or add to your
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
statement to avoid the error. (For example, tables that rely on
full-text search must be MyISAM tables rather than InnoDB ones.)
If you didn’t 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’s part of MySQL 5.5, to get a true idea of the performance with a full application under a realistic workload, you’ll want to install the real MySQL 5.5 and run benchmarks. (Those who use the InnoDB Plugin with MySQL 5.1 have already seen some of these improvements, but to keep things simple, this article focuses on 5.5, where the latest and greatest InnoDB is fully integrated.)
Test the full application lifecycle, from installation, through heavy usage, and server restart. (For bonus points, 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, because some of the InnoDB options have different defaults than in previous releases.
To know what the status of InnoDB is, whether you’re doing what-if testing with an older MySQL or comprehensive testing with the real MySQL 5.5:
- Issue the command
SHOW VARIABLES LIKE 'have_innodb';to confirm that InnoDB is available at all. If the result is
NO, you have a mysqld binary that was compiled without InnoDB support and you need to get a different one. If the result is
DISABLED, go back through your startup options and configuration file and get rid of any
- Issue the command
SHOW ENGINES;to see all the different MySQL storage engines. You want to see
DEFAULTin the InnoDB line.