InnoDB Website

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 an alternative 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.

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
• group commit of transactions
• supports XA protocol for distributed transaction management
• asynchronous, fuzzy checkpoint
• log mirroring (not available under MySQL)
• asynchronous archiving (not available through MySQL which uses its own binlog)

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:

• automatically managed buffer pool using the buddy algorithm, to avoid memory fragmentation.
• asynchronous i/o
• direct i/o, bypassing o/s buffers for better performance and memory use
• 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
• space within tablespaces managed as in Unix Fast File System
• database page sizes configurable, from 4 to 64 kB, default 16 kB
• maximum tablespace size 4 billion pages.

Indexing:

• automatic maintenance of primary and secondary B-tree indexes
• data organized for fast primary key retrieval, with “clustering indexes” (also called “index-organized tables”); data rows are stored in the primary key B-tree index
• unique and nonunique 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
• up to 1000 columns per table
• up to 8000 bytes per rows (not including BLOBs)
• unlimited number of rows, no limit on total table size

Performance monitoring:

• InnoDB can output detailed information of the database state, aiding performance tuning

Source code, architecture and footprint:

• 140,000 lines of ANSI C
• OS-dependent code is isolated in a directory with some 6,000 lines of C
• machine-dependent code isolated to a directory with some 500 lines of C
• modularly written, comprising around 50 compilation modules
• 800 kB executable on Intel x86
• multithreaded, tested on a 10-processor SMP machine

Supported platforms:

• Microsoft Windows NT/2000/XP
• GNU/Linux: Intel x86, Compaq Alpha, IBM/Motorola PowerPC, IBM zSeries
• Apple Mac OS X PowerPC
• Sun Solaris: SPARC, Intel x86
• Hewlett-Packard HP-UX PA-RISC
• IBM AIX RS6000
• SCO Intel x86
• FreeBSD Intel x86