Table of Contents
InnoDB 1.1 combines the familiar reliability and performance of the InnoDB storage engine, with new performance and usability enhancements. InnoDB 1.1 includes all the features that were part of the InnoDB Plugin for MySQL 5.1, plus new features specific to MySQL 5.5 and higher.
Beginning with MySQL version 5.5, InnoDB is the default storage engine, rather than MyISAM, to promote greater data reliability and reducing the chance of corruption.
InnoDB in MySQL 5.5 contains several important new features:
Fast index creation: add or drop indexes without copying the data
Data compression: shrink tables, to significantly reduce storage and I/O
New row format: fully
off-page storage of long BLOB,
TEXT, and VARCHAR
columns
File format management: protects upward and downward compatibility
INFORMATION_SCHEMA
tables: information about compression and locking
Performance and scalability enhancements:
Section 7.12, “Controlling the Flushing Rate of Dirty Pages”
Section 7.13, “Using the PAUSE Instruction in InnoDB Spin Loops”
Section 7.18, “Improvements to Performance from Multiple Buffer Pools”
Section 7.19, “Better Scalability with Multiple Rollback Segments”
Section 7.20, “Better Scalability with Improved Purge Scheduling”
Note that the ability to use data compression and the new row format require the use of a new InnoDB file format called Barracuda. The previous file format, used by the built-in InnoDB in MySQL 5.1 and earlier, is now called Antelope and does not support these features, but does support the other features introduced with the InnoDB storage engine.
The InnoDB storage engine is upward compatible from standard InnoDB as
built in to, and distributed with, MySQL. Existing databases can
be used with the InnoDB Storage Engine for MySQL. The new parameter
innodb_file_format can help
protect upward and downward compatibility between InnoDB versions
and database files, allowing users to enable or disable use of new
features that can only be used with certain versions of InnoDB.
InnoDB since version 5.0.21 has a safety feature that prevents it from opening tables that are in an unknown format. However, the system tablespace may contain references to new-format tables that confuse the built-in InnoDB in MySQL 5.1 and earlier. These references are cleared in a slow shutdown.
With previous versions of InnoDB, no error would be returned until you try to access a table that is in a format “too new” for the software. To provide early feedback, InnoDB 1.1 checks the system tablespace before startup to ensure that the file format used in the database is supported by the storage engine. See Section 4.2.1, “Compatibility Check When InnoDB Is Started” for the details.
Starting with MySQL 5.4.2, you do not need to do anything special to get or install the most up-to-date InnoDB storage engine. From that version forward, the InnoDB storage engine in the server is what was formerly known as the InnoDB Plugin. Earlier versions of MySQL required some extra build and configuration steps to get the Plugin-specific features such as fast index creation and table compression.
Report any bugs in the InnoDB storage engine using the My Oracle Support site. For general discussions about InnoDB Storage Engine for MySQL, see http://forums.mysql.com/list.php?22.
InnoDB storage engine releases are numbered with version numbers independent of MySQL release numbers. The initial release of the InnoDB storage engine is version 1.0, and it is designed to work with MySQL 5.1. Version 1.1 of the InnoDB storage engine is for MySQL 5.5 and up.
The first component of the InnoDB storage engine version number designates a major release level.
The second component corresponds to the MySQL release. The digit 0 corresponds to MySQL 5.1. The digit 1 corresponds to MySQL 5.5.
The third component indicates the specific release of the InnoDB storage engine (at a given major release level and for a specific MySQL release); only bug fixes and minor functional changes are introduced at this level.
Once you have installed the InnoDB storage engine, you can check its version number in three ways:
In the error log, it is printed during startup.
SELECT * FROM information_schema.plugins;
SELECT @@innodb_version;
The InnoDB storage engine writes its version number to the error log, which can be helpful in diagnosis of errors:
091105 12:28:06 InnoDB Plugin 1.0.5 started; log sequence number 46509
Note that the PLUGIN_VERSION column in the
table INFORMATION_SCHEMA.PLUGINS does not
display the third component of the version number, only the first
and second components, as in 1.0.
Because InnoDB 1.1 supports the “Barracuda” file format, with new on-disk data structures within both the database and log files, pay special attention to file format compatibility with respect to the following scenarios:
Downgrading from MySQL 5.5 to the MySQL 5.1 or earlier (without the InnoDB Plugin enabled), or otherwise using earlier versions of MySQL with database files created by MySQL 5.5 and higher.
Using mysqldump.
Using MySQL replication.
Using MySQL Enterprise Backup or InnoDB Hot Backup.
WARNING: Once you create any
tables with the Barracuda file format, take care to avoid crashes
and corruptions when using those files with an earlier version of
MySQL. It is strongly
recommended that you use a “slow shutdown”
(SET GLOBAL innodb_fast_shutdown=0) when
stopping the MySQL server before downgrading to MySQL 5.1 or
earlier. This ensures that the log files and other system
information do not cause consistency issues or startup problems
when using a prior version of MySQL.
WARNING: If you dump a database
containing compressed tables with mysqldump,
the dump file may contain CREATE
TABLE statements that attempt to create compressed
tables, or those using ROW_FORMAT=DYNAMIC in
the new database. Therefore, be sure the new database is running
the InnoDB storage engine, with the proper settings for
innodb_file_format and
innodb_file_per_table, if you
want to have the tables re-created as they exist in the original
database. Typically, when the mysqldump file is
loaded, MySQL and InnoDB ignore CREATE
TABLE options they do not recognize, and the table(s)
are created in a format used by the running server.
WARNING: If you use MySQL
replication, ensure all slaves are configured with the
InnoDB storage engine, with the same settings for
innodb_file_format and
innodb_file_per_table. If you do
not do so, and you create tables that require the new Barracuda
file format, replication errors may occur. If a slave MySQL server
is running an older version of MySQL, it ignores the
CREATE TABLE options to create a
compressed table or one with
ROW_FORMAT=DYNAMIC, and creates the table
uncompressed, with ROW_FORMAT=COMPACT.
WARNING: Version 3.0 of
InnoDB Hot Backup does not support the new Barracuda file format. Using
InnoDB Hot Backup Version 3 to backup databases in this format causes
unpredictable behavior. MySQL Enterprise Backup, the successor product to
InnoDB Hot Backup, does support tables with the Barracuda file format.
You can also back up such databases with
mysqldump.