Fast, Reliable, Proven transactional storage for MySQL

InnoDBFeatures

InnoDB is the leading transactional storage engine for MySQL, and provides enterprise-class features for transaction processing applications. InnoDB provides storage management, transactions and concurrency control, support for referential integrity and crash recovery, and incorporates a number of unique capabilities to maximize performance. It has a range of features for high-performance transaction processing including mutli-version concurrency control with row-level locking. InnoDB supports referential integrity, automatic recovery, and automatic maintenance of indexes. It uses several innovative techniques such as automatic hash indexes and insert buffering to maximize the efficient use of memory, cpu and disk i/o.

Click “Read more” below to see further information on InnoDB’s features for the following areas:

  • Transactions and Locking
  • Logging, Checkpointing and Recovery
  • Backups
  • Memory management and I/O
  • File space management
  • Indexing
  • Referential Integrity
  • InnoDB Tables and SQL Support
  • Performance monitoring
  • Source code, architecture and footprint
  • Supported platforms

Read more

Transactions and Locking:
  • full “ACID” transaction support: Atomicity, Consistency, Isolation, Durability for concurrently executing transactions
  • multi-version read consistency; readers automatically see data consistent with the time a query begins without setting locks, thus not delaying updates. Multiversioning is achieved through internally managed rollback segments that contain undo information, as in Oracle.
  • row level locking without limits; locks fit in a very small space so no lock escalation to page or table-level locking is needed;
  • next-key locking prevents so-called phantoms (thus no rows will appear to a second query that did not appear in the first) and transactions are serializable (meaning they execute as if there were no other concurrently executing transactions)
  • all SQL-1992 isolation levels: READ UNCOMMITTED,READ COMMITTED, REPEATABLE READ, and SERIALIZABLE;
  • SQL transactional control: COMMIT, ROLLBACK and SAVEPOINT
  • table level locks on demand
  • automatic deadlock detection and rollback of “smallest” transaction
  • parallel rollback
  • lock monitor helps diagnose application locking problems
  • efficient semaphore, mutex and latch management to reduce contention
Logging, Checkpointing and Recovery:
  • circular log files (similar to Oracle)
  • supports long-running transactions, since undo information (needed for rollback) is stored in the database (as in Oracle)
  • full automatic roll-forward recovery after system crashes; no data lost for committed transactions
  • rollforward from a database backup
  • supports XA protocol for distributed transaction management
  • asynchronous, fuzzy checkpoint
  • log mirroring (not available under MySQL)
Backups:
  • online (“hot”) backups, via InnoDB Hot Backup, producing consistent backups without disruption to online update transactions
  • support for backing up MyISAM tables and indexes (via innobackup Perl script
Memory management and I/O:
  • asynchronous i/o
  • direct i/o, bypassing o/s buffers for better performance and memory use
  • inserts to secondary indexes are buffered and later merged in batches to index nodes, reducing random i/o, speeding inserts
  • doublewrite i/o avoids partially written pages on disk for reliability
  • automatic sequential read-ahead
  • Inserts to secondary indexes buffered in main memory if the index blocks are not in memory, later merged in batches, saving i/o
  • semaphore contention reduced by several methods
File space management:
  • tables and indexes are stored in files managed by InnoDB, in tablespaces (like Oracle’s)
  • the InnoDB system tablespace can store multiple tables and indexes, in one or more o/s files
  • alternatively, each table and its indexes can be stored in a separate o/s file
  • efficient management of space within tablespaces, similar to UFS
  • maximum tablespace size over 2 billion pages, 36TB of 16 KB pages
Indexing:
  • data organized for fast primary key retrieval, with “clustered indexes” (also called “index-organized tables”); data rows are stored in the primary key B-tree index
  • automatic maintenance of primary and secondary B-tree indexes
  • unique and non-unique indexes
  • automatic, adaptive hash indexing
Referential Integrity:
  • foreign key constraint enforcement via indexes
  • ON DELETE CASCADE, RESTRICT, or SET NULL
  • ON UPDATE CASCADE, RESTRICT, or SET NULL
InnoDB Tables and SQL Support:
  • supports full MySQL SQL language, including triggers and stored procedures
  • supports all MySQL datatypes,
  • supports AUTO-INCREMENT
  • unlimited number of tables
  • up to 1000 columns per table
  • up to 8000 bytes per rows (not including BLOBs)
  • unlimited number of rows, total table size limited to tablespace size
Performance monitoring:
  • InnoDB can output detailed information of the database state, aiding performance tuning
Source code, architecture and footprint:
  • Approximately 140,000 lines of ISO/IEC 9899:1990 (C90) C code
  • OS-dependent code is isolated in a directory with some 7,000 lines of C
  • machine-dependent code isolated to a directory with some 1,300 lines of C
  • modularly written, comprising around 50 compilation modules
  • 800 kB executable on Intel x86
  • fully multi-threaded, single o/s process
  • multiple concurrent user threads and background system threads
Supported platforms:
  • Windows (32- and 64-bit)
  • Linux: x86 generic, AMD64/Intel EM64T/Intel IA64 generic,Red Hat Enterprise Linux, SuSE Linux Enterprise Server
  • Solaris
  • FreeBSD
  • Mac OS X
  • HP-UX
  • IBM AIX, i5/OS

See the list of available downloads from the MySQL website.

InnoDB Plugin

The InnoDB Plugin adds significant new features for performance, scalability, flexibility, reliability and ease of use. It can replace the “built-in” InnoDB in the MySQL server as distributed by Sun/MySQL. See this page for more information.

InnoDB and MySQL

The MySQL database server architecture supports the use of pluggable storage engines to manage data storage and coordinate data access among multiple users. MySQL itself provides interprets and optimizes SQL language requests, manages data security, and handles communication with users.

Click “Read more” below for some details on the functions performed by MySQL and those performed by InnoDB.

Read more

The division of functions between MySQL and InnoDB is as follows.

Functions performed by MySQL:

  • handles most of the portability code for different platforms
  • does all communication with the client or application
  • takes care of access privileges of users
  • parses and optimizes the SQL statements
  • executes SQL code by requesting that InnoDB perform low-level operations like search a row from an index, or insert a row to a table
  • handles replication
  • keeps a logical log (called the “binlog”) of the SQL statements executed in the database, used for recovery and replication

Functions performed by InnoDB:

  • does transaction management: row-level locking, multi-versioning of data to provide consistent reads, commit, rollback, savepoints, deadlock detection
  • manages a main memory buffer pool, where it caches data and index pages from disk
  • performs crash recovery from its own logs
  • builds and maintains indexes on InnoDB tables
  • manages storage for InnoDB tables and indexes
  • InnoDB has an internal SQL interpreter. which is not accessible to users, since MySQL uses its own SQL interpreter

The big picture is that MySQL is the upper level in the database server, and InnoDB is a low-level module which the upper level uses. Using MySQL with InnoDB implies using about 130,000 lines of InnoDB source code and 400,000 lines of MySQL source code.