- 14 Storage Engines
- 14.1 Comparing Transaction and Nontransaction Engines
- 14.2 Other Storage Engines
- 14.3 Setting the Storage Engine
- 14.4 Overview of MySQL Storage Engine Architecture
- 14.5 The MyISAM Storage Engine
- 14.6 The InnoDB Storage Engine
- 14.6.1 Configuring InnoDB
- 14.6.2 InnoDB Startup Options and System Variables
- 14.6.3 Creating and Using InnoDB Tables
- 14.6.4 Adding, Removing, or Resizing InnoDB Data and Log Files
- 14.6.5 Backing Up and Recovering an InnoDB Database
- 14.6.6 Moving or Copying InnoDB Tables to Another Machine
- 14.6.7 The InnoDB Transaction Model and Locking
- 14.6.8 InnoDB Multi-Versioning
- 14.6.9 InnoDB Table and Index Structures
- 14.6.10 InnoDB Disk I/O and File Space Management
- 14.6.11 InnoDB Error Handling
- 14.6.12 InnoDB Performance Tuning and Troubleshooting
- 14.6.13 Limits on InnoDB Tables
- 14.7 The IBMDB2I Storage Engine
- 14.8 The MERGE Storage Engine
- 14.9 The MEMORY Storage Engine
- 14.10 The EXAMPLE Storage Engine
- 14.11 The FEDERATED Storage Engine
- 14.12 The ARCHIVE Storage Engine
- 14.13 The CSV Storage Engine
- 14.14 The BLACKHOLE Storage Engine
[+/-]
- 14.6.1. Configuring
InnoDB - 14.6.2.
InnoDBStartup Options and System Variables - 14.6.3. Creating and Using
InnoDBTables - 14.6.4. Adding, Removing, or Resizing
InnoDBData and Log Files - 14.6.5. Backing Up and Recovering an
InnoDBDatabase - 14.6.6. Moving or Copying
InnoDBTables to Another Machine - 14.6.7. The
InnoDBTransaction Model and Locking - 14.6.8.
InnoDBMulti-Versioning - 14.6.9.
InnoDBTable and Index Structures - 14.6.10.
InnoDBDisk I/O and File Space Management - 14.6.11.
InnoDBError Handling - 14.6.12.
InnoDBPerformance Tuning and Troubleshooting - 14.6.13. Limits on
InnoDBTables
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 13.7.5.17, “SHOW ENGINES Syntax”.
Table 14.4. InnoDB Storage Engine
Features
| 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.
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
InnoDBstorage engine is available at http://forums.mysql.com/list.php?22.At the 2008 MySQL User Conference, Innobase announced availability of an
InnoDBPlugin for MySQL. This plugin for MySQL exploits the “pluggable storage engine” architecture of MySQL, to permit users to replace the “built-in” version ofInnoDBin MySQL 5.1.As of MySQL 5.1.38, the
InnoDB Pluginis included in MySQL 5.1 releases, in addition to the built-in version ofInnoDBthat has been included in previous releases. MySQL 5.1.42 through 5.1.45 includeInnoDB Plugin1.0.6, which is considered of Release Candidate (RC) quality. MySQL 5.1.46 and up includeInnoDB Plugin1.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.The
InnoDB Pluginoffers new features, improved performance and scalability, enhanced reliability and new capabilities for flexibility and ease of use. Among the features of theInnoDB Pluginare “Fast index creation,” table and index compression, file format management, newINFORMATION_SCHEMAtables, capacity tuning, multiple background I/O threads, and group commit.For information about these features, see InnoDB Plugin 1.0 for MySQL 5.1 User’s Guide.
The
InnoDB Pluginis 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
InnoDBwithInnoDB Plugin, see Section 14.6.1.1, “UsingInnoDB PluginInstead of the Built-InInnoDB”.The MySQL Enterprise Backup product lets you back up a running MySQL database, including
InnoDBandMyISAMtables, with minimal disruption to operations while producing a consistent snapshot of the database. When MySQL Enterprise Backup is copyingInnoDBtables, reads and writes to bothInnoDBandMyISAMtables can continue. During the copying ofMyISAMand 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 ofInnoDBtables. 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 subscription.For a more complete description of MySQL Enterprise Backup, see MySQL Enterprise Backup User's Guide (Version 3.8.1).
User Comments
Add your own comment.