InnoDB Plugin 1.0 for MySQL 5.1 (Early Adopter Release) User’s Guide

The unique architecture of MySQL permits multiple storage engines with different capabilities to be accessed via the same SQL language and APIs. Starting with version 5.1, MySQL AB has promoted the idea of a “pluggable” storage engine architecture, which permits multiple storage engines to be added to MySQL. Currently, however, most users have accessed only those storage engines that are distributed by MySQL AB, and are linked into the binary (executable) releases.

Since 2001, MySQL AB has distributed the InnoDB transactional storage engine with its releases (both source and binary). Beginning with MySQL version 5.1, it is possible for users to swap out one version of InnoDB and use another. The pluggable storage engine architecture also permits Innobase Oy to release new versions of InnoDB containing bug fixes and new features independently of the release cycle for MySQL. Users can thus take advantage of these new versions of InnoDB in the context of their deployed MySQL installations.

The InnoDB Plugin for MySQL contains several important new features:

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 versions 5.0 and 5.1 is now called “Antelope” and does not support these features, but does support the other features introduced with the InnoDB Plugin.

The InnoDB Plugin is upward compatible from standard InnoDB as built in to, and distributed with, MySQL. Existing databases can be used with the InnoDB Plugin for MySQL. As described in Section 9.5, “Configuring the InnoDB Plugin”, 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.

The built-in InnoDB in MySQL since version 5.0.21 has a safety feature that prevents it from opening tables that are in an unknown format. However, as noted in Section 11.2, “The Built-in InnoDB, the Plugin and File Formats”, the system tablespace may contain references to new-format tables that will confuse the built-in InnoDB in MySQL. These references will be cleared in a “slow” shutdown of the InnoDB Plugin.

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. Beginning with version 1.0.1 of the InnoDB Plugin, however, to provide early feedback, InnoDB will check the system tablespace to ensure that the file format used in the database is enabled for use before it will start. See Section 4.4.1, “Startup File Format Compatibility Checking” for the details.

From the InnoDB web site, you can download source and binary versions of an upgraded version of InnoDB with additional features beyond the standard InnoDB provided by MySQL in Version 5.1. The InnoDB Plugin for MySQL is licensed under the same license that MySQL uses (GPLv2). It is available at no charge for use and can be freely redistributed, subject to the same requirements of the GPL as is MySQL itself (see the GNU General Public License, version 2). Limited support is available for the InnoDB Plugin for MySQL via http://forums.innodb.com. You may also report bugs in the InnoDB Plugin using the MySQL bug database.

In many environments, the InnoDB plugin can dynamically be added to a MySQL instance without relinking the MySQL server. The plugin version of InnoDB then “takes over” from the statically linked InnoDB that is part of the mysqld binary. In other environments, it is currently necessary to build the entire MySQL server, including the InnoDB plugin, from source code.

On Linux, Unix and Windows, it is a simple matter of installing the InnoDB Plugin for MySQL using the MySQL command INSTALL PLUGIN. When the InnoDB Plugin for MySQL is installed, it replaces the statically-linked version of InnoDB that is incorporated in the MySQL binary as distributed by MySQL AB.

On platforms where the dynamic plugin is not available, users must download the source code for the InnoDB Plugin for MySQL and build MySQL from source. Building from source also facilitates the distribution of a MySQL server where the InnoDB Plugin is already “installed”, so all users of an organization can use the new capabilities without the INSTALL step. The procedure for building from source code is documented in Section 9.4, “Building the InnoDB Plugin from Source Code”.

Full instructions are provided in Chapter 9, Installing the InnoDB Plugin.

InnoDB Plugin releases are numbered with version numbers independent of MySQL release numbers. The initial release of the InnoDB Plugin is version 1.0, and it is designed to work with MySQL 5.1.

Once you have installed the InnoDB Plugin, you can check its version number in three ways:

The InnoDB Plugin 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 the InnoDB Plugin introduces a new file format, with new on-disk data structures within both the database and log files, there are important restrictions on the use of the plugin in typical user environments. Specifically, you should pay special attention to the information presented here about file format compatibility with respect to the following scenarios:

WARNING: Once you use the InnoDB Plugin on a set of database files, care must be taken to avoid crashes and corruptions when using those files with an earlier version of InnoDB, as might happen by opening the database with MySQL when the plugin is not installed. It is strongly recommended that you use a “slow shutdown” (SET GLOBAL innodb_fast_shutdown=0) when stopping the MySQL server when the InnoDB Plugin is enabled. This will ensure log files and other system information written by the plugin will not cause problems when using a prior version of InnoDB. See Section 11.3, “How to Downgrade”.

Because of these considerations, and although it may be useful in certain circumstances to use the plugin in a temporary way as just described, many users will find it preferable to test their application with the plugin and use it on an on-going basis, without reverting back to the standard, built-in InnoDB.

WARNING: If you dump a database containing compressed tables with mysqldump, the dump file may contain CREATE TABLE commands that attempt to create compressed tables, or those using ROW_FORMAT=DYNAMIC in the new database. Therefore, you should be sure the new database is running the InnoDB Plugin, 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, however, when the mysqldump file is loaded, MySQL and InnoDB will ignore CREATE TABLE options they do not recognize, and the table(s) will be created in a format used by the running server.

WARNING: If you use MySQL replication, you should be careful to ensure all slaves are configured with the InnoDB Plugin, 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 the built-in InnoDB, it will ignore the CREATE TABLE options to create a compressed table or one with ROW_FORMAT=DYNAMIC, and create the table uncompressed, with ROW_FORMAT=COMPACT.

WARNING: The current version of InnoDB Hot Backup does not support the new “Barracuda” file format. Using InnoDB Hot Backup Version 3 to backup databases in this format will cause unpredictable behavior. A future version of InnoDB Hot Backup will support databases used with the InnoDB Plugin. As an alternative, you may back up such databases with mysqldump.

In MySQL versions up to 5.0, adding or dropping an index on a table with existing data can be very slow if the table has many rows. The CREATE INDEX and DROP INDEX commands work by creating a new, empty table defined with the requested set of indexes. It then copies the existing rows to the new table one-by-one, updating the indexes as it goes. Inserting entries into the indexes in this fashion, where the key values are not sorted, requires random access to the index nodes, and is far from optimal. After all rows from the original table are copied, the old table is dropped and the copy is renamed with the name of the original table.

Beginning with version 5.1, MySQL allows a storage engine to create or drop indexes without copying the contents of the entire table. The standard built-in InnoDB in MySQL version 5.1, however, does not take advantage of this capability. With the InnoDB Plugin, however, users can in most cases add and drop indexes much more efficiently than with prior releases.

In InnoDB, the rows of a table are stored in a clustered (or primary key) index, forming what some database systems call an “index-organized table”. Changing the clustered index requires copying the data, even with the InnoDB Plugin. However, adding or dropping a secondary index with the InnoDB Plugin is much faster, since it does not involve copying the data.

This new mechanism also means that you can generally speed the overall process of creating and loading an indexed table by creating the table with only the clustered index, and adding the secondary indexes after the data is loaded.

No syntax changes are required in the CREATE INDEX or DROP INDEX commands. However, there are some considerations of which you should be aware (see Section 2.6, “Limitations”).

Because the ability to create and drop indexes does not require use of a new on-disk file format, it is possible to temporarily use the InnoDB Plugin to create or drop an index, and then fall back to using the standard built-in InnoDB in MySQL for normal operations if you wish. See Chapter 11, Downgrading from the InnoDB Plugin for more information.

It is possible to create multiple indexes on a table with one ALTER TABLE command. This is relatively efficient, because the clustered index of the table needs to be scanned only once (although the data is sorted separately for each new index). For example:

CREATE TABLE T1(A INT PRIMARY KEY, B INT, C CHAR(1)) ENGINE=InnoDB;
INSERT INTO T1 VALUES
(1,2,'a'), (2,3,'b'), (3,2,'c'), (4,3,'d'), (5,2,'e');
COMMIT;
ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);

The above commands will create table T1 with the clustered index (primary key) on column A, insert several rows, and then build two new indexes on columns B and C. If there were many rows inserted into T1 before the ALTER TABLE command, this approach would be much more efficient than creating the table with all its indexes before loading the data.

You may also create the indexes one at a time, but then the clustered index of the table is scanned (as well as sorted) once for each CREATE INDEX command. Thus, the following commands are not as efficient as the ALTER TABLE command above, even though neither requires recreating the clustered index for table T1.

CREATE INDEX B ON T1 (B);
CREATE UNIQUE INDEX C ON T1 (C);

Dropping indexes in the InnoDB Plugin does not require any copying of table data. Thus, you can equally quickly drop multiple indexes with a single ALTER TABLE command or multiple DROP INDEX commands:

ALTER TABLE T1 DROP INDEX B, DROP INDEX C;

or

DROP INDEX B ON T1;
DROP INDEX C ON T1;

Restructuring the clustered index in InnoDB always requires copying the data in the table. For example, if you create a table without a primary key, InnoDB chooses one for you, which may be the first UNIQUE key defined on NOT NULL columns, or a system-generated key. Defining a PRIMARY KEY later causes the data to be copied, as in the following example:

CREATE TABLE T2 (A INT, B INT) ENGINE=InnoDB;
INSERT INTO T2 VALUES (NULL, 1);
ALTER TABLE T2 ADD PRIMARY KEY (B);

Note that when you create a UNIQUE or PRIMARY KEY index, InnoDB must do some extra work. For UNIQUE indexes, InnoDB checks that the table contains no duplicate values for the key. For a PRIMARY KEY index, InnoDB also checks that none of the PRIMARY KEY columns contains a NULL. It is best to define the primary key when you create a table, so you need not rebuild the table later.

InnoDB has two types of indexes: the clustered index and secondary indexes. Since the clustered index contains the data values in its B-tree nodes, adding or dropping a clustered index does involve copying the data, and creating a new copy of the table. A secondary index, however, contains only the index key and the value of the primary key. This type of index may be created or dropped without copying the data in the clustered index. Furthermore, because the secondary index contains the values of the primary key (used to access the clustered index when needed), when you change the definition of the primary key, thus recreating the clustered index, all secondary indexes are recreated as well.

Dropping a secondary index is simple. Only the internal InnoDB system tables and the MySQL data dictionary tables need to be updated to reflect the fact that the index no longer exists. InnoDB returns the storage used for the index to the tablespace that contained it, so that new indexes or additional table rows may use the space.

To add a secondary index to an existing table, InnoDB scans the table, and sorts the rows using memory buffers and temporary files in order by the value(s) of the secondary index key column(s). The B-tree is then built in key-value order, which is more efficient than inserting rows into an index in random order with respect to the key values. Because the B-tree nodes are split when they fill, building the index in this way results in a higher fill-factor for the index, making it more efficient for subsequent access.

While a secondary index is being created or dropped, the table is locked in shared mode. That is, any writes to the table are blocked, but the data in the table may be read. When you alter the clustered index of a table, however, the table is locked in exclusive mode, because the data must be copied. Thus, during the creation of a new clustered index, all operations on the table are blocked.

Before it can start executing, a CREATE INDEX or ALTER TABLE command must always wait for currently executing transactions that are accessing the table to commit or rollback before it can proceed. In addition, ALTER TABLE commands that create a new clustered index must wait for all SELECT statements that access the table to complete (or their containing transactions to commit). Even though the original index exists throughout the creation of the new clustered index, no transactions whose execution spans the creation of the index can be accessing the table, because the original table must be dropped when clustered index is restructured.

Once a CREATE INDEX or ALTER TABLE command that creates a secondary index begins executing, queries may access the table for read access, but may not update the table. If an ALTER TABLE command is changing the clustered index, all queries must wait until the operation completes.

A newly-created secondary index contains only data that is current in the table as of the time the CREATE INDEX or ALTER TABLE command begins to execute. Specifically, a newly-created index contains only the versions of data as of the most-recently committed transactions prior to the creation of the index. The index thus does not contain any rows that were deleted (and therefore marked for deletion) by transactions that completed before the CREATE INDEX or ALTER TABLE began. Similarly, the index contains only current versions of every row, and none of the old versions of rows that were updated by transactions that ran before the index was created.

Because a newly-created index contains only information about data current at the time the index was created, queries that need to see data that was deleted or changed before the index was created cannot use the index. The only queries that could be affected by this limitation are those executing in transactions that began before the creation of the index was begun. For such queries, unpredictable results could occur. Newer queries can use the index.

No data is lost if the server crashes while an ALTER TABLE command is executing. Recovery, however, is different for clustered indexes and secondary indexes.

If the server crashes while creating a secondary index, upon recovery, InnoDB drops any partially created indexes. All you need to do to create the index is to re-run the ALTER TABLE or CREATE INDEX command.

However, when a crash occurs during the creation of a clustered index, recovery is somewhat more complicated, because the data in the table must be copied to an entirely new clustered index. Remember that all InnoDB tables are stored as clustered indexes. In the following discussion, we use the word table and clustered index interchangeably.

The InnoDB Plugin creates the new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to this temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new clustered index is then renamed with the name of the original table, and the original table is then dropped from the database.

If a system crash occurs while creating a new clustered index, no data is lost, but users must complete the recovery process using the temporary tables that exist during the process.

Users rarely re-create a clustered index or re-define primary keys on large tables. Because system crashes are uncommon and the situation described here is rare, this manual does not provide information on recovering from this scenario. Instead, please see the InnoDB web site: http://www.innodb.com/support/tips.

You should be aware of the following considerations when creating or dropping indexes using the InnoDB Plugin:

If any of the indexed columns use the UTF-8 character encoding, MySQL will copy the table. This has been reported as MySQL Bug #33650.

Due to a limitation of MySQL, the table is copied, rather than using “Fast Index Creation” when you create an index on a TEMPORARY TABLE. This has been reported as MySQL Bug #39833.

The command ALTER IGNORE TABLE t ADD UNIQUE INDEX does not delete duplicate rows. This has been reported as MySQL Bug #40344. The IGNORE keyword is ignored, and duplicates cause failure of the operation with the following error message:

ERROR 23000: Duplicate entry '347' for key 'pl'

As noted above, a newly-created index contains only information about data current at the time the index was created. Therefore, you should not run queries in a transaction that might use a secondary index that did not exist at the beginning of the transaction. There is no way for InnoDB to access “old” data that is consistent with the rest of the data read by the transaction. See the discussion of locking in Section 2.4, “Concurrency Considerations”.

Prior to InnoDB Plugin 1.0.4, unexpected results could occur if a query attempts to use an index created after the start of the transaction containing the query. If an old transaction attempts to access a “too new” index, InnoDB Plugin 1.0.4 and later reports an error:

ERROR HY000: Table definition has changed, please retry transaction

As the error message suggests, committing (or rolling back) the transaction, and restarting it, cures the problem.

InnoDB Plugin 1.0.2 introduces some improvements in error handling when users attempt to drop indexes. See section Section 8.7, “Better Error Handling When Dropping Indexes” for details.

Unfortunately, MySQL 5.1 does not support efficient creation or dropping of FOREIGN KEY constraints. Therefore, if you use ALTER TABLE to add or remove a REFERENCES constraint, the child table will be copied, rather than using “Fast Index Creation”.

Over the years, processors and cache memories have become much faster, but mass storage based on rotating magnetic disks has not kept pace. While the storage capacity of disks has grown by about a factor of 1,000 in the past decade, random seek times and data transfer rates are still severely limited by mechanical constraints. Therefore, many workloads are i/o-bound. The idea of data compression is to pay a small cost in increased CPU utilization for the benefit of smaller databases and reduced i/o to improve throughput, potentially significantly.

The ability to compress user data is an important new capability of the InnoDB Plugin. Compressed tables reduce the size of the database on disk, resulting in fewer reads and writes needed to access the user data. For many InnoDB workloads and many typical user tables (especially with read-intensive applications where sufficient memory is available to keep frequently-used data in memory), compression not only significantly reduces the storage required for the database, but also improves throughput by reducing the i/o workload, at a modest cost in processing overhead. The storage cost savings can be important, but the reduction in i/o costs can be even more valuable.

The usual size of InnoDB data pages is 16K. Beginning with the InnoDB Plugin, you can use the attributes ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE in the CREATE TABLE and ALTER TABLE commands to request InnoDB to compress each page to 1K, 2K, 4K, 8K, or 16K bytes.

(The term KEY_BLOCK_SIZE may be confusing. It does not refer to a “key” at all, but simply specifies the size of compressed pages that will be used for the table. Likewise, in the InnoDB Plugin, compression is applicable to tables, not to individual rows, so the option ROW_FORMAT really should be TABLE_FORMAT. Unfortunately, MySQL does not permit a storage engine to add syntax to SQL statements, so the InnoDB Plugin simply re-uses the clauses originally defined for MyISAM).

To create a compressed table, you might use a command like this:

CREATE TABLE name (column1 INT PRIMARY KEY) ENGINE=InnoDB
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4;

If KEY_BLOCK_SIZE not specified, the default compressed page size of 8K will be used. If KEY_BLOCK_SIZE is specified, the attribute ROW_FORMAT=COMPRESSED may be omitted.

Setting KEY_BLOCK_SIZE=16 most often will not result in much compression, since the normal InnoDB page size is 16K. However, this setting may be useful for tables with many long BLOB, VARCHAR or TEXT columns, because such data often do compress well, and might therefore require fewer “overflow” pages as described in Section 3.3.2.2, “ Compressing BLOB, VARCHAR and TEXT columns ”.

Note that compression is specified on a table-by-table basis. All indexes of a table (including the clustered index) will be compressed using the same page size, as specified on the CREATE TABLE or ALTER TABLE command. Table attributes such as ROW_FORMAT and KEY_BLOCK_SIZE are not part of the CREATE INDEX syntax, and are ignored if they are specified (although you will see them in the output of the SHOW CREATE TABLE command).

mysql> CREATE TABLE x (id INT PRIMARY KEY, c INT)
    -> ENGINE=INNODB KEY_BLOCK_SIZE=33333;
ERROR 1005 (HY000): Can't create table 'test.x' (errno: 1478)
mysql> SHOW ERRORS;
+-------+------+-------------------------------------------+ 
| Level | Code | Message                                   | 
+-------+------+-------------------------------------------+ 
| Error | 1478 | InnoDB: invalid KEY_BLOCK_SIZE=33333.     | 
| Error | 1005 | Can't create table 'test.x' (errno: 1478) | 
+-------+------+-------------------------------------------+ 

2 rows in set (0.00 sec)

This section describes some internal implementation details about compression in InnoDB. The information presented here may be helpful in tuning for performance, but is not necessary to know for basic use of compression.

Most often, the internal optimizations in InnoDB described in section Section 3.3.2, “InnoDB Data Storage and Compression” will ensure that the system runs well with compressed data. However, because the efficiency of compression depends on the nature of your data, there are some factors you should consider to get best performance. You need to choose which tables to compress, and what compressed page size to use. You may also want to adjust the size of the buffer pool based on run-time performance characteristics such as the amount of time the system spends compressing and uncompressing data.

As InnoDB evolves, new on-disk data structures are sometimes required to support new features. This release of InnoDB introduces two such new data structures: compressed tables (see Chapter 3, InnoDB Data Compression), and long variable-length columns stored off-page (see Chapter 5, Storage of Variable-Length Columns). These new data structures are not compatible with prior versions of InnoDB. Note, however, that the other new features of the InnoDB Plugin do not require the use of the new file format.

In general, a newer version of InnoDB may create a table or index that cannot safely be read or written with a prior version of InnoDB without risk of crashes, hangs, wrong results or corruptions. The InnoDB Plugin introduces a new mechanism to guard against these conditions, and to help preserve compatibility among database files and versions of InnoDB. Henceforth, users can take advantage of some new features of an InnoDB release (e.g., performance improvements and bug fixes), and still preserve the option of using their database with a prior version of InnoDB, by precluding the use of new features that create downward incompatible on-disk data structures.

The InnoDB Plugin introduces the idea of a named file format and a configuration parameter to enable the use of features that require use of that format. The new file format is the “Barracuda” format, and the file format supported by prior releases of InnoDB is called file format “Antelope”. Compressed tables and the new row format that stores long columns “off-page” require the use of the “Barracuda” file format or newer. Future versions of InnoDB may introduce a series of file formats, identified with the names of animals, in ascending alphabetical order.

Beginning with this release, every InnoDB per-table tablespace file is labeled with a file format identifier. This does not apply to the system tablespace (the ibdata files) but only the files of separate tablespaces (the *.ibd files where tables and indexes are stored in their own tablespace). As noted below, however, the system tablespace is tagged with the “highest” file format in use in a group of InnoDB database files, and this tag is checked when the files are opened.

In this release, when you create a compressed table, or a table with ROW_FORMAT=DYNAMIC, the file header for the corresponding .ibd file and the table type in the InnoDB data dictionary are updated with the identifier for the “Barracuda” file format. From that point forward, the table cannot be used with a version of InnoDB that does not support this new file format. To protect against anomalous behavior, InnoDB version 5.0.21 and later will perform a compatibility check when the table is opened, as described below. (Note that the ALTER TABLE command will, in many cases, cause a table to be recreated and thereby change its properties. The special case of adding or dropping indexes without rebuilding the table is described in Chapter 2, Fast Index Creation in the InnoDB Storage Engine.)

If a version of InnoDB supports a particular file format (whether or not it is enabled), you can access and even update any table that requires that format or an earlier format. Only the creation of new tables using new features is limited based on the particular file format enabled. Conversely, if a tablespace contains a table or index that uses a file format that is not supported by the currently running software, it cannot be accessed at all, even for read access.

The only way to “downgrade” an InnoDB tablespace to an earlier file format is to copy the data to a new table, in a tablespace that uses the earlier format. This can be done with the ALTER TABLE command, as described in Section 4.6, “Downgrading the File Format”.

The easiest way to determine the file format of an existing InnoDB tablespace is to examine the properties of the table it contains, using the SHOW TABLE STATUS command or querying the table INFORMATION_SCHEMA.TABLES. If the Row_format of the table is reported as 'Compressed' or 'Dynamic', the tablespace containing the table uses the “Barracuda” format. Otherwise, it uses the prior InnoDB file format, “Antelope”.

The new configuration parameter innodb_file_format controls whether such commands as CREATE TABLE and ALTER TABLE can be used to create tables that depend on support for the “Barracuda” file format.

The file format is a dynamic, global parameter that can be specified in the MySQL option file (my.cnf or my.ini) or changed with the SET GLOBAL command, as described in Section 9.5, “Configuring the InnoDB Plugin”.

To avoid confusion, for the purposes of this discussion we define the term “ib-file set” to mean the set of operating system files that InnoDB manages as a unit. The ib-file set includes the following files:

This collection of files is transactionally consistent, and recoverable as a unit. An “ib-file set” specifically does not include the related MySQL .frm files that contain meta data about InnoDB tables. The .frm files are created and managed exclusively by MySQL, and can sometimes get out of sync with the internal meta data in InnoDB.

Instead of “ib-file set”, we might call such a collection a “database”. However, MySQL uses the word “database” to mean a logical collection of tables, what other systems term a “schema” or “catalog”. Given MySQL terminology, multiple tables (even from more than one database) can be stored in a single “ib-file set”.

The InnoDB Plugin incorporates several checks to guard against the possible crashes and data corruptions that might occur if you use an ib-file set in a file format that is not supported by the software release in use. These checks take place when the server is started, and when you first access a table. This section details these checks, how you can control them, and error and warning conditions that may arise.

InnoDB: Error: the system tablespace is in a
file format that this version doesn't support

InnoDB: Warning: the system tablespace is in a
file format that this version doesn't support

ERROR 1146 (42S02): Table 'test.t1' doesn't exist

InnoDB: table test/t1: unknown table type 33

Although you may have enabled a given innodb_file_format at a particular time, unless you create a new table, the database file format is unchanged. If you do create a new table, the tablespace containing the table is tagged with the “earliest” or “simplest” file format that is required for the table’s features. For example, if you enable file format “Barracuda”, and create a new table that is not compressed and does not use ROW_FORMAT=DYNAMIC, the new tablespace that contains the table will be tagged as using file format “Antelope”.

It is easy to identify the file format used by a given tablespace or table. The table uses the “Barracuda” format if the Row_format reported by SHOW CREATE TABLE or INFORMATION_SCHEMA.TABLES is one of 'Compressed' or 'Dynamic'. (Please note that the Row_format is a separate column, and ignore the contents of the Create_options column, which may contain the string ROW_FORMAT.) If the table in a tablespace uses neither of those features, the file uses the format supported by prior releases of InnoDB, now called file format “Antelope”. Then, the Row_format will be one of 'Redundant' or 'Compact'.

The file format identifier is written as part of the tablespace flags (a 32-bit number) in the *.ibd file in the 4 bytes starting at position 54 of the file, most significant byte first. (The first byte of the file is byte zero.) On some systems, you can display these bytes in hexadecimal with the command od -t x1 -j 54 -N 4 tablename.ibd. If all bytes are zero, the tablespace uses the “Antelope” file format (which is the format used by the standard built-in InnoDB in MySQL up to version 5.1). Otherwise, the least significant bit should be set in the tablespace flags, and the file format identifier is written in the bits 5 through 11. (Divide the tablespace flags by 32 and take the remainder after dividing the integer part of the result by 128.)

The InnoDB tablespaces files (*.ibd file) are tagged with the file format used to create its table and indexes. The way to downgrade the tablespace is to re-create the table and its indexes. The easiest way to recreate a table and its indexes is to use the command

ALTER TABLE t ROW_FORMAT=COMPACT;

on each table that you want to downgrade. The COMPACT row format uses the file format “Antelope”. It was introduced in MySQL 5.0.3.

The file format used by the standard built-in InnoDB in MySQL 5.1 is the “Antelope” format, and the new file format introduced with the InnoDB Plugin 1.0 is the “Barracuda” format. No definitive plans have been made to introduce new features that would require additional new file formats. However, the file format mechanism introduced with the InnoDB Plugin allows for further enhancements.

For the sake of completeness, these are the file format names that might be used for future file formats: Antelope, Barracuda, Cheetah, Dragon, Elk, Fox, Gazelle, Hornet, Impala, Jaguar, Kangaroo, Leopard, Moose, Nautilus, Ocelot, Porpoise, Quail, Rabbit, Shark, Tiger, Urchin, Viper, Whale, X, Y and Zebra. These file formats correspond to the internal identifiers 0..25.

All data in InnoDB is stored in database pages comprising a B-tree index (the so-called clustered index or primary key index). The essential idea is that the nodes of the B-tree contain, for each primary key value (whether user-specified or generated or chosen by the system), the values of the remaining columns of the row as well as the key. In some other database systems, a clustered index is called an “index-organized table”. Secondary indexes in InnoDB are also B-trees, containing pairs of values of the index key and the value of the primary key, which acts as a pointer to the row in the clustered index.

There is an exception to this rule. Variable-length columns (such as BLOB and VARCHAR) that are too long to fit on a B-tree page are stored on separately allocated disk (“overflow”) pages. We call these “off-page columns”. The values of such columns are stored on singly-linked lists of overflow pages, and each such column has its own list of one or more overflow pages. In some cases, all or a prefix of the long column values is stored in the B-tree, to avoid wasting storage and eliminating the need to read a separate page.

The new “Barracuda” file format provides a new option to control how much column data is stored in the clustered index, and how much is placed on overflow pages.

Previous versions of InnoDB used an unnamed file format (now called “Antelope”) for database files. With that format, tables were defined with ROW_FORMAT=COMPACT (or ROW_FORMAT=REDUNDANT) and InnoDB stored up to the first 768 bytes of variable-length columns (such as BLOB and VARCHAR) in the index record within the B-tree node, with the remainder stored on the overflow page(s).

To preserve compatibility with those prior versions, tables created with the InnoDB Plugin will use the prefix format, unless one of ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED is specified (or implied) on the CREATE TABLE command.

With the “Antelope” file format, if the value of a column is not longer than 768 bytes, no overflow page is needed, and some savings in i/o may result, since the value is in the B-tree node. This works well for relatively short BLOBs, but may cause B-tree nodes to fill with data rather than key values, thereby reducing their efficiency. Tables with many BLOB columns could cause B-tree nodes to become too full of data, and contain too few rows, making the entire index less efficient than if the rows were shorter or if the column values were stored off-page.

When innodb_file_format is set to “Barracuda” and a table is created with ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED, long column values are stored fully off-page, and the clustered index record contains only a 20-byte pointer to the overflow page.

Whether any columns are stored off-page depends on the page size and the total size of the row. When the row is too long, InnoDB will choose the longest columns for off-page storage until the clustered index record fits on the B-tree page.

The DYNAMIC row format maintains the efficiency of storing the entire row in the index node if it will fit (as do the COMPACT and REDUNDANT formats), but this new format avoids the problem of filling B-tree nodes with a large number of data bytes of long columns. The DYNAMIC format is predicated on the idea that if a portion of a long data value is stored off-page, it is usually most efficient to store all of the value off-page. With DYNAMIC format, shorter columns are likely to remain in the B-tree node, minimizing the number of overflow pages needed for any given row.

The row format used for a table is specified with the ROW_FORMAT clause of the CREATE TABLE and ALTER TABLE commands. Note that COMPRESSED format implies DYNAMIC format. See Section 3.2, “Specifying Compression” for more details on the relationship between this clause and other clauses of these commands.

The seven INFORMATION_SCHEMA tables INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM, INNODB_CMPMEM_RESET, INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS contain live information about compressed InnoDB tables, the compressed InnoDB buffer pool, all transactions currently executing inside InnoDB, the locks that transactions hold and those that are blocking transactions waiting for access to a resource (a table or row).

Note that the Information Schema tables are themselves plugins to the MySQL server. As such they need to be INSTALLed as described in Chapter 9, Installing the InnoDB Plugin. If they are installed, but the InnoDB storage engine plugin is not installed, these tables will appear to be empty.

Following is a description of the new Information Schema tables introduced in the InnoDB Plugin, and some examples of their use.

Two new pairs of Information Schema tables provided by the InnoDB Plugin can give you some insight into how well compression is working overall. One pair of tables contains information about the number of compression operations and the amount of time spent performing compression. Another pair of tables contains information on the way memory is allocated for compression.

Three new Information Schema tables introduced in the InnoDB Plugin make it much easier to monitor transactions and diagnose possible locking problems. The three tables are INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS.

SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id;

InnoDB has always been highly efficient, and includes several unique architectural elements to assure high performance and scalability. InnoDB Plugin 1.0.6 includes several new features that take better advantage of recent advances in operating systems and hardware platforms, such as multi-core processors and improved memory allocation systems. In addition, this release permits you to better control the use of some InnoDB internal subsystems to achieve the best performance with your workload.

InnoDB Plugin 1.0.6 includes new capabilities in these areas:

In MySQL and InnoDB, multiple threads of execution access shared data structures. InnoDB synchronizes these accesses with its own implementation of mutexes and read/write locks. InnoDB has historically protected the internal state of a read/write lock with an InnoDB mutex. On Unix and Linux platforms, the internal state of an InnoDB mutex is protected by a Pthreads mutex, as in IEEE Std 1003.1c (POSIX.1c).

On many platforms, there is a more efficient way to implement mutexes and read/write locks. Atomic operations can often be used synchronize the actions of multiple threads more efficiently than Pthreads. Each operation to acquire or release a lock can be done in fewer CPU instructions, and thus result in less wasted time when threads are contending for access to shared data structures. This in turn means greater scalability on multi-core platforms.

Beginning with InnoDB Plugin 1.0.3, InnoDB implements mutexes and read/write locks with the built-in functions provided by the GNU Compiler Collection (GCC) for atomic memory access instead of using the Pthreads approach previously used. More specifically, an InnoDB Plugin that is compiled with GCC version 4.1.2 or later will use the atomic builtins instead of a pthread_mutex_t to implement InnoDB mutexes and read/write locks.

On 32-bit Microsoft Windows, InnoDB has implemented mutexes (but not read/write locks) with hand-written assembler instructions. Beginning with Microsoft Windows 2000, it is possible to use functions for Interlocked Variable Access that are similar to the built-in functions provided by GCC. Beginning with InnoDB Plugin 1.0.4, InnoDB makes use of the Interlocked functions on Windows. Unlike the old hand-written assembler code, the new implementation supports read/write locks and 64-bit platforms.

Solaris 10 introduced library functions for atomic operations. Beginning with InnoDB Plugin 1.0.4, when InnoDB is compiled on Solaris 10 with a compiler that does not support the built-in functions provided by the GNU Compiler Collection (GCC) for atomic memory access, the library functions will be used.

This change improves the scalability of InnoDB on multi-core systems. Note that the user does not have to set any particular parameter or option to take advantage of this new feature. This feature is enabled out-of-the-box on the platforms where it is supported. On platforms where the GCC, Windows, or Solaris functions for atomic memory access are not available, InnoDB will use the traditional Pthreads method of implementing mutexes and read/write locks.

When MySQL starts, InnoDB will write a message to the log file indicating whether atomic memory access will be used for mutexes, for mutexes and read/write locks, or neither. If suitable tools are used to build the InnoDB Plugin and the target CPU supports the atomic operations required, InnoDB will use the built-in functions for mutexing. If, in addition, the compare-and-swap operation can be used on thread identifiers (pthread_t), then InnoDB will use the instructions for read-write locks as well.

Note: If you are building from source, see Section 9.4.1, “Building the InnoDB Plugin on Linux or Unix” to ensure that your build process properly takes advantage of your platform capabilities.

When InnoDB was developed, the memory allocators supplied with operating systems and run-time libraries were often lacking in performance and scalability. At that time, there were no memory allocator libraries tuned for multi-core CPUs. Therefore, InnoDB implemented its own memory allocator in the mem subsystem. This allocator is guarded by a single mutex, which may become a bottleneck. InnoDB also implements a wrapper interface around the system allocator (malloc and free) that is likewise guarded by a single mutex.

Today, as multi-core systems have become more widely available, and as operating systems have matured, significant improvements have been made in the memory allocators provided with operating systems. New memory allocators perform better and are more scalable than they were in the past. The leading high-performance memory allocators include Hoard, libumem, mtmalloc, ptmalloc, tbbmalloc, and TCMalloc. Most workloads, especially those where memory is frequently allocated and released (such as multi-table joins) will benefit from using a more highly tuned memory allocator as opposed to the internal, InnoDB-specific memory allocator.

Beginning with InnoDB Plugin 1.0.3, you control whether InnoDB uses its own memory allocator or an allocator of the operating system, by setting the value of the new system configuration parameter innodb_use_sys_malloc in the MySQL option file (my.cnf or my.ini). If set to ON or 1 (the default), InnoDB will use the malloc and free functions of the underlying system rather than manage memory pools itself. This parameter is not dynamic, and takes effect only when the system is started. To continue to use the InnoDB memory allocator in InnoDB Plugin, you will have to set innodb_use_sys_malloc to 0.

Note that when the InnoDB memory allocator is disabled, InnoDB will ignore the value of the parameter innodb_additional_mem_pool_size. The InnoDB memory allocator uses an additional memory pool for satisfying allocation requests without having to fall back to the system memory allocator. When the InnoDB memory allocator is disabled, all such allocation requests will be fulfilled by the system memory allocator.

Furthermore, since InnoDB cannot track all memory use when the system memory allocator is used (innodb_use_sys_malloc is ON), the section “BUFFER POOL AND MEMORY” in the output of the SHOW ENGINE INNODB STATUS command will only include the buffer pool statistics in the “Total memory allocated”. Any memory allocated via the mem subsystem or via ut_malloc will be excluded.

On Unix-like systems that use dynamic linking, replacing the memory allocator may be as easy as making the environment variable LD_PRELOAD or LD_LIBRARY_PATH point to the dynamic library that implements the allocator. On other systems, some relinking may be necessary. Please refer to the documentation of the memory allocator library of your choice.

When INSERTs are done to a table, often the values of indexed columns (particularly the values of secondary keys) are not in sorted order. This means that the inserts of such values into secondary B-tree indexes is “random”, and this can cause excessive i/o if the entire index does not fit in memory. InnoDB has an insert buffer that caches changes to secondary index entries when the relevant page is not in the buffer pool, thus avoiding I/O operations by not reading in the page from the disk. The buffered changes are written into a special insert buffer tree and are subsequently merged when the page is loaded to the buffer pool. The InnoDB main thread merges buffered changes when the server is nearly idle.

Usually, this process will result in fewer disk reads and writes, especially during bulk inserts. However, the insert buffer tree will occupy a part of the buffer pool. If the working set almost fits in the buffer pool, it may be useful to disable insert buffering. If the working set entirely fits in the buffer pool, insert buffering will not be used anyway, because the index would exist in memory.

Beginning with InnoDB Plugin 1.0.3, you can control whether InnoDB performs insert buffering with the system configuration parameter innodb_change_buffering. The allowed values of innodb_change_buffering are none (do not buffer any operations) and inserts (buffer insert operations, the default). You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege. Changing the setting affects the buffering of new operations; the merging of already buffered entries is not affected.

If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes rather than B-tree lookups. InnoDB monitors searches on each index defined for a table. If it notices that certain index values are being accessed frequently, it automatically builds an in-memory hash table for that index. Based on the pattern of searches that InnoDB observes, it will build a hash index using a prefix of the index key. The prefix of the key can be any length, and it may be that only a subset of the values in the B-tree will appear in the hash index. InnoDB builds hash indexes on demand for those pages of the index that are often accessed.

The adaptive hash index mechanism allows InnoDB to take advantage of large amounts of memory, something typically done only by database systems specifically designed for databases that reside entirely in memory. Normally, the automatic building and use of adaptive hash indexes will improve performance. However, sometimes, the read/write lock that guards access to the adaptive hash index may become a source of contention under heavy workloads, such as multiple concurrent joins.

You can monitor the use of the adaptive hash index and the contention for its use in the “SEMAPHORES” section of the output of the SHOW ENGINE INNODB STATUS command. If you see many threads waiting on an RW-latch created in btr0sea.c, then it might be useful to disable adaptive hash indexing.

The configuration parameter innodb_adaptive_hash_index can be set to disable or enable the adaptive hash index. See Section 8.3.4, “Dynamically Changing innodb_adaptive_hash_index” for details.

InnoDB uses operating system threads to process requests from user transactions. (Transactions may issue many requests to InnoDB before they commit or roll back.) On today’s modern operating systems and servers with multi-core processors, where context switching is efficient, most workloads will run well without any limit on the number of concurrent threads. Thanks to several scalability improvements in InnoDB Plugin 1.0.3, and further changes in release 1.0.4, there should be less need to artificially limit the number of concurrently executing threads inside InnoDB.

However, for some situations, it may be helpful to minimize context switching between threads. InnoDB can use a number of techniques to limit the number of concurrently executing operating system threads (and thus the number of requests that are processed at any one time). When InnoDB receives a new request from a user session, if the number of threads concurrently executing is at a pre-defined limit, the new request will sleep for a short time before it tries again. A request that cannot be rescheduled after the sleep is put in a first-in/first-out queue and eventually will be processed. Threads waiting for locks are not counted in the number of concurrently executing threads.

The limit on the number of concurrent threads is given by the settable global variable innodb_thread_concurrency. Once the number of executing threads reaches this limit, additional threads will sleep for a number of microseconds, set by the system configuration parameter innodb_thread_sleep_delay, before being placed into the queue.

The default value for innodb_thread_concurrency and the implied default limit on the number of concurrent threads has been changed in various releases of MySQL and the InnoDB Plugin. Starting with InnoDB Plugin 1.0.3, the default value of innodb_thread_concurrency is 0, so that by default there is no limit on the number of concurrently executing threads, as shown in Table 7.1, “Changes to innodb_thread_concurrency”.

Note that InnoDB will cause threads to sleep only when the number of concurrent threads is limited. When there is no limit on the number of threads, all will contend equally to be scheduled. That is, if innodb_thread_concurrency is 0, the value of innodb_thread_sleep_delay is ignored.

When there is a limit on the number of threads, InnoDB reduces context switching overhead by permitting multiple requests made during the execution of a single SQL statement to enter InnoDB without observing the limit set by innodb_thread_concurrency. Since a SQL statement (such as a join) may comprise multiple row operations within InnoDB, InnoDB assigns “tickets” that allow a thread to be scheduled repeatedly with minimal overhead.

When starting to execute a new SQL statement, a thread will have no tickets, and it must observe innodb_thread_concurrency. Once the thread is entitled to enter InnoDB, it will be assigned a number of tickets that it can use for subsequently entering InnoDB. If the tickets run out, innodb_thread_concurrency will be observed again and further tickets will be assigned. The number of tickets to assign is specified by the global option innodb_concurrency_tickets, which is 500 by default. A thread that is waiting for a lock will be given one ticket once the lock becomes available.

The correct values of these variables are dependent on your environment and workload. You will need to try a range of different values to determine what value works for your applications. Before limiting the number of concurrently executing threads, you should review configuration options that may improve the performance of InnoDB on multi-core and multi-processor computers, such as innodb_use_sys_malloc and innodb_adaptive_hash_index.

A read ahead request is an I/O request to prefetch multiple pages in the buffer cache asynchronously in anticipation that these pages will be needed in the near future. InnoDB has historically used two read ahead algorithms to improve I/O performance.

Random read ahead is done if a certain number of pages from the same extent (64 consecutive pages) are found in the buffer cache. In such cases, InnoDB asynchronously issues a request to prefetch the remaining pages of the extent. Random read ahead added unnecessary complexity to the InnoDB code and often resulted in performance degradation rather than improvement. Starting with InnoDB Plugin 1.0.4, this feature has been removed from InnoDB, and users should generally see equivalent or improved performance.

Linear read ahead is based on the access pattern of the pages in the buffer cache, not just their number. In releases before 1.0.4, if most pages belonging to some extent are accessed sequentially, InnoDB will issue an asynchronous prefetch request for the entire next extent when it reads in the last page of the current extent. Beginning with InnoDB Plugin 1.0.4, users can control when InnoDB performs a read ahead, by adjusting the number of sequential page accesses required to trigger an asynchronous read request using the new configuration parameter innodb_read_ahead_threshold.

If the number of pages read from an extent of 64 pages is greater or equal to innodb_read_ahead_threshold, InnoDB will initiate an asynchronous read ahead of the entire following extent. Thus, this parameter controls how sensitive InnoDB is to the pattern of page accesses within an extent in deciding whether to read the following extent asynchronously. The higher the value, the more strict will be the access pattern check. For example, if you set the value to 48, InnoDB will trigger a linear read ahead request only when 48 pages in the current extent have been accessed sequentially. If the value is 8, InnoDB would trigger an asynchronous read ahead even if as few as 8 pages in the extent were accessed sequentially.

The new configuration parameter innodb_read_ahead_threshold may be set to any value from 0-64. The default value is 56, meaning that an asynchronous read ahead is performed only when 56 of the 64 pages in the extent are accessed sequentially. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini), or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege.

Starting with InnoDB Plugin 1.0.5 more statistics are provided through SHOW ENGINE INNODB STATUS command to measure the effectiveness of the read ahead algorithm. See Section 8.9, “More Read Ahead Statistics” for more information.

InnoDB uses background threads to service various types of I/O requests. Starting from InnoDB Plugin 1.0.4, the number of background threads tasked with servicing read and write I/O on data pages is configurable. In previous versions of InnoDB, there was only one thread each for read and write on non-Windows platforms. On Windows, the number of background threads was controlled by innodb_file_io_threads. The configuration parameter innodb_file_io_threads has been removed in InnoDB Plugin 1.0.4. If you try to set a value for this parameter, a warning will be written to the log file and the value will be ignored.

In place of innodb_file_io_threads, two new configuration parameters are introduced in the InnoDB Plugin 1.0.4, which are effective on all supported platforms. The two parameters innodb_read_io_threads and innodb_write_io_threads signify the number of background threads used for read and write requests respectively. You can set the value of these parameters in the MySQL option file (my.cnf or my.ini). These parameters cannot be changed dynamically. The default value for these parameters is 4 and the permissible values range from 1-64.

The purpose of this change is to make InnoDB more scalable on high end systems. Each background thread can handle up to 256 pending I/O requests. A major source of background I/O is the read ahead requests. InnoDB tries to balance the load of incoming requests in such way that most of the background threads share work equally. InnoDB also attempts to allocate read requests from the same extent to the same thread to increase the chances of coalescing the requests together. If you have a high end I/O subsystem and you see more than 64 times innodb_read_io_threads pending read requests in SHOW ENGINE INNODB STATUS, then you may gain by increasing the value of innodb_read_io_threads.

InnoDB, like any other ACID compliant database engine, is required to flush the redo log of a transaction before it is committed. Historically InnoDB used group commit functionality to group multiple such flush requests together to avoid one flush for each commit. With group commit, InnoDB can issue a single write to the log file to effectuate the commit action for multiple user transactions that commit at about the same time, significantly improving throughput.

Group commit in InnoDB worked until MySQL 4.x. With the introduction of support for the distributed transactions and Two Phase Commit (2PC) in MySQL 5.0, group commit functionality inside InnoDB was broken.

Beginning with InnoDB Plugin 1.0.4, the group commit functionality inside InnoDB works with the Two Phase Commit protocol in MySQL. Re-enabling of the group commit functionality fully ensures that the ordering of commit in the MySQL binlog and the InnoDB logfile is the same as it was before. It means it is totally safe to use InnoDB Hot Backup with InnoDB Plugin 1.0.4.

Group commit is transparent to the user and nothing needs to be done by the user to take advantage of this significant performance improvement.

The master thread in InnoDB is a thread that performs various tasks in the background. Most of these tasks are I/O related like flushing of the dirty pages from the buffer cache or writing the buffered inserts to the appropriate secondary indexes. The master thread attempts to perform these tasks in a way that does not adversely affect the normal working of the server. It tries to estimate the free I/O bandwidth available and tune its activities to take advantage of this free capacity. Historically, InnoDB has used a hard coded value of 100 IOPs (input/output operations per second) as the total I/O capacity of the server.

Beginning with InnoDB Plugin 1.0.4, a new configuration parameter is introduced to indicate the overall I/O capacity available to InnoDB. The new parameter innodb_io_capacity should be set to approximately the number of I/O operations that the system can perform per second. The value will of course depend on your system configuration. When innodb_io_capacity is set, the master threads estimates the I/O bandwidth available for background tasks based on the set value. Setting the value to 100 reverts to the old behavior.

You can set the value of innodb_io_capacity to any number 100 or greater, and the default value is 200. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege.

InnoDB performs certain tasks in the background, including flushing of dirty pages (those pages that have been changed but are not yet written to the database files) from the buffer cache, a task performed by the “master thread”. Currently, the master thread aggressively flushes buffer pool pages if the percentage of dirty pages in the buffer pool exceeds innodb_max_dirty_pages_pct.

This behavior can cause temporary reductions in throughput when excessive buffer pool flushing takes place, limiting the I/O capacity available for ordinary read and write activity. Beginning with release 1.0.4, InnoDB Plugin uses a new algorithm to estimate the required rate of flushing based on the speed of redo log generation and the current rate of flushing. The intent of this change is to smooth overall performance, eliminating steep dips in throughput, by ensuring that buffer flush activity keeps up with the need to keep the buffer pool “clean”.

Remember that InnoDB uses its log files in a circular fashion. To make a log file (or a portion of it) reusable, InnoDB must flush to disk all dirty buffer pool pages whose redo entries are contained in that portion of the log file. When required, InnoDB performs a so-called “sharp checkpoint” by flushing the appropriate dirty pages to make space available in the log file. If a workload is write intensive, it will generate a lot of redo information (writes to the log file). In this case, it is possible that available space in the log files will be used up, even though innodb_max_dirty_pages_pct is not reached. This will cause a sharp checkpoint, causing a temporary reduction in throughput.

Beginning with release 1.0.4, InnoDB Plugin uses a new heuristic-based algorithm to avoid such a scenario. The heuristic is a function of the number of dirty pages in the buffer cache and the rate at which redo is being generated. Based on this heuristic, the master thread will decide how many dirty pages to flush from the buffer cache each second. This self adapting heuristic is able to deal with sudden changes in the workload.

The primary aim of this feature is to smooth out I/O activity, avoiding sudden dips in throughput when flushing activity becomes high. Internal benchmarking has also shown that this algorithm not only maintains throughput over time, but can also improve overall throughput significantly.

Because adaptive flushing is a new feature that can significantly affect the I/O pattern of a workload, the InnoDB Plugin introduces a new configuration parameter that can be used to disable this feature. The default value of the new boolean parameter innodb_adaptive_flushing is TRUE, enabling the new algorithm. You can set the value of this parameter in the MySQL option file (my.cnf or my.ini) or change it dynamically with the SET GLOBAL command, which requires the SUPER privilege.

Synchronization inside InnoDB frequently involves the use of spin loops (where, while waiting, InnoDB executes a tight loop of instructions repeatedly to avoid having the InnoDB process and threads be rescheduled by the operating system). If the spin loops are executed too quickly, system resources are wasted, imposing a relatively severe penalty on transaction throughput. Most modern processors implement the PAUSE instruction for use in spin loops, so the processor can be more efficient.

Beginning with 1.0.4, the InnoDB Plugin uses a PAUSE instruction in its spin loops on all platforms where such an instruction is available. This technique increases overall performance with CPU-bound workloads, and has the added benefit of minimizing power consumption during the execution of the spin loops.

Using the PAUSE instruction in InnoDB spin loops is transparent to the user. User does not have to do anything to take advantage of this performance improvement.

Many InnoDB mutexes and rw-locks are reserved for a short amount of time. On a multi-core system, it is often more efficient for a thread to actively poll a mutex or rw-lock for a while before sleeping. If the mutex or rw-lock becomes available during this polling period, the thread may continue immediately, in the same time slice. Alas, if a shared object is being polled too frequently by multiple threads, it may result in “cache ping-pong”, the shipping of cache lines between processors. InnoDB tries to avoid this by making threads busy, waiting a random time between subsequent polls. The delay is implemented as a busy loop.

Starting with InnoDB Plugin 1.0.4, it is possible to control the maximum delay between sampling a mutex or rw-lock using the new parameter innodb_spin_wait_delay. In the 100 MHz Pentium era, the unit of delay used to be one microsecond. The duration of the delay loop depends on the C compiler and the target processor. On a system where all processor cores share a fast cache memory, it might be useful to reduce the maximum delay or disable the busy loop altogether by setting innodb_spin_wait_delay=0. On a system that consists of multiple processor chips, the shipping of cache lines can be slower and it may be useful to increase the maximum delay.

The default value of innodb_spin_wait_delay is 6. The spin wait delay is a dynamic, global parameter that can be specified in the MySQL option file (my.cnf or my.ini) or changed at runtime with the command SET GLOBAL innodb_spin_wait_delay=delay, where delay is the desired maximum delay. Changing the setting requires the SUPER privilege.

Historically, InnoDB has inserted newly read blocks into the middle of the list representing the buffer cache, to avoid pollution of the cache due to excessive read-ahead. The idea is that the read-ahead algorithm should not pollute the buffer cache by forcing the frequently accessed (“hot”) pages out of the LRU list. To achieve this, InnoDB internally maintains a pointer at 3/8 from the tail of the LRU list, and all newly read pages are inserted at this location in the LRU list. The pages are moved to the from of the list (the most-recently used end) when they are accessed from the buffer cache for the first time. Thus pages that are never accessed never make it to the front 5/8 of the LRU list.

The above arrangement logically divides the LRU list into two segments where the 3/8 pages downstream of the insertion point are considered “old” and are desirable victims for LRU eviction. Starting with InnoDB Plugin 1.0.5, this mechanism has been extended in two ways.

You can control the insertion point in the LRU list. A new configuration parameter innodb_old_blocks_pct now controls the percentage of “old” blocks in the LRU list. The default value of innodb_old_blocks_pct is 37, corresponding to the original fixed ratio of 3/8. The permissible value range is 5 to 95.

The optimization that keeps the buffer cache from being churned too much by read-ahead, is extended to avoid similar problems resulting from table or index scans. During an index scan, a data page is typically accessed a few times in quick succession and is then never touched again. InnoDB Plugin 1.0.5 introduces a new configuration parameter innodb_old_blocks_time which specifies the time window (in milliseconds) after the first access to a page during which it can be accessed without being moved to the front (most-recently used end) of the LRU list. The default value of innodb_old_blocks_time is 0, corresponding to the original behavior of moving a page to the MRU end of the LRU list on first access in the buffer pool.

Both the new parameters innodb_old_blocks_pct and innodb_old_blocks_time are dynamic, global and can be specified in the MySQL option file (my.cnf or my.ini) or changed at runtime with the SET GLOBAL command. Changing the setting requires the SUPER privilege.

To help you gauge the effect of setting these parameters, some additional statistics are reported by SHOW ENGINE INNODB STATUS command. The BUFFER POOL AND MEMORY section now looks like:

Total memory allocated 1107296256; in additional pool allocated 0
Dictionary memory allocated 80360
Buffer pool size   65535
Free buffers       0
Database pages     63920
Old database pages 23600
Modified db pages  34969
Pending reads 32
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 414946, not young 2930673
1274.75 youngs/s, 16521.90 non-youngs/s
Pages read 486005, created 3178, written 160585
2132.37 reads/s, 3.40 creates/s, 323.74 writes/s
Buffer pool hit rate 950 / 1000, young-making rate 30 / 1000 not 392 / 1000
Pages read ahead 1510.10/s, evicted without access 0.00/s
LRU len: 63920, unzip_LRU len: 0
I/O sum[43690]:cur[221], unzip sum[0]:cur[0]

This chapter describes several changes in the InnoDB Plugin that offer new flexibility and improve ease of use, reliability and performance:

The InnoDB Plugin introduces named file formats to improve compatibility between database file formats and various InnoDB versions.

To create new tables that require a new file format, you must enable the new “Barracuda” file format, using the configuration parameter innodb_file_format. The value of this parameter will determine whether it will be possible to create a table or index using compression or the new DYNAMIC row format. If you omit innodb_file_format or set it to “Antelope”, you preclude the use of new features that would make your database inaccessible to the built-in InnoDB in MySQL 5.1 and prior releases.

You can set the value of innodb_file_format on the command line when you start mysqld, or in the option file my.cnf (Unix operating systems) or my.ini (Windows). You can also change it dynamically with the SET GLOBAL command.

Further information about managing file formats is presented in Chapter 4, InnoDB File Format Management.

With the InnoDB Plugin it now is possible to chance certain system configuration parameters dynamically, without shutting down and restarting the server as was previously necessary. This increases up-time and facilitates testing of various options. You can now set these parameters dynamically:

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

Starting with the InnoDB Plugin, when the user requests to TRUNCATE a table that is stored in an .ibd file of its own (because innodb_file_per_table was enabled when the table was created), and if the table is not referenced in a FOREIGN KEY constraint, the InnoDB Plugin will drop and re-create the table in a new .idb file. This operation is much faster than deleting the rows one by one, and will return disk space to the operating system and reduce the size of page-level backups.

Previous versions of InnoDB would re-use the existing .idb file, thus releasing the space only to InnoDB for storage management, but not to the operating system. Note that when the table is truncated, the count of rows affected by the TRUNCATE command is an arbitrary number.

Note: if there are referential constraints between the table being truncated and other tables, MySQL instead automatically converts the TRUNCATE command to a DELETE command that operates row-by-row, so that ON DELETE operations can occur on “child” tables.

To guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL commands, the InnoDB Plugin provides a “strict mode” of operations. In this mode, InnoDB will raise error conditions in certain cases, rather than issue a warning and process the specified command (perhaps with some unintended defaults). This is analogous to MySQL’s sql_mode, which controls what SQL syntax MySQL will accept, and determines whether it will silently ignore errors, or validate input syntax and data values. Note that there is no strict mode with the built-in InnoDB, so some commands that execute without errors with the built-in InnoDB will generate errors with the InnoDB Plugin, unless you disable strict mode.

In the InnoDB Plugin, the setting of InnoDB strict mode affects the handling of syntax errors on the CREATE TABLE, ALTER TABLE and CREATE INDEX commands. Starting with InnoDB Plugin version 1.0.2, the strict mode also enables a record size check, so that an INSERT or UPDATE will never fail due to the record being too large for the selected page size.

Using the new clauses and settings for ROW_FORMAT and KEY_BLOCK_SIZE on CREATE TABLE and ALTER TABLE commands and the CREATE INDEX can be confusing when not running in strict mode. Unless you run in strict mode, InnoDB will ignore certain syntax errors and will create the table or index, with only a warning in the message log. However if InnoDB strict mode is on, such errors will generate an immediate error and the table or index will not be created, thus saving time by catching the error at the time the command is issued.

The default for strict mode is off, but in the future, the default may be changed. It is best to start using strict mode with the InnoDB Plugin, and make sure your SQL scripts use commands that do not generate warnings or unintended effects.

InnoDB strict mode is set with the configuration parameter innodb_strict_mode, which can be specified as on or off. You can set the value on the command line when you start mysqld, or in the configuration file my.cnf (Unix operating systems) or my.ini (Windows). You can also enable or disable InnoDB strict mode at runtime with the command SET [GLOBAL|SESSION] innodb_strict_mode=mode, where mode is either ON or OFF. Changing the GLOBAL setting requires the SUPER privilege and affects the operation of all clients that subsequently connect. Any client can change the SESSION setting for innodb_strict_mode, which affects only that client.

The MySQL query optimizer uses estimated statistics about key distributions to select or avoid using an index in an execution plan, based on the relative selectivity of the index. Previously, InnoDB sampled 8 random pages from an index to get an estimate of the cardinality of (i.e., the number of distinct values in) the index. (This page sampling technique is frequently described as “index dives”.) This small number of page samples frequently was insufficient, and could give inaccurate estimates of an index’s selectivity and thus lead to poor choices by the query optimizer.

To give users control over the quality of the statistics estimate (and thus better information for the query optimizer), the number of sampled pages now can be changed via the parameter innodb_stats_sample_pages.

This feature addresses user requests such as that as expressed in MySQL Bug #25640: InnoDB Analyze Table Should Allow User Selection of Index Dives.

You can change the number of sampled pages via the global parameter innodb_stats_sample_pages, which can be set at runtime (i.e., it is a dynamic parameter). The default value for this parameter is 8, preserving the same behavior as in past releases.

Note that the value of innodb_stats_sample_pages affects the index sampling for all tables and indexes. You should also be aware that there are the following potentially significant impacts when you change the index sample size:

Note that the cardinality estimation can be disabled for metadata commands such as SHOW TABLE STATUS by executing the command SET GLOBAL innodb_stats_on_metadata=OFF (or 0). Before InnoDB Plugin 1.0.2, this variable could only be set in the MySQL option file (my.cnf or my.ini), and changing it required shutting down and restarting the server.

The cardinality (the number of different key values) in every index of a table is calculated when a table is opened, at SHOW TABLE STATUS and ANALYZE TABLE and on other circumstances (like when the table has changed too much). Note that all tables are opened, and the statistics are re-estimated, when the mysql client starts if the auto-rehash setting is set on (the default). The auto-rehash feature enables automatic name completion of database, table, and column names for interactive users. You may prefer setting auto-rehash off to improve the start up time of the mysql client.

You should note that it does not make sense to increase the index sample size, then run ANALYZE TABLE and decrease sample size to attempt to obtain better statistics. This is because the statistics are not persistent. They are automatically recalculated at various times other than on execution of ANALYZE TABLE. Sooner or later the “better” statistics calculated by ANALYZE running with a high value of innodb_stats_sample_pages will be wiped away.

The estimated cardinality for an index will be more accurate with a larger number of samples, but each sample might require a disk read, so you do not want to make the sample size too large. You should choose a value for innodb_stats_sample_pages that results in reasonably accurate estimates for all tables in your database without requiring excessive I/O.

Although it is not possible to specify the sample size on a per-table basis, smaller tables generally would require fewer index samples than larger tables require. If your database has many large tables, you may want to consider using a higher value for innodb_stats_sample_pages than if you have mostly smaller tables.

For efficiency, InnoDB requires an index to exist on foreign key columns so that UPDATE and DELETE operations on a “parent” table can easily check for the existence or non-existence of corresponding rows in the “child” table. To ensure that there is an appropriate index for such checks, MySQL will sometimes implicitly create or drop such indexes as a side-effect of CREATE TABLE, CREATE INDEX, and ALTER TABLE statements.

When you explicitly DROP an index, InnoDB will check that an index suitable for referential integrity checking will still exist following the DROP of the index. InnoDB will prevent you from dropping the last usable index for enforcing any given referential constraint. Users have been confused by this behavior, as reported in MySQL Bug #21395.

In releases prior to InnoDB Plugin 1.0.2, attempts to drop the only usable index would result in an error message such as

ERROR 1025 (HY000): Error on rename of './db2/#sql-18eb_3'
to './db2/foo'(errno: 150)

Beginning with InnoDB Plugin 1.0.2, this error condition is reported with a more friendly message:

ERROR 1553 (HY000): Cannot drop index 'fooIdx':
needed in a foreign key constraint

As a related matter, because all user data in InnoDB is maintained in the so-called “clustered index” (or primary key index), InnoDB ensures that there is such an index for every table, even if the user does not declare an explicit PRIMARY KEY. In such cases, InnoDB will create an implicit clustered index using the first columns of the table that have been declared UNIQUE and NOT NULL.

When the InnoDB Plugin is used with a MySQL version earlier than 5.1.29, an attempt to drop an implicit clustered index (the first UNIQUE NOT NULL index) will fail if the table does not contain a PRIMARY KEY. This has been reported as MySQL Bug #31233. Attempts to use the DROP INDEX or ALTER TABLE command to drop such an index will generate this error:

ERROR 42000: This table type requires a primary key

Beginning with MySQL 5.1.29 when using the InnoDB Plugin, attempts to drop such an index will copy the table, rebuilding the index using a different UNIQUE NOT NULL group of columns or a system-generated key. Note that all indexes will be re-created by copying the table, as described in Section 2.3, “Implementation”.

In those versions of MySQL that are affected by this bug, one way to change an index of this type is to create a new table and copy the data into it using INSERT INTO newtable SELECT * FROM oldtable, and then DROP the old table and rename the new table.

However, if there are existing tables with references to the table whose index you are dropping, you will first need to use the ALTER TABLE command to remove foreign key references from or to other tables. Unfortunately, MySQL does not support dropping or creating FOREIGN KEY constraints, even though dropping a constraint would be trivial. Therefore, if you use ALTER TABLE to add or remove a REFERENCES constraint, the child table will be copied, rather than using “Fast Index Creation”.

The command SHOW ENGINE INNODB MUTEX displays information about InnoDB mutexes and rw-locks. It can be a useful tuning aid on multi-core systems. However, with a big buffer pool, the size of the output may be overwhelming. There is a mutex and rw-lock in each 16K buffer pool block. It is highly improbable that an individual block mutex or rw-lock could become a performance bottleneck, and there are 65,536 blocks per gigabyte.

Starting with InnoDB Plugin 1.0.4, SHOW ENGINE INNODB MUTEX will skip the mutexes and rw-locks of buffer pool blocks. Furthermore, it will not list any mutexes or rw-locks that have never been waited on (os_waits=0). Therefore, SHOW ENGINE INNODB MUTEX only displays information about such mutexes and rw-locks that does not belong to the buffer pool blocks and for whom there have been at least one OS level wait.

As described in Section 7.7, “Changes in the Read Ahead Algorithm” a read ahead request is an asynchronous IO request issued in anticipation that the page being read in will be used in near future. It can be very useful if a DBA has the information about how many pages are read in as part of read ahead and how many of them are evicted from the buffer pool without ever being accessed. Based on this information a DBA can then fine tune the degree of aggressiveness of the read ahead using the parameter innodb_read_ahead_threshold.

Starting from InnoDB Plugin 1.0.5 two new status variables are added to the SHOW STATUS output. These global status variables Innodb_buffer_pool_read_ahead and Innodb_buffer_pool_read_ahead_evicted indicate the number of pages read in as part of read ahead and the number of such pages evicted without ever being accessed respectively. These counters provide global values since the start of the server. Please also note that the status variables Innodb_buffer_pool_read_ahead_rnd and Innodb_buffer_pool_read_ahead_seq have been removed from the SHOW STATUS output.

In addition to the two counters mentioned above SHOW INNODB STATUS will also show the rate at which the read ahead pages are being read in and the rate at which such pages are being evicted without being accessed. The per second averages are based on the statistics collected since the last invocation of SHOW INNODB STATUS and are displayed in the BUFFER POOL AND MEMORY section of the output.

The InnoDB Plugin is available at the download section of the InnoDB website. You can download the plugin in these formats:

Use the following table to confirm that the version of the InnoDB Plugin (whether source or binary) is compatible with your platform, hardware type (including 32-bit vs 64-bit) and with your version of MySQL. In general, a specific release of the InnoDB Plugin is designed to be compatible with a range of MySQL versions, but this may vary, so check the information on the download page.

When building MySQL from source, you can generally use the source for the InnoDB Plugin in place of the source for the built-in InnoDB. However, due to limitations of MySQL, a given binary version of the InnoDB Plugin is compatible only with a specific version of MySQL, as follows.

The simplest way to install the InnoDB Plugin is to use a precompiled (binary) shared library file, when one is available. The procedures are similar for installing the InnoDB Plugin using the binary on different hardware and operating systems platforms, but the specific details differ between Unix or Linux and Microsoft Windows. See below for notes specific to your platform.

Note that due to MySQL Bug #42610, the procedure of installing the binary InnoDB Plugin changed in MySQL 5.1.33. If your version of MySQL is older than 5.1.33, refer to Appendix B, Using the InnoDB Plugin with MySQL 5.1.30 or Earlier.

The steps for installing the InnoDB Plugin as a shared library are as follows:

The following sections detail these steps for Unix or Linux systems, and for Microsoft Windows.

Sometimes, you may wish to build the plugin from the source code using special compilation options, or there might be no binary plugin available for your server platform. With the resulting special version of MySQL containing the new InnoDB functionality, it is not necessary to INSTALL any plugins or be concerned about startup parameters that preclude loading plugins.

To build the InnoDB Plugin from the source code, you also need the MySQL source code and some software tools. You should become familiar with the MySQL manual section on MySQL Installation Using a Source Distribution.

The general steps for building MySQL from source, containing the InnoDB Plugin in place of the standard built-in InnoDB, are as follows:

The following sections detail these steps for Linux or Unix systems, and for Microsoft Windows.

cscript //H:CScript

Because the MySQL server as distributed by MySQL includes a built-in copy of InnoDB, if you are using the dynamic InnoDB Plugin and have INSTALLed it into the MySQL server, you must always start the server with the option ignore_builtin_innodb, either in the option file or on the mysqld command line. Also, remember that the startup option skip_grant_tables prevents MySQL from loading any plugins. Neither of these options is needed when using a specialized version of MySQL that you build from source.

By default, the InnoDB Plugin does not create tables in a format that is incompatible with the built-in InnoDB in MySQL. Tables in the new format may be compressed, and they may store portions of long columns off-page, outside the B-tree nodes. You may wish to enable the creation of tables in the new format, using one of these techniques:

You may also want to enable the new InnoDB strict mode, which guards SQL or certain operational errors that otherwise generate warnings and possible unintended consequences of ignored or incorrect SQL commands or parameters. As described in Section 8.5, “InnoDB Strict Mode”, the GLOBAL parameter innodb_strict_mode can be set ON or OFF in the same way as the parameters just mentioned. You can also use the command SET SESSION innodb_strict_mode=mode (where mode is ON or OFF) to enable or disable InnoDB strict mode on a per-session basis.

Take care when using new InnoDB configuration parameters or values that apply only when using the InnoDB Plugin. When the MySQL server encounters an unknown option, it fails to start and returns an error: unknown variable. This happens, for example, if you include the new parameter innodb_file_format when you start the MySQL server with the built-in InnoDB rather than the plugin. This can cause a problem if you accidentally use the built-in InnoDB after a system crash, because InnoDB crash recovery runs before MySQL checks the startup parameters. See Section 11.4, “Possible Problems” why this can be a problem. One safeguard is to specify the prefix loose_ before the names of new options, so that if they are not recognized on startup, the server gives a warning instead of a fatal error.

Before shutting down the MySQL server containing the InnoDB Plugin, you must enable “slow” shutdown:

SET GLOBAL innodb_fast_shutdown=0;

For the details of the shutdown procedure, see the MySQL manual on The Shutdown Process.

In the directory where the MySQL server looks for plugins, rename the executable file of the old InnoDB Plugin (ha_innodb.so or ha_innodb.dll), so that you can restore it later if needed. You may remove the file later. The plugin directory is specified by the system variable plugin_dir. The default location is usually the lib/plugin subdirectory of the directory specified by basedir.

Download a suitable package for your server platform, operating system and MySQL version. Extract the contents of the archive using tar or a similar tool for Linux and Unix, or Windows Explorer or WinZip or similar utility for Windows. Copy the file ha_innodb.so or ha_innodb.dll to the directory where the MySQL server looks for plugins.

Start the MySQL server. Follow the procedure in Section 10.3, “Converting Compressed Tables Created Before Version 1.0.2” to convert any compressed tables if needed.

As with a dynamically installed InnoDB Plugin, you must perform a “slow” shutdown of the MySQL server. If you have built MySQL from source code and replaced the built-in InnoDB in MySQL with the InnoDB Plugin in the source tree as discussed in Section 9.4, “Building the InnoDB Plugin from Source Code”, you will have a special version of the mysqld executable that contains the InnoDB Plugin.

If you intend to upgrade to a dynamically linked InnoDB Plugin, you can follow the advice of Section 11.3.4, “Uninstalling a Statically Built InnoDB Plugin” and Section 9.3, “Installing the Precompiled InnoDB Plugin as a Shared Library”.

If you intend to upgrade a statically built InnoDB Plugin to another statically built plugin, you will have to rebuild the mysqld executable, shut down the server, and replace the mysqld executable before starting the server.

Either way, please be sure to follow the instructions of Section 10.3, “Converting Compressed Tables Created Before Version 1.0.2” if any compressed tables were created.

The InnoDB Plugin version 1.0.2 introduces an incompatible change to the format of compressed tables. This means that some compressed tables that were created with an earlier version of the InnoDB Plugin may need to be rebuilt with a bigger KEY_BLOCK_SIZE before they can be used.

If you must keep your existing database when you upgrade to InnoDB Plugin 1.0.2 or newer, you will need to perform a “slow” shutdown of MySQL running the previous version of the InnoDB Plugin. Following such a shutdown, and using the newer release of the InnoDB Plugin, you will need to determine which compressed tables need conversion and then follow a procedure to upgrade these tables. Because most users will not have tables where this process is required, this manual does not detail the procedures required. If you have created compressed tables with the InnoDB Plugin prior to release 1.0.2, you may want to review the relevant information on the InnoDB website.

There are times when you might want to use the InnoDB Plugin with a given database, and then downgrade to the built-in InnoDB in MySQL. One reason to do this is because you want to take advantage of a new InnoDB Plugin feature (such as “Fast Index Creation”), but revert to the standard built-in InnoDB in MySQL for production operation.

If you have created new tables using the InnoDB Plugin, you may need to convert them to a format that the built-in InnoDB in MySQL can read. Specifically, if you have created tables that use ROW_FORMAT=COMPRESSED or ROW_FORMAT=DYNAMIC you must convert them to a different format, if you plan to access these tables with the built-in InnoDB in MySQL. If you do not do so, anomalous results may occur.

Although InnoDB will check the format of tables and database files (specifically *.ibd files) for compatibility, it is unable to start if there are buffered changes for “too new format” tables in the redo log or in the system tablespace. Thus it is important to carefully follow these procedures when downgrading from the InnoDB Plugin to the built-in InnoDB in MySQL, version 5.1.

This chapter describes the downgrade scenario, and the steps you should follow to ensure correct processing of your database.

Starting with version 5.0.21, the built-in InnoDB in MySQL checks the table type before opening a table. Until now, all InnoDB tables have been tagged with the same type, although some changes to the format have been introduced in MySQL versions 4.0, 4.1, and 5.0.

One of the important new features introduced with the InnoDB Plugin is support for identified file formats. This allows the InnoDB Plugin and versions of InnoDB since 5.0.21 to check for file compatibility. It also allows the user to preclude the use of features that would generate downward incompatibilities. By paying attention to the file format used, you can protect your database from corruptions, and ensure a smooth downgrade process.

In general, before using a database file created with the InnoDB Plugin with the built-in InnoDB in MySQL you should verify that the tablespace files (the *.ibd files) are compatible with the built-in InnoDB in MySQL. The InnoDB Plugin can read and write tablespaces in both the formats “Antelope” and “Barracuda”. The built-in InnoDB can only read and write tablespaces in “Antelope” format. To make all tablespaces “legible” to the built-in InnoDB in MySQL, you should follow the instructions in Section 11.3, “How to Downgrade” to reformat all tablespaces to be in the “Antelope” format.

Generally, after a “slow” shutdown of the InnoDB Plugin (innodb_fast_shutdown=0), it should be safe to open the data files with the built-in InnoDB in MySQL. See Section 11.4, “Possible Problems” for a discussion of possible problems that can arise in this scenario and workarounds for them.

Failure to follow the downgrading procedure described in Section 11.3, “How to Downgrade” may lead to compatibility issues when files written by the InnoDB Plugin are accessed by the built-in InnoDB in MySQL. This section describes some internal recovery algorithms, to help explain why it is important to follow the downgrade procedure described above. It discusses the issues that may arise, and covers possible ways to fix them.

A general fix is to install the plugin as described in Chapter 9, Installing the InnoDB Plugin and then follow the downgrading procedure described in Section 11.3, “How to Downgrade”.

In the future, the file format management features described in Chapter 4, InnoDB File Format Management will guard against the types of problems described in this section.

Fixed MySQL Bug #48782: On lock wait timeout, CREATE INDEX attempts DROP TABLE.

Report duplicate table names to the client connection, not to the error log.

Allow CREATE INDEX to be interrupted.

Fixed MySQL Bug #47167: InnoDB Plugin "set global innodb_file_format_check" cannot set value by User-Defined Variable.

Fixed MySQL Bug #45992: InnoDB memory not freed after shutdown; and MySQL Bug #46656: InnoDB Plugin memory leaks (Valgrind).

Clean up after a crash during DROP INDEX. When InnoDB crashes while dropping an index, ensure that the index will be completely dropped during crash recovery.

When a secondary index exists in the MySQL .frm file but not in the InnoDB data dictionary, return an error instead of letting an assertion fail in index_read.

Prevent the reuse of tablespace identifiers after InnoDB has crashed during table creation. Also, refuse to start if files with duplicate tablespace identifiers are encountered.

Fixed MySQL Bug #47055: Unconditional exit on ERROR_WORKING_SET_QUOTA 1453 (0x5AD) for InnoDB backend.

Fixed MySQL Bug #37232: InnoDB might get too many read locks for DML with repeatable-read.

Fixed MySQL Bug #31183: Tablespace full problems not reported in error log; error message unclear.

Modified innodb-zip.test so that the test will pass with zlib 1.2.3.3. Apparently, the zlib function compressBound() has been slightly changed, and the maximum record size of a table with 1K compressed page size has been reduced by one byte.

Fixed a regression introduced by the fix for MySQL Bug #26316.

Fixed MySQL Bug #44571: InnoDB Plugin crashes on ADD INDEX.

Fixed a bug in the merge sort that can corrupt indexes in fast index creation.

Introduced the settable global variables innodb_old_blocks_pct and innodb_old_blocks_time for controlling the buffer pool eviction policy, making it possible to tune the buffer pool LRU eviction policy to be more resistant against index scans. See Section 7.14, “Making Buffer Cache Scan Resistant”.

Fixed MySQL Bug #42885: buf_read_ahead_random, buf_read_ahead_linear counters, thread wakeups. See Section 8.9, “More Read Ahead Statistics”.

Fixed MySQL Bug #46650: InnoDB assertion autoinc_lock == lock in lock_table_remove_low on INSERT SELECT.

Fixed MySQL Bug #46657: InnoDB Plugin: invalid read in index_merge_innodb test (Valgrind).

Fixed MySQL Bug #42829: binlogging enabled for all schemas regardless of binlog-db-db / binlog-ignore-db.

Enabled inlining of functions and prefetch with Sun Studio.

Changed the defaults for innodb_sync_spin_loops from 20 to 30 and innodb_spin_wait_delay from 5 to 6.

Implemented adaptive flushing of dirty pages, which uses heuristics to avoid I/O bursts at checkpoint. A new parameter innodb_adaptive_flushing is added to control whether the new flushing algorithm should be used. See Section 7.11, “Controlling the Flushing Rate of Dirty Pages”.

Implemented I/O capacity tuning. A new parameter innodb_io_capacity is added to control the master threads I/O rate. (To preserve the former behavior, set this parameter to a value of 100.) The ibuf merge is also changed from synchronous to asynchronous. See Section 7.10, “Controlling the Master Thread I/O Rate”.

Introduced the PAUSE instruction inside spin-loop where available. See Section 7.12, “Using the PAUSE instruction in InnoDB spin loops”.

Fixed a crash on SET GLOBAL innodb_file_format=DEFAULT or SET GLOBAL innodb_file_format_check=DEFAULT.

Changed the default values for innodb_max_dirty_pages_pct, innodb_additional_mem_pool_size, innodb_buffer_pool_size, and innodb_log_buffer_size.

Enabled group commit functionality that was broken in 5.0 when distributed transactions were introduced. See Section 7.9, “Group Commit”.

Enabled the functionality of having multiple background threads, with two new configuration parameters, innodb_read_io_threads and innodb_write_io_threads. The Windows only parameter innodb_file_io_threads has been removed. See Section 7.8, “Multiple Background I/O Threads”.

Changed the linear read ahead algorithm and disabled random read ahead. Also introduced a new configuration parameter innodb_read_ahead_threshold to control the sensitivity of the linear read ahead. See Section 7.7, “Changes in the Read Ahead Algorithm”.

Standardized comments that allow the extraction of documentation from code base with the Doxygen tool.

Fixed a bug that could cause failures in secondary index lookups in consistent reads right after crash recovery.

Corrected the estimation of space needed on a compressed page when performing an update by delete-and-insert.

Removed the statically linked copies of the zlib and strings libraries from the binary Windows plugin. Invoke the copies of these libraries in the mysqld executable, like the binary InnoDB Plugin does on other platforms.

Trimmed the output of SHOW ENGINE INNODB MUTEX. See Section 8.8, “More compact output of SHOW ENGINE INNODB MUTEX”.

On Microsoft Windows, make use of atomic memory access to implement mutexes and rw-locks more efficiently. On Sun Solaris 10, if GCC built-in functions for atomic memory access are unavailable, use library functions instead. See Section 7.2, “Faster Locking for Improved Scalability”.

Fixed MySQL Bug #44032: in ROW_FORMAT=REDUNDANT, update UTF-8 CHAR to/from NULL is not in-place.

Fixed MySQL Bug #43660: SHOW INDEXES/ANALYZE does not update cardinality for indexes of InnoDB table.

Made the parameter innodb_change_buffering settable by mysqld start-up option. Due to a programming mistake, it was only possible to set this parameter by the SET GLOBAL command in InnoDB Plugin 1.0.3.

Added a parameter innodb_spin_wait_delay for controlling the polling of mutexes and rw-locks. See Section 7.13, “Control over spin lock polling”.

In consistent reads, issue an error message on attempts to use newly created indexes that may lack required history. See Section 2.6, “Limitations”.

Improved the scalability of InnoDB on multi-core CPUs. See Section 7.2, “Faster Locking for Improved Scalability”.

Added a parameter innodb_change_buffering for controlling the insert buffering. See Section 7.4, “Controlling InnoDB Insert Buffering”.

Added a parameter innodb_use_sys_malloc for using an operating system memory allocation rather than the InnoDB internal memory allocator. See Section 7.3, “Using Operating System Memory Allocators”.

Made it possible to dynamically enable or disable adaptive hash indexing. See Section 7.5, “Controlling Adaptive Hash Indexing”.

Changed the default value of innodb_thread_concurrency from 8 to 0, for unlimited concurrency by default. See Section 7.6, “Changes Regarding Thread Concurrency”.

Fixed an issue that the InnoDB Plugin fails if innodb_buffer_pool_size is defined bigger than 4095M on 64-bit Windows.

Fixed MySQL bug #41676: Table names are case insensitive in locking.

Fixed MySQL bug #41904: Create unique index problem.

Fixed MySQL bug #43043: Crash on BLOB delete operation.

Fixed a bug in recovery when dropping incomplete indexes left behind by fast index creation.

Fixed a crash bug when all rows of a compressed table are deleted.

Fixed a corruption bug when a table is dropped on a busy system that contains compressed tables.

Fixed an assertion failure involving the variable ut_total_allocated_memory that was caused by unprotected access during fast index creation.

Implemented the dynamic plugin (ha_innodb.dll) on Windows.

Added a parameter innodb_stats_sample_pages for controlling the index cardinality estimates.

Made innodb_stats_on_metadata a settable global parameter. (MySQL bug #38189)

Made innodb_lock_wait_timeout a settable session parameter. (MySQL bug #36285)

Fixed bugs related to off-page columns (see Section 5.3, “DYNAMIC Row Format”).

Fixed various bugs related to compressed tables. This includes MySQL bug #36172, a possible but rare corruption, and an incompatible file format change relating to very long rows in compressed tables, and to off-page storage of long column values.

Fixed a bug in crash recovery which was a side effect of incorrect implementation of the system tablespace tagging.

Fixed MySQL bugs related to auto_increment columns: #26316, #35498, #35602, #36411, #37531, #37788, #38839, #39830, #40224.

Fixed some race conditions, hangs or crashes related to INFORMATION_SCHEMA tables, fast index creation, and to the recovery of PREPARED transactions.

Fixed crashes on DROP TABLE or CREATE TABLE when there are FOREIGN KEY constraints. (MySQL bug #38786)

Fixed a crash caused by a conflict between TRUNCATE TABLE and LOCK TABLES. (MySQL bug #38231)

Fixed MySQL bug #39939: DROP TABLE or DISCARD TABLESPACE takes a long time.

Fixed MySQL bug #40359: InnoDB plugin error/warning message during shutdown.

Fixed MySQL bug #40360: Binlog related errors with binlog off.

Applied all changes from MySQL through version 5.1.30.

Fixed bugs related to the packaging of the InnoDB Plugin: MySQL bugs #36222, #36434.

Fixed crash bugs related to the new features of the InnoDB Plugin: MySQL bugs #36169, #36310.

Implemented the system tablespace tagging discussed in Section 4.4.1, “Startup File Format Compatibility Checking”.

Applied all changes from MySQL through version 5.1.25.

The initial release of the InnoDB Plugin is based on the built-in InnoDB in MySQL version 5.1. See Section 1.2, “Features of the InnoDB Plugin” for the main features.