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.