Since MySQL version 4.1, InnoDB has provided two options for how tables are stored on disk. You can choose to create a new table and its indexes in the shared system tablespace (corresponding to the set of files named ibdata files), along with other internal InnoDB system information. Or, you can chose to use a separate file (an .ibd file) to store a new table and its indexes.

The tablespace style used for new tables is determined by the setting of the configuration parameter innodb_file_per_table at the time a table is created. Previously, the only way to set this parameter was in the MySQL option file (my.cnf or my.ini), and changing it required shutting down and restarting the server. In InnoDB 1.1, the configuration parameter innodb_file_per_table is dynamic, and can be set ON or OFF using the SET GLOBAL statement. The default setting is OFF, so new tables and indexes are created in the system tablespace. Dynamically changing the value of this parameter requires the SUPER privilege and immediately affects the operation of all connections.

Tables created when innodb_file_per_table is disabled cannot use the new compression capability, or use the new row format DYNAMIC. Tables created when innodb_file_per_table is enabled can use those new features, and each table and its indexes are stored in a new .ibd file.

The ability to change the setting of innodb_file_per_table dynamically is useful for testing. As noted above, the parameter innodb_file_format is also dynamic, and must be set to “Barracuda” to create new compressed tables, or tables that use the new row format DYNAMIC. Since both parameters are dynamic, it is easy to experiment with these table formats without a system shutdown and restart.

Note that InnoDB can add and drop a table's secondary indexes without re-creating the table, but must recreate the table when you change the clustered (primary key) index (see Section 13.7.2, “Fast Index Creation in the InnoDB Storage Engine”). When a table is recreated as a result of creating or dropping an index, the table and its indexes are stored in the shared system tablespace or in a separate .ibd file just as if it were created using a CREATE TABLE statement (and depending on the setting of innodb_file_per_table). When an index is created without rebuilding the table, the index is stored in the same file as the clustered index, regardless of the setting of innodb_file_per_table.

As noted in Section 13.7.8.5, “Controlling Optimizer Statistics Estimation”, you can control how InnoDB gathers information about the number of distinct values in an index key. A related parameter, innodb_stats_on_metadata, has existed since MySQL release 5.1.17 to control whether or not InnoDB performs statistics gathering when metadata statements are executed. See Section 13.6.4, “InnoDB Startup Options and System Variables” for details.

Beginning with release InnoDB 1.0.2, you can change the setting of innodb_stats_on_metadata dynamically at runtime with the statement SET GLOBAL innodb_stats_on_metadata=mode, where mode is either ON or OFF (or 1 or 0). Changing this setting requires the SUPER privilege and immediately affects the operation of all connections.

When a transaction is waiting for a resource, it will wait for the resource to become free, or stop waiting and return with the error

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

The length of time a transaction will wait for a resource before “giving up” is determined by the value of the configuration parameter innodb_lock_wait_timeout. The default setting for this parameter is 50 seconds. The minimum setting is 1 second, and values above 100,000,000 disable the timeout, so a transaction will wait “forever”. Following a timeout, the SQL statement that was executing will be rolled back. (In MySQL 5.0.12 and earlier, the transaction rolled back.) The user application may try the statement again (usually after waiting for a while), or rollback the entire transaction and restart.

Before InnoDB 1.0.2, the only way to set this parameter was in the MySQL option file (my.cnf or my.ini), and changing it required shutting down and restarting the server. Beginning with InnoDB 1.0.2, the configuration parameter innodb_lock_wait_timeout can be set at runtime with the SET GLOBAL or SET SESSION statement. 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_lock_wait_timeout, which affects only that client.

As described in Section 13.7.7.5, “Controlling Adaptive Hash Indexing”, it may be desirable, depending on your workload, to dynamically enable or disable the adaptive hash indexing scheme InnoDB uses to improve query performance.

Version 5.1.24 of MySQL introduced the start-up option innodb_adaptive_hash_index that allows the adaptive hash index to be disabled. It is enabled by default. Starting with InnoDB 1.0.3, the parameter can be modified by the SET GLOBAL statement, without restarting the server. Changing the setting requires the SUPER privilege.

Disabling the adaptive hash index will empty the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that have been using the hash table will access the index B-trees directly instead of attempting to utilize the hash index. When the adaptive hash index is enabled, the hash table will be populated during normal operation.