Skip navigation links
MySQL 5.4 Reference Manual :: 13 Storage Engines :: 13.6 The InnoDB Storage Engine
13.6.1 InnoDB Contact Information »
Section Navigation      [Toggle]

13.6. The InnoDB Storage Engine

[+/-]

13.6.1. InnoDB Contact Information
13.6.2. InnoDB Configuration
13.6.3. InnoDB Startup Options and System Variables
13.6.4. Creating and Using InnoDB Tables
13.6.5. Adding, Removing, or Resizing InnoDB Data and Log Files
13.6.6. Backing Up and Recovering an InnoDB Database
13.6.7. Moving an InnoDB Database to Another Machine
13.6.8. The InnoDB Transaction Model and Locking
13.6.9. InnoDB Multi-Versioning
13.6.10. InnoDB Table and Index Structures
13.6.11. InnoDB Disk I/O and File Space Management
13.6.12. InnoDB Error Handling
13.6.13. InnoDB Performance Tuning and Troubleshooting
13.6.14. Restrictions on InnoDB Tables

InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement.

To determine whether your server supports InnoDB use the SHOW ENGINES statement. See Section 12.4.5.17, “SHOW ENGINES Syntax”.

Table 13.3. InnoDB Storage Engine Features

Storage limits 64TB Transactions Yes Locking granularity Row
MVCC Yes Geospatial datatype support Yes Geospatial indexing support No
B-tree indexes Yes Hash indexes No Full-text search indexes No
Clustered indexes Yes Data caches Yes Index caches Yes
Compressed data Yes[a] Encrypted data[b] Yes Cluster database support No
Replication support[c] Yes Foreign key support Yes Backup / point-in-time recovery[d] Yes
Query cache support Yes Update statistics for data dictionary Yes    

[a] Compressed InnoDB tables are supported only by InnoDB Plugin.

[b] Implemented in the server (via encryption functions), rather than in the storage engine.

[c] Implemented in the server, rather than in the storage product

[d] Implemented in the server, rather than in the storage product

InnoDB has been designed for maximum performance when processing large data volumes. Its CPU efficiency is probably not matched by any other disk-based relational database engine.

The InnoDB storage engine maintains its own buffer pool for caching data and indexes in main memory. InnoDB stores its tables and indexes in a tablespace, which may consist of several files (or raw disk partitions). This is different from, for example, MyISAM tables where each table is stored using separate files. InnoDB tables can be very large even on operating systems where file size is limited to 2GB.

The Windows Essentials installer makes InnoDB the MySQL default storage engine on Windows, if the server being installed supports InnoDB.

InnoDB is used in production at numerous large database sites requiring high performance. The famous Internet news site Slashdot.org runs on InnoDB. Mytrix, Inc. stores more than 1TB of data in InnoDB, and another site handles an average load of 800 inserts/updates per second in InnoDB.

InnoDB is published under the same GNU GPL License Version 2 (of June 1991) as MySQL. For more information on MySQL licensing, see http://www.mysql.com/company/legal/licensing/.

Additional Resources

  • A forum dedicated to the InnoDB storage engine is available at http://forums.mysql.com/list.php?22.

  • Innobase Oy also hosts several forums, available at http://forums.innodb.com.

  • At the 2008 MySQL User Conference, Innobase announced availability of an InnoDB Plugin for MySQL. This plugin for MySQL exploits the “pluggable storage engine” architecture of MySQL. As of MySQL 5.4.2, the InnoDB Plugin is included in MySQL 5.4 releases. It becomes the built-in version of InnoDB in MySQL Server, replacing the version previously included as the built-in InnoDB engine. The current version of the InnoDB Plugin is 1.0.5 and is considered of Release Candidate (RC) quality.

    The InnoDB Plugin offers new features, improved performance and scalability, enhanced reliability and new capabilities for flexibility and ease of use. Among the features of the InnoDB Plugin are “Fast index creation,” table and index compression, file format management, new INFORMATION_SCHEMA tables, capacity tuning, multiple background I/O threads, and group commit.

    For information about these features, see the InnoDB Plugin Manual at http://www.innodb.com/products/innodb_plugin/plugin-documentation. For general information about using InnoDB in MySQL, see Section 13.6, “The InnoDB Storage Engine”.

    For a specific list of changes in MySQL 5.4.2 compared to earlier 5.4.x releases, see Section C.1.2, “Changes in MySQL 5.4.2 (01 September 2009)”.

  • InnoDB Hot Backup enables you to back up a running MySQL database, including InnoDB and MyISAM tables, with minimal disruption to operations while producing a consistent snapshot of the database. When InnoDB Hot Backup is copying InnoDB tables, reads and writes to both InnoDB and MyISAM tables can continue. During the copying of MyISAM tables, reads (but not writes) to those tables are permitted. In addition, InnoDB Hot Backup supports creating compressed backup files, and performing backups of subsets of InnoDB tables. In conjunction with MySQL’s binary log, users can perform point-in-time recovery. InnoDB Hot Backup is commercially licensed by Innobase Oy. For a more complete description of InnoDB Hot Backup, see http://www.innodb.com/products/hot-backup/features/ or download the documentation from http://www.innodb.com/doc/hot_backup/manual.html. You can order trial, term, and perpetual licenses from Innobase at http://www.innodb.com/wp/products/hot-backup/order/.