14.2.5.5. Dynamic Control of System Configuration Parameters

Since MySQL version 4.1, InnoDB has provided two alternatives for how tables are stored on disk. You can create a new table and its indexes in the shared system tablespace, physically stored in the ibdata files. Or, you can store a new table and its indexes in a separate tablespace (a .ibd file). The storage layout for each InnoDB table is determined by the the configuration parameter innodb_file_per_table at the time the table is created.

In MySQL 5.5 and higher, the configuration parameter innodb_file_per_table is dynamic, and can be set ON or OFF using the SET GLOBAL. Previously, the only way to set this parameter was in the MySQL configuration file (my.cnf or my.ini), and changing it required shutting down and restarting the server.

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 enabled can use the Barracuda file format, and TRUNCATE returns the disk space for those tables to the operating system. The Barracuda file format in turn enables features such as table compression and the DYNAMIC row format. Tables created when innodb_file_per_table is off cannot use these features. To take advantage of those features for an existing table, you can turn on the file-per-table setting and run ALTER TABLE t ENGINE=INNODB for that table.

When you redefine the primary key for an InnoDB table, the table is re-created using the current settings for innodb_file_per_table and innodb_file_format. This behavior does not apply when adding or dropping InnoDB secondary indexes, as explained in Fast Index Creation in the InnoDB Storage Engine. When a secondary index is created without rebuilding the table, the index is stored in the same file as the table data, regardless of the current innodb_file_per_table setting.

In MySQL 5.5 and higher, you can change the setting of innodb_stats_on_metadata dynamically at runtime, to control whether or not InnoDB performs statistics gathering when metadata statements are executed. To change the setting, issue 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.

This setting is related to the feature described in Section 14.2.5.8, "Controlling Optimizer Statistics Estimation".

The length of time a transaction waits for a resource, before giving up and rolling back the statement, is determined by the value of the configuration parameter innodb_lock_wait_timeout. (In MySQL 5.0.12 and earlier, the entire transaction was rolled back, not just the statement.) Your application can try the statement again (usually after waiting for a while), or roll back the entire transaction and restart.

The error returned when the timeout period is exceeded is:

ERROR HY000: Lock wait timeout exceeded; try restarting transaction

In MySQL 5.5 and higher, 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.

In MySQL 5.1 and earlier, the only way to set this parameter was in the MySQL configuration file (my.cnf or my.ini), and changing it required shutting down and restarting the server.

As described in Section 14.2.4.2.14, "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.

The configuration option innodb_adaptive_hash_index lets you disable the adaptive hash index. It is enabled by default. You can modify this parameter through the SET GLOBAL statement, without restarting the server. Changing the setting requires the SUPER privilege.

Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.