Table of Contents
TRUNCATE TABLE Reclaims SpaceSHOW ENGINE INNODB MUTEXThis 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 change 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:
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 choose 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.
Beginning with the InnoDB Plugin, the configuration parameter
innodb_file_per_table is dynamic, and can be set
ON or OFF using the
SET GLOBAL command. 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 will be 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 and
the downgrade procedure described in
Chapter 11, Downgrading from the InnoDB Plugin without a system shutdown
and restart.
Note that the InnoDB Plugin 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 Chapter 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 will be stored in the shared system
tablespace or in its own .ibd file just as if it were created
using a CREATE TABLE command (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 8.6, “Controlling Optimizer Statistics Estimation”,
the InnoDB Plugin allows you to control the way in which
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 the MySQL
manual on
InnoDB
Startup Options and System Variables for details.
Beginning with release 1.0.2 of the InnoDB Plugin, it is
possible to change the setting of innodb_stats_on_metadata
dynamically at runtime with the command SET GLOBAL
innodb_stats_on_metadata=,
where modemodeON 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 Plugin 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 the
InnoDB Plugin 1.0.2, the configuration parameter
innodb_lock_wait_timeout can be set at runtime with the
SET GLOBAL or SET SESSION
commands. 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 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 Plugin 1.0.3, the parameter can be modified by the
SET GLOBAL command, 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.
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
.ibd 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
.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 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=,
where modemodeON 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 using 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 using 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:
small values like 1 or 2 can result in very inaccurate estimates of cardinality
values much larger than 8 (say, 100), can cause a big
slowdown in the time it takes to open a table or execute
SHOW TABLE STATUS.
the optimizer may choose very different query plans based on different estimates of index selectivity
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
, and then
newtable SELECT * FROM
oldtableDROP 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.