InnoDBStartup Options and System Variables
InnoDBData and Log Files
InnoDBTables to Another Machine
InnoDBTransaction Model and Locking
InnoDBTable and Index Structures
InnoDBDisk I/O and File Space Management
InnoDBPerformance Tuning and Troubleshooting
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
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.
InnoDB Storage Engine
|Storage limits||64TB||Transactions||Yes||Locking granularity||Row|
|MVCC||Yes||Geospatial data type support||Yes||Geospatial indexing support||No|
|B-tree indexes||Yes||T-tree indexes||No||Hash indexes||No[a]|
|Full-text search indexes||Yes[b]||Clustered indexes||Yes||Data caches||Yes|
|Index caches||Yes||Compressed data||Yes[c]||Encrypted data[d]||Yes|
|Cluster database support||No||Replication support[e]||Yes||Foreign key support||Yes|
|Backup / point-in-time recovery[f]||Yes||Query cache support||Yes||Update statistics for data dictionary||Yes|
[a] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.
[b] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.
[c] Compressed InnoDB tables require the InnoDB Barracuda file format.
[d] Implemented in the server (via encryption functions), rather than in the storage engine.
[e] Implemented in the server, rather than in the storage engine.
[f] Implemented in the server, rather than in the storage engine.
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.
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
InnoDB tables can be very large
even on operating systems where file size is limited to 2GB.
The Windows Essentials installer makes
MySQL default storage engine on Windows, if the server being
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
another site handles an average load of 800 inserts/updates per
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/.
A forum dedicated to the
engine is available at http://forums.mysql.com/list.php?22.
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, to permit users to replace
the “built-in” version of
in MySQL 5.1.
As of MySQL 5.1.38, the
InnoDB Plugin is
included in MySQL 5.1 releases, in addition to the
built-in version of
InnoDB that has been
included in previous releases. MySQL 5.1.42 through 5.1.45
InnoDB Plugin 1.0.6, which is
considered of Release Candidate (RC) quality. MySQL 5.1.46 and
InnoDB Plugin 1.0.7 or higher,
which is considered of General Availability (GA) quality.
Prior to MySQL Cluster NDB 7.1.11, MySQL Cluster was not
compatible with 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
capacity tuning, multiple background I/O threads, and group
For information about these features, see InnoDB Plugin 1.0 for MySQL 5.1 User’s Guide.
InnoDB Plugin is included in source and
binary distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64,
ia64), and generic Linux RPM packages.
For instructions on replacing the built-in version of
see Section 18.104.22.168, “Using
InnoDB Plugin Instead of the Built-In
The MySQL Enterprise Backup product lets you back up a running
MySQL database, including
MyISAM tables, with minimal disruption to
operations while producing a consistent snapshot of the
database. When MySQL Enterprise Backup is copying
InnoDB tables, reads and writes to both
can continue. During the copying of
and other non-InnoDB tables, reads (but not writes) to those
tables are permitted. In addition, MySQL Enterprise Backup can
create compressed backup files, and back up subsets of
InnoDB tables. In conjunction with the MySQL
binary log, you can perform point-in-time recovery. MySQL
Enterprise Backup is included as part of the MySQL Enterprise
For a more complete description of MySQL Enterprise Backup, see MySQL Enterprise Backup User's Guide (Version 3.8.1).