Skip navigation links
MySQL 5.0 Reference Manual :: 13 Storage Engines :: 13.2 The InnoDB Storage Engine
13.2.1 InnoDB Contact Information »
Section Navigation      [Toggle]

13.2. The InnoDB Storage Engine

[+/-]

13.2.1. InnoDB Contact Information
13.2.2. InnoDB Configuration
13.2.3. InnoDB Startup Options and System Variables
13.2.4. Creating and Using InnoDB Tables
13.2.5. Adding, Removing, or Resizing InnoDB Data and Log Files
13.2.6. Backing Up and Recovering an InnoDB Database
13.2.7. Moving an InnoDB Database to Another Machine
13.2.8. The InnoDB Transaction Model and Locking
13.2.9. InnoDB Multi-Versioning
13.2.10. InnoDB Table and Index Structures
13.2.11. InnoDB Disk I/O and File Space Management
13.2.12. InnoDB Error Handling
13.2.13. InnoDB Performance Tuning and Troubleshooting
13.2.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.13, “SHOW ENGINES Syntax”.

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.

  • 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/.