Databases Reference
In-Depth Information
InnoDB in depth to learn as much as you can about it, rather than studying all storage
engines equally.
InnoDB's history
InnoDB has a complex release history, but it's very helpful to understand it. In 2008,
the so-called InnoDB plugin was released for MySQL 5.1. This was the next generation
of InnoDB created by Oracle, which at that time owned InnoDB but not MySQL. For
various reasons that are great to discuss over beers, MySQL continued shipping the
older version of InnoDB, compiled into the server. But you could disable this and install
the newer, better-performing, more scalable InnoDB plugin if you wished. Eventually,
Oracle acquired Sun Microsystems and thus MySQL, and removed the older codebase,
replacing it with the “plugin” by default in MySQL 5.5. (Yes, this means that now the
“plugin” is actually compiled in, not installed as a plugin. Old terminology dies hard.)
The modern version of InnoDB, introduced as the InnoDB plugin in MySQL 5.1, sports
new features such as building indexes by sorting, the ability to drop and add indexes
without rebuilding the whole table, and a new storage format that offers compression,
a new way to store large values such as
BLOB
columns, and file format management.
Many people who use MySQL 5.1 don't use the plugin, sometimes because they aren't
aware of it. If you're using MySQL 5.1, please ensure that you're using the InnoDB
plugin. It's much better than the older version of InnoDB.
InnoDB is such an important engine that many people and companies have invested
in developing it, not just Oracle's team. Notable contributions have come from Google,
Yasufumi Kinoshita, Percona, and Facebook, among others. Some of these improve-
ments have been included into the official InnoDB source code, and many others have
been reimplemented in slightly different ways by the InnoDB team. In general,
InnoDB's development has accelerated greatly in the last few years, with major im-
provements to instrumentation, scalability, configurability, performance, features, and
support for Windows, among other notable items. MySQL 5.6 lab previews and mile-
stone releases include a remarkable palette of new features for InnoDB, too.
Oracle is investing tremendous resources in improving InnoDB performance, and doing
a great job of it (a considerable amount of external contribution has helped with this,
too). In the second edition of this topic, we noted that InnoDB failed pretty miserably
beyond four CPU cores. It now scales well to 24 CPU cores, and arguably up to 32 or
even more cores depending on the scenario. Many improvements are slated for the
upcoming 5.6 release, but there are still opportunities for enhancement.
InnoDB overview
InnoDB stores its data in a series of one or more data files that are collectively known
as a
tablespace
. A tablespace is essentially a black box that InnoDB manages all by itself.
In MySQL 4.1 and newer versions, InnoDB can store each table's data and indexes in
