Questions tagged [innodb]

InnoDB is the ACID-compliant Storage Engine used in MySQL. InnoDB also features the use for MVCC (Multiversion Concurrency Control) to support Transaction Isolation Levels for InnoDB. InnoDB is not a standalone database product. It has been distributed as a part of the MySQL database during its early years of InnoBase Oy as a partner with MySQL AB.

In the early days of MySQL, InnoDB was made available to MySQL as an additional transactional storage along with BDB. The company that developed InnoDB, InnoBase Oy, was purchased by Oracle in October 2005. Percona has contributed great improvements to its own Open Source version of InnoDB (XtraDB). Oracle, who eventually became the owner of MySQL via purchasing Sun, has incorporated most of those changes into InnoDB, as well adding improvements of their own. As a result, InnoDB has transformed into a more mature storage engine that handles mulitprocessing and multithreading more robustly. As of December 2010, InnoDB has become the default storage engine for MySQL 5.5.

MySQL 5.5 also has enhancements to facilitate InnoDB in engaging multiple CPUs. Those enhancements were introduced in MySQL 5.1.38 in the InnoDB Plugin only. Those enhancements have now been included with MySQL 5.5.

MySQL 5.5 also comes with new features such as Semisynchronous Replication, Multiple InnoDB Buffers Pools, plugins for user-defined authentication, performance metrics instrumentation, and more !!!

In a recent Oracle press release, one of the new features for MySQL 5.6 is to have InnoDB with FULLTEXT searching. This will be a major step forward for this storage engine as this was one of the most requested and sought after features.

The basic infrastructure of InnoDB centers around three major files

• ibdata1
• ib_logfile0
• ib_logfile1

In conjunction with memory structures, ibdata1 processes info for 6 basic data structures

• Table Data Pages
• Table Index Pages
• Table MetaData (List of Tablespave IDs + Misc Info)
• MVCC Records
  • Rollback Segments
  • Undo Space
• Double Write Buffer (Allows Background Page Writes)
• Insert Buffer (For Collecting/Processing Changes to Secondary Indexes)

Configurations can accommodate

• Separating Table Data and Index Pages
• Storing ibdata1 in a Raw Disk Partition
• Creating Multiple ibdata Files
• Creating multiple Log Files
• and more...

There is important cache known as the InnoDB Buffer Pool. As of MySQL 5.5, you can configure multiple buffer pool instances. Prior to MySQL 5.5, there is only one buffer pool instance.