Chapter 8. Changes for Flexibility, Ease of Use and Reliability

InnoDB has started using named file formats to improve compatibility in upgrade and downgrade situations, or heterogeneous systems running different levels of MySQL. Many important InnoDB features, such as table compression and the DYNAMIC row format for more efficient BLOB storage, require creating tables in the Barracuda file format. The original file format, which previously didn't have a name, is known now as Antelope.

To create new tables that take advantage of the Barracuda features, enable that file format using the configuration parameter innodb_file_format. The value of this parameter determines whether a newly created table or index can use compression or the new DYNAMIC row format.

To preclude the use of new features that would make your database inaccessible to the built-in InnoDB in MySQL 5.1 and prior releases, omit innodb_file_format or set it to Antelope.

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 statement.

For more information about managing file formats, see Chapter 4, InnoDB File-Format Management.

In MySQL 5.5 and higher, you can change certain system configuration parameters without shutting down and restarting the server, as was necessary in MySQL 5.1 and lower. This increases uptime, and makes it easier to test and prototype new SQL and application code. The following sections explain these parameters.

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

When you truncate a table that is stored in a .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 table is dropped and re-created in a new .ibd file. This operation is much faster than deleting the rows one by one. The operating system can reuse the disk space, in contrast to tables within the InnoDB system tablespace, where only InnoDB can use the space after they are truncated. Physical backups can also be smaller, without big blocks of unused space in the middle of the system tablespace.

Previous versions of InnoDB would re-use the existing .ibd 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 TABLE statement is an arbitrary number.

To guard against ignored typos and syntax errors in SQL, or other unintended consequences of various combinations of operational modes and SQL statements, InnoDB provides a strict mode of operations. In this mode, InnoDB raises error conditions in certain cases, rather than issuing a warning and processing the specified statement (perhaps with unintended behavior). This is analogous to sql_mode in MySQL, which controls what SQL syntax MySQL accepts, and determines whether it silently ignores errors, or validates input syntax and data values. Since strict mode is relatively new, some statements that execute without errors with earlier versions of MySQL might generate errors unless you disable strict mode.

The setting of InnoDB strict mode affects the handling of syntax errors on the CREATE TABLE, ALTER TABLE and CREATE INDEX statements. The strict mode also enables a record size check, so that an INSERT or UPDATE never fails due to the record being too large for the selected page size.

We recommend running in strict mode when using the ROW_FORMAT and KEY_BLOCK_SIZE clauses on CREATE TABLE, ALTER TABLE, and CREATE INDEX statements. Without strict mode, InnoDB ignores conflicting clauses and creates the table or index, with only a warning in the message log. The resulting table might have different behavior than you intended, such as having no compression when you tried to create a compressed table. When InnoDB strict mode is on, such problems generate an immediate error and the table or index is not created, avoiding a troubleshooting session later.

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 or my.ini. You can also enable or disable InnoDB strict mode at run time with the statement 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, and the setting affects only that client.

The MySQL query optimizer uses estimated statistics about key distributions to choose the indexes for an execution plan, based on the relative selectivity of the index. Certain operations cause InnoDB to sample random pages from each index on a table to estimate the cardinality of the index. (This technique is known as random dives.) These operations include the ANALYZE TABLE statement, the SHOW TABLE STATUS statement, and accessing the table for the first time after a restart.

To give you control over the quality of the statistics estimate (and thus better information for the query optimizer), you can now change the number of sampled pages using the parameter innodb_stats_sample_pages. Previously, the number of sampled pages was always 8, which could be insufficient to produce an accurate estimate, leading to poor index choices by the query optimizer. This technique is especially important for large tables and tables used in joins. Unnecessary full table scans for such tables can be a substantial performance issue.

You can set the global parameter innodb_stats_sample_pages, at run time. The default value for this parameter is 8, preserving the same behavior as in past releases.

To disable the cardinality estimation for metadata statements such as SHOW TABLE STATUS, execute the statement SET GLOBAL innodb_stats_on_metadata=OFF (or 0). The ability to set this option dynamically is also relatively new.

All InnoDB tables are opened, and the statistics are re-estimated for all associated indexes, when the mysql client starts if the auto-rehash setting is set on (the default). To improve the start up time of the mysql client, you can turn auto-rehash off. The auto-rehash feature enables automatic name completion of database, table, and column names for interactive users.

Whatever value of innodb_stats_sample_pages works best for a system, set the option and leave it at that value. Choose a value that results in reasonably accurate estimates for all tables in your database without requiring excessive I/O. Because the statistics are automatically recalculated at various times other than on execution of ANALYZE TABLE, it does not make sense to increase the index sample size, run ANALYZE TABLE, then decrease sample size again. The more accurate statistics calculated by ANALYZE running with a high value of innodb_stats_sample_pages can be wiped away later.

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

For optimal performance with DML statements, InnoDB requires an index to exist on foreign key columns, so that UPDATE and DELETE operations on a parent table can easily check whether corresponding rows exist in the child table. MySQL creates or drops such indexes automatically when needed, as a side-effect of CREATE TABLE, CREATE INDEX, and ALTER TABLE statements.

When you drop an index, InnoDB checks whether the index is not used for checking a foreign key constraint. It is still OK to drop the index if there is another index that can be used to enforce the same constraint. InnoDB prevents you from dropping the last index that can enforce a particular referential constraint.

The message that reports this error condition is:

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

This message is friendlier than the earlier message it replaces:

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

A similar change in error reporting applies to an attempt to drop the primary key index. For tables without an explicit PRIMARY KEY, InnoDB creates an implicit clustered index using the first columns of the table that are declared UNIQUE and NOT NULL. When you drop such an index, InnoDB automatically copies the table and rebuilds the index using a different UNIQUE NOT NULL group of columns or a system-generated key. Since this operation changes the primary key, it uses the slow method of copying the table and re-creating the index, rather than the Fast Index Creation technique from Section 2.3, “Implementation Details of Fast Index Creation”.

Previously, an attempt to drop an implicit clustered index (the first UNIQUE NOT NULL index) failed if the table did not contain a PRIMARY KEY:

ERROR 42000: This table type requires a primary key

The statement SHOW ENGINE INNODB MUTEX displays information about InnoDB mutexes and rw-locks. Although this information is useful for tuning on multi-core systems, the amount of output can be overwhelming on systems with a big buffer pool. There is one mutex and one rw-lock in each 16K buffer pool block, and there are 65,536 blocks per gigabyte. It is unlikely that a single block mutex or rw-lock from the buffer pool could become a performance bottleneck.

SHOW ENGINE INNODB MUTEX now skips the mutexes and rw-locks of buffer pool blocks. It also does not list any mutexes or rw-locks that have never been waited on (os_waits=0). Thus, SHOW ENGINE INNODB MUTEX only displays information about mutexes and rw-locks outside of the buffer pool that have caused 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 I/O request issued in anticipation that a page will be used in the near future. Knowing how many pages are read through this read-ahead mechanism, and how many of them are evicted from the buffer pool without ever being accessed, can be useful to help fine-tune the parameter innodb_read_ahead_threshold.

SHOW ENGINE INNODB STATUS output displays the global status variables Innodb_buffer_pool_read_ahead and Innodb_buffer_pool_read_ahead_evicted. These variables indicate the number of pages brought into the buffer pool by read-ahead requests, and the number of such pages evicted from the buffer pool without ever being accessed respectively. These counters provide global values since the last server restart.

SHOW ENGINE INNODB INNODB STATUS also shows the rate at which the read-ahead pages are read in and the rate at which such pages are evicted without being accessed. The per-second averages are based on the statistics collected since the last invocation of SHOW ENGINE INNODB INNODB STATUS and are displayed in the BUFFER POOL AND MEMORY section of the output.

Since the InnoDB read-ahead mechanism has been simplified to remove random read-ahead, the status variables Innodb_buffer_pool_read_ahead_rnd and Innodb_buffer_pool_read_ahead_seq are no longer part of the SHOW ENGINE INNODB STATUS output.