|
Chapter 13. Storage Engines
MySQL supports several storage engines that act as handlers for
different table types. MySQL storage engines include both those that
handle transaction-safe tables and those that handle
nontransaction-safe tables.
As of MySQL 5.1, MySQL Server uses a pluggable storage engine
architecture that allows storage engines to be loaded into and
unloaded from a running MySQL server.
Note
Prior to MySQL 5.1.38, the pluggable storage engine architecture
is supported on Unix platforms only and pluggable storage engines
are not supported on Windows.
To determine which storage engines your server supports by using the
SHOW ENGINES statement. The value in
the Support column indicates whether an engine
can be used. A value of YES ,
NO , or DEFAULT indicates that
an engine is available, not available, or avaiable and current set
as the default storage engine.
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
XA: NO
Savepoints: NO
...
This chapter describes each of the MySQL storage engines except for
NDBCLUSTER , which is covered in
Chapter 17, MySQL Cluster NDB 6.X/7.X. It also contains a description of
the pluggable storage engine architecture (see
Section 13.4, “Overview of MySQL Storage Engine Architecture”).
For information about storage engine support offered in commercial
MySQL Server binaries, see
MySQL
Enterprise Server 5.1, on the MySQL Web site.
The storage engines available might depend on which edition of
Enterprise Server you are using.
For answers to some commonly asked questions about MySQL storage
engines, see Section A.2, “MySQL 5.1 FAQ — Storage Engines”.
MySQL 5.1 supported storage engines
MyISAM
— The default MySQL storage engine and the one that is
used the most in Web, data warehousing, and other application
environments. MyISAM is supported in all
MySQL configurations, and is the default storage engine unless
you have configured MySQL to use a different one by default.
InnoDB —
A transaction-safe (ACID compliant) storage engine for MySQL
that has commit, rollback, and crash-recovery capabilities to
protect user data. InnoDB row-level locking
(without escalation to coarser granularity locks) and
Oracle-style consistent nonlocking reads increase multi-user
concurrency and performance. InnoDB stores
user data in clustered indexes to reduce I/O for common queries
based on primary keys. To maintain data integrity,
InnoDB also supports FOREIGN
KEY referential-integrity constraints.
Memory
— Stores all data in RAM for extremely fast access in
environments that require quick lookups of reference and other
like data. This engine was formerly known as the
HEAP engine.
Merge
— Allows a MySQL DBA or developer to logically group a
series of identical MyISAM tables and
reference them as one object. Good for VLDB environments such as
data warehousing.
Archive
— Provides the perfect solution for storing and retrieving
large amounts of seldom-referenced historical, archived, or
security audit information.
Federated
— Offers the ability to link separate MySQL servers to
create one logical database from many physical servers. Very
good for distributed or data mart environments.
NDBCLUSTER
(also known as NDB ) — This
clustered database engine is particularly suited for
applications that require the highest possible degree of uptime
and availability.
Note
The NDBCLUSTER storage engine is
not supported in standard MySQL 5.1 releases. MySQL Cluster
users wishing to upgrade from MySQL 5.0 should instead migrate
to MySQL Cluster NDB 6.2 or 6.3; these are based on MySQL 5.1
but contain the latest improvements and fixes for
NDBCLUSTER .
CSV
— The CSV storage engine stores data in text files using
comma-separated values format. You can use the CSV engine to
easily exchange data between other software and applications
that can import and export in CSV format.
Blackhole
— The Blackhole storage engine accepts but does not store
data and retrievals always return an empty set. The
functionality can be used in distributed database design where
data is automatically replicated, but not stored locally.
Example
— The Example storage engine is “stub” engine
that does nothing. You can create tables with this engine, but
no data can be stored in them or retrieved from them. The
purpose of this engine is to serve as an example in the MySQL
source code that illustrates how to begin writing new storage
engines. As such, it is primarily of interest to developers.
It is important to remember that you are not restricted to using the
same storage engine for an entire server or schema: you can use a
different storage engine for each table in your schema.
Choosing a Storage Engine
The various storage engines provided with MySQL are designed with
different use-cases in mind. To use the pluggable storage
architecture effectively, it is good to have an idea of the
advantages and disadvantages of the various storage engines. The
following table provides an overview of some storage engines
provided with MySQL:
Table 13.1. Storage Engine Features Feature | MyISAM | Memory | InnoDB | Archive | NDB |
---|
Storage limits | 256TB | RAM | 64TB | None | 384EB | Transactions | No | No | Yes | No | Yes | Locking granularity | Table | Table | Row | Row | Row | MVCC | No | No | Yes | No | No | Geospatial datatype support | Yes | No | Yes | Yes | Yes | Geospatial indexing support | Yes | No | No | No | No | B-tree indexes | Yes | Yes | Yes | No | Yes | Hash indexes | No | Yes | No | No | Yes | Full-text search indexes | Yes | No | No | No | No | Clustered indexes | No | No | Yes | No | No | Data caches | No | N/A | Yes | No | Yes | Index caches | Yes | N/A | Yes | No | Yes | Compressed data | Yes[a] | No | Yes[b] | Yes | No | Encrypted data[c] | Yes | Yes | Yes | Yes | Yes | Cluster database support | No | No | No | No | Yes | Replication support[d] | Yes | Yes | Yes | Yes | Yes | Foreign key support | No | No | Yes | No | No | Backup / point-in-time recovery[e] | Yes | Yes | Yes | Yes | Yes | Query cache support | Yes | Yes | Yes | Yes | Yes | Update statistics for data dictionary | Yes | Yes | Yes | Yes | Yes |
13.1. Comparing Transaction and Nontransaction Engines
Transaction-safe tables (TSTs) have several advantages over
nontransaction-safe tables (NTSTs):
They are safer. Even if MySQL crashes or you get hardware
problems, you can get your data back, either by automatic
recovery or from a backup plus the transaction log.
You can combine many statements and accept them all at the
same time with the COMMIT
statement (if autocommit is disabled).
You can execute
ROLLBACK to
ignore your changes (if autocommit is disabled).
If an update fails, all of your changes are reverted. (With
nontransaction-safe tables, all changes that have taken place
are permanent.)
Transaction-safe storage engines can provide better
concurrency for tables that get many updates concurrently with
reads.
You can combine transaction-safe and nontransaction-safe tables in
the same statements to get the best of both worlds. However,
although MySQL supports several transaction-safe storage engines,
for best results, you should not mix different storage engines
within a transaction with autocommit disabled. For example, if you
do this, changes to nontransaction-safe tables still are committed
immediately and cannot be rolled back. For information about this
and other problems that can occur in transactions that use mixed
storage engines, see Section 12.4.1, “START TRANSACTION ,
COMMIT , and
ROLLBACK Syntax”.
Nontransaction-safe tables have several advantages of their own,
all of which occur because there is no transaction overhead:
13.2. Other Storage Engines
Other storage engines may be available from third parties and
community members that have used the Custom Storage Engine
interface.
You can find more information on the list of third party storage
engines on the
MySQL
Forge Storage Engines page.
Note
Third party engines are not supported by MySQL. For further
information, documentation, installation guides, bug reporting
or for any help or assistance with these engines, please contact
the developer of the engine directly.
Third party engines that are known to be available include the
following; please see the MySQL Forge links provided for more
information:
PrimeBase
XT (PBXT) — PBXT has been designed
for modern, web-based, high concurrency environments.
RitmarkFS
— RitmarkFS allows you to access and manipulate the file
system using SQL queries. RitmarkFS also supports file system
replication and directory change tracking.
Distributed
Data Engine — The Distributed Data
Engine is an Open Source project that is dedicated to provide
a Storage Engine for distributed data according to workload
statistics.
mdbtools
— A pluggable storage engine that allows read-only
access to Microsoft Access .mdb database
files.
solidDB
for MySQL — solidDB Storage Engine
for MySQL is an open source, transactional storage engine for
MySQL Server. It is designed for mission-critical
implementations that require a robust, transactional database.
solidDB Storage Engine for MySQL is a multi-threaded storage
engine that supports full ACID compliance with all expected
transaction isolation levels, row-level locking, and
Multi-Version Concurrency Control (MVCC) with nonblocking
reads and writes.
BLOB
Streaming Engine (MyBS) — The
Scalable BLOB Streaming infrastructure for MySQL will
transform MySQL into a scalable media server capable of
streaming pictures, films, MP3 files and other binary and text
objects (BLOBs) directly in and out of the database.
The
InnoDB Plugin —
The InnoDB Plugin for MySQL permits users
to replace the “built-in” version of
InnoDB in MySQL 5.1 with a new release of
InnoDB that offers new features, improved
performance, enhanced reliability and new capabilities for
flexibility and ease of use. Among the new features of the
InnoDB Plugin are “Fast index
creation”, table and index compression, file format
management and new INFORMATION_SCHEMA
tables.
For more information on developing a customer storage engine that
can be used with the Pluggable Storage Engine Architecture, see
Writing
a Custom Storage Engine on
MySQL Forge.
13.3. Setting the Storage Engine
When you create a new table, you can specify which storage engine
to use by adding an ENGINE table option to the
CREATE TABLE statement:
CREATE TABLE t (i INT) ENGINE = INNODB;
If you omit the ENGINE option, the default
storage engine is used. Normally, this is
MyISAM , but you can change it by using the
--default-storage-engine server
startup option, or by setting the
default-storage-engine option in the
my.cnf configuration file.
You can set the default storage engine to be used during the
current session by setting the
storage_engine variable:
SET storage_engine=MYISAM;
When MySQL is installed on Windows using the MySQL Configuration
Wizard, the InnoDB storage engine can be
selected as the default instead of MyISAM . See
Section 2.5.4.5, “The Database Usage Dialog”.
To convert a table from one storage engine to another, use an
ALTER TABLE statement that
indicates the new engine:
ALTER TABLE t ENGINE = MYISAM;
See Section 12.1.17, “CREATE TABLE Syntax”, and
Section 12.1.7, “ALTER TABLE Syntax”.
If you try to use a storage engine that is not compiled in or that
is compiled in but deactivated, MySQL instead creates a table
using the default storage engine, usually
MyISAM . This behavior is convenient when you
want to copy tables between MySQL servers that support different
storage engines. (For example, in a replication setup, perhaps
your master server supports transactional storage engines for
increased safety, but the slave servers use only nontransactional
storage engines for greater speed.)
This automatic substitution of the default storage engine for
unavailable engines can be confusing for new MySQL users. A
warning is generated whenever a storage engine is automatically
changed.
For new tables, MySQL always creates an .frm
file to hold the table and column definitions. The table's index
and data may be stored in one or more other files, depending on
the storage engine. The server creates the
.frm file above the storage engine level.
Individual storage engines create any additional files required
for the tables that they manage. If a table name contains special
characters, the names for the table files contain encoded versions
of those characters as described in
Section 8.2.3, “Mapping of Identifiers to File Names”.
A database may contain tables of different types. That is, tables
need not all be created with the same storage engine.
13.4. Overview of MySQL Storage Engine Architecture
The MySQL pluggable storage engine architecture allows a database
professional to select a specialized storage engine for a
particular application need while being completely shielded from
the need to manage any specific application coding requirements.
The MySQL server architecture isolates the application programmer
and DBA from all of the low-level implementation details at the
storage level, providing a consistent and easy application model
and API. Thus, although there are different capabilities across
different storage engines, the application is shielded from these
differences.
The MySQL pluggable storage engine architecture is shown in
Figure 13.1, “MySQL Architecture with Pluggable Storage Engines”.
The pluggable storage engine architecture provides a standard set
of management and support services that are common among all
underlying storage engines. The storage engines themselves are the
components of the database server that actually perform actions on
the underlying data that is maintained at the physical server
level.
This efficient and modular architecture provides huge benefits for
those wishing to specifically target a particular application need
— such as data warehousing, transaction processing, or high
availability situations — while enjoying the advantage of
utilizing a set of interfaces and services that are independent of
any one storage engine.
The application programmer and DBA interact with the MySQL
database through Connector APIs and service layers that are above
the storage engines. If application changes bring about
requirements that demand the underlying storage engine change, or
that one or more additional storage engines be added to support
new needs, no significant coding or process changes are required
to make things work. The MySQL server architecture shields the
application from the underlying complexity of the storage engine
by presenting a consistent and easy-to-use API that applies across
storage engines.
13.4.1. Pluggable Storage Engine Architecture
As of MySQL 5.1, MySQL Server uses a pluggable storage engine
architecture that allows storage engines to be loaded into and
unloaded from a running MySQL server.
Note
Prior to MySQL 5.1.38, the pluggable storage engine
architecture is supported on Unix platforms only and pluggable
storage engines are not supported on Windows.
Plugging in a Storage Engine
Before a storage engine can be used, the storage engine plugin
shared library must be loaded into MySQL using the
INSTALL PLUGIN statement. For
example, if the EXAMPLE engine plugin is
named ha_example and the shared library is
named ha_example.so , you load it with the
following statement:
mysql> INSTALL PLUGIN ha_example SONAME 'ha_example.so';
To install a pluggable storage engine, the plugin file must be
located in the MySQL plugin directory, and the user issuing the
INSTALL PLUGIN statement must
have INSERT privilege for the
mysql.plugin table.
The shared library must be located in the MySQL server plugin
directory, the location of which is given by the
plugin_dir system variable.
Unplugging a Storage Engine
To unplug a storage engine, use the
UNINSTALL PLUGIN statement:
mysql> UNINSTALL PLUGIN ha_example;
If you unplug a storage engine that is needed by existing
tables, those tables become inaccessible, but will still be
present on disk (where applicable). Ensure that there are no
tables using a storage engine before you unplug the storage
engine.
13.4.2. The Common Database Server Layer
A MySQL pluggable storage engine is the component in the MySQL
database server that is responsible for performing the actual
data I/O operations for a database as well as enabling and
enforcing certain feature sets that target a specific
application need. A major benefit of using specific storage
engines is that you are only delivered the features needed for a
particular application, and therefore you have less system
overhead in the database, with the end result being more
efficient and higher database performance. This is one of the
reasons that MySQL has always been known to have such high
performance, matching or beating proprietary monolithic
databases in industry standard benchmarks.
From a technical perspective, what are some of the unique
supporting infrastructure components that are in a storage
engine? Some of the key feature differentiations include:
Concurrency — some applications
have more granular lock requirements (such as row-level
locks) than others. Choosing the right locking strategy can
reduce overhead and therefore improve overall performance.
This area also includes support for capabilities such as
multi-version concurrency control or “snapshot”
read.
Transaction Support — Not every
application needs transactions, but for those that do, there
are very well defined requirements such as ACID compliance
and more.
Referential Integrity — The need
to have the server enforce relational database referential
integrity through DDL defined foreign keys.
Physical Storage — This involves
everything from the overall page size for tables and indexes
as well as the format used for storing data to physical
disk.
Index Support — Different
application scenarios tend to benefit from different index
strategies. Each storage engine generally has its own
indexing methods, although some (such as B-tree indexes) are
common to nearly all engines.
Memory Caches — Different
applications respond better to some memory caching
strategies than others, so although some memory caches are
common to all storage engines (such as those used for user
connections or MySQL's high-speed Query Cache), others are
uniquely defined only when a particular storage engine is
put in play.
Performance Aids — This includes
multiple I/O threads for parallel operations, thread
concurrency, database checkpointing, bulk insert handling,
and more.
Miscellaneous Target Features —
This may include support for geospatial operations, security
restrictions for certain data manipulation operations, and
other similar features.
Each set of the pluggable storage engine infrastructure
components are designed to offer a selective set of benefits for
a particular application. Conversely, avoiding a set of
component features helps reduce unnecessary overhead. It stands
to reason that understanding a particular application's set of
requirements and selecting the proper MySQL storage engine can
have a dramatic impact on overall system efficiency and
performance.
13.5. The MyISAM Storage Engine
MyISAM is the default storage engine. It is based
on the older ISAM code but has many useful
extensions. (Note that MySQL 5.1 does
not support ISAM .)
Table 13.2. MyISAM Storage Engine
Features Storage limits | 256TB | Transactions | No | Locking granularity | Table | MVCC | No | Geospatial datatype support | Yes | Geospatial indexing support | Yes | B-tree indexes | Yes | Hash indexes | No | Full-text search indexes | Yes | Clustered indexes | No | Data caches | No | Index caches | Yes | Compressed data | Yes[a] | Encrypted data[b] | Yes | Cluster database support | No | Replication support[c] | Yes | Foreign key support | No | Backup / point-in-time recovery[d] | Yes | Query cache support | Yes | Update statistics for data dictionary | Yes | | |
Each MyISAM table is stored on disk in three
files. The files have names that begin with the table name and have
an extension to indicate the file type. An .frm
file stores the table format. The data file has an
.MYD (MYData ) extension. The
index file has an .MYI
(MYIndex ) extension.
To specify explicitly that you want a MyISAM
table, indicate that with an ENGINE table option:
CREATE TABLE t (i INT) ENGINE = MYISAM;
Normally, it is unnecessary to use ENGINE to
specify the MyISAM storage engine.
MyISAM is the default engine unless the default
has been changed. To ensure that MyISAM is used
in situations where the default might have been changed, include the
ENGINE option explicitly.
You can check or repair MyISAM tables with the
mysqlcheck client or myisamchk
utility. You can also compress MyISAM tables with
myisampack to take up much less space. See
Section 4.5.3, “mysqlcheck — A Table Maintenance Program”, Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”, and
Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
MyISAM tables have the following characteristics:
All data values are stored with the low byte first. This makes
the data machine and operating system independent. The only
requirements for binary portability are that the machine uses
two's-complement signed integers and IEEE floating-point format.
These requirements are widely used among mainstream machines.
Binary compatibility might not be applicable to embedded
systems, which sometimes have peculiar processors.
There is no significant speed penalty for storing data low byte
first; the bytes in a table row normally are unaligned and it
takes little more processing to read an unaligned byte in order
than in reverse order. Also, the code in the server that fetches
column values is not time critical compared to other code.
All numeric key values are stored with the high byte first to
allow better index compression.
Large files (up to 63-bit file length) are supported on file
systems and operating systems that support large files.
There is a limit of 232 (~4.295E+09)
rows in a MyISAM table. If you build MySQL
with the --with-big-tables
option, the row limitation is increased to
(232)2
(1.844E+19) rows. See Section 2.3.2, “Typical configure Options”.
Binary distributions for Unix and Linux are built with this
option.
The maximum number of indexes per MyISAM
table is 64. This can be changed by recompiling. Beginning with
MySQL 5.1.4, you can configure the build by invoking
configure with the
--with-max-indexes=N
option, where N is the maximum number
of indexes to permit per MyISAM table.
N must be less than or equal to 128.
Before MySQL 5.1.4, you must change the source.
The maximum number of columns per index is 16.
The maximum key length is 1000 bytes. This can also be changed
by changing the source and recompiling. For the case of a key
longer than 250 bytes, a larger key block size than the default
of 1024 bytes is used.
When rows are inserted in sorted order (as when you are using an
AUTO_INCREMENT column), the index tree is
split so that the high node only contains one key. This improves
space utilization in the index tree.
Internal handling of one AUTO_INCREMENT
column per table is supported. MyISAM
automatically updates this column for
INSERT and
UPDATE operations. This makes
AUTO_INCREMENT columns faster (at least 10%).
Values at the top of the sequence are not reused after being
deleted. (When an AUTO_INCREMENT column is
defined as the last column of a multiple-column index, reuse of
values deleted from the top of a sequence does occur.) The
AUTO_INCREMENT value can be reset with
ALTER TABLE or
myisamchk.
Dynamic-sized rows are much less fragmented when mixing deletes
with updates and inserts. This is done by automatically
combining adjacent deleted blocks and by extending blocks if the
next block is deleted.
MyISAM supports concurrent inserts: If a
table has no free blocks in the middle of the data file, you can
INSERT new rows into it at the
same time that other threads are reading from the table. A free
block can occur as a result of deleting rows or an update of a
dynamic length row with more data than its current contents.
When all free blocks are used up (filled in), future inserts
become concurrent again. See
Section 7.3.3, “Concurrent Inserts”.
You can put the data file and index file in different
directories on different physical devices to get more speed with
the DATA DIRECTORY and INDEX
DIRECTORY table options to CREATE
TABLE . See Section 12.1.17, “CREATE TABLE Syntax”.
BLOB and
TEXT columns can be indexed.
NULL values are allowed in indexed columns.
This takes 0–1 bytes per key.
Each character column can have a different character set. See
Section 9.1, “Character Set Support”.
There is a flag in the MyISAM index file that
indicates whether the table was closed correctly. If
mysqld is started with the
--myisam-recover option,
MyISAM tables are automatically checked when
opened, and are repaired if the table wasn't closed properly.
myisamchk marks tables as checked if you run
it with the --update-state
option. myisamchk --fast checks only those
tables that don't have this mark.
myisamchk --analyze stores statistics for
portions of keys, as well as for entire keys.
myisampack can pack
BLOB and
VARCHAR columns.
MyISAM also supports the following features:
Support for a true VARCHAR type;
a VARCHAR column starts with a
length stored in one or two bytes.
Tables with VARCHAR columns may
have fixed or dynamic row length.
The sum of the lengths of the
VARCHAR and
CHAR columns in a table may be up
to 64KB.
Arbitrary length UNIQUE constraints.
Additional Resources
13.5.1. MyISAM Startup Options
The following options to mysqld can be used to
change the behavior of MyISAM tables. For
additional information, see Section 5.1.2, “Server Command Options”.
Table 13.3. MyISAM Option/Variable Reference
The following system variables affect the behavior of
MyISAM tables. For additional information, see
Section 5.1.4, “Server System Variables”.
Automatic recovery is activated if you start
mysqld with the
--myisam-recover option. In this
case, when the server opens a MyISAM table, it
checks whether the table is marked as crashed or whether the open
count variable for the table is not 0 and you are running the
server with external locking disabled. If either of these
conditions is true, the following happens:
The server checks the table for errors.
If the server finds an error, it tries to do a fast table
repair (with sorting and without re-creating the data file).
If the repair fails because of an error in the data file (for
example, a duplicate-key error), the server tries again, this
time re-creating the data file.
If the repair still fails, the server tries once more with the
old repair option method (write row by row without sorting).
This method should be able to repair any type of error and has
low disk space requirements.
MySQL Enterprise
Subscribers to MySQL Enterprise Monitor receive notification if
the --myisam-recover option has
not been set. For more information, see
http://www.mysql.com/products/enterprise/advisors.html.
If the recovery wouldn't be able to recover all rows from
previously completed statements and you didn't specify
FORCE in the value of the
--myisam-recover option, automatic
repair aborts with an error message in the error log:
Error: Couldn't repair table: test.g00pages
If you specify FORCE , a warning like this is
written instead:
Warning: Found 344 of 354 rows when repairing ./test/g00pages
Note that if the automatic recovery value includes
BACKUP , the recovery process creates files with
names of the form
tbl_name-datetime .BAK .
You should have a cron script that
automatically moves these files from the database directories to
backup media.
13.5.2. Space Needed for Keys
MyISAM tables use B-tree indexes. You can
roughly calculate the size for the index file as
(key_length+4)/0.67 , summed over all keys. This
is for the worst case when all keys are inserted in sorted order
and the table doesn't have any compressed keys.
String indexes are space compressed. If the first index part is a
string, it is also prefix compressed. Space compression makes the
index file smaller than the worst-case figure if a string column
has a lot of trailing space or is a
VARCHAR column that is not always
used to the full length. Prefix compression is used on keys that
start with a string. Prefix compression helps if there are many
strings with an identical prefix.
In MyISAM tables, you can also prefix compress
numbers by specifying the PACK_KEYS=1 table
option when you create the table. Numbers are stored with the high
byte first, so this helps when you have many integer keys that
have an identical prefix.
13.5.3. MyISAM Table Storage Formats
MyISAM supports three different storage
formats. Two of them, fixed and dynamic format, are chosen
automatically depending on the type of columns you are using. The
third, compressed format, can be created only with the
myisampack utility (see
Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”).
When you use CREATE TABLE or
ALTER TABLE for a table that has no
BLOB or
TEXT columns, you can force the
table format to FIXED or
DYNAMIC with the ROW_FORMAT
table option.
See Section 12.1.17, “CREATE TABLE Syntax”, for information about
ROW_FORMAT .
You can decompress (unpack) compressed MyISAM
tables using myisamchk
--unpack ; see
Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”, for more information.
13.5.3.1. Static (Fixed-Length) Table Characteristics
Static format is the default for MyISAM
tables. It is used when the table contains no variable-length
columns (VARCHAR ,
VARBINARY ,
BLOB , or
TEXT ). Each row is stored using a
fixed number of bytes.
Of the three MyISAM storage formats, static
format is the simplest and most secure (least subject to
corruption). It is also the fastest of the on-disk formats due
to the ease with which rows in the data file can be found on
disk: To look up a row based on a row number in the index,
multiply the row number by the row length to calculate the row
position. Also, when scanning a table, it is very easy to read a
constant number of rows with each disk read operation.
The security is evidenced if your computer crashes while the
MySQL server is writing to a fixed-format
MyISAM file. In this case,
myisamchk can easily determine where each row
starts and ends, so it can usually reclaim all rows except the
partially written one. Note that MyISAM table
indexes can always be reconstructed based on the data rows.
Note
Fixed-length row format is only available for tables without
BLOB or
TEXT columns. Creating a table
with these columns with an explicit
ROW_FORMAT clause will not raise an error
or warning; the format specification will be ignored.
Static-format tables have these characteristics:
CHAR and
VARCHAR columns are
space-padded to the specified column width, although the
column type is not altered.
BINARY and
VARBINARY columns are padded
with 0x00 bytes to the column width.
Very quick.
Easy to cache.
Easy to reconstruct after a crash, because rows are located
in fixed positions.
Reorganization is unnecessary unless you delete a huge
number of rows and want to return free disk space to the
operating system. To do this, use
OPTIMIZE TABLE or
myisamchk -r.
Usually require more disk space than dynamic-format tables.
13.5.3.2. Dynamic Table Characteristics
Dynamic storage format is used if a MyISAM
table contains any variable-length columns
(VARCHAR ,
VARBINARY ,
BLOB , or
TEXT ), or if the table was
created with the ROW_FORMAT=DYNAMIC table
option.
Dynamic format is a little more complex than static format
because each row has a header that indicates how long it is. A
row can become fragmented (stored in noncontiguous pieces) when
it is made longer as a result of an update.
You can use OPTIMIZE TABLE or
myisamchk -r to defragment a table. If you
have fixed-length columns that you access or change frequently
in a table that also contains some variable-length columns, it
might be a good idea to move the variable-length columns to
other tables just to avoid fragmentation.
Dynamic-format tables have these characteristics:
All string columns are dynamic except those with a length
less than four.
Each row is preceded by a bitmap that indicates which
columns contain the empty string (for string columns) or
zero (for numeric columns). Note that this does not include
columns that contain NULL values. If a
string column has a length of zero after trailing space
removal, or a numeric column has a value of zero, it is
marked in the bitmap and not saved to disk. Nonempty strings
are saved as a length byte plus the string contents.
Much less disk space usually is required than for
fixed-length tables.
Each row uses only as much space as is required. However, if
a row becomes larger, it is split into as many pieces as are
required, resulting in row fragmentation. For example, if
you update a row with information that extends the row
length, the row becomes fragmented. In this case, you may
have to run OPTIMIZE TABLE or
myisamchk -r from time to time to improve
performance. Use myisamchk -ei to obtain
table statistics.
More difficult than static-format tables to reconstruct
after a crash, because rows may be fragmented into many
pieces and links (fragments) may be missing.
The expected row length for dynamic-sized rows is calculated
using the following expression:
3
+ (number of columns + 7) / 8
+ (number of char columns )
+ (packed size of numeric columns )
+ (length of strings )
+ (number of NULL columns + 7) / 8
There is a penalty of 6 bytes for each link. A dynamic row
is linked whenever an update causes an enlargement of the
row. Each new link is at least 20 bytes, so the next
enlargement probably goes in the same link. If not, another
link is created. You can find the number of links using
myisamchk -ed. All links may be removed
with OPTIMIZE TABLE or
myisamchk -r.
13.5.3.3. Compressed Table Characteristics
Compressed storage format is a read-only format that is
generated with the myisampack tool.
Compressed tables can be uncompressed with
myisamchk.
Compressed tables have the following characteristics:
Compressed tables take very little disk space. This
minimizes disk usage, which is helpful when using slow disks
(such as CD-ROMs).
Each row is compressed separately, so there is very little
access overhead. The header for a row takes up one to three
bytes depending on the biggest row in the table. Each column
is compressed differently. There is usually a different
Huffman tree for each column. Some of the compression types
are:
Suffix space compression.
Prefix space compression.
Numbers with a value of zero are stored using one bit.
If values in an integer column have a small range, the
column is stored using the smallest possible type. For
example, a BIGINT column
(eight bytes) can be stored as a
TINYINT column (one byte)
if all its values are in the range from
-128 to 127 .
If a column has only a small set of possible values, the
data type is converted to
ENUM .
A column may use any combination of the preceding
compression types.
Can be used for fixed-length or dynamic-length rows.
Note
While a compressed table is read only, and you cannot
therefore update or add rows in the table, DDL (Data
Definition Language) operations are still valid. For example,
you may still use DROP to drop the table,
and TRUNCATE TABLE to empty the
table.
13.5.4. MyISAM Table Problems
The file format that MySQL uses to store data has been extensively
tested, but there are always circumstances that may cause database
tables to become corrupted. The following discussion describes how
this can happen and how to handle it.
13.5.4.1. Corrupted MyISAM Tables
Even though the MyISAM table format is very
reliable (all changes to a table made by an SQL statement are
written before the statement returns), you can still get
corrupted tables if any of the following events occur:
The mysqld process is killed in the
middle of a write.
An unexpected computer shutdown occurs (for example, the
computer is turned off).
Hardware failures.
You are using an external program (such as
myisamchk) to modify a table that is
being modified by the server at the same time.
A software bug in the MySQL or MyISAM
code.
Typical symptoms of a corrupt table are:
You get the following error while selecting data from the
table:
Incorrect key file for table: '...'. Try to repair it
Queries don't find rows in the table or return incomplete
results.
You can check the health of a MyISAM table
using the CHECK TABLE statement,
and repair a corrupted MyISAM table with
REPAIR TABLE . When
mysqld is not running, you can also check or
repair a table with the myisamchk command.
See Section 12.5.2.3, “CHECK TABLE Syntax”,
Section 12.5.2.6, “REPAIR TABLE Syntax”, and Section 4.6.3, “myisamchk — MyISAM Table-Maintenance Utility”.
If your tables become corrupted frequently, you should try to
determine why this is happening. The most important thing to
know is whether the table became corrupted as a result of a
server crash. You can verify this easily by looking for a recent
restarted mysqld message in the error log. If
there is such a message, it is likely that table corruption is a
result of the server dying. Otherwise, corruption may have
occurred during normal operation. This is a bug. You should try
to create a reproducible test case that demonstrates the
problem. See Section B.5.4.2, “What to Do If MySQL Keeps Crashing”, and
MySQL
Internals: Porting.
MySQL Enterprise
Find out about problems before they occur. Subscribe to the
MySQL Enterprise Monitor for expert advice about the state of
your servers. For more information, see
http://www.mysql.com/products/enterprise/advisors.html.
13.5.4.2. Problems from Tables Not Being Closed Properly
Each MyISAM index file
(.MYI file) has a counter in the header
that can be used to check whether a table has been closed
properly. If you get the following warning from
CHECK TABLE or
myisamchk, it means that this counter has
gone out of sync:
clients are using or haven't closed the table properly
This warning doesn't necessarily mean that the table is
corrupted, but you should at least check the table.
The counter works as follows:
The first time a table is updated in MySQL, a counter in the
header of the index files is incremented.
The counter is not changed during further updates.
When the last instance of a table is closed (because a
FLUSH
TABLES operation was performed or because there is
no room in the table cache), the counter is decremented if
the table has been updated at any point.
When you repair the table or check the table and it is found
to be okay, the counter is reset to zero.
To avoid problems with interaction with other processes that
might check the table, the counter is not decremented on
close if it was zero.
In other words, the counter can become incorrect only under
these conditions:
13.6. The InnoDB Storage Engine
InnoDB is a transaction-safe (ACID compliant)
storage engine for MySQL that has commit, rollback, and
crash-recovery capabilities to protect user data.
InnoDB row-level locking (without escalation to
coarser granularity locks) and Oracle-style consistent nonlocking
reads increase multi-user concurrency and performance.
InnoDB stores user data in clustered indexes to
reduce I/O for common queries based on primary keys. To maintain
data integrity, InnoDB also supports
FOREIGN KEY referential-integrity constraints.
You can freely mix InnoDB tables with tables from
other MySQL storage engines, even within the same statement.
To determine whether your server supports InnoDB
use the SHOW ENGINES statement. See
Section 12.5.5.17, “SHOW ENGINES Syntax”.
Table 13.4. InnoDB Storage Engine
Features Storage limits | 64TB | Transactions | Yes | Locking granularity | Row | MVCC | Yes | Geospatial datatype support | Yes | Geospatial indexing support | No | B-tree indexes | Yes | Hash indexes | No | Full-text search indexes | No | Clustered indexes | Yes | Data caches | Yes | Index caches | Yes | Compressed data | Yes[a] | Encrypted data[b] | Yes | Cluster database support | No | Replication support[c] | Yes | Foreign key support | Yes | Backup / point-in-time recovery[d] | Yes | Query cache support | Yes | Update statistics for data dictionary | Yes | | |
InnoDB has been designed for maximum performance
when processing large data volumes. Its CPU efficiency is probably
not matched by any other disk-based relational database engine.
The InnoDB storage engine maintains its own
buffer pool for caching data and indexes in main memory.
InnoDB stores its tables and indexes in a
tablespace, which may consist of several files (or raw disk
partitions). This is different from, for example,
MyISAM tables where each table is stored using
separate files. InnoDB tables can be very large
even on operating systems where file size is limited to 2GB.
The Windows Essentials installer makes InnoDB the
MySQL default storage engine on Windows, if the server being
installed supports InnoDB .
InnoDB is used in production at numerous large
database sites requiring high performance. The famous Internet news
site Slashdot.org runs on InnoDB . Mytrix, Inc.
stores more than 1TB of data in InnoDB , and
another site handles an average load of 800 inserts/updates per
second in InnoDB .
InnoDB is published under the same GNU GPL
License Version 2 (of June 1991) as MySQL. For more information on
MySQL licensing, see http://www.mysql.com/company/legal/licensing/.
Additional Resources
A forum dedicated to the InnoDB storage
engine is available at http://forums.mysql.com/list.php?22.
Innobase Oy also hosts several forums, available at
http://forums.innodb.com.
At the 2008 MySQL User Conference, Innobase announced
availability of an InnoDB Plugin for MySQL.
This plugin for MySQL exploits the “pluggable storage
engine” architecture of MySQL, to permit users to replace
the “built-in” version of InnoDB
in MySQL 5.1.
As of MySQL 5.1.38, the InnoDB Plugin is
included in MySQL 5.1 releases, in addition to the
built-in version of InnoDB that has been
included in previous releases. The version of the
InnoDB Plugin is 1.0.6 as of MySQL 5.1.42 and
is considered of Release Candidate (RC) quality.
Currently, MySQL Cluster is not compatible with the
InnoDB Plugin . See
Section 17.2.1, “MySQL Cluster Multi-Computer Installation”.
The InnoDB Plugin offers new features,
improved performance and scalability, enhanced reliability and
new capabilities for flexibility and ease of use. Among the
features of the InnoDB Plugin are “Fast
index creation,” table and index compression, file format
management, new INFORMATION_SCHEMA tables,
capacity tuning, multiple background I/O threads, and group
commit.
For information about these features, see the InnoDB
Plugin Manual at
http://www.innodb.com/products/innodb_plugin/plugin-documentation.
For general information about using InnoDB in
MySQL, see Section 13.6, “The InnoDB Storage Engine”.
The InnoDB Plugin is included in source and
binary distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64,
ia64), and generic Linux RPM packages.
To use the InnoDB Plugin , you must disable
the built-in version of InnoDB that is also
included and instruct the server to use InnoDB
Plugin instead. To accomplish this, use the following
lines in your my.cnf file:
[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so
For the plugin-load option,
innodb is the name to associate with the
plugin and ha_innodb_plugin.so is the name
of the shared object library that contains the plugin code. The
extension of .so applies for Unix (and
similar) systems. For HP-UX on HPPA (11.11) or Windows, the
extension should be .sl or
.dll , respectively, rather than
.so .
If the server has problems finding the plugin when it starts up,
specify the pathname to the plugin directory. For example, if
plugins are located in the lib/mysql/plugin
directory under the MySQL installation directory and you have
installed MySQL at /usr/local/mysql , use
these lines in your my.cnf file:
[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so
plugin_dir=/usr/local/mysql/lib/mysql/plugin
The previous examples show how to activate the storage engine
part of InnoDB Plugin, but the plugin also
implements several InnoDB-related
INFORMATION_SCHEMA tables. (For information
about these tables, see
http://www.innodb.com/doc/innodb_plugin-1.0/innodb-information-schema.html.)
To enable these tables, include additional
name =library
pairs in the value of the
plugin-load option:
[mysqld]
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so
;innodb_trx=ha_innodb_plugin.so
;innodb_locks=ha_innodb_plugin.so
;innodb_lock_waits=ha_innodb_plugin.so
;innodb_cmp=ha_innodb_plugin.so
;innodb_cmp_reset=ha_innodb_plugin.so
;innodb_cmpmem=ha_innodb_plugin.so
;innodb_cmpmem_reset=ha_innodb_plugin.so
The plugin-load option value as
shown here is formatted on multiple lines for display purposes
but should be written in my.cnf using a
single line without spaces in the option value. On Windows,
substitute .dll for each instance of the
.so extension.
After the server starts , verify that InnoDB
Plugin has been loaded by using the
SHOW PLUGINS statement. For
example, if you have loaded the storage engine and the
INFORMATION_SCHEMA tables, the output should
include lines similar to these:
mysql> SHOW PLUGINS;
+---------------------+--------+--------------------+---------------------...
| Name | Status | Type | Library ...
+---------------------+--------+--------------------+---------------------...
...
| InnoDB | ACTIVE | STORAGE ENGINE | ha_innodb_plugin.so ...
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
+---------------------+--------+--------------------+---------------------...
An alternative to using the
plugin-load option at server
startup is to use the INSTALL
PLUGIN statement at runtime. First start the server
with the ignore-builtin-innodb
option to disable the built-in version of
InnoDB :
[mysqld]
ignore-builtin-innodb
Then issue an INSTALL PLUGIN
statement for each plugin that you want to load:
mysql> INSTALL PLUGIN InnoDB SONAME 'ha_innodb_plugin.so';
mysql> INSTALL PLUGIN INNODB_TRX SONAME 'ha_innodb_plugin.so';
mysql> INSTALL PLUGIN INNODB_LOCKS SONAME 'ha_innodb_plugin.so';
...
INSTALL PLUGIN need be issued
only once for each plugin. Installed plugins will be loaded
automatically on subsequent server restarts.
If you build MySQL from a source distribution, InnoDB
Plugin is one of the storage engines that is built by
default. Build MySQL the way you normally do; for example, by
using the instructions at Section 2.3, “MySQL Installation Using a Source Distribution”.
After the build completes, you should find the plugin shared
object file under the storage/innodb_plugin
directory, and make install should install it
in the plugin directory. Configure MySQL to use InnoDB
Plugin as described earlier for binary distributions.
If you use gcc, InnoDB
Plugin cannot be compiled with gcc
3.x; you must use gcc 4.x instead.
InnoDB Hot Backup enables you to back up a
running MySQL database, including InnoDB and
MyISAM tables, with minimal disruption to
operations while producing a consistent snapshot of the
database. When InnoDB Hot Backup is copying
InnoDB tables, reads and writes to both
InnoDB and MyISAM tables
can continue. During the copying of MyISAM
tables, reads (but not writes) to those tables are permitted. In
addition, InnoDB Hot Backup supports creating
compressed backup files, and performing backups of subsets of
InnoDB tables. In conjunction with MySQL’s
binary log, users can perform point-in-time recovery.
InnoDB Hot Backup is commercially licensed by
Innobase Oy. For a more complete description of InnoDB
Hot Backup, see
http://www.innodb.com/products/hot-backup/features/
or download the documentation from
http://www.innodb.com/doc/hot_backup/manual.html.
You can order trial, term, and perpetual licenses from Innobase
at
http://www.innodb.com/wp/products/hot-backup/order/.
13.6.1. InnoDB Contact Information
Contact information for Innobase Oy, producer of the
InnoDB engine:
Web site: http://www.innodb.com/
Email: innodb_sales_ww at oracle.com or use
this contact form:
http://www.innodb.com/contact-form
Phone:
+358-9-6969 3250 (office, Heikki Tuuri)
+358-40-5617367 (mobile, Heikki Tuuri)
+358-40-5939732 (mobile, Satu Sir?n)
Address:
Innobase Oy Inc.
World Trade Center Helsinki
Aleksanterinkatu 17
P.O.Box 800
00101 Helsinki
Finland
13.6.2. InnoDB Configuration
If you do not want to use InnoDB tables, start
the server with the
--skip-innodb
option to disable the InnoDB startup engine.
Caution
InnoDB is a transaction-safe (ACID compliant)
storage engine for MySQL that has commit, rollback, and
crash-recovery capabilities to protect user data.
However, it cannot do so if the
underlying operating system or hardware does not work as
advertised. Many operating systems or disk subsystems may delay
or reorder write operations to improve performance. On some
operating systems, the very fsync() system
call that should wait until all unwritten data for a file has
been flushed might actually return before the data has been
flushed to stable storage. Because of this, an operating system
crash or a power outage may destroy recently committed data, or
in the worst case, even corrupt the database because of write
operations having been reordered. If data integrity is important
to you, you should perform some “pull-the-plug”
tests before using anything in production. On Mac OS X 10.3 and
up, InnoDB uses a special
fcntl() file flush method. Under Linux, it is
advisable to disable the write-back
cache.
On ATA/SATA disk drives, a command such hdparm -W0
/dev/hda may work to disable the write-back cache.
Beware that some drives or disk
controllers may be unable to disable the write-back
cache.
Two important disk-based resources managed by the
InnoDB storage engine are its tablespace data
files and its log files. If you specify no
InnoDB configuration options, MySQL creates an
auto-extending 10MB data file named ibdata1
and two 5MB log files named ib_logfile0 and
ib_logfile1 in the MySQL data directory. To
get good performance, you should explicitly provide
InnoDB parameters as discussed in the following
examples. Naturally, you should edit the settings to suit your
hardware and requirements.
Caution
It is not a good idea to configure InnoDB to
use data files or log files on NFS volumes. Otherwise, the files
might be locked by other processes and become unavailable for
use by MySQL.
MySQL Enterprise
For advice on settings suitable to your specific circumstances,
subscribe to the MySQL Enterprise Monitor. For more information,
see http://www.mysql.com/products/enterprise/advisors.html.
The examples shown here are representative. See
Section 13.6.3, “InnoDB Startup Options and System Variables” for additional information
about InnoDB -related configuration parameters.
To set up the InnoDB tablespace files, use the
innodb_data_file_path option in
the [mysqld] section of the
my.cnf option file. On Windows, you can use
my.ini instead. The value of
innodb_data_file_path should be a
list of one or more data file specifications. If you name more
than one data file, separate them by semicolon
(“; ”) characters:
innodb_data_file_path=datafile_spec1 [;datafile_spec2 ]...
For example, the following setting explicitly creates a tablespace
having the same characteristics as the default:
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
This setting configures a single 10MB data file named
ibdata1 that is auto-extending. No location
for the file is given, so by default, InnoDB
creates it in the MySQL data directory.
Sizes are specified using K ,
M , or G suffix letters to
indicate units of KB, MB, or GB.
A tablespace containing a fixed-size 50MB data file named
ibdata1 and a 50MB auto-extending file named
ibdata2 in the data directory can be
configured like this:
[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
The full syntax for a data file specification includes the file
name, its size, and several optional attributes:
file_name :file_size [:autoextend[:max:max_file_size ]]
The autoextend and max
attributes can be used only for the last data file in the
innodb_data_file_path line.
If you specify the autoextend option for the
last data file, InnoDB extends the data file if
it runs out of free space in the tablespace. The increment is 8MB
at a time by default. To modify the increment, change the
innodb_autoextend_increment
system variable.
If the disk becomes full, you might want to add another data file
on another disk. For tablespace reconfiguration instructions, see
Section 13.6.5, “Adding, Removing, or Resizing InnoDB Data and Log
Files”.
InnoDB is not aware of the file system maximum
file size, so be cautious on file systems where the maximum file
size is a small value such as 2GB. To specify a maximum size for
an auto-extending data file, use the max
attribute following the autoextend attribute.
The following configuration allows ibdata1 to
grow up to a limit of 500MB:
[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M
InnoDB creates tablespace files in the MySQL
data directory by default. To specify a location explicitly, use
the innodb_data_home_dir option.
For example, to use two files named ibdata1
and ibdata2 but create them in the
/ibdata directory, configure
InnoDB like this:
[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
Note
InnoDB does not create directories, so make
sure that the /ibdata directory exists
before you start the server. This is also true of any log file
directories that you configure. Use the Unix or DOS
mkdir command to create any necessary
directories.
Make sure that the MySQL server has the proper access rights to
create files in the data directory. More generally, the server
must have access rights in any directory where it needs to
create data files or log files.
InnoDB forms the directory path for each data
file by textually concatenating the value of
innodb_data_home_dir to the data
file name, adding a path name separator (slash or backslash)
between values if necessary. If the
innodb_data_home_dir option is
not mentioned in my.cnf at all, the default
value is the “dot” directory ./ ,
which means the MySQL data directory. (The MySQL server changes
its current working directory to its data directory when it begins
executing.)
If you specify
innodb_data_home_dir as an empty
string, you can specify absolute paths for the data files listed
in the innodb_data_file_path
value. The following example is equivalent to the preceding one:
[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend
A simple my.cnf
example. Suppose that you have a computer with 512MB
RAM and one hard disk. The following example shows possible
configuration parameters in my.cnf or
my.ini for InnoDB ,
including the autoextend attribute. The example
suits most users, both on Unix and Windows, who do not want to
distribute InnoDB data files and log files onto
several disks. It creates an auto-extending data file
ibdata1 and two InnoDB log
files ib_logfile0 and
ib_logfile1 in the MySQL data directory.
[mysqld]
# You can write your other MySQL server options here
# ...
# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=20M
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=64M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
Note that data files must be less than 2GB in some file systems.
The combined size of the log files must be less than 4GB. The
combined size of data files must be at least 10MB.
When you create an InnoDB tablespace for the
first time, it is best that you start the MySQL server from the
command prompt. InnoDB then prints the
information about the database creation to the screen, so you can
see what is happening. For example, on Windows, if
mysqld is located in C:\Program
Files\MySQL\MySQL Server 5.1\bin , you can
start it like this:
C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --console
If you do not send server output to the screen, check the server's
error log to see what InnoDB prints during the
startup process.
For an example of what the information displayed by
InnoDB should look like, see
Section 13.6.2.3, “Creating the InnoDB Tablespace”.
You can place InnoDB options in the
[mysqld] group of any option file that your
server reads when it starts. The locations for option files are
described in Section 4.2.3.3, “Using Option Files”.
If you installed MySQL on Windows using the installation and
configuration wizards, the option file will be the
my.ini file located in your MySQL
installation directory. See
The Location of the my.ini File.
If your PC uses a boot loader where the C:
drive is not the boot drive, your only option is to use the
my.ini file in your Windows directory
(typically C:\WINDOWS ). You can use the
SET command at the command prompt in a console
window to print the value of WINDIR :
C:\> SET WINDIR
windir=C:\WINDOWS
To make sure that mysqld reads options only
from a specific file, use the
--defaults-file option as the
first option on the command line when starting the server:
mysqld --defaults-file=your_path_to_my_cnf
An advanced my.cnf
example. Suppose that you have a Linux computer with
2GB RAM and three 60GB hard disks at directory paths
/ , /dr2 and
/dr3 . The following example shows possible
configuration parameters in my.cnf for
InnoDB .
[mysqld]
# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
#
# Data files must be able to hold your data and indexes
innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=250M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
#
# Uncomment the next line if you want to use it
#innodb_thread_concurrency=5
In some cases, database performance improves if the data is not
all placed on the same physical disk. Putting log files on a
different disk from data is very often beneficial for performance.
The example illustrates how to do this. It places the two data
files on different disks and places the log files on the third
disk. InnoDB fills the tablespace beginning
with the first data file. You can also use raw disk partitions
(raw devices) as InnoDB data files, which may
speed up I/O. See Section 13.6.2.2, “Using Raw Devices for the Shared Tablespace”.
Warning
On 32-bit GNU/Linux x86, you must be careful not to set memory
usage too high. glibc may allow the process
heap to grow over thread stacks, which crashes your server. It
is a risk if the value of the following expression is close to
or exceeds 2GB:
innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB
Each thread uses a stack (often 2MB, but only 256KB in MySQL
binaries provided by Sun Microsystems, Inc.) and in the worst
case also uses sort_buffer_size +
read_buffer_size additional memory.
Tuning other mysqld
server parameters. The following values are typical and
suit most users:
[mysqld]
skip-external-locking
max_connections=200
read_buffer_size=1M
sort_buffer_size=1M
#
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
key_buffer_size=value
On Linux, if the kernel is enabled for large page support,
InnoDB can use large pages to allocate memory
for its buffer pool and additional memory pool. See
Section 7.5.9, “Enabling Large Page Support”.
13.6.2.1. Using Per-Table Tablespaces
You can store each InnoDB table and its
indexes in its own file. This feature is called “multiple
tablespaces” because in effect each table has its own
tablespace.
Using multiple tablespaces can be beneficial to users who want
to move specific tables to separate physical disks or who wish
to restore backups of single tables quickly without interrupting
the use of other InnoDB tables.
To enable multiple tablespaces, start the server with the
--innodb_file_per_table option.
For example, add a line to the [mysqld]
section of my.cnf :
[mysqld]
innodb_file_per_table
With multiple tablespaces enabled, InnoDB
stores each newly created table into its own
tbl_name .ibd
file in the database directory where the table belongs. This is
similar to what the MyISAM storage engine
does, but MyISAM divides the table into a
tbl_name .MYD
data file and an
tbl_name .MYI
index file. For InnoDB , the data and the
indexes are stored together in the .ibd
file. The
tbl_name .frm
file is still created as usual.
You cannot freely move .ibd files between
database directories as you can with MyISAM
table files. This is because the table definition that is stored
in the InnoDB shared tablespace includes the
database name, and because InnoDB must
preserve the consistency of transaction IDs and log sequence
numbers.
If you remove the
innodb_file_per_table line from
my.cnf and restart the server,
InnoDB creates tables inside the shared
tablespace files again.
The --innodb_file_per_table
option affects only table creation, not access to existing
tables. If you start the server with this option, new tables are
created using .ibd files, but you can still
access tables that exist in the shared tablespace. If you start
the server without this option, new tables are created in the
shared tablespace, but you can still access any tables that were
created using multiple tablespaces.
Note
InnoDB always needs the shared tablespace
because it puts its internal data dictionary and undo logs
there. The .ibd files are not sufficient
for InnoDB to operate.
To move an .ibd file and the associated
table from one database to another, use a
RENAME TABLE statement:
RENAME TABLE db1.tbl_name TO db2.tbl_name ;
If you have a “clean” backup of an
.ibd file, you can restore it to the MySQL
installation from which it originated as follows:
Issue this ALTER TABLE
statement to delete the current .ibd
file:
ALTER TABLE tbl_name DISCARD TABLESPACE;
Copy the backup .ibd file to the proper
database directory.
Issue this ALTER TABLE
statement to tell InnoDB to use the new
.ibd file for the table:
ALTER TABLE tbl_name IMPORT TABLESPACE;
In this context, a “clean”
.ibd file backup is one for which the
following requirements are satisfied:
There are no uncommitted modifications by transactions in
the .ibd file.
There are no unmerged insert buffer entries in the
.ibd file.
Purge has removed all delete-marked index records from the
.ibd file.
mysqld has flushed all modified pages of
the .ibd file from the buffer pool to
the file.
You can make a clean backup .ibd file using
the following method:
Stop all activity from the mysqld server
and commit all transactions.
Wait until SHOW
ENGINE INNODB STATUS shows that there are no
active transactions in the database, and the main thread
status of InnoDB is Waiting for
server activity . Then you can make a copy of the
.ibd file.
Another method for making a clean copy of an
.ibd file is to use the commercial
InnoDB Hot Backup tool:
Use InnoDB Hot Backup to back up the
InnoDB installation.
Start a second mysqld server on the
backup and let it clean up the .ibd
files in the backup.
13.6.2.2. Using Raw Devices for the Shared Tablespace
You can use raw disk partitions as data files in the shared
tablespace. By using a raw disk, you can perform nonbuffered I/O
on Windows and on some Unix systems without file system
overhead. This may improve performance, but you are advised to
perform tests with and without raw partitions to verify whether
this is actually so on your system.
When you create a new data file, you must put the keyword
newraw immediately after the data file size
in innodb_data_file_path . The
partition must be at least as large as the size that you
specify. Note that 1MB in InnoDB is 1024
? 1024 bytes, whereas 1MB in disk specifications usually
means 1,000,000 bytes.
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
The next time you start the server, InnoDB
notices the newraw keyword and initializes
the new partition. However, do not create or change any
InnoDB tables yet. Otherwise, when you next
restart the server, InnoDB reinitializes the
partition and your changes are lost. (As a safety measure
InnoDB prevents users from modifying data
when any partition with newraw is specified.)
After InnoDB has initialized the new
partition, stop the server, change newraw in
the data file specification to raw :
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
Then restart the server and InnoDB allows
changes to be made.
On Windows, you can allocate a disk partition as a data file
like this:
[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw
The //./ corresponds to the Windows syntax
of \\.\ for accessing physical drives.
When you use a raw disk partition, be sure that it has
permissions that allow read and write access by the account used
for running the MySQL server. For example, if you run the server
as the mysql user, the partition must allow
read and write access to mysql . If you run
the server with the --memlock
option, the server must be run as root , so
the partition must allow access to root .
13.6.2.3. Creating the InnoDB Tablespace
Suppose that you have installed MySQL and have edited your
option file so that it contains the necessary
InnoDB configuration parameters. Before
starting MySQL, you should verify that the directories you have
specified for InnoDB data files and log files
exist and that the MySQL server has access rights to those
directories. InnoDB does not create
directories, only files. Check also that you have enough disk
space for the data and log files.
It is best to run the MySQL server mysqld
from the command prompt when you first start the server with
InnoDB enabled, not from
mysqld_safe or as a Windows service. When you
run from a command prompt you see what mysqld
prints and what is happening. On Unix, just invoke
mysqld. On Windows, start
mysqld with the
--console option to direct the
output to the console window.
When you start the MySQL server after initially configuring
InnoDB in your option file,
InnoDB creates your data files and log files,
and prints something like this:
InnoDB: The first specified datafile /home/heikki/data/ibdata1
did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file /home/heikki/data/ibdata1 size to 134217728
InnoDB: Database physically writes the file full: wait...
InnoDB: datafile /home/heikki/data/ibdata2 did not exist:
new to be created
InnoDB: Setting file /home/heikki/data/ibdata2 size to 262144000
InnoDB: Database physically writes the file full: wait...
InnoDB: Log file /home/heikki/data/logs/ib_logfile0 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile0 size
to 5242880
InnoDB: Log file /home/heikki/data/logs/ib_logfile1 did not exist:
new to be created
InnoDB: Setting log file /home/heikki/data/logs/ib_logfile1 size
to 5242880
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
InnoDB: Started
mysqld: ready for connections
At this point InnoDB has initialized its
tablespace and log files. You can connect to the MySQL server
with the usual MySQL client programs like
mysql. When you shut down the MySQL server
with mysqladmin shutdown, the output is like
this:
010321 18:33:34 mysqld: Normal shutdown
010321 18:33:34 mysqld: Shutdown Complete
InnoDB: Starting shutdown...
InnoDB: Shutdown completed
You can look at the data file and log directories and you see
the files created there. When MySQL is started again, the data
files and log files have been created already, so the output is
much briefer:
InnoDB: Started
mysqld: ready for connections
If you add the
innodb_file_per_table option to
my.cnf , InnoDB stores
each table in its own .ibd file in the same
MySQL database directory where the .frm
file is created. See Section 13.6.2.1, “Using Per-Table Tablespaces”.
13.6.2.4. Dealing with InnoDB Initialization Problems
If InnoDB prints an operating system error
during a file operation, usually the problem has one of the
following causes:
You did not create the InnoDB data file
directory or the InnoDB log directory.
mysqld does not have access rights to
create files in those directories.
mysqld cannot read the proper
my.cnf or my.ini
option file, and consequently does not see the options that
you specified.
The disk is full or a disk quota is exceeded.
You have created a subdirectory whose name is equal to a
data file that you specified, so the name cannot be used as
a file name.
There is a syntax error in the
innodb_data_home_dir or
innodb_data_file_path
value.
If something goes wrong when InnoDB attempts
to initialize its tablespace or its log files, you should delete
all files created by InnoDB . This means all
ibdata files and all
ib_logfile files. In case you have already
created some InnoDB tables, delete the
corresponding .frm files for these tables
(and any .ibd files if you are using
multiple tablespaces) from the MySQL database directories as
well. Then you can try the InnoDB database
creation again. It is best to start the MySQL server from a
command prompt so that you see what is happening.
13.6.3. InnoDB Startup Options and System Variables
This section describes the InnoDB -related
command options and system variables. System variables that are
true or false can be enabled at server startup by naming them, or
disabled by using a --skip prefix. For example,
to enable or disable InnoDB checksums, you can
use --innodb_checksums or
--skip-innodb_checksums
on the command line, or
innodb_checksums or
skip-innodb_checksums in an option file. System
variables that take a numeric value can be specified as
--var_name =value
on the command line or as
var_name =value
in option files. For more information on specifying options and
system variables, see Section 4.2.3, “Specifying Program Options”. Many of
the system variables can be changed at runtime (see
Section 5.1.6.2, “Dynamic System Variables”).
MySQL Enterprise
The MySQL Enterprise Monitor provides expert advice on InnoDB
start-up options and related system variables. For more
information, see
http://www.mysql.com/products/enterprise/advisors.html.
Table 13.5. InnoDB Option/Variable
Reference
InnoDB command options:
--ignore-builtin-innodb
This option that causes the server to behave as if the
built-in InnoDB is not present. It causes
other InnoDB options not to be recognized.
This option was added in MySQL 5.1.33.
--innodb[=value ]
Controls loading of the InnoDB storage
engine, if the server was compiled with
InnoDB support. As of MySQL 5.1.36, this
option has a tristate format, with possible values of
OFF , ON , or
FORCE . Before MySQL 5.1.36, this is a
boolean option. Use --innodb to
enable or
--skip-innodb
to disable InnoDB . See
Section 5.1.3, “Server Options for Loading Plugins”.
--innodb_status_file
Controls whether InnoDB creates a file
named
innodb_status.<pid>
in the MySQL data directory. If enabled,
InnoDB periodically writes the output of
SHOW ENGINE
INNODB STATUS to this file.
By default, the file is not created. To create it, start
mysqld with the
--innodb_status_file=1 option.
The file is deleted during normal shutdown.
InnoDB system variables:
ignore_builtin_innodb
Whether the server was started with the
--ignore-builtin-innodb option,
which causes the server to behave as if the built-in
InnoDB is not present. This variable was
added in MySQL 5.1.33.
innodb_adaptive_flushing
(InnoDB Plugin only) InnoDB
Plugin 1.0.4 and up uses a heuristic to determine
when to flush dirty pages in the buffer cache. This heuristic
is designed to avoid bursts of I/O activity and is used when
innodb_adaptive_flushing is
enabled (which is the default).
innodb_adaptive_hash_index
Whether InnoDB adaptive hash indexes are enabled or disabled
(see Section 13.6.10.4, “Adaptive Hash Indexes”). This variable is
enabled by default. Use
--skip-innodb_adaptive_hash_index at server
startup to disable it. This variable was added in MySQL
5.1.24.
innodb_additional_mem_pool_size
The size in bytes of a memory pool InnoDB
uses to store data dictionary information and other internal
data structures. The more tables you have in your application,
the more memory you need to allocate here. If
InnoDB runs out of memory in this pool, it
starts to allocate memory from the operating system and writes
warning messages to the MySQL error log. The default value is
1MB for the built-in InnoDB , 8MB
for InnoDB Plugin .
innodb_autoextend_increment
The increment size (in MB) for extending the size of an
auto-extending tablespace file when it becomes full. The
default value is 8.
innodb_buffer_pool_awe_mem_mb
The size of the buffer pool (in MB), if it is placed in the
AWE memory. If it is greater than 0,
innodb_buffer_pool_size is
the window in the 32-bit address space of
mysqld where InnoDB maps
that AWE memory. A good value for
innodb_buffer_pool_size is
500MB. The maximum possible value is 63000.
To take advantage of AWE memory, you will need to recompile
MySQL yourself. The current project settings needed for doing
this can be found in the
storage/innobase/os/os0proc.c source
file.
This variable was removed in MySQL 5.1.13. Before that, it is
relevant only in 32-bit Windows. If your 32-bit Windows
operating system supports more than 4GB memory, using
so-called “Address Windowing Extensions,” you can
allocate the InnoDB buffer pool into the
AWE physical memory using this variable.
innodb_autoinc_lock_mode
The locking mode to use for generating auto-increment values.
The allowable values are 0, 1, or 2, for
“traditional”, “consecutive”, or
“interleaved” lock mode, respectively.
Section 13.6.4.3, “AUTO_INCREMENT Handling in InnoDB ”, describes
the characteristics of these modes.
This variable was added in MySQL 5.1.22 with a default of 1
(“consecutive” lock mode). Before 5.1.22,
InnoDB uses “traditional” lock
mode.
innodb_buffer_pool_size
The size in bytes of the memory buffer
InnoDB uses to cache data and indexes of
its tables. The default value is 8MB. The larger you set this
value, the less disk I/O is needed to access data in tables.
On a dedicated database server, you may set this to up to 80%
of the machine physical memory size. However, do not set it
too large because competition for physical memory might cause
paging in the operating system. Also, the time to initialize
the buffer pool is roughly proportional to its size. On large
installations, this initialization time may be significant.
For example, on a modern Linux x86_64 server, initialization
of a 10GB buffer pool takes approximately 6 seconds. See
Section 7.4.6, “The InnoDB Buffer Pool”
innodb_change_buffering
(InnoDB Plugin only) Whether
InnoDB performs insert buffering.
The allowed values none (do not buffer any
operations) and inserts (buffer insert
operations). The default is inserts . For
details, see
Controlling
InnoDB Insert Buffering.
innodb_checksums
InnoDB can use checksum validation on all
pages read from the disk to ensure extra fault tolerance
against broken hardware or data files. This validation is
enabled by default. However, under some rare circumstances
(such as when running benchmarks) this extra safety feature is
unneeded and can be disabled with
--skip-innodb-checksums .
innodb_commit_concurrency
The number of threads that can commit at the same time. A
value of 0 (the default) allows any number of transactions to
commit simultaneously.
As of MySQL 5.1.36, the value of
innodb_commit_concurrency
cannot be changed at runtime from zero to nonzero or vice
versa. The value can still be changed from one nonzero value
to another.
innodb_concurrency_tickets
The number of threads that can enter InnoDB
concurrently is determined by the
innodb_thread_concurrency
variable. A thread is placed in a queue when it tries to enter
InnoDB if the number of threads has already
reached the concurrency limit. When a thread is allowed to
enter InnoDB , it is given a number of
“free tickets” equal to the value of
innodb_concurrency_tickets ,
and the thread can enter and leave InnoDB
freely until it has used up its tickets. After that point, the
thread again becomes subject to the concurrency check (and
possible queuing) the next time it tries to enter
InnoDB . The default value is 500.
innodb_data_file_path
The paths to individual data files and their sizes. The full
directory path to each data file is formed by concatenating
innodb_data_home_dir to each
path specified here. The file sizes are specified in KB, MB,
or GB (1024MB) by appending K ,
M , or G to the size
value. The sum of the sizes of the files must be at least
10MB. If you do not specify
innodb_data_file_path , the
default behavior is to create a single 10MB auto-extending
data file named ibdata1 . The size limit
of individual files is determined by your operating system.
You can set the file size to more than 4GB on those operating
systems that support big files. You can also use raw disk
partitions as data files. For detailed information on
configuring InnoDB tablespace files, see
Section 13.6.2, “InnoDB Configuration”.
innodb_data_home_dir
The common part of the directory path for all
InnoDB data files in the shared tablespace.
This setting does not affect the location of per-file
tablespaces when
innodb_file_per_table is
enabled. The default value is the MySQL data directory. If you
specify the value as an empty string, you can use absolute
file paths in
innodb_data_file_path .
innodb_doublewrite
If this variable is enabled (the default),
InnoDB stores all data twice, first to the
doublewrite buffer, and then to the actual data files. This
variable can be turned off with
--skip-innodb_doublewrite
for benchmarks or cases when top performance is needed rather
than concern for data integrity or possible failures.
innodb_fast_shutdown
The InnoDB shutdown mode. By default, the
value is 1, which causes a “fast” shutdown (the
normal type of shutdown). If the value is 0,
InnoDB does a full purge and an insert
buffer merge before a shutdown. These operations can take
minutes, or even hours in extreme cases. If the value is 1,
InnoDB skips these operations at shutdown.
If the value is 2, InnoDB will just flush
its logs and then shut down cold, as if MySQL had crashed; no
committed transaction will be lost, but crash recovery will be
done at the next startup. A value of 2 cannot be used on
NetWare.
innodb_file_format
(InnoDB Plugin only) The file format to use
for new InnoDB tables. Currently
Antelope and Barracuda
are supported. This applies only for tables that have their
own tablespace, so for it to have an effect
innodb_file_per_table must be
enabled.
innodb_file_format_check
(InnoDB Plugin only) If this variable is
enabled, InnoDB checks the file format tag
in the shared tablespace at server startup. If the tag is
higher than that supported by the current version of
InnoDB , an error occurs and
InnoDB does not start.
innodb_file_io_threads
The number of file I/O threads in InnoDB .
Normally, this should be left at the default value of 4, but
disk I/O on Windows may benefit from a larger number. On Unix,
increasing the number has no effect; InnoDB
always uses the default value.
With InnoDB Plugin , this variable is
unused. Use
innodb_read_io_threads and
innodb_write_io_threads
instead.
innodb_file_per_table
If innodb_file_per_table is
disabled (the default), InnoDB creates
tables in the shared tablespace. If
innodb_file_per_table is
enabled, InnoDB creates each new table
using its own .ibd file for storing data
and indexes, rather than in the shared tablespace. See
Section 13.6.2.1, “Using Per-Table Tablespaces”.
innodb_flush_log_at_trx_commit
If the value of
innodb_flush_log_at_trx_commit
is 0, the log buffer is written out to the log file once per
second and the flush to disk operation is performed on the log
file, but nothing is done at a transaction commit. When the
value is 1 (the default), the log buffer is written out to the
log file at each transaction commit and the flush to disk
operation is performed on the log file. When the value is 2,
the log buffer is written out to the file at each commit, but
the flush to disk operation is not performed on it. However,
the flushing on the log file takes place once per second also
when the value is 2. Note that the once-per-second flushing is
not 100% guaranteed to happen every second, due to process
scheduling issues.
The default value of 1 is the value required for ACID
compliance. You can achieve better performance by setting the
value different from 1, but then you can lose at most one
second worth of transactions in a crash. With a value of 0,
any mysqld process crash can erase the last
second of transactions. With a value of 2, then only an
operating system crash or a power outage can erase the last
second of transactions. However, InnoDB 's
crash recovery is not affected and thus crash recovery does
work regardless of the value.
For the greatest possible durability and consistency in a
replication setup using InnoDB with
transactions, use innodb_flush_log_at_trx_commit =
1 and sync_binlog = 1 in your
master server my.cnf file.
Caution
Many operating systems and some disk hardware fool the
flush-to-disk operation. They may tell
mysqld that the flush has taken place,
even though it has not. Then the durability of transactions
is not guaranteed even with the setting 1, and in the worst
case a power outage can even corrupt the
InnoDB database. Using a battery-backed
disk cache in the SCSI disk controller or in the disk itself
speeds up file flushes, and makes the operation safer. You
can also try using the Unix command
hdparm to disable the caching of disk
writes in hardware caches, or use some other command
specific to the hardware vendor.
innodb_flush_method
By default, InnoDB uses
fsync() to flush both the data and log
files. If innodb_flush_method
option is set to O_DSYNC ,
InnoDB uses O_SYNC to
open and flush the log files, and fsync()
to flush the data files. If O_DIRECT is
specified (available on some GNU/Linux versions, FreeBSD, and
Solaris), InnoDB uses
O_DIRECT (or directio()
on Solaris) to open the data files, and uses
fsync() to flush both the data and log
files. Note that InnoDB uses
fsync() instead of
fdatasync() , and it does not use
O_DSYNC by default because there have been
problems with it on many varieties of Unix. This variable is
relevant only for Unix. On Windows, the flush method is always
async_unbuffered and cannot be changed.
Different values of this variable can have a marked effect on
InnoDB performance. For example, on some
systems where InnoDB data and log files are
located on a SAN, it has been found that setting
innodb_flush_method to
O_DIRECT can degrade performance of simple
SELECT statements by a factor
of three.
Formerly it was possible to explicitly specify a value of
fdatasync to obtain the default behavior.
This is no longer possible as of MySQL 5.1.24 because it can
be confusing that a value of fdatasync
causes use of fsync() rather than
fdatasync() for flushing. To obtain the
default value now, do not set
innodb_flush_method at
startup.
innodb_force_recovery
The crash recovery mode. Possible values are from 0 to 6. The
meanings of these values are described in
Section 13.6.6.2, “Forcing InnoDB Recovery”.
Warning
This variable should be set greater than 0 only in an
emergency situation when you want to dump your tables from a
corrupt database! As a safety measure,
InnoDB prevents any changes to its data
when this variable is greater than 0.
innodb_io_capacity
(InnoDB Plugin only) The maximum number of
I/O operations per second that InnoDB will
perform. This variable can be set at server startup, which
enables higher values to be selected for systems capable of
higher I/O rates. Having a higher I/O rate can help the server
handle a higher rate of row changes because it may be able to
increase dirty-page flushing, deleted-row removal, and
application of changes to the insert buffer. The default value
of innodb_io_capacity is 200.
In general, you can increase the value as a function of the
number of drives used for InnoDB
I/O.
The ability to raise the I/O limit should be especially
beneficial on platforms that support many IOPS. For example,
systems that use multiple disks or solid-state disks for
InnoDB are likely to benefit from
the ability to control this parameter.
This variable was added in MySQL 5.1.38.
innodb_lock_wait_timeout
The timeout in seconds an InnoDB
transaction may wait for a row lock before giving up. The
default value is 50 seconds. A transaction that tries to
access a row that is locked by another
InnoDB transaction will hang for at most
this many seconds before issuing the following error:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
When a lock wait timeout occurs, the current statement is not
executed. The current transaction is not
rolled back. (To have the entire transaction roll back, start
the server with the
--innodb_rollback_on_timeout
option, available as of MySQL 5.1.15. See also
Section 13.6.12, “InnoDB Error Handling”.)
innodb_lock_wait_timeout
applies to InnoDB row locks only. A MySQL
table lock does not happen inside InnoDB
and this timeout does not apply to waits for table locks.
InnoDB does detect transaction deadlocks in
its own lock table immediately and rolls back one transaction.
The lock wait timeout value does not apply to such a wait.
innodb_locks_unsafe_for_binlog
This variable affects how InnoDB uses gap
locking for searches and index scans. Normally,
InnoDB uses an algorithm called
next-key locking that combines
index-row locking with gap locking. InnoDB
performs row-level locking in such a way that when it searches
or scans a table index, it sets shared or exclusive locks on
the index records it encounters. Thus, the row-level locks are
actually index-record locks. In addition, a next-key lock on
an index record also affects the “gap” before
that index record. That is, a next-key lock is an index-record
lock plus a gap lock on the gap preceding the index record. If
one session has a shared or exclusive lock on record
R in an index, another session cannot
insert a new index record in the gap immediately before
R in the index order. See
Section 13.6.8.4, “InnoDB Record, Gap, and Next-Key Locks”.
By default, the value of
innodb_locks_unsafe_for_binlog
is 0 (disabled), which means that gap locking is enabled:
InnoDB uses next-key locks for searches and
index scans. To enable the variable, set it to 1. This causes
gap locking to be disabled: InnoDB uses
only index-record locks for searches and index scans.
Enabling
innodb_locks_unsafe_for_binlog
does not disable the use of gap locking for foreign-key
constraint checking or duplicate-key checking.
The effect of enabling
innodb_locks_unsafe_for_binlog
is similar to but not identical to setting the transaction
isolation level to READ
COMMITTED :
Enabling
innodb_locks_unsafe_for_binlog
is a global setting and affects all sessions, whereas the
isolation level can be set globally for all sessions, or
individually per session.
innodb_locks_unsafe_for_binlog
can be set only at server startup, whereas the isolation
level can be set at startup or changed at runtime.
READ COMMITTED therefore
offers finer and more flexible control than
innodb_locks_unsafe_for_binlog .
For additional details about the effect of isolation level on
gap locking, see Section 12.4.6, “SET TRANSACTION Syntax”.
Enabling
innodb_locks_unsafe_for_binlog
may cause phantom problems because other sessions can insert
new rows into the gaps when gap locking is disabled. Suppose
that there is an index on the id column of
the child table and that you want to read
and lock all rows from the table having an identifier value
larger than 100, with the intention of updating some column in
the selected rows later:
SELECT * FROM child WHERE id > 100 FOR UPDATE;
The query scans the index starting from the first record where
id is greater than 100. If the locks set on
the index records in that range do not lock out inserts made
in the gaps, another session can insert a new row into the
table. Consequently, if you were to execute the same
SELECT again within the same
transaction, you would see a new row in the result set
returned by the query. This also means that if new items are
added to the database, InnoDB does not
guarantee serializability. Therefore, if
innodb_locks_unsafe_for_binlog
is enabled, InnoDB guarantees at most an
isolation level of READ
COMMITTED . (Conflict serializability is still
guaranteed.) For additional information about phantoms, see
Section 13.6.8.5, “Avoiding the Phantom Problem Using Next-Key Locking”.
Enabling
innodb_locks_unsafe_for_binlog
has additional effects:
For UPDATE or
DELETE statements,
InnoDB holds locks only for rows that
it updates or deletes. Record locks for nonmatching rows
are released after MySQL has evaluated the
WHERE condition. This greatly reduces
the probability of deadlocks, but they can still happen.
For UPDATE statements, if a
row is already locked, InnoDB performs
a “semi-consistent” read, returning the
latest committed version to MySQL so that MySQL can
determine whether the row matches the
WHERE condition of the
UPDATE . If the row matches
(must be updated), MySQL reads the row again and this time
InnoDB either locks it or waits for a
lock on it.
Consider the following example, beginning with this table:
CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;
In this case, table has no indexes, so searches and index
scans use the hidden clustered index for record locking (see
Section 13.6.10.1, “Clustered and Secondary Indexes”).
Suppose that one client performs an
UPDATE using these statements:
SET autocommit = 0;
UPDATE t SET b = 5 WHERE b = 3;
Suppose also that a second client performs an
UPDATE by executing these
statements following those of the first client:
SET autocommit = 0;
UPDATE t SET b = 4 WHERE b = 2;
As InnoDB executes each
UPDATE , it first acquires an
exclusive lock for each row, and then determines whether to
modify it. If InnoDB does not
modify the row and
innodb_locks_unsafe_for_binlog
is enabled, it releases the lock. Otherwise,
InnoDB retains the lock until the
end of the transaction. This affects transaction processing as
follows.
If
innodb_locks_unsafe_for_binlog
is disabled, the first UPDATE
acquires x-locks and does not release any of them:
x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock
The second UPDATE blocks as
soon as it tries to acquire any locks (because first update
has retained locks on all rows), and does not proceed until
the first UPDATE commits or
rolls back:
x-lock(1,2); block and wait for first UPDATE to commit or roll back
If
innodb_locks_unsafe_for_binlog
is enabled, the first UPDATE
acquires x-locks and releases those for rows that it does not
modify:
x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)
For the second UPDATE ,
InnoDB does a
“semi-consistent” read, returning the latest
committed version of each row to MySQL so that MySQL can
determine whether the row matches the WHERE
condition of the UPDATE :
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock
Semi-consistent read is available as of MySQL 5.1.5. Before
5.1.5, the second UPDATE
proceeds part way before it blocks. It begins acquiring
x-locks, and blocks when it tries to acquire one for a row
still locked by first UPDATE .
The second UPDATE does not
proceed until the first UPDATE
commits or rolls back:
x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); block and wait for first UPDATE to commit or roll back
In this case, the second UPDATE
must wait for a commit or rollback of the first
UPDATE , even though it affects
different rows. The first
UPDATE has an exclusive lock on
row (2,3) that it has not released. As the second
UPDATE scans rows, it tries to
acquire an exclusive lock for that same row, which it cannot
have. Thus, before MySQL 5.1.5, enabling
innodb_locks_unsafe_for_binlog
still does not allow operations such as
UPDATE to overtake other
similar operations (such as another
UPDATE ) even when they affect
different rows.
innodb_log_arch_dir
This variable is deprecated, and was removed in MySQL 5.1.21.
innodb_log_archive
This variable is deprecated, and was removed in MySQL 5.1.18.
innodb_log_buffer_size
The size in bytes of the buffer that InnoDB
uses to write to the log files on disk. The default value is
1MB. Sensible values range from 1MB to 8MB. A large log buffer
allows large transactions to run without a need to write the
log to disk before the transactions commit. Thus, if you have
big transactions, making the log buffer larger saves disk I/O.
innodb_log_file_size
The size in bytes of each log file in a log group. The
combined size of log files must be less than 4GB. The default
value is 5MB. Sensible values range from 1MB to
1/N -th of the size of the buffer
pool, where N is the number of log
files in the group. The larger the value, the less checkpoint
flush activity is needed in the buffer pool, saving disk I/O.
But larger log files also mean that recovery is slower in case
of a crash.
innodb_log_files_in_group
The number of log files in the log group.
InnoDB writes to the files in a circular
fashion. The default (and recommended) value is 2.
innodb_log_group_home_dir
The directory path to the InnoDB log files.
If you do not specify any InnoDB log
variables, the default is to create two 5MB files names
ib_logfile0 and
ib_logfile1 in the MySQL data directory.
innodb_max_dirty_pages_pct
This is an integer in the range from 0 to 100. The default
value is 90. The main thread in InnoDB
tries to write pages from the buffer pool so that the
percentage of dirty (not yet written) pages will not exceed
this value.
innodb_max_purge_lag
This variable controls how to delay
INSERT ,
UPDATE , and
DELETE operations when purge
operations are lagging (see
Section 13.6.9, “InnoDB Multi-Versioning”). The default value
0 (no delays).
The InnoDB transaction system maintains a
list of transactions that have delete-marked index records by
UPDATE or
DELETE operations. Let the
length of this list be purge_lag .
When purge_lag exceeds
innodb_max_purge_lag , each
INSERT ,
UPDATE , and
DELETE operation is delayed by
((purge_lag /innodb_max_purge_lag )?10)–5
milliseconds. The delay is computed in the beginning of a
purge batch, every ten seconds. The operations are not delayed
if purge cannot run because of an old consistent read view
that could see the rows to be purged.
A typical setting for a problematic workload might be 1
million, assuming that transactions are small, only 100 bytes
in size, and it is allowable to have 100MB of unpurged
InnoDB table rows.
innodb_mirrored_log_groups
The number of identical copies of log groups to keep for the
database. This should be set to 1.
innodb_old_blocks_pct
(InnoDB Plugin only) Specifies the
approximate percentage of the
InnoDB buffer pool used for the
old block sublist. The range of values is 5 to 95. The default
value is 37 (that is, 3/8 of the pool). See
Section 7.4.6, “The InnoDB Buffer Pool”
This variable was added in MySQL 5.1.41.
innodb_old_blocks_time
(InnoDB Plugin only) Specifies how long in
milliseconds (ms) a block inserted into the old sublist must
stay there after its first access before it can be moved to
the new sublist. The default value is 0: A block inserted into
the old sublist moves immediately to the new sublist the first
time it is accessed, no matter how soon after insertion the
access occurs. If the value is greater than 0, blocks remain
in the old sublist until an access occurs at least that many
ms after the first access. For example, a value of 1000 causes
blocks to stay in the old sublist for 1 second after the first
access before they become eligible to move to the new sublist.
See Section 7.4.6, “The InnoDB Buffer Pool”
This variable was added in MySQL 5.1.41.
innodb_open_files
This variable is relevant only if you use multiple tablespaces
in InnoDB . It specifies the maximum number
of .ibd files that
InnoDB can keep open at one time. The
minimum value is 10. The default value is 300.
The file descriptors used for .ibd files
are for InnoDB only. They are independent
of those specified by the
--open-files-limit server
option, and do not affect the operation of the table cache.
innodb_read_ahead_threshold
(InnoDB Plugin only) Controls the
sensitivity of linear read-ahead that
InnoDB uses to prefetch pages into the
buffer cache. If InnoDB reads at least
innodb_read_ahead_threshold
pages sequentially from an extent (64 pages), it initiates an
asynchronous read for the entire following extent. The
allowable range of values is 0 to 64. The default is 56:
InnoDB must read at least 56 pages
sequentially from an extent to initiate an asynchronous read
for the following extent.
This variable was added in MySQL 5.1.38.
innodb_read_io_threads
(InnoDB Plugin only) The number of I/O
threads for read operations in InnoDB . The
default value is 4.
This variable was added in MySQL 5.1.38.
innodb_replication_delay
(InnoDB Plugin only) The replication thread
delay (in ms) on a slave server if
innodb_thread_concurrency is
reached.
This variable was added in MySQL 5.1.38.
innodb_rollback_on_timeout
In MySQL 5.1, InnoDB rolls
back only the last statement on a transaction timeout by
default. If
--innodb_rollback_on_timeout is
specified, a transaction timeout causes
InnoDB to abort and roll back the entire
transaction (the same behavior as in MySQL 4.1). This variable
was added in MySQL 5.1.15.
innodb_spin_wait_delay
(InnoDB Plugin only) The maximum delay
between polls for a spin lock. The default value is 6.
This variable was added in MySQL 5.1.38.
innodb_stats_on_metadata
When this variable is enabled (which is the default, as before
the variable was created), InnoDB updates
statistics during metadata statements such as
SHOW TABLE STATUS or
SHOW INDEX , or when accessing
the INFORMATION_SCHEMA tables
TABLES or
STATISTICS . (These updates are
similar to what happens for ANALYZE
TABLE .) When disabled, InnoDB
does not updates statistics during these operations. Disabling
this variable can improve access speed for schemas that have a
large number of tables or indexes. It can also improve the
stability of execution plans for queries that involve
InnoDB tables.
This variable was added in MySQL 5.1.17.
innodb_stats_sample_pages
(InnoDB Plugin only) The number of index
pages to sample for index distribution statistics such as are
calculated by ANALYZE TABLE .
The default value is 8. For more information, see the
InnoDB
Plugin manual.
This variable was added in MySQL 5.1.38.
innodb_strict_mode
(InnoDB Plugin only) Whether
InnoDB returns errors rather than warnings
for exceptional conditions. This is analogous to strict SQL
mode. The default value is OFF .
This variable was added in MySQL 5.1.38.
innodb_support_xa
When the variable is enabled (the default),
InnoDB support for two-phase commit in XA
transactions is enabled, which causes an extra disk flush for
transaction preparation.
If you do not wish to use XA transactions, you can disable
this variable to reduce the number of disk flushes and get
better InnoDB performance.
innodb_sync_spin_loops
The number of times a thread waits for an
InnoDB mutex to be freed before the thread
is suspended. The default value is 20.
innodb_table_locks
If autocommit = 0 ,
InnoDB honors LOCK
TABLES ; MySQL does not return from LOCK
TABLES ... WRITE until all other threads have
released all their locks to the table. The default value of
innodb_table_locks is 1,
which means that LOCK TABLES
causes InnoDB to lock a table internally if
autocommit = 0 .
innodb_thread_concurrency
InnoDB tries to keep the number of
operating system threads concurrently inside
InnoDB less than or equal to the limit
given by this variable. Once the number of threads reaches
this limit, additional threads are placed into a wait state
within a FIFO queue for execution. Threads waiting for locks
are not counted in the number of concurrently executing
threads.
The correct value for this variable is dependent on
environment and workload. You will need to try a range of
different values to determine what value works for your
applications. A recommended value is 2 times the number of
CPUs plus the number of disks.
The range of this variable is 0 to 1000. A value of 20 or
higher is interpreted as infinite concurrency before MySQL
5.1.12. From 5.1.12 on, you can disable thread concurrency
checking by setting the value to 0. Disabling thread
concurrency checking allows InnoDB to create as many threads
as it needs.
The default value is 20 before MySQL 5.1.11, and 8 from 5.1.11
on.
innodb_thread_sleep_delay
How long InnoDB threads sleep before
joining the InnoDB queue, in microseconds.
The default value is 10,000. A value of 0 disables sleep.
innodb_use_legacy_cardinality_algorithm
InnoDB uses random numbers to
generate dives into indexes for calculating index cardinality.
However, under certain conditions, the algorithm does not
generate random numbers, so ANALYZE
TABLE sometimes does not update cardinality
estimates properly. An alternative algorithm was introduced in
MySQL 5.1.35 with better randomization properties, and the
innodb_use_legacy_cardinality_algorithm ,
system variable which algorithm to use. The default value of
the variable is 1 (ON ), to use the original
algorithm for compatibility with existing applications. The
variable can be set to 0 (OFF ) to use the
new algorithm with improved randomness.
This variable is not used in InnoDB Plugin
because the improved algorithm is used by default. Also, the
number of random dives can be changed by modifying the
innodb_stats_sample_pages
system variable.
innodb_use_sys_malloc
(InnoDB Plugin only) Whether
InnoDB uses the operating system memory
allocator (ON ) or its own
(OFF ). The default value is
ON .
This variable was added in MySQL 5.1.38.
innodb_version
(InnoDB Plugin only) The
InnoDB version number.
This variable was added in MySQL 5.1.38.
innodb_write_io_threads
(InnoDB Plugin only) The number of I/O
threads for write operations in InnoDB . The
default value is 4.
This variable was added in MySQL 5.1.38.
sync_binlog
If the value of this variable is greater than 0, the MySQL
server synchronizes its binary log to disk (using
fdatasync() ) after every
sync_binlog writes to the
binary log. There is one write to the binary log per statement
if autocommit is enabled, and one write per transaction
otherwise. The default value of
sync_binlog is 0, which does
no synchronizing to disk. A value of 1 is the safest choice,
because in the event of a crash you lose at most one statement
or transaction from the binary log. However, it is also the
slowest choice (unless the disk has a battery-backed cache,
which makes synchronization very fast).
13.6.4. Creating and Using InnoDB Tables
To create an InnoDB table, specify an
ENGINE = InnoDB option in the
CREATE TABLE statement:
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
The statement creates a table and an index on column
a in the InnoDB tablespace
that consists of the data files that you specified in
my.cnf . In addition, MySQL creates a file
customers.frm in the
test directory under the MySQL database
directory. Internally, InnoDB adds an entry for
the table to its own data dictionary. The entry includes the
database name. For example, if test is the
database in which the customers table is
created, the entry is for 'test/customers' .
This means you can create a table of the same name
customers in some other database, and the table
names do not collide inside InnoDB .
You can query the amount of free space in the
InnoDB tablespace by issuing a
SHOW TABLE STATUS statement for any
InnoDB table. The amount of free space in the
tablespace appears in the Data_free section in
the output of SHOW TABLE STATUS (or
the Comment section prior to MySQL 5.1.24). For
example:
SHOW TABLE STATUS FROM test LIKE 'customers'
The statistics SHOW displays for
InnoDB tables are only approximate. They are
used in SQL optimization. Table and index reserved sizes in bytes
are accurate, though.
13.6.4.1. How to Use Transactions in InnoDB with Different APIs
By default, each client that connects to the MySQL server begins
with autocommit mode enabled, which automatically commits every
SQL statement as you execute it. To use multiple-statement
transactions, you can switch autocommit off with the SQL
statement SET autocommit = 0 and end each
transaction with either COMMIT or
ROLLBACK . If
you want to leave autocommit on, you can begin your transactions
within START
TRANSACTION and end them with
COMMIT or
ROLLBACK . The
following example shows two transactions. The first is
committed; the second is rolled back.
shell> mysql test
mysql> CREATE TABLE customer (a INT, b CHAR (20), INDEX (a))
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO customer VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM customer;
+------+--------+
| a | b |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>
In APIs such as PHP, Perl DBI, JDBC, ODBC, or the standard C
call interface of MySQL, you can send transaction control
statements such as COMMIT to the
MySQL server as strings just like any other SQL statements such
as SELECT or
INSERT . Some APIs also offer
separate special transaction commit and rollback functions or
methods.
13.6.4.2. Converting Tables from Other Storage Engines to
InnoDB
To convert a non-InnoDB table to use
InnoDB use ALTER
TABLE :
ALTER TABLE t1 ENGINE=InnoDB;
Important
Do not convert MySQL system tables in the
mysql database (such as
user or host ) to the
InnoDB type. This is an unsupported
operation. The system tables must always be of the
MyISAM type.
InnoDB does not have a special optimization
for separate index creation the way the
MyISAM storage engine does. Therefore, it
does not pay to export and import the table and create indexes
afterward. The fastest way to alter a table to
InnoDB is to do the inserts directly to an
InnoDB table. That is, use ALTER
TABLE ... ENGINE=INNODB , or create an empty
InnoDB table with identical definitions and
insert the rows with INSERT INTO ... SELECT * FROM
... .
If you have UNIQUE constraints on secondary
keys, you can speed up a table import by turning off the
uniqueness checks temporarily during the import operation:
SET unique_checks=0;
... import operation ...
SET unique_checks=1;
For big tables, this saves a lot of disk I/O because
InnoDB can then use its insert buffer to
write secondary index records as a batch. Be certain that the
data contains no duplicate keys.
unique_checks allows but does
not require storage engines to ignore duplicate keys.
To get better control over the insertion process, it might be
good to insert big tables in pieces:
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey <= somethingelse;
After all records have been inserted, you can rename the tables.
During the conversion of big tables, you should increase the
size of the InnoDB buffer pool to reduce disk
I/O. Do not use more than 80% of the physical memory, though.
You can also increase the sizes of the InnoDB
log files.
Make sure that you do not fill up the tablespace:
InnoDB tables require a lot more disk space
than MyISAM tables. If an
ALTER TABLE operation runs out of
space, it starts a rollback, and that can take hours if it is
disk-bound. For inserts, InnoDB uses the
insert buffer to merge secondary index records to indexes in
batches. That saves a lot of disk I/O. For rollback, no such
mechanism is used, and the rollback can take 30 times longer
than the insertion.
In the case of a runaway rollback, if you do not have valuable
data in your database, it may be advisable to kill the database
process rather than wait for millions of disk I/O operations to
complete. For the complete procedure, see
Section 13.6.6.2, “Forcing InnoDB Recovery”.
If you want all your (nonsystem) tables to be created as
InnoDB tables, add the line
default-storage-engine=innodb to the
[mysqld] section of your server option file.
13.6.4.3. AUTO_INCREMENT Handling in InnoDB
Beginning with MySQL 5.1.22, InnoDB provides
a locking strategy that significantly improves scalability and
performance of SQL statements that add rows to tables with
AUTO_INCREMENT columns. This section provides
background information on the original
(“traditional”) implementation of auto-increment
locking in InnoDB , explains the configurable
locking mechanism, documents the parameter for configuring the
mechanism, and describes its behavior and interaction with
replication.
13.6.4.3.1. “Traditional” InnoDB Auto-Increment
Locking
The original implementation of auto-increment handling in
InnoDB uses the following strategy to
prevent problems when using the binary log for statement-based
replication or for certain recovery scenarios.
If you specify an AUTO_INCREMENT column for
an InnoDB table, the table handle in the
InnoDB data dictionary contains a special
counter called the auto-increment counter that is used in
assigning new values for the column. This counter is stored
only in main memory, not on disk.
InnoDB uses the following algorithm to
initialize the auto-increment counter for a table
t that contains an
AUTO_INCREMENT column named
ai_col : After a server startup, for the
first insert into a table t ,
InnoDB executes the equivalent of this
statement:
SELECT MAX(ai_col) FROM t FOR UPDATE;
InnoDB increments by one the value
retrieved by the statement and assigns it to the column and to
the auto-increment counter for the table. If the table is
empty, InnoDB uses the value
1 . If a user invokes a
SHOW TABLE STATUS statement
that displays output for the table t and
the auto-increment counter has not been initialized,
InnoDB initializes but does not increment
the value and stores it for use by later inserts. This
initialization uses a normal exclusive-locking read on the
table and the lock lasts to the end of the transaction.
InnoDB follows the same procedure for
initializing the auto-increment counter for a freshly created
table.
After the auto-increment counter has been initialized, if a
user does not explicitly specify a value for an
AUTO_INCREMENT column,
InnoDB increments the counter by one and
assigns the new value to the column. If the user inserts a row
that explicitly specifies the column value, and the value is
bigger than the current counter value, the counter is set to
the specified column value.
When accessing the auto-increment counter,
InnoDB uses a special table-level
AUTO-INC lock that it keeps to the end of
the current SQL statement, not to the end of the transaction.
The special lock release strategy was introduced to improve
concurrency for inserts into a table containing an
AUTO_INCREMENT column. Nevertheless, two
transactions cannot have the AUTO-INC lock
on the same table simultaneously, which can have a performance
impact if the AUTO-INC lock is held for a
long time. That might be the case for a statement such as
INSERT INTO t1 ... SELECT ... FROM t2 that
inserts all rows from one table into another.
InnoDB uses the in-memory auto-increment
counter as long as the server runs. When the server is stopped
and restarted, InnoDB reinitializes the
counter for each table for the first
INSERT to the table, as
described earlier.
You may see gaps in the sequence of values assigned to the
AUTO_INCREMENT column if you roll back
transactions that have generated numbers using the counter.
If a user specifies NULL or
0 for the AUTO_INCREMENT
column in an INSERT ,
InnoDB treats the row as if the value had
not been specified and generates a new value for it.
The behavior of the auto-increment mechanism is not defined if
a user assigns a negative value to the column or if the value
becomes bigger than the maximum integer that can be stored in
the specified integer type.
An AUTO_INCREMENT column must appear as the
first column in an index on an InnoDB
table.
InnoDB supports the AUTO_INCREMENT
= N table option in
CREATE TABLE and
ALTER TABLE statements, to set
the initial counter value or alter the current counter value.
The effect of this option is canceled by a server restart, for
reasons discussed earlier in this section.
13.6.4.3.2. Configurable InnoDB Auto-Increment Locking
As described in the previous section,
InnoDB uses a special lock called the
table-level AUTO-INC lock for inserts into
tables with AUTO_INCREMENT columns. This
lock is normally held to the end of the statement (not to the
end of the transaction), to ensure that auto-increment numbers
are assigned in a predictable and repeatable order for a given
sequence of INSERT statements.
In the case of statement-based replication, this means that
when an SQL statement is replicated on a slave server, the
same values are used for the auto-increment column as on the
master server. The result of execution of multiple
INSERT statements is
deterministic, and the slave reproduces the same data as on
the master. If auto-increment values generated by multiple
INSERT statements were
interleaved, the result of two concurrent
INSERT statements would be
nondeterministic, and could not reliably be propagated to a
slave server using statement-based replication.
To make this clear, consider an example that uses this table:
CREATE TABLE t1 (
c1 INT(11) NOT NULL AUTO_INCREMENT,
c2 VARCHAR(10) DEFAULT NULL,
PRIMARY KEY (c1)
) ENGINE=InnoDB;
Suppose that there are two transactions running, each
inserting rows into a table with an
AUTO_INCREMENT column. One transaction is
using an INSERT
... SELECT statement that inserts 1000 rows, and
another is using a simple
INSERT statement that inserts
one row:
Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
InnoDB cannot tell in advance how many rows
will be retrieved from the
SELECT in the
INSERT statement in Tx1, and it
assigns the auto-increment values one at a time as the
statement proceeds. With a table-level lock, held to the end
of the statement, only one
INSERT statement referring to
table t1 can execute at a time, and the
generation of auto-increment numbers by different statements
is not interleaved. The auto-increment value generated by the
Tx1 INSERT ...
SELECT statement will be consecutive, and the
(single) auto-increment value used by the
INSERT statement in Tx2 will
either be smaller or larger than all those used for Tx1,
depending on which statement executes first.
As long as the SQL statements execute in the same order when
replayed from the binary log (when using statement-based
replication, or in recovery scenarios), the results will be
the same as they were when Tx1 and Tx2 first ran. Thus,
table-level locks held until the end of a statement make
INSERT statements using
auto-increment safe for use with statement-based replication.
However, those locks limit concurrency and scalability when
multiple transactions are executing insert statements at the
same time.
In the preceding example, if there were no table-level lock,
the value of the auto-increment column used for the
INSERT in Tx2 depends on
precisely when the statement executes. If the
INSERT of Tx2 executes while
the INSERT of Tx1 is running
(rather than before it starts or after it completes), the
specific auto-increment values assigned by the two
INSERT statements are
nondeterministic, and may vary from run to run.
As of MySQL 5.1.22, InnoDB can avoid using
the table-level AUTO-INC lock for a class
of INSERT statements where the
number of rows is known in advance, and still preserve
deterministic execution and safety for statement-based
replication. Further, if you are not using the binary log to
replay SQL statements as part of recovery or replication, you
can entirely eliminate use of the table-level
AUTO-INC lock for even greater concurrency
and performance—at the cost of permitting gaps in
auto-increment numbers assigned by a statement and potentially
having the numbers assigned by concurrently executing
statements interleaved.
For INSERT statements where the
number of rows to be inserted is known at the beginning of
processing the statement, InnoDB quickly
allocates the required number of auto-increment values without
taking any lock, but only if there is no concurrent session
already holding the table-level AUTO-INC
lock (because that other statement will be allocating
auto-increment values one-by-one as it proceeds). More
precisely, such an INSERT
statement obtains auto-increment values under the control of a
mutex (a light-weight lock) that is not
held until the statement completes, but only for the duration
of the allocation process.
This new locking scheme allows much greater scalability, but
it does introduce some subtle differences in how
auto-increment values are assigned compared to the original
mechanism. To describe the way auto-increment works in
InnoDB , the following discussion defines
some terms, and explains how InnoDB behaves
using different settings of the new
innodb_autoinc_lock_mode
configuration parameter. Additional considerations are
described following the explanation of auto-increment locking
behavior.
First, some definitions:
“INSERT -like”
statements
All statements that generate new rows in a table,
including INSERT ,
INSERT ...
SELECT , REPLACE ,
REPLACE ...
SELECT , and LOAD
DATA .
“Simple inserts”
Statements for which the number of rows to be inserted can
be determined in advance (when the statement is initially
processed). This includes single-row and multiple-row
INSERT and
REPLACE statements that do
not have a nested subquery, but not
INSERT
... ON DUPLICATE KEY UPDATE .
“Bulk inserts”
Statements for which the number of rows to be inserted
(and the number of required auto-increment values) is not
known in advance. This includes
INSERT ...
SELECT ,
REPLACE ...
SELECT , and LOAD
DATA statements. InnoDB will
assign new values for the
AUTO_INCREMENT column one at a time as
each row is processed.
“Mixed-mode inserts”
These are “simple insert” statements that
specify the auto-increment value for some (but not all) of
the new rows. An example follows, where
c1 is an
AUTO_INCREMENT column of table
t1 :
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
Another type of “mixed-mode insert” is
INSERT
... ON DUPLICATE KEY UPDATE , which in the worst
case is in effect an INSERT
followed by a UPDATE , where
the allocated value for the
AUTO_INCREMENT column may or may not be
used during the update phase.
Beginning with MySQL 5.1.22, there is a configuration
parameter that controls how InnoDB uses
locking when generating values for
AUTO_INCREMENT columns. This parameter can
be set using the
--innodb-autoinc-lock-mode
option at mysqld startup.
In general, if you encounter problems with the way
auto-increment works (which will most likely involve
replication), you can force use of the original behavior by
setting the lock mode to 0.
There are three possible settings for the
innodb_autoinc_lock_mode
parameter:
innodb_autoinc_lock_mode = 0
(“traditional” lock mode)
This lock mode provides the same behavior as before
innodb_autoinc_lock_mode
existed. For all
“INSERT -like”
statements, a special table-level
AUTO-INC lock is obtained and held to
the end of the statement. This assures that the
auto-increment values assigned by any given statement are
consecutive (although “gaps” can exist within
a table if a transaction that generated auto-increment
values is rolled back, as discussed later).
This lock mode is provided only for backward compatibility
and performance testing. There is little reason to use
this lock mode unless you use “mixed-mode
inserts” and care about the important difference in
semantics described later.
innodb_autoinc_lock_mode = 1
(“consecutive” lock mode)
This is the default lock mode. In this mode, “bulk
inserts” use the special
AUTO-INC table-level lock and hold it
until the end of the statement. This applies to all
INSERT ...
SELECT ,
REPLACE ...
SELECT , and LOAD
DATA statements. Only one statement holding the
AUTO-INC lock can execute at a time.
With this lock mode, “simple inserts” (only)
use a new locking model where a light-weight mutex is used
during the allocation of auto-increment values, and no
table-level AUTO-INC lock is used,
unless an AUTO-INC lock is held by
another transaction. If another transaction does hold an
AUTO-INC lock, a “simple
insert” waits for the AUTO-INC
lock, as if it too were a “bulk insert.”
This lock mode ensures that, in the presence of
INSERT statements where the
number of rows is not known in advance (and where
auto-increment numbers are assigned as the statement
progresses), all auto-increment values assigned by any
“INSERT -like”
statement are consecutive, and operations are safe for
statement-based replication.
Simply put, the important impact of this lock mode is
significantly better scalability. This mode is safe for
use with statement-based replication. Further, as with
“traditional” lock mode, auto-increment
numbers assigned by any given statement are
consecutive. In this mode, there is
no change in semantics compared to
“traditional” mode for any statement that
uses auto-increment, with one important exception.
The exception is for “mixed-mode inserts”,
where the user provides explicit values for an
AUTO_INCREMENT column for some, but not
all, rows in a multiple-row “simple insert.”
For such inserts, InnoDB will allocate
more auto-increment values than the number of rows to be
inserted. However, all values automatically assigned are
consecutively generated (and thus higher than) the
auto-increment value generated by the most recently
executed previous statement. “Excess” numbers
are lost.
A similar situation exists if you use
INSERT
... ON DUPLICATE KEY UPDATE . This statement is
also classified as a “mixed-mode insert”
since an auto-increment value is not necessarily generated
for each row. Because InnoDB allocates
the auto-increment value before the insert is actually
attempted, it cannot know whether an inserted value will
be a duplicate of an existing value and thus cannot know
whether the auto-increment value it generates will be used
for a new row. Therefore, if you are using statement-based
replication, you must either avoid
INSERT
... ON DUPLICATE KEY UPDATE or use
innodb_autoinc_lock_mode = 0
(“traditional” lock mode).
innodb_autoinc_lock_mode = 2
(“interleaved” lock mode)
In this lock mode, no
“INSERT -like”
statements use the table-level AUTO-INC
lock, and multiple statements can execute at the same
time. This is the fastest and most scalable lock mode, but
it is not safe when using
statement-based replication or recovery scenarios when SQL
statements are replayed from the binary log.
In this lock mode, auto-increment values are guaranteed to
be unique and monotonically increasing across all
concurrently executing
“INSERT -like”
statements. However, because multiple statements can be
generating numbers at the same time (that is, allocation
of numbers is interleaved across
statements), the values generated for the rows inserted by
any given statement may not be consecutive.
If the only statements executing are “simple
inserts” where the number of rows to be inserted is
known ahead of time, there will be no gaps in the numbers
generated for a single statement, except for
“mixed-mode inserts.” However, when
“bulk inserts” are executed, there may be
gaps in the auto-increment values assigned by any given
statement.
The auto-increment locking modes provided by
innodb_autoinc_lock_mode have
several usage implications:
Using auto-increment with replication
If you are using statement-based replication, you should
set
innodb_autoinc_lock_mode
to 0 or 1 and use the same value on the master and its
slaves. Auto-increment values are not ensured to be the
same on the slaves as on the master if you use
innodb_autoinc_lock_mode
= 2 (“interleaved”) or configurations where
the master and slaves do not use the same lock mode.
If you are using row-based replication, all of the
auto-increment lock modes are safe. Row-based replication
is not sensitive to the order of execution of the SQL
statements.
“Lost” auto-increment values and sequence
gaps
In all lock modes (0, 1, and 2), if a transaction that
generated auto-increment values rolls back, those
auto-increment values are “lost.” Once a
value is generated for an auto-increment column, it cannot
be rolled back, whether or not the
“INSERT -like”
statement is completed, and whether or not the containing
transaction is rolled back. Such lost values are not
reused. Thus, there may be gaps in the values stored in an
AUTO_INCREMENT column of a table.
Gaps in auto-increment values for “bulk
inserts”
With
innodb_autoinc_lock_mode
set to 0 (“traditional”) or 1
(“consecutive”), the auto-increment values
generated by any given statement will be consecutive,
without gaps, because the table-level
AUTO-INC lock is held until the end of
the statement, and only one such statement can execute at
a time.
With
innodb_autoinc_lock_mode
set to 2 (“interleaved”), there may be gaps
in the auto-increment values generated by “bulk
inserts,” but only if there are concurrently
executing
“INSERT -like”
statements.
For lock modes 1 or 2, gaps may occur between successive
statements because for bulk inserts the exact number of
auto-increment values required by each statement may not
be known and overestimation is possible.
Auto-increment values assigned by “mixed-mode
inserts”
Consider a “mixed-mode insert,” where a
“simple insert” specifies the auto-increment
value for some (but not all) resulting rows. Such a
statement will behave differently in lock modes 0, 1, and
2. For example, assume c1 is an
AUTO_INCREMENT column of table
t1 , and that the most recent
automatically generated sequence number is 100. Consider
the following “mixed-mode insert” statement:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
With
innodb_autoinc_lock_mode
set to 0 (“traditional”), the four new rows
will be:
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+-----+------+
The next available auto-increment value will be 103
because the auto-increment values are allocated one at a
time, not all at once at the beginning of statement
execution. This result is true whether or not there are
concurrently executing
“INSERT -like”
statements (of any type).
With
innodb_autoinc_lock_mode
set to 1 (“consecutive”), the four new rows
will also be:
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+-----+------+
However, in this case, the next available auto-increment
value will be 105, not 103 because four auto-increment
values are allocated at the time the statement is
processed, but only two are used. This result is true
whether or not there are concurrently executing
“INSERT -like”
statements (of any type).
With
innodb_autoinc_lock_mode
set to mode 2 (“interleaved”), the four new
rows will be:
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| x | b |
| 5 | c |
| y | d |
+-----+------+
The values of x and
y will be unique and larger
than any previously generated rows. However, the specific
values of x and
y will depend on the number of
auto-increment values generated by concurrently executing
statements.
Finally, consider the following statement, issued when the
most-recently generated sequence number was the value 4:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
With any
innodb_autoinc_lock_mode
setting, this statement will generate a duplicate-key
error 23000 (Can't write; duplicate key in
table ) because 5 will be allocated for the row
(NULL, 'b') and insertion of the row
(5, 'c') will fail.
13.6.4.4. FOREIGN KEY Constraints
InnoDB supports foreign key constraints. The
syntax for a foreign key constraint definition in
InnoDB looks like this:
[CONSTRAINT [symbol ]] FOREIGN KEY
[index_name ] (index_col_name , ...)
REFERENCES tbl_name (index_col_name ,...)
[ON DELETE reference_option ]
[ON UPDATE reference_option ]
reference_option :
RESTRICT | CASCADE | SET NULL | NO ACTION
index_name represents a foreign key
ID. If given, this is ignored if an index for the foreign key is
defined explicitly. Otherwise, if InnoDB
creates an index for the foreign key, it uses
index_name for the index name.
Foreign keys definitions are subject to the following
conditions:
Both tables must be InnoDB tables and
they must not be TEMPORARY tables.
Corresponding columns in the foreign key and the referenced
key must have similar internal data types inside
InnoDB so that they can be compared
without a type conversion. The size and sign of
integer types must be the same. The length of
string types need not be the same. For nonbinary (character)
string columns, the character set and collation must be the
same.
InnoDB requires indexes on foreign keys
and referenced keys so that foreign key checks can be fast
and not require a table scan. In the referencing table,
there must be an index where the foreign key columns are
listed as the first columns in the same
order. Such an index is created on the referencing table
automatically if it does not exist. (This is in contrast to
some older versions, in which indexes had to be created
explicitly or the creation of foreign key constraints would
fail.) index_name , if given, is
used as described previously.
InnoDB allows a foreign key to reference
any index column or group of columns. However, in the
referenced table, there must be an index where the
referenced columns are listed as the
first columns in the same order.
Index prefixes on foreign key columns are not supported. One
consequence of this is that
BLOB and
TEXT columns cannot be
included in a foreign key because indexes on those columns
must always include a prefix length.
If the CONSTRAINT
symbol clause is given,
the symbol value must be unique
in the database. If the clause is not given,
InnoDB creates the name automatically.
InnoDB rejects any
INSERT or
UPDATE operation that attempts to
create a foreign key value in a child table if there is no a
matching candidate key value in the parent table. The action
InnoDB takes for any
UPDATE or
DELETE operation that attempts to
update or delete a candidate key value in the parent table that
has some matching rows in the child table is dependent on the
referential action specified using
ON UPDATE and ON DELETE
subclauses of the FOREIGN KEY clause. When
the user attempts to delete or update a row from a parent table,
and there are one or more matching rows in the child table,
InnoDB supports five options regarding the
action to be taken. If ON DELETE or
ON UPDATE are not specified, the default
action is RESTRICT .
CASCADE : Delete or update the row from
the parent table and automatically delete or update the
matching rows in the child table. Both ON DELETE
CASCADE and ON UPDATE CASCADE
are supported. Between two tables, you should not define
several ON UPDATE CASCADE clauses that
act on the same column in the parent table or in the child
table.
Note
Currently, cascaded foreign key actions to not activate
triggers.
SET NULL : Delete or update the row from
the parent table and set the foreign key column or columns
in the child table to NULL . This is valid
only if the foreign key columns do not have the NOT
NULL qualifier specified. Both ON DELETE
SET NULL and ON UPDATE SET NULL
clauses are supported.
If you specify a SET NULL action,
make sure that you have not declared the columns
in the child table as NOT
NULL .
NO ACTION : In standard SQL, NO
ACTION means no action in the
sense that an attempt to delete or update a primary key
value is not allowed to proceed if there is a related
foreign key value in the referenced table.
InnoDB rejects the delete or update
operation for the parent table.
RESTRICT : Rejects the delete or update
operation for the parent table. Specifying
RESTRICT (or NO
ACTION ) is the same as omitting the ON
DELETE or ON UPDATE clause.
(Some database systems have deferred checks, and NO
ACTION is a deferred check. In MySQL, foreign key
constraints are checked immediately, so NO
ACTION is the same as
RESTRICT .)
SET DEFAULT : This action is recognized by
the parser, but InnoDB rejects table
definitions containing ON DELETE SET
DEFAULT or ON UPDATE SET
DEFAULT clauses.
InnoDB supports foreign key references within
a table. In these cases, “child table records”
really refers to dependent records within the same table.
Here is a simple example that relates parent
and child tables through a single-column
foreign key:
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;
A more complex example in which a
product_order table has foreign keys for two
other tables. One foreign key references a two-column index in
the product table. The other references a
single-column index in the customer table:
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) ENGINE=INNODB;
InnoDB allows you to add a new foreign key
constraint to a table by using ALTER
TABLE :
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol ]] FOREIGN KEY
[index_name ] (index_col_name , ...)
REFERENCES tbl_name (index_col_name ,...)
[ON DELETE reference_option ]
[ON UPDATE reference_option ]
The foreign key can be self referential (referring to the same
table). When you add a foreign key constraint to a table using
ALTER TABLE , remember
to create the required indexes first.
InnoDB supports the use of
ALTER TABLE to drop foreign keys:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol ;
If the FOREIGN KEY clause included a
CONSTRAINT name when you created the foreign
key, you can refer to that name to drop the foreign key.
Otherwise, the fk_symbol value is
internally generated by InnoDB when the
foreign key is created. To find out the symbol value when you
want to drop a foreign key, use the SHOW
CREATE TABLE statement. For example:
mysql> SHOW CREATE TABLE ibtest11c\G
*************************** 1. row ***************************
Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY `0_38775`;
You cannot add a foreign key and drop a foreign key in separate
clauses of a single ALTER TABLE
statement. Separate statements are required.
If ALTER TABLE for an
InnoDB table results in changes to column
values (for example, because a column is truncated),
InnoDB 's FOREIGN KEY
constraint checks do not notice possible violations caused by
changing the values.
The InnoDB parser allows table and column
identifiers in a FOREIGN KEY ... REFERENCES
... clause to be quoted within backticks.
(Alternatively, double quotes can be used if the
ANSI_QUOTES SQL mode is
enabled.) The InnoDB parser also takes into
account the setting of the
lower_case_table_names system
variable.
InnoDB returns a table's foreign key
definitions as part of the output of the
SHOW CREATE TABLE statement:
SHOW CREATE TABLE tbl_name ;
mysqldump also produces correct definitions
of tables in the dump file, and does not forget about the
foreign keys.
You can also display the foreign key constraints for a table
like this:
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name ';
The foreign key constraints are listed in the
Comment column of the output.
When performing foreign key checks, InnoDB
sets shared row-level locks on child or parent records it has to
look at. InnoDB checks foreign key
constraints immediately; the check is not deferred to
transaction commit.
To make it easier to reload dump files for tables that have
foreign key relationships, mysqldump
automatically includes a statement in the dump output to set
foreign_key_checks to 0. This
avoids problems with tables having to be reloaded in a
particular order when the dump is reloaded. It is also possible
to set this variable manually:
mysql> SET foreign_key_checks = 0;
mysql> SOURCE dump_file_name ;
mysql> SET foreign_key_checks = 1;
This allows you to import the tables in any order if the dump
file contains tables that are not correctly ordered for foreign
keys. It also speeds up the import operation. Setting
foreign_key_checks to 0 can
also be useful for ignoring foreign key constraints during
LOAD DATA and
ALTER TABLE operations. However,
even if foreign_key_checks = 0 ,
InnoDB does not allow the creation of a foreign key constraint
where a column references a nonmatching column type. Also, if an
InnoDB table has foreign key constraints,
ALTER TABLE cannot be used to
change the table to use another storage engine. To alter the
storage engine, you must drop any foreign key constraints first.
InnoDB does not allow you to drop a table
that is referenced by a FOREIGN KEY
constraint, unless you do SET foreign_key_checks =
0 . When you drop a table, the constraints that were
defined in its create statement are also dropped.
If you re-create a table that was dropped, it must have a
definition that conforms to the foreign key constraints
referencing it. It must have the right column names and types,
and it must have indexes on the referenced keys, as stated
earlier. If these are not satisfied, MySQL returns error number
1005 and refers to error 150 in the error message.
If MySQL reports an error number 1005 from a
CREATE TABLE statement, and the
error message refers to error 150, table creation failed because
a foreign key constraint was not correctly formed. Similarly, if
an ALTER TABLE fails and it
refers to error 150, that means a foreign key definition would
be incorrectly formed for the altered table. You can use
SHOW ENGINE INNODB
STATUS to display a detailed explanation of the most
recent InnoDB foreign key error in the
server.
Important
For users familiar with the ANSI/ISO SQL Standard, please note
that no storage engine, including InnoDB ,
recognizes or enforces the MATCH clause
used in referential-integrity constraint definitions. Use of
an explicit MATCH clause will not have the
specified effect, and also causes ON DELETE
and ON UPDATE clauses to be ignored. For
these reasons, specifying MATCH should be
avoided.
The MATCH clause in the SQL standard
controls how NULL values in a composite
(multiple-column) foreign key are handled when comparing to a
primary key. InnoDB essentially implements
the semantics defined by MATCH SIMPLE ,
which allow a foreign key to be all or partially
NULL . In that case, the (child table) row
containing such a foreign key is allowed to be inserted, and
does not match any row in the referenced (parent) table. It is
possible to implement other semantics using triggers.
Additionally, MySQL and InnoDB require that
the referenced columns be indexed for performance. However,
the system does not enforce a requirement that the referenced
columns be UNIQUE or be declared
NOT NULL . The handling of foreign key
references to nonunique keys or keys that contain
NULL values is not well defined for
operations such as UPDATE or
DELETE CASCADE . You are advised to use
foreign keys that reference only UNIQUE and
NOT NULL keys.
Furthermore, InnoDB does not recognize or
support “inline REFERENCES
specifications” (as defined in the SQL standard) where
the references are defined as part of the column
specification. InnoDB accepts
REFERENCES clauses only when specified as
part of a separate FOREIGN KEY
specification. For other storage engines, MySQL Server parses
and ignores foreign key specifications.
Deviation from SQL standards:
If there are several rows in the parent table that have the same
referenced key value, InnoDB acts in foreign
key checks as if the other parent rows with the same key value
do not exist. For example, if you have defined a
RESTRICT type constraint, and there is a
child row with several parent rows, InnoDB
does not allow the deletion of any of those parent rows.
InnoDB performs cascading operations through
a depth-first algorithm, based on records in the indexes
corresponding to the foreign key constraints.
Deviation from SQL standards: A
FOREIGN KEY constraint that references a
non-UNIQUE key is not standard SQL. It is an
InnoDB extension to standard SQL.
Deviation from SQL standards:
If ON UPDATE CASCADE or ON UPDATE
SET NULL recurses to update the same
table it has previously updated during the cascade,
it acts like RESTRICT . This means that you
cannot use self-referential ON UPDATE CASCADE
or ON UPDATE SET NULL operations. This is to
prevent infinite loops resulting from cascaded updates. A
self-referential ON DELETE SET NULL , on the
other hand, is possible, as is a self-referential ON
DELETE CASCADE . Cascading operations may not be nested
more than 15 levels deep.
Deviation from SQL standards:
Like MySQL in general, in an SQL statement that inserts,
deletes, or updates many rows, InnoDB checks
UNIQUE and FOREIGN KEY
constraints row-by-row. According to the SQL standard, the
default behavior should be deferred checking. That is,
constraints are only checked after the entire SQL
statement has been processed. Until
InnoDB implements deferred constraint
checking, some things will be impossible, such as deleting a
record that refers to itself via a foreign key.
13.6.4.5. InnoDB and MySQL Replication
MySQL replication works for InnoDB tables as
it does for MyISAM tables. It is also
possible to use replication in a way where the storage engine on
the slave is not the same as the original storage engine on the
master. For example, you can replicate modifications to an
InnoDB table on the master to a
MyISAM table on the slave.
To set up a new slave for a master, you have to make a copy of
the InnoDB tablespace and the log files, as
well as the .frm files of the
InnoDB tables, and move the copies to the
slave. If the
innodb_file_per_table variable
is enabled, you must also copy the .ibd
files as well. For the proper procedure to do this, see
Section 13.6.6, “Backing Up and Recovering an InnoDB
Database”.
If you can shut down the master or an existing slave, you can
take a cold backup of the InnoDB tablespace
and log files and use that to set up a slave. To make a new
slave without taking down any server you can also use the
commercial
InnoDB
Hot Backup tool.
You cannot set up replication for InnoDB
using the LOAD TABLE FROM MASTER
statement, which works only for MyISAM
tables. There are two possible workarounds:
Dump the table on the master and import the dump file into
the slave.
Use ALTER TABLE tbl_name
ENGINE=MyISAM on the master before setting up
replication with LOAD TABLE
tbl_name FROM MASTER ,
and then use ALTER TABLE to
convert the master table back to InnoDB
afterward. However, this should not be done for tables that
have foreign key definitions because the definitions will be
lost.
Transactions that fail on the master do not affect replication
at all. MySQL replication is based on the binary log where MySQL
writes SQL statements that modify data. A transaction that fails
(for example, because of a foreign key violation, or because it
is rolled back) is not written to the binary log, so it is not
sent to slaves. See Section 12.4.1, “START TRANSACTION ,
COMMIT , and
ROLLBACK Syntax”.
Replication and CASCADE .
Cascading actions for InnoDB tables on the
master are replicated on the slave only
if the tables sharing the foreign key relation use
InnoDB on both the master and slave. This
is true whether you are using statement-based or row-based
replication. Suppose that you have started replication, and
then create two tables on the master using the following
CREATE TABLE statements:
CREATE TABLE fc1 (
i INT PRIMARY KEY,
j INT
) ENGINE = InnoDB;
CREATE TABLE fc2 (
m INT PRIMARY KEY,
n INT,
FOREIGN KEY ni (n) REFERENCES fc1 (i)
ON DELETE CASCADE
) ENGINE = InnoDB;
Suppose that the slave does not have InnoDB
support enabled. If this is the case, then the tables on the
slave are created, but they use the MyISAM
storage engine, and the FOREIGN KEY option
is ignored. Now we insert some rows into the tables on the
master:
master> INSERT INTO fc1 VALUES (1, 1), (2, 2);
Query OK, 2 rows affected (0.09 sec)
Records: 2 Duplicates: 0 Warnings: 0
master> INSERT INTO fc2 VALUES (1, 1), (2, 2), (3, 1);
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
At this point, on both the master and the slave, table
fc1 contains 2 rows, and table
fc2 contains 3 rows, as shown here:
master> SELECT * FROM fc1;
+---+------+
| i | j |
+---+------+
| 1 | 1 |
| 2 | 2 |
+---+------+
2 rows in set (0.00 sec)
master> SELECT * FROM fc2;
+---+------+
| m | n |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
+---+------+
3 rows in set (0.00 sec)
slave> SELECT * FROM fc1;
+---+------+
| i | j |
+---+------+
| 1 | 1 |
| 2 | 2 |
+---+------+
2 rows in set (0.00 sec)
slave> SELECT * FROM fc2;
+---+------+
| m | n |
+---+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
+---+------+
3 rows in set (0.00 sec)
Now suppose that you perform the following
DELETE statement on the master:
master> DELETE FROM fc1 WHERE i=1;
Query OK, 1 row affected (0.09 sec)
Due to the cascade, table fc2 on the master
now contains only 1 row:
master> SELECT * FROM fc2;
+---+---+
| m | n |
+---+---+
| 2 | 2 |
+---+---+
1 row in set (0.00 sec)
However, the cascade does not propagate on the slave because
on the slave the DELETE for
fc1 deletes no rows from
fc2 . The slave's copy of
fc2 still contains all of the rows that
were originally inserted:
slave> SELECT * FROM fc2;
+---+---+
| m | n |
+---+---+
| 1 | 1 |
| 3 | 1 |
| 2 | 2 |
+---+---+
3 rows in set (0.00 sec)
This difference is due to the fact that the cascading deletes
are handled internally by the InnoDB
storage engine, which means that none of the changes are
logged.
13.6.5. Adding, Removing, or Resizing InnoDB Data and Log
Files
This section describes what you can do when your
InnoDB tablespace runs out of room or when you
want to change the size of the log files.
The easiest way to increase the size of the
InnoDB tablespace is to configure it from the
beginning to be auto-extending. Specify the
autoextend attribute for the last data file in
the tablespace definition. Then InnoDB
increases the size of that file automatically in 8MB increments
when it runs out of space. The increment size can be changed by
setting the value of the
innodb_autoextend_increment
system variable, which is measured in MB.
Alternatively, you can increase the size of your tablespace by
adding another data file. To do this, you have to shut down the
MySQL server, change the tablespace configuration to add a new
data file to the end of
innodb_data_file_path , and start
the server again.
If your last data file was defined with the keyword
autoextend , the procedure for reconfiguring the
tablespace must take into account the size to which the last data
file has grown. Obtain the size of the data file, round it down to
the closest multiple of 1024 ? 1024 bytes (= 1MB), and
specify the rounded size explicitly in
innodb_data_file_path . Then you
can add another data file. Remember that only the last data file
in the innodb_data_file_path can
be specified as auto-extending.
As an example, assume that the tablespace has just one
auto-extending data file ibdata1 :
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend
Suppose that this data file, over time, has grown to 988MB. Here
is the configuration line after modifying the original data file
to not be auto-extending and adding another auto-extending data
file:
innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend
When you add a new file to the tablespace configuration, make sure
that it does not exist. InnoDB will create and
initialize the file when you restart the server.
Currently, you cannot remove a data file from the tablespace. To
decrease the size of your tablespace, use this procedure:
Use mysqldump to dump all your
InnoDB tables.
Stop the server.
Remove all the existing tablespace files, including the
ibdata and ib_log
files. If you want to keep a backup copy of the information,
then copy all the ib* files to another
location before the removing the files in your MySQL
installation.
Remove any .frm files for
InnoDB tables.
Configure a new tablespace.
Restart the server.
Import the dump files.
If you want to change the number or the size of your
InnoDB log files, use the following
instructions. The procedure to use depends on the value of
innodb_fast_shutdown :
If innodb_fast_shutdown is
not set to 2: Stop the MySQL server and make sure that it
shuts down without errors (to ensure that there is no
information for outstanding transactions in the log). Copy the
old log files into a safe place in case something went wrong
during the shutdown and you need them to recover the
tablespace. Delete the old log files from the log file
directory, edit my.cnf to change the log
file configuration, and start the MySQL server again.
mysqld sees that no
InnoDB log files exist at startup
and creates new ones.
If innodb_fast_shutdown is
set to 2: Set
innodb_fast_shutdown to 1:
mysql> SET GLOBAL innodb_fast_shutdown = 1;
Then follow the instructions in the previous item.
13.6.6. Backing Up and Recovering an InnoDB
Database
The key to safe database management is making regular backups.
InnoDB Hot Backup enables you to back up a
running MySQL database, including
InnoDB and
MyISAM tables, with minimal
disruption to operations while producing a consistent snapshot of
the database. When InnoDB Hot Backup is copying
InnoDB tables, reads and writes to
both InnoDB and
MyISAM tables can continue. During
the copying of MyISAM tables, reads
(but not writes) to those tables are permitted. In addition,
InnoDB Hot Backup supports creating compressed
backup files, and performing backups of subsets of
InnoDB tables. In conjunction with
MySQL’s binary log, users can perform point-in-time recovery.
InnoDB Hot Backup is commercially licensed by
Innobase Oy. For a more complete description of InnoDB
Hot Backup, see
http://www.innodb.com/products/hot-backup/features/
or download the documentation from
http://www.innodb.com/doc/hot_backup/manual.html.
You can order trial, term, and perpetual licenses from Innobase at
http://www.innodb.com/wp/products/hot-backup/order/.
If you are able to shut down your MySQL server, you can make a
binary backup that consists of all files used by
InnoDB to manage its tables. Use the
following procedure:
Shut down the MySQL server and make sure that it stops without
errors.
Copy all InnoDB data files
(ibdata files and
.ibd files) into a safe place.
Copy all the .frm files for
InnoDB tables to a safe place.
Copy all InnoDB log files
(ib_logfile files) to a safe place.
Copy your my.cnf configuration file or
files to a safe place.
In addition to making binary backups as just described, you should
also regularly make dumps of your tables with
mysqldump. The reason for this is that a binary
file might be corrupted without you noticing it. Dumped tables are
stored into text files that are human-readable, so spotting table
corruption becomes easier. Also, because the format is simpler,
the chance for serious data corruption is smaller.
mysqldump also has a
--single-transaction option for
making a consistent snapshot without locking out other clients.
See Section 6.3.1, “Backup Policy”.
Replication works with InnoDB tables,
so you can use MySQL replication capabilities to keep a copy of
your database at database sites requiring high availability.
To be able to recover your InnoDB
database to the present from the time at which the binary backup
was made, you must run your MySQL server with binary logging
turned on. To achieve point-in-time recovery after restoring a
backup, you can apply changes from the binary log that occurred
after the backup was made. See
Section 6.4, “Point-in-Time (Incremental) Recovery Using the Binary Log”.
To recover from a crash of your MySQL server, the only requirement
is to restart it. InnoDB
automatically checks the logs and performs a roll-forward of the
database to the present. InnoDB
automatically rolls back uncommitted transactions that were
present at the time of the crash. During recovery,
mysqld displays output something like this:
InnoDB: Database was not shut down normally.
InnoDB: Starting recovery from log files...
InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 13674004
InnoDB: Doing recovery: scanned up to log sequence number 0 13739520
InnoDB: Doing recovery: scanned up to log sequence number 0 13805056
InnoDB: Doing recovery: scanned up to log sequence number 0 13870592
InnoDB: Doing recovery: scanned up to log sequence number 0 13936128
...
InnoDB: Doing recovery: scanned up to log sequence number 0 20555264
InnoDB: Doing recovery: scanned up to log sequence number 0 20620800
InnoDB: Doing recovery: scanned up to log sequence number 0 20664692
InnoDB: 1 uncommitted transaction(s) which must be rolled back
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Rolling back trx no 16745
InnoDB: Rolling back of trx no 16745 completed
InnoDB: Rollback of uncommitted transactions completed
InnoDB: Starting an apply batch of log records to the database...
InnoDB: Apply batch completed
InnoDB: Started
mysqld: ready for connections
If your database becomes corrupted or disk failure occurs, you
must perform the recovery using a backup. In the case of
corruption, you should first find a backup that is not corrupted.
After restoring the base backup, do a point-in-time recovery from
the binary log files using mysqlbinlog and
mysql to restore the changes that occurred
after the backup was made.
In some cases of database corruption it is enough just to dump,
drop, and re-create one or a few corrupt tables. You can use the
CHECK TABLE SQL statement to check
whether a table is corrupt, although CHECK
TABLE naturally cannot detect every possible kind of
corruption. You can use the Tablespace Monitor to check the
integrity of the file space management inside the tablespace
files.
In some cases, apparent database page corruption is actually due
to the operating system corrupting its own file cache, and the
data on disk may be okay. It is best first to try restarting your
computer. Doing so may eliminate errors that appeared to be
database page corruption.
13.6.6.1. The InnoDB Recovery Process
InnoDB crash recovery consists of several
steps. The first step, redo log application, is performed during
the initialization, before accepting any connections. If all
changes were flushed from the buffer pool to the tablespaces
(ibdata* and *.ibd
files) at the time of the shutdown or crash, the redo log
application can be skipped. If the redo log files are missing at
startup, InnoDB skips the redo log
application.
The remaining steps after redo log application do not depend on
the redo log (other than for logging the writes) and are
performed in parallel with normal processing. These include:
Rolling back incomplete transactions: Any transactions that
were active at the time of crash or fast shutdown.
Insert buffer merge: Applying changes from the insert buffer
tree (from the shared tablespace) to leaf pages of secondary
indexes as the index pages are read to the buffer pool.
Purge: Deleting delete-marked records that are no longer
visible for any active transaction.
Of these, only rollback of incomplete transactions is special to
crash recovery. The insert buffer merge and the purge are
performed during normal processing.
13.6.6.2. Forcing InnoDB Recovery
If there is database page corruption, you may want to dump your
tables from the database with SELECT INTO ...
OUTFILE . Usually, most of the data obtained in this
way is intact. However, it is possible that the corruption might
cause SELECT * FROM
tbl_name statements or
InnoDB background operations to crash or
assert, or even cause InnoDB roll-forward
recovery to crash. In such cases, you can use the
innodb_force_recovery option to
force the InnoDB storage engine to start up
while preventing background operations from running, so that you
are able to dump your tables. For example, you can add the
following line to the [mysqld] section of
your option file before restarting the server:
[mysqld]
innodb_force_recovery = 4
innodb_force_recovery is 0 by
default (normal startup without forced recovery) The allowable
nonzero values for
innodb_force_recovery follow. A
larger number includes all precautions of smaller numbers. If
you are able to dump your tables with an option value of at most
4, then you are relatively safe that only some data on corrupt
individual pages is lost. A value of 6 is more drastic because
database pages are left in an obsolete state, which in turn may
introduce more corruption into B-trees and other database
structures.
1
(SRV_FORCE_IGNORE_CORRUPT )
Let the server run even if it detects a corrupt page. Try to
make SELECT * FROM
tbl_name jump over
corrupt index records and pages, which helps in dumping
tables.
2
(SRV_FORCE_NO_BACKGROUND )
Prevent the main thread from running. If a crash would occur
during the purge operation, this recovery value prevents it.
3
(SRV_FORCE_NO_TRX_UNDO )
Do not run transaction rollbacks after recovery.
4
(SRV_FORCE_NO_IBUF_MERGE )
Prevent insert buffer merge operations. If they would cause
a crash, do not do them. Do not calculate table statistics.
5
(SRV_FORCE_NO_UNDO_LOG_SCAN )
Do not look at undo logs when starting the database:
InnoDB treats even incomplete
transactions as committed.
6
(SRV_FORCE_NO_LOG_REDO )
Do not do the log roll-forward in connection with recovery.
The database must not otherwise be used with any
nonzero value of
innodb_force_recovery .
As a safety measure, InnoDB prevents users
from performing INSERT ,
UPDATE , or
DELETE operations when
innodb_force_recovery is
greater than 0.
You can SELECT from tables to
dump them, or DROP or
CREATE tables even if forced recovery is
used. If you know that a given table is causing a crash on
rollback, you can drop it. You can also use this to stop a
runaway rollback caused by a failing mass import or
ALTER TABLE . You can kill the
mysqld process and set
innodb_force_recovery to
3 to bring the database up without the
rollback, then DROP the table that is causing
the runaway rollback.
13.6.6.3. InnoDB Checkpoints
InnoDB implements a checkpoint mechanism
known as “fuzzy” checkpointing.
InnoDB flushes modified database pages from
the buffer pool in small batches. There is no need to flush the
buffer pool in one single batch, which would in practice stop
processing of user SQL statements during the checkpointing
process.
During crash recovery, InnoDB looks for a
checkpoint label written to the log files. It knows that all
modifications to the database before the label are present in
the disk image of the database. Then InnoDB
scans the log files forward from the checkpoint, applying the
logged modifications to the database.
InnoDB writes to its log files on a rotating
basis. It also writes checkpoint information to the first log
file at each checkpoint. All committed modifications that make
the database pages in the buffer pool different from the images
on disk must be available in the log files in case
InnoDB has to do a recovery. This means that
when InnoDB starts to reuse a log file, it
has to make sure that the database page images on disk contain
the modifications logged in the log file that
InnoDB is going to reuse. In other words,
InnoDB must create a checkpoint and this
often involves flushing of modified database pages to disk.
The preceding description explains why making your log files
very large may reduce disk I/O in checkpointing. It often makes
sense to set the total size of the log files as large as the
buffer pool or even larger. The disadvantage of using large log
files is that crash recovery can take longer because there is
more logged information to apply to the database.
13.6.7. Moving an InnoDB Database to Another Machine
On Windows, InnoDB always stores database and
table names internally in lowercase. To move databases in a binary
format from Unix to Windows or from Windows to Unix, you should
create all databases and tables using lowercase names. A
convenient way to accomplish this is to add the following line to
the [mysqld] section of your
my.cnf or my.ini file
before creating any databases or tables:
[mysqld]
lower_case_table_names=1
Like MyISAM data files,
InnoDB data and log files are binary-compatible
on all platforms having the same floating-point number format. You
can move an InnoDB database simply by copying
all the relevant files listed in Section 13.6.6, “Backing Up and Recovering an InnoDB
Database”.
If the floating-point formats differ but you have not used
FLOAT or
DOUBLE data types in your tables,
then the procedure is the same: simply copy the relevant files. If
you use mysqldump to dump your tables on one
machine and then import the dump files on the other machine, it
does not matter whether the formats differ or your tables contain
floating-point data.
One way to increase performance is to switch off autocommit mode
when importing data, assuming that the tablespace has enough space
for the big rollback segment that the import transactions
generate. Do the commit only after importing a whole table or a
segment of a table.
13.6.8. The InnoDB Transaction Model and Locking
In the InnoDB transaction model, the goal is to
combine the best properties of a multi-versioning database with
traditional two-phase locking. InnoDB does
locking on the row level and runs queries as nonlocking consistent
reads by default, in the style of Oracle. The lock table in
InnoDB is stored so space-efficiently that lock
escalation is not needed: Typically several users are allowed to
lock every row in InnoDB tables, or any random
subset of the rows, without causing InnoDB
memory exhaustion.
In InnoDB , all user activity occurs inside a
transaction. If autocommit mode is enabled, each SQL statement
forms a single transaction on its own. By default, MySQL starts
the session for each new connection with autocommit enabled, so
MySQL does a commit after each SQL statement if that statement did
not return an error. If a statement returns an error, the commit
or rollback behavior depends on the error. See
Section 13.6.12, “InnoDB Error Handling”.
A session that has autocommit enabled can perform a
multiple-statement transaction by starting it with an explicit
START
TRANSACTION or
BEGIN statement
and ending it with a COMMIT or
ROLLBACK
statement.
If autocommit mode is disabled within a session with SET
autocommit = 0 , the session always has a transaction
open. A COMMIT or
ROLLBACK
statement ends the current transaction and a new one starts.
A COMMIT means that the changes
made in the current transaction are made permanent and become
visible to other sessions. A
ROLLBACK
statement, on the other hand, cancels all modifications made by
the current transaction. Both
COMMIT and
ROLLBACK release
all InnoDB locks that were set during the
current transaction.
In terms of the SQL:1992 transaction isolation levels, the default
InnoDB level is
REPEATABLE READ .
InnoDB offers all four transaction isolation
levels described by the SQL standard:
READ UNCOMMITTED ,
READ COMMITTED ,
REPEATABLE READ , and
SERIALIZABLE .
A user can change the isolation level for a single session or for
all subsequent connections with the SET
TRANSACTION statement. To set the server's default
isolation level for all connections, use the
--transaction-isolation option on
the command line or in an option file. For detailed information
about isolation levels and level-setting syntax, see
Section 12.4.6, “SET TRANSACTION Syntax”.
In row-level locking, InnoDB normally uses
next-key locking. That means that besides index records,
InnoDB can also lock the “gap”
preceding an index record to block insertions by other sessions in
the gap immediately before the index record. A next-key lock
refers to a lock that locks an index record and the gap before it.
A gap lock refers to a lock that locks only the gap before some
index record.
For more information about row-level locking, and the
circumstances under which gap locking is disabled, see
Section 13.6.8.4, “InnoDB Record, Gap, and Next-Key Locks”.
13.6.8.1. InnoDB Lock Modes
InnoDB implements standard row-level locking
where there are two types of locks:
If transaction T1 holds a shared
(S ) lock on row r ,
then requests from some distinct transaction
T2 for a lock on row r are
handled as follows:
A request by T2 for an
S lock can be granted
immediately. As a result, both T1 and
T2 hold an S
lock on r .
A request by T2 for an
X lock cannot be granted
immediately.
If a transaction T1 holds an exclusive
(X ) lock on row r ,
a request from some distinct transaction T2
for a lock of either type on r cannot be
granted immediately. Instead, transaction T2
has to wait for transaction T1 to release its
lock on row r .
Additionally, InnoDB supports
multiple granularity locking which allows
coexistence of record locks and locks on entire tables. To make
locking at multiple granularity levels practical, additional
types of locks called intention locks are
used. Intention locks are table locks in
InnoDB . The idea behind intention locks is
for a transaction to indicate which type of lock (shared or
exclusive) it will require later for a row in that table. There
are two types of intention locks used in
InnoDB (assume that transaction
T has requested a lock of the indicated type
on table t ):
For example, SELECT ...
LOCK IN SHARE MODE sets an
IS lock and
SELECT ... FOR
UPDATE sets an IX lock.
The intention locking protocol is as follows:
Before a transaction can acquire an
S lock on a row in table
t , it must first acquire an
IS or stronger lock on
t .
Before a transaction can acquire an
X lock on a row, it must first
acquire an IX lock on
t .
These rules can be conveniently summarized by means of the
following lock type compatibility matrix.
A lock is granted to a requesting transaction if it is
compatible with existing locks, but not if it conflicts with
existing locks. A transaction waits until the conflicting
existing lock is released. If a lock request conflicts with an
existing lock and cannot be granted because it would cause
deadlock, an error occurs.
Thus, intention locks do not block anything except full table
requests (for example, LOCK TABLES ...
WRITE ). The main purpose of
IX and IS
locks is to show that someone is locking a row, or going to lock
a row in the table.
The following example illustrates how an error can occur when a
lock request would cause a deadlock. The example involves two
clients, A and B.
First, client A creates a table containing one row, and then
begins a transaction. Within the transaction, A obtains an
S lock on the row by selecting it in
share mode:
mysql> CREATE TABLE t (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec)
mysql> INSERT INTO t (i) VALUES(1);
Query OK, 1 row affected (0.09 sec)
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t WHERE i = 1 LOCK IN SHARE MODE;
+------+
| i |
+------+
| 1 |
+------+
1 row in set (0.10 sec)
Next, client B begins a transaction and attempts to delete the
row from the table:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> DELETE FROM t WHERE i = 1;
The delete operation requires an X
lock. The lock cannot be granted because it is incompatible with
the S lock that client A holds, so
the request goes on the queue of lock requests for the row and
client B blocks.
Finally, client A also attempts to delete the row from the
table:
mysql> DELETE FROM t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock;
try restarting transaction
Deadlock occurs here because client A needs an
X lock to delete the row. However,
that lock request cannot be granted because client B already has
a request for an X lock and is
waiting for client A to release its S
lock. Nor can the S lock held by A be
upgraded to an X lock because of the
prior request by B for an X lock. As
a result, InnoDB generates an error for
client A and releases its locks. At that point, the lock request
for client B can be granted and B deletes the row from the
table.
13.6.8.2. Consistent Nonlocking Reads
A consistent read means that InnoDB uses
multi-versioning to present to a query a snapshot of the
database at a point in time. The query sees the changes made by
transactions that committed before that point of time, and no
changes made by later or uncommitted transactions. The exception
to this rule is that the query sees the changes made by earlier
statements within the same transaction. This exception causes
the following anomaly: If you update some rows in a table, a
SELECT will see the latest
version of the updated rows, but it might also see older
versions of any rows. If other sessions simultaneously update
the same table, the anomaly means that you may see the table in
a state that never existed in the database.
If the transaction isolation level is
REPEATABLE READ (the default
level), all consistent reads within the same transaction read
the snapshot established by the first such read in that
transaction. You can get a fresher snapshot for your queries by
committing the current transaction and after that issuing new
queries.
With READ COMMITTED isolation
level, each consistent read within a transaction sets and reads
its own fresh snapshot.
Consistent read is the default mode in which
InnoDB processes
SELECT statements in
READ COMMITTED and
REPEATABLE READ isolation
levels. A consistent read does not set any locks on the tables
it accesses, and therefore other sessions are free to modify
those tables at the same time a consistent read is being
performed on the table.
Suppose that you are running in the default
REPEATABLE READ isolation
level. When you issue a consistent read (that is, an ordinary
SELECT statement),
InnoDB gives your transaction a timepoint
according to which your query sees the database. If another
transaction deletes a row and commits after your timepoint was
assigned, you do not see the row as having been deleted. Inserts
and updates are treated similarly.
You can advance your timepoint by committing your transaction
and then doing another SELECT .
This is called multi-versioned concurrency
control.
In the following example, session A sees the row inserted by B
only when B has committed the insert and A has committed as
well, so that the timepoint is advanced past the commit of B.
Session A Session B
SET autocommit=0; SET autocommit=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
---------------------
| 1 | 2 |
---------------------
1 row in set
If you want to see the “freshest” state of the
database, you should use either the
READ COMMITTED isolation
level or a locking read:
SELECT * FROM t LOCK IN SHARE MODE;
With READ COMMITTED isolation
level, each consistent read within a transaction sets and reads
its own fresh snapshot. With LOCK IN SHARE
MODE , a locking read occurs instead: A
SELECT blocks until the transaction
containing the freshest rows ends (see
Section 13.6.8.3, “SELECT ... FOR UPDATE
and SELECT ... LOCK IN
SHARE MODE Locking Reads”).
Consistent read does not work over DROP
TABLE or over ALTER
TABLE :
Consistent read does not work over DROP
TABLE because MySQL cannot use a table that has
been dropped and InnoDB destroys the
table.
Consistent read does not work over
ALTER TABLE because
ALTER TABLE works by making a
temporary copy of the original table and deleting the
original table when the temporary copy is built. When you
reissue a consistent read within a transaction, rows in the
new table are not visible because those rows did not exist
when the transaction's snapshot was taken.
InnoDB uses a consistent read for select in
clauses like INSERT INTO
... SELECT ,
UPDATE ...
(SELECT) , and
CREATE TABLE ...
SELECT that do not specify FOR
UPDATE or LOCK IN SHARE MODE if the
innodb_locks_unsafe_for_binlog
option is set and the isolation level of the transaction is not
set to SERIALIZABLE . Thus, no
locks are set on rows read from the selected table. Otherwise,
InnoDB uses stronger locks and the
SELECT part acts like
READ COMMITTED , where each
consistent read, even within the same transaction, sets and
reads its own fresh snapshot.
13.6.8.3. SELECT ... FOR UPDATE
and SELECT ... LOCK IN
SHARE MODE Locking Reads
In some circumstances, a consistent (nonlocking) read is not
convenient and a locking read is required instead.
InnoDB supports two types of locking reads:
SELECT ... LOCK IN
SHARE MODE sets a shared mode lock on the rows
read. A shared mode lock enables other sessions to read the
rows but not to modify them. The rows read are the latest
available, so if they belong to another transaction that has
not yet committed, the read blocks until that transaction
ends.
For index records the search encounters,
SELECT ... FOR
UPDATE blocks other sessions from doing
SELECT ... LOCK IN
SHARE MODE or from reading in certain transaction
isolation levels. Consistent reads will ignore any locks set
on the records that exist in the read view. (Old versions of
a record cannot be locked; they will be reconstructed by
applying undo logs on an in-memory copy of the record.)
Locks set by LOCK IN SHARE MODE and
FOR UPDATE reads are released when the
transaction is committed or rolled back.
As an example of a situation in which a locking read is useful,
suppose that you want to insert a new row into a table
child , and make sure that the child row has a
parent row in table parent . The following
discussion describes how to implement referential integrity in
application code.
Suppose that you use a consistent read to read the table
parent and indeed see the parent row of the
to-be-inserted child row in the table. Can you safely insert the
child row to table child ? No, because it is
possible for some other session to delete the parent row from
the table parent in the meantime without you
being aware of it.
The solution is to perform the
SELECT in a locking mode using
LOCK IN SHARE MODE :
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
A read performed with LOCK IN SHARE MODE
reads the latest available data and sets a shared mode lock on
the rows read. A shared mode lock prevents others from updating
or deleting the row read. Also, if the latest data belongs to a
yet uncommitted transaction of another session, we wait until
that transaction ends. After we see that the LOCK IN
SHARE MODE query returns the parent
'Jones' , we can safely add the child record
to the child table and commit our
transaction.
Let us look at another example: We have an integer counter field
in a table child_codes that we use to assign
a unique identifier to each child added to table
child . It is not a good idea to use either
consistent read or a shared mode read to read the present value
of the counter because two users of the database may then see
the same value for the counter, and a duplicate-key error occurs
if two users attempt to add children with the same identifier to
the table.
Here, LOCK IN SHARE MODE is not a good
solution because if two users read the counter at the same time,
at least one of them ends up in deadlock when it attempts to
update the counter.
In this case, there are two good ways to implement reading and
incrementing the counter:
First update the counter by incrementing it by 1, and then
read it.
First perform a locking read of the counter using
FOR UPDATE , and then increment the
counter.
The latter approach can be implemented as follows:
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;
A SELECT ... FOR
UPDATE reads the latest available data, setting
exclusive locks on each row it reads. Thus, it sets the same
locks a searched SQL UPDATE would
set on the rows.
The preceding description is merely an example of how
SELECT ... FOR
UPDATE works. In MySQL, the specific task of
generating a unique identifier actually can be accomplished
using only a single access to the table:
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();
The SELECT statement merely
retrieves the identifier information (specific to the current
connection). It does not access any table.
Note
Locking of rows for update using SELECT FOR
UPDATE only applies when autocommit is disabled
(either by beginning transaction with
START
TRANSACTION or by setting
autocommit to 0. If
autocommit is enabled, the rows matching the specification are
not locked.
13.6.8.4. InnoDB Record, Gap, and Next-Key Locks
InnoDB has several types of record-level
locks:
Record lock: This is a lock on an index record.
Gap lock: This is a lock on a gap between index records, or
a lock on the gap before the first or after the last index
record.
Next-key lock: This is a combination of a record lock on the
index record and a gap lock on the gap before the index
record.
Record locks always lock index records, even if a table is
defined with no indexes. For such cases,
InnoDB creates a hidden clustered index and
uses this index for record locking. See
Section 13.6.10.1, “Clustered and Secondary Indexes”.
By default, InnoDB operates in
REPEATABLE READ transaction
isolation level and with the
innodb_locks_unsafe_for_binlog
system variable disabled. In this case,
InnoDB uses next-key locks for searches and
index scans, which prevents phantom rows (see
Section 13.6.8.5, “Avoiding the Phantom Problem Using Next-Key Locking”).
Next-key locking combines index-row locking with gap locking.
InnoDB performs row-level locking in such a
way that when it searches or scans a table index, it sets shared
or exclusive locks on the index records it encounters. Thus, the
row-level locks are actually index-record locks. In addition, a
next-key lock on an index record also affects the
“gap” before that index record. That is, a next-key
lock is an index-record lock plus a gap lock on the gap
preceding the index record. If one session has a shared or
exclusive lock on record R in an index,
another session cannot insert a new index record in the gap
immediately before R in the index order.
Suppose that an index contains the values 10, 11, 13, and 20.
The possible next-key locks for this index cover the following
intervals, where ( or )
denote exclusion of the interval endpoint and
[ or ] denote inclusion of
the endpoint:
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
For the last interval, the next-key lock locks the gap above the
largest value in the index and the “supremum”
pseudo-record having a value higher than any value actually in
the index. The supremum is not a real index record, so, in
effect, this next-key lock locks only the gap following the
largest index value.
The preceding example shows that a gap might span a single index
value, multiple index values, or even be empty.
Gap locking is not needed for statements that lock rows using a
unique index to search for a unique row. For example, if the
id column has a unique index, the following
statement uses only an index-record lock for the row having
id value 100 and it does not matter whether
other sessions insert rows in the preceding gap:
SELECT * FROM child WHERE id = 100;
If id is not indexed or has a nonunique
index, the statement does lock the preceding gap.
A type of gap lock called an insertion intention gap lock is set
by INSERT operations prior to row
insertion. This lock signals the intent to insert in such a way
that multiple transactions inserting into the same index gap
need not wait for each other if they are not inserting at the
same position within the gap. Suppose that there are index
records with values of 4 and 7. Separate transactions that
attempt to insert values of 5 and 6 each lock the gap between 4
and 7 with insert intention locks prior to obtaining the
exclusive lock on the inserted row, but do not block each other
because the rows are nonconflicting.
Gap locking can be disabled explicitly. This occurs if you
change the transaction isolation level to
READ COMMITTED or enable the
innodb_locks_unsafe_for_binlog
system variable. Under these circumstances, gap locking is
disabled for searches and index scans and is used only for
foreign-key constraint checking and duplicate-key checking.
There are also other effects of using the
READ COMMITTED isolation
level or enabling
innodb_locks_unsafe_for_binlog :
Record locks for nonmatching rows are released after MySQL has
evaluated the WHERE condition. For
UPDATE statements,
InnoDB does a
“semi-consistent” read, such that it returns the
latest committed version to MySQL so that MySQL can determine
whether the row matches the WHERE condition
of the UPDATE .
13.6.8.5. Avoiding the Phantom Problem Using Next-Key Locking
The so-called phantom problem occurs
within a transaction when the same query produces different sets
of rows at different times. For example, if a
SELECT is executed twice, but
returns a row the second time that was not returned the first
time, the row is a “phantom” row.
Suppose that there is an index on the id
column of the child table and that you want
to read and lock all rows from the table having an identifier
value larger than 100, with the intention of updating some
column in the selected rows later:
SELECT * FROM child WHERE id > 100 FOR UPDATE;
The query scans the index starting from the first record where
id is bigger than 100. Let the table contain
rows having id values of 90 and 102. If the
locks set on the index records in the scanned range do not lock
out inserts made in the gaps (in this case, the gap between 90
and 102), another session can insert a new row into the table
with an id of 101. If you were to execute the
same SELECT within the same
transaction, you would see a new row with an
id of 101 (a “phantom”) in the
result set returned by the query. If we regard a set of rows as
a data item, the new phantom child would violate the isolation
principle of transactions that a transaction should be able to
run so that the data it has read does not change during the
transaction.
To prevent phantoms, InnoDB uses an algorithm
called next-key locking that combines
index-row locking with gap locking. InnoDB
performs row-level locking in such a way that when it searches
or scans a table index, it sets shared or exclusive locks on the
index records it encounters. Thus, the row-level locks are
actually index-record locks. In addition, a next-key lock on an
index record also affects the “gap” before that
index record. That is, a next-key lock is an index-record lock
plus a gap lock on the gap preceding the index record. If one
session has a shared or exclusive lock on record
R in an index, another session cannot insert
a new index record in the gap immediately before
R in the index order.
When InnoDB scans an index, it can also lock
the gap after the last record in the index. Just that happens in
the preceding example: To prevent any insert into the table
where id would be bigger than 100, the locks
set by InnoDB include a lock on the gap
following id value 102.
You can use next-key locking to implement a uniqueness check in
your application: If you read your data in share mode and do not
see a duplicate for a row you are going to insert, then you can
safely insert your row and know that the next-key lock set on
the successor of your row during the read prevents anyone
meanwhile inserting a duplicate for your row. Thus, the next-key
locking allows you to “lock” the nonexistence of
something in your table.
Gap locking can be disabled as discussed in
Section 13.6.8.4, “InnoDB Record, Gap, and Next-Key Locks”. This may cause
phantom problems because other sessions can insert new rows into
the gaps when gap locking is disabled.
13.6.8.6. Locks Set by Different SQL Statements in InnoDB
A locking read, an UPDATE , or a
DELETE generally set record locks
on every index record that is scanned in the processing of the
SQL statement. It does not matter whether there are
WHERE conditions in the statement that would
exclude the row. InnoDB does not remember the
exact WHERE condition, but only knows which
index ranges were scanned. The locks are normally next-key locks
that also block inserts into the “gap” immediately
before the record. However, gap locking can be disabled
explicitly, which causes next-key locking not to be used. For
more information, see
Section 13.6.8.4, “InnoDB Record, Gap, and Next-Key Locks”. The transaction
isolation level also can affect which locks are set; see
Section 12.4.6, “SET TRANSACTION Syntax”.
If a secondary index is used in a search and index record locks
to be set are exclusive, InnoDB also
retrieves the corresponding clustered index records and sets
locks on them.
Differences between shared and exclusive locks are described in
Section 13.6.8.1, “InnoDB Lock Modes”.
If you have no indexes suitable for your statement and MySQL
must scan the entire table to process the statement, every row
of the table becomes locked, which in turn blocks all inserts by
other users to the table. It is important to create good indexes
so that your queries do not unnecessarily scan many rows.
For SELECT ... FOR
UPDATE or
SELECT ... LOCK IN SHARE
MODE , locks are acquired for scanned rows, and
expected to be released for rows that do not qualify for
inclusion in the result set (for example, if they do not meet
the criteria given in the WHERE clause).
However, in some cases, rows might not be unlocked immediately
because the relationship between a result row and its original
source is lost during query execution. For example, in a
UNION , scanned (and locked) rows
from a table might be inserted into a temporary table before
evaluation whether they qualify for the result set. In this
circumstance, the relationship of the rows in the temporary
table to the rows in the original table is lost and the latter
rows are not unlocked until the end of query execution.
InnoDB sets specific types of locks as
follows.
SELECT ...
FROM is a consistent read, reading a snapshot of
the database and setting no locks unless the transaction
isolation level is set to
SERIALIZABLE . For
SERIALIZABLE level, the
search sets shared next-key locks on the index records it
encounters.
SELECT ... FROM ...
LOCK IN SHARE MODE sets shared next-key locks on
all index records the search encounters.
For index records the search encounters,
SELECT ... FROM ...
FOR UPDATE blocks other sessions from doing
SELECT ... FROM ...
LOCK IN SHARE MODE or from reading in certain
transaction isolation levels. Consistent reads will ignore
any locks set on the records that exist in the read view.
UPDATE ... WHERE
... sets an exclusive next-key lock on every
record the search encounters.
DELETE FROM ...
WHERE ... sets an exclusive next-key lock on every
record the search encounters.
INSERT sets an exclusive lock
on the inserted row. This lock is an index-record lock, not
a next-key lock (that is, there is no gap lock) and does not
prevent other sessions from inserting into the gap before
the inserted row.
Prior to inserting the row, a type of gap lock called an
insertion intention gap lock is set. This lock signals the
intent to insert in such a way that multiple transactions
inserting into the same index gap need not wait for each
other if they are not inserting at the same position within
the gap. Suppose that there are index records with values of
4 and 7. Separate transactions that attempt to insert values
of 5 and 6 each lock the gap between 4 and 7 with insert
intention locks prior to obtaining the exclusive lock on the
inserted row, but do not block each other because the rows
are nonconflicting.
If a duplicate-key error occurs, a shared lock on the
duplicate index record is set. This use of a shared lock can
result in deadlock should there be multiple sessions trying
to insert the same row if another session already has an
exclusive lock. This can occur if another session deletes
the row. Suppose that an InnoDB table
t1 has the following structure:
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
Now suppose that three sessions perform the following
operations in order:
Session 1:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:
ROLLBACK;
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
in a duplicate-key error and they both request a shared lock
for the row. When session 1 rolls back, it releases its
exclusive lock on the row and the queued shared lock
requests for sessions 2 and 3 are granted. At this point,
sessions 2 and 3 deadlock: Neither can acquire an exclusive
lock for the row because of the shared lock held by the
other.
A similar situation occurs if the table already contains a
row with key value 1 and three sessions perform the
following operations in order:
Session 1:
START TRANSACTION;
DELETE FROM t1 WHERE i = 1;
Session 2:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 3:
START TRANSACTION;
INSERT INTO t1 VALUES(1);
Session 1:
COMMIT;
The first operation by session 1 acquires an exclusive lock
for the row. The operations by sessions 2 and 3 both result
in a duplicate-key error and they both request a shared lock
for the row. When session 1 commits, it releases its
exclusive lock on the row and the queued shared lock
requests for sessions 2 and 3 are granted. At this point,
sessions 2 and 3 deadlock: Neither can acquire an exclusive
lock for the row because of the shared lock held by the
other.
REPLACE is done like an
INSERT if there is no
collision on a unique key. Otherwise, an exclusive next-key
lock is placed on the row that must be updated.
INSERT INTO T SELECT ... FROM S WHERE ...
sets an exclusive index record without a gap lock on each
row inserted into T . If the transaction
isolation level is READ
COMMITTED or
innodb_locks_unsafe_for_binlog
is enabled, and the transaction isolation level is not
SERIALIZABLE ,
InnoDB does the search on
S as a consistent read (no locks).
Otherwise, InnoDB sets shared next-key
locks on rows from S .
InnoDB has to set locks in the latter
case: In roll-forward recovery from a backup, every SQL
statement must be executed in exactly the same way it was
done originally.
CREATE TABLE
... SELECT ... performs the
SELECT with shared next-key
locks or as a consistent read, as for
INSERT ...
SELECT .
For REPLACE INTO T SELECT ... FROM S WHERE
... , InnoDB sets shared
next-key locks on rows from S .
While initializing a previously specified
AUTO_INCREMENT column on a table,
InnoDB sets an exclusive lock on the end
of the index associated with the
AUTO_INCREMENT column. In accessing the
auto-increment counter, InnoDB uses a
specific AUTO-INC table lock mode where
the lock lasts only to the end of the current SQL statement,
not to the end of the entire transaction. Other sessions
cannot insert into the table while the
AUTO-INC table lock is held; see
Section 13.6.8, “The InnoDB Transaction Model and Locking”.
InnoDB fetches the value of a previously
initialized AUTO_INCREMENT column without
setting any locks.
If a FOREIGN KEY constraint is defined on
a table, any insert, update, or delete that requires the
constraint condition to be checked sets shared record-level
locks on the records that it looks at to check the
constraint. InnoDB also sets these locks
in the case where the constraint fails.
LOCK TABLES sets table locks,
but it is the higher MySQL layer above the
InnoDB layer that sets these locks.
InnoDB is aware of table locks if
innodb_table_locks = 1 (the default) and
autocommit = 0 , and the
MySQL layer above InnoDB knows about
row-level locks.
Otherwise, InnoDB 's automatic deadlock
detection cannot detect deadlocks where such table locks are
involved. Also, because in this case the higher MySQL layer
does not know about row-level locks, it is possible to get a
table lock on a table where another session currently has
row-level locks. However, this does not endanger transaction
integrity, as discussed in
Section 13.6.8.8, “Deadlock Detection and Rollback”. See also
Section 13.6.14, “Restrictions on InnoDB Tables”.
13.6.8.7. Implicit Transaction Commit and Rollback
By default, MySQL starts the session for each new connection
with autocommit mode enabled, so MySQL does a commit after each
SQL statement if that statement did not return an error. If a
statement returns an error, the commit or rollback behavior
depends on the error. See
Section 13.6.12, “InnoDB Error Handling”.
If a session that has autocommit disabled ends without
explicitly committing the final transaction, MySQL rolls back
that transaction.
Some statements implicitly end a transaction, as if you had done
a COMMIT before executing the
statement. For details, see Section 12.4.3, “Statements That Cause an Implicit Commit”.
13.6.8.8. Deadlock Detection and Rollback
InnoDB automatically detects transaction
deadlocks and rolls back a transaction or transactions to break
the deadlock. InnoDB tries to pick small
transactions to roll back, where the size of a transaction is
determined by the number of rows inserted, updated, or deleted.
InnoDB is aware of table locks if
innodb_table_locks = 1 (the default) and
autocommit = 0 , and the MySQL
layer above it knows about row-level locks. Otherwise,
InnoDB cannot detect deadlocks where a table
lock set by a MySQL LOCK TABLES
statement or a lock set by a storage engine other than
InnoDB is involved. You must resolve these
situations by setting the value of the
innodb_lock_wait_timeout system
variable.
When InnoDB performs a complete rollback of a
transaction, all locks set by the transaction are released.
However, if just a single SQL statement is rolled back as a
result of an error, some of the locks set by the statement may
be preserved. This happens because InnoDB
stores row locks in a format such that it cannot know afterward
which lock was set by which statement.
As of MySQL 5.1.24, if a SELECT
calls a stored function in a transaction, and a statement within
the function fails, that statement rolls back. Furthermore, if
ROLLBACK is
executed after that, the entire transaction rolls back. Before
5.1.24, the failed statement did not roll back when it failed
(even though it might ultimately get rolled back by a
ROLLBACK later
that rolls back the entire transaction).
13.6.8.9. How to Cope with Deadlocks
Deadlocks are a classic problem in transactional databases, but
they are not dangerous unless they are so frequent that you
cannot run certain transactions at all. Normally, you must write
your applications so that they are always prepared to re-issue a
transaction if it gets rolled back because of a deadlock.
InnoDB uses automatic row-level locking. You
can get deadlocks even in the case of transactions that just
insert or delete a single row. That is because these operations
are not really “atomic”; they automatically set
locks on the (possibly several) index records of the row
inserted or deleted.
You can cope with deadlocks and reduce the likelihood of their
occurrence with the following techniques:
Use SHOW ENGINE
INNODB STATUS to determine the cause of the latest
deadlock. That can help you to tune your application to
avoid deadlocks.
Always be prepared to re-issue a transaction if it fails due
to deadlock. Deadlocks are not dangerous. Just try again.
Commit your transactions often. Small transactions are less
prone to collision.
If you are using locking reads
(SELECT ... FOR
UPDATE or SELECT ...
LOCK IN SHARE MODE ), try using a lower isolation
level such as READ
COMMITTED .
Access your tables and rows in a fixed order. Then
transactions form well-defined queues and do not deadlock.
Add well-chosen indexes to your tables. Then your queries
need to scan fewer index records and consequently set fewer
locks. Use EXPLAIN
SELECT to determine which indexes the MySQL server
regards as the most appropriate for your queries.
Use less locking. If you can afford to allow a
SELECT to return data from an
old snapshot, do not add the clause FOR
UPDATE or LOCK IN SHARE MODE to
it. Using the READ
COMMITTED isolation level is good here, because
each consistent read within the same transaction reads from
its own fresh snapshot.
If nothing else helps, serialize your transactions with
table-level locks. The correct way to use
LOCK TABLES with
transactional tables, such as InnoDB
tables, is to begin a transaction with SET
autocommit = 0 (not
START
TRANSACTION ) followed by LOCK
TABLES , and to not call
UNLOCK
TABLES until you commit the transaction
explicitly. For example, if you need to write to table
t1 and read from table
t2 , you can do this:
SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;
Table-level locks make your transactions queue nicely and
avoid deadlocks.
Another way to serialize transactions is to create an
auxiliary “semaphore” table that contains just
a single row. Have each transaction update that row before
accessing other tables. In that way, all transactions happen
in a serial fashion. Note that the InnoDB
instant deadlock detection algorithm also works in this
case, because the serializing lock is a row-level lock. With
MySQL table-level locks, the timeout method must be used to
resolve deadlocks.
13.6.9. InnoDB Multi-Versioning
Because InnoDB is a multi-versioned storage
engine, it must keep information about old versions of rows in the
tablespace. This information is stored in a data structure called
a rollback segment (after an analogous data
structure in Oracle).
Internally, InnoDB adds three fields to each
row stored in the database. A 6-byte DB_TRX_ID
field indicates the transaction identifier for the last
transaction that inserted or updated the row. Also, a deletion is
treated internally as an update where a special bit in the row is
set to mark it as deleted. Each row also contains a 7-byte
DB_ROLL_PTR field called the roll pointer. The
roll pointer points to an undo log record written to the rollback
segment. If the row was updated, the undo log record contains the
information necessary to rebuild the content of the row before it
was updated. A 6-byte DB_ROW_ID field contains
a row ID that increases monotonically as new rows are inserted. If
InnoDB generates a clustered index
automatically, the index contains row ID values. Otherwise, the
DB_ROW_ID column does not appear in any index.
InnoDB uses the information in the rollback
segment to perform the undo operations needed in a transaction
rollback. It also uses the information to build earlier versions
of a row for a consistent read.
Undo logs in the rollback segment are divided into insert and
update undo logs. Insert undo logs are needed only in transaction
rollback and can be discarded as soon as the transaction commits.
Update undo logs are used also in consistent reads, but they can
be discarded only after there is no transaction present for which
InnoDB has assigned a snapshot that in a
consistent read could need the information in the update undo log
to build an earlier version of a database row.
You must remember to commit your transactions regularly, including
those transactions that issue only consistent reads. Otherwise,
InnoDB cannot discard data from the update undo
logs, and the rollback segment may grow too big, filling up your
tablespace.
The physical size of an undo log record in the rollback segment is
typically smaller than the corresponding inserted or updated row.
You can use this information to calculate the space need for your
rollback segment.
In the InnoDB multi-versioning scheme, a row is
not physically removed from the database immediately when you
delete it with an SQL statement. Only when
InnoDB can discard the update undo log record
written for the deletion can it also physically remove the
corresponding row and its index records from the database. This
removal operation is called a purge, and it is quite fast, usually
taking the same order of time as the SQL statement that did the
deletion.
In a scenario where the user inserts and deletes rows in smallish
batches at about the same rate in the table, it is possible that
the purge thread starts to lag behind, and the table grows bigger
and bigger, making everything disk-bound and very slow. Even if
the table carries just 10MB of useful data, it may grow to occupy
10GB with all the “dead” rows. In such a case, it
would be good to throttle new row operations and allocate more
resources to the purge thread. The
innodb_max_purge_lag system
variable exists for exactly this purpose. See
Section 13.6.3, “InnoDB Startup Options and System Variables”, for more information.
13.6.10. InnoDB Table and Index Structures
MySQL stores its data dictionary information for tables in
.frm files in database directories. This is
true for all MySQL storage engines, but every
InnoDB table also has its own entry in the
InnoDB internal data dictionary inside the
tablespace. When MySQL drops a table or a database, it has to
delete one or more .frm files as well as the
corresponding entries inside the InnoDB data
dictionary. Consequently, you cannot move
InnoDB tables between databases simply by
moving the .frm files.
13.6.10.1. Clustered and Secondary Indexes
Every InnoDB table has a special index called
the clustered index where the data for
the rows is stored:
If you define a PRIMARY KEY on your
table, InnoDB uses it as the clustered
index.
If you do not define a PRIMARY KEY for
your table, MySQL picks the first UNIQUE
index that has only NOT NULL columns as
the primary key and InnoDB uses it as the
clustered index.
If the table has no PRIMARY KEY or
suitable UNIQUE index,
InnoDB internally generates a hidden
clustered index on a synthetic column containing row ID
values. The rows are ordered by the ID that
InnoDB assigns to the rows in such a
table. The row ID is a 6-byte field that increases
monotonically as new rows are inserted. Thus, the rows
ordered by the row ID are physically in insertion order.
Accessing a row through the clustered index is fast because the
row data is on the same page where the index search leads. If a
table is large, the clustered index architecture often saves a
disk I/O operation when compared to storage organizations that
store row data using a different page from the index record.
(For example, MyISAM uses one file for data
rows and another for index records.)
In InnoDB , the records in nonclustered
indexes (also called secondary indexes) contain the primary key
columns for the row that are not in the secondary index.
InnoDB uses this primary key value to search
for the row in the clustered index. If the primary key is long,
the secondary indexes use more space, so it is advantageous to
have a short primary key.
13.6.10.2. Physical Structure of an Index
All InnoDB indexes are B-trees where the
index records are stored in the leaf pages of the tree. The
default size of an index page is 16KB. When new records are
inserted, InnoDB tries to leave 1/16 of the
page free for future insertions and updates of the index
records.
If index records are inserted in a sequential order (ascending
or descending), the resulting index pages are about 15/16 full.
If records are inserted in a random order, the pages are from
1/2 to 15/16 full. If the fill factor of an index page drops
below 1/2, InnoDB tries to contract the index
tree to free the page.
Note
Changing the page size is not a supported operation and there
is no guarantee that InnoDB will
function normally with a page size other than 16KB. Problems
compiling or running InnoDB may occur. In particular,
ROW_FORMAT=COMPRESSED in the
InnoDB Plugin assumes that the page size is
at most 16KB and uses 14-bit pointers.
A version of InnoDB built for one
page size cannot use data files or log files from a version
built for a different page size.
13.6.10.3. Insert Buffering
It is a common situation in database applications that the
primary key is a unique identifier and new rows are inserted in
the ascending order of the primary key. Thus, insertions into
the clustered index do not require random reads from a disk.
On the other hand, secondary indexes are usually nonunique, and
insertions into secondary indexes happen in a relatively random
order. This would cause a lot of random disk I/O operations
without a special mechanism used in InnoDB .
If an index record should be inserted into a nonunique secondary
index, InnoDB checks whether the secondary
index page is in the buffer pool. If that is the case,
InnoDB does the insertion directly to the
index page. If the index page is not found in the buffer pool,
InnoDB inserts the record to a special insert
buffer structure. The insert buffer is kept so small that it
fits entirely in the buffer pool, and insertions can be done
very fast.
Periodically, the insert buffer is merged into the secondary
index trees in the database. Often it is possible to merge
several insertions into the same page of the index tree, saving
disk I/O operations. It has been measured that the insert buffer
can speed up insertions into a table up to 15 times.
The insert buffer merging may continue to happen
after the inserting transaction has been
committed. In fact, it may continue to happen after a server
shutdown and restart (see Section 13.6.6.2, “Forcing InnoDB Recovery”).
Insert buffer merging may take many hours when many secondary
indexes must be updated and many rows have been inserted. During
this time, disk I/O will be increased, which can cause
significant slowdown on disk-bound queries. Another significant
background I/O operation is the purge thread (see
Section 13.6.9, “InnoDB Multi-Versioning”).
13.6.10.4. Adaptive Hash Indexes
If a table fits almost entirely in main memory, the fastest way
to perform queries on it is to use hash indexes.
InnoDB has a mechanism that monitors index
searches made to the indexes defined for a table. If
InnoDB notices that queries could benefit
from building a hash index, it does so automatically.
The hash index is always built based on an existing B-tree index
on the table. InnoDB can build a hash index
on a prefix of any length of the key defined for the B-tree,
depending on the pattern of searches that
InnoDB observes for the B-tree index. A hash
index can be partial: It is not required that the whole B-tree
index is cached in the buffer pool. InnoDB
builds hash indexes on demand for those pages of the index that
are often accessed.
In a sense, InnoDB tailors itself through the
adaptive hash index mechanism to ample main memory, coming
closer to the architecture of main-memory databases.
13.6.10.5. Physical Row Structure
The physical row structure for an InnoDB
table depends on the row format specified when the table was
created. For MySQL 5.1, InnoDB uses the
COMPACT format by default, but the
REDUNDANT format is available to retain
compatibility with older versions of MySQL. To check the row
format of an InnoDB table, use
SHOW TABLE STATUS .
The compact row format decreases row storage space by about 20%
at the cost of increasing CPU use for some operations. If your
workload is a typical one that is limited by cache hit rates and
disk speed, compact format is likely to be faster. If the
workload is a rare case that is limited by CPU speed, compact
format might be slower.
Rows in InnoDB tables that use
REDUNDANT row format have the following
characteristics:
Each index record contains a six-byte header. The header is
used to link together consecutive records, and also in
row-level locking.
Records in the clustered index contain fields for all
user-defined columns. In addition, there is a six-byte
transaction ID field and a seven-byte roll pointer field.
If no primary key was defined for a table, each clustered
index record also contains a six-byte row ID field.
Each secondary index record also contains all the primary
key fields defined for the clustered index key that are not
in the secondary index.
A record contains a pointer to each field of the record. If
the total length of the fields in a record is less than 128
bytes, the pointer is one byte; otherwise, two bytes. The
array of these pointers is called the record directory. The
area where these pointers point is called the data part of
the record.
Internally, InnoDB stores fixed-length
character columns such as
CHAR(10) in a fixed-length
format. InnoDB does not truncate trailing
spaces from VARCHAR columns.
An SQL NULL value reserves one or two
bytes in the record directory. Besides that, an SQL
NULL value reserves zero bytes in the
data part of the record if stored in a variable length
column. In a fixed-length column, it reserves the fixed
length of the column in the data part of the record.
Reserving the fixed space for NULL values
enables an update of the column from NULL
to a non-NULL value to be done in place
without causing fragmentation of the index page.
Rows in InnoDB tables that use
COMPACT row format have the following
characteristics:
Each index record contains a five-byte header that may be
preceded by a variable-length header. The header is used to
link together consecutive records, and also in row-level
locking.
The variable-length part of the record header contains a bit
vector for indicating NULL columns. If
the number of columns in the index that can be
NULL is N , the
bit vector occupies
CEILING(N /8)
bytes. (For example, if there are anywhere from 9 to 15
columns that can be NULL , the bit vector
uses two bytes.) Columns that are NULL do
not occupy space other than the bit in this vector. The
variable-length part of the header also contains the lengths
of variable-length columns. Each length takes one or two
bytes, depending on the maximum length of the column. If all
columns in the index are NOT NULL and
have a fixed length, the record header has no
variable-length part.
For each non-NULL variable-length field,
the record header contains the length of the column in one
or two bytes. Two bytes will only be needed if part of the
column is stored externally in overflow pages or the maximum
length exceeds 255 bytes and the actual length exceeds 127
bytes. For an externally stored column, the two-byte length
indicates the length of the internally stored part plus the
20-byte pointer to the externally stored part. The internal
part is 768 bytes, so the length is 768+20. The 20-byte
pointer stores the true length of the column.
The record header is followed by the data contents of the
non-NULL columns.
Records in the clustered index contain fields for all
user-defined columns. In addition, there is a six-byte
transaction ID field and a seven-byte roll pointer field.
If no primary key was defined for a table, each clustered
index record also contains a six-byte row ID field.
Each secondary index record also contains all the primary
key fields defined for the clustered index key that are not
in the secondary index. If any of these primary key fields
are variable length, the record header for each secondary
index will have a variable-length part to record their
lengths, even if the secondary index is defined on
fixed-length columns.
Internally, InnoDB stores fixed-length,
fixed-width character columns such as
CHAR(10) in a fixed-length
format. InnoDB does not truncate trailing
spaces from VARCHAR columns.
Internally, InnoDB attempts to store
UTF-8
CHAR(N )
columns in N bytes by trimming
trailing spaces. (With REDUNDANT row
format, such columns occupy 3 ?
N bytes.) Reserving the minimum
space N in many cases enables
column updates to be done in place without causing
fragmentation of the index page.
13.6.11. InnoDB Disk I/O and File Space Management13.6.11.1. InnoDB Disk I/O
InnoDB uses simulated asynchronous disk I/O:
InnoDB creates a number of threads to take
care of I/O operations, such as read-ahead.
There are two read-ahead heuristics in
InnoDB :
In sequential read-ahead, if InnoDB
notices that the access pattern to a segment in the
tablespace is sequential, it posts in advance a batch of
reads of database pages to the I/O system.
In random read-ahead, if InnoDB notices
that some area in a tablespace seems to be in the process of
being fully read into the buffer pool, it posts the
remaining reads to the I/O system.
InnoDB uses a novel file flush technique
called doublewrite. It adds safety to
recovery following an operating system crash or a power outage,
and improves performance on most varieties of Unix by reducing
the need for fsync() operations.
Doublewrite means that before writing pages to a data file,
InnoDB first writes them to a contiguous
tablespace area called the doublewrite buffer. Only after the
write and the flush to the doublewrite buffer has completed does
InnoDB write the pages to their proper
positions in the data file. If the operating system crashes in
the middle of a page write, InnoDB can later
find a good copy of the page from the doublewrite buffer during
recovery.
13.6.11.2. File Space Management
The data files that you define in the configuration file form
the InnoDB tablespace. The files are simply
concatenated to form the tablespace. There is no striping in
use. Currently, you cannot define where within the tablespace
your tables are allocated. However, in a newly created
tablespace, InnoDB allocates space starting
from the first data file.
The tablespace consists of database pages with a default size of
16KB. The pages are grouped into extents of size 1MB (64
consecutive pages). The “files” inside a tablespace
are called segments in
InnoDB . The term “rollback
segment” is somewhat confusing because it actually
contains many tablespace segments.
When a segment grows inside the tablespace,
InnoDB allocates the first 32 pages to it
individually. After that, InnoDB starts to
allocate whole extents to the segment. InnoDB
can add up to 4 extents at a time to a large segment to ensure
good sequentiality of data.
Two segments are allocated for each index in
InnoDB . One is for nonleaf nodes of the
B-tree, the other is for the leaf nodes. The idea here is to
achieve better sequentiality for the leaf nodes, which contain
the data.
Some pages in the tablespace contain bitmaps of other pages, and
therefore a few extents in an InnoDB
tablespace cannot be allocated to segments as a whole, but only
as individual pages.
When you ask for available free space in the tablespace by
issuing a SHOW TABLE STATUS
statement, InnoDB reports the extents that
are definitely free in the tablespace. InnoDB
always reserves some extents for cleanup and other internal
purposes; these reserved extents are not included in the free
space.
When you delete data from a table, InnoDB
contracts the corresponding B-tree indexes. Whether the freed
space becomes available for other users depends on whether the
pattern of deletes frees individual pages or extents to the
tablespace. Dropping a table or deleting all rows from it is
guaranteed to release the space to other users, but remember
that deleted rows are physically removed only in an (automatic)
purge operation after they are no longer needed for transaction
rollbacks or consistent reads. (See
Section 13.6.9, “InnoDB Multi-Versioning”.)
The maximum row length, except for variable-length columns
(VARBINARY ,
VARCHAR ,
BLOB and
TEXT ), is slightly less than half
of a database page. That is, the maximum row length is about
8000 bytes. LONGBLOB and
LONGTEXT columns
must be less than 4GB, and the total row length, including
BLOB and
TEXT columns, must be less than
4GB.
If a row is less than half a page long, all of it is stored
locally within the page. If it exceeds half a page,
variable-length columns are chosen for external off-page storage
until the row fits within half a page. For a column chosen for
off-page storage, InnoDB stores the first 768
bytes locally in the row, and the rest externally into overflow
pages. Each such column has its own list of overflow pages. The
768-byte prefix is accompanied by a 20-byte value that stores
the true length of the column and points into the overflow list
where the rest of the value is stored.
To see information about the tablespace, use the Tablespace
Monitor. See Section 13.6.13.2, “SHOW ENGINE INNODB
STATUS and the InnoDB Monitors”.
13.6.11.3. Defragmenting a Table
If there are random insertions into or deletions from the
indexes of a table, the indexes may become fragmented.
Fragmentation means that the physical ordering of the index
pages on the disk is not close to the index ordering of the
records on the pages, or that there are many unused pages in the
64-page blocks that were allocated to the index.
One symptom of fragmentation is that a table takes more space
than it “should” take. How much that is exactly, is
difficult to determine. All InnoDB data and
indexes are stored in B-trees, and their fill factor may vary
from 50% to 100%. Another symptom of fragmentation is that a
table scan such as this takes more time than it
“should” take:
SELECT COUNT(*) FROM t WHERE a_non_indexed_column <> 12345;
(In the preceding query, we are “fooling” the SQL
optimizer into scanning the clustered index rather than a
secondary index.) Most disks can read 10MB/s to 50MB/s, which
can be used to estimate how fast a table scan should be.
It can speed up index scans if you periodically perform a
“null” ALTER TABLE
operation, which causes MySQL to rebuild the table:
ALTER TABLE tbl_name ENGINE=INNODB
Another way to perform a defragmentation operation is to use
mysqldump to dump the table to a text file,
drop the table, and reload it from the dump file.
If the insertions into an index are always ascending and records
are deleted only from the end, the InnoDB
filespace management algorithm guarantees that fragmentation in
the index does not occur.
13.6.12. InnoDB Error Handling
Error handling in InnoDB is not always the same
as specified in the SQL standard. According to the standard, any
error during an SQL statement should cause rollback of that
statement. InnoDB sometimes rolls back only
part of the statement, or the whole transaction. The following
items describe how InnoDB performs error
handling:
If you run out of file space in the tablespace, a MySQL
Table is full error occurs and
InnoDB rolls back the SQL statement.
A transaction deadlock causes InnoDB to
roll back the entire transaction. You should normally retry
the whole transaction when this happens.
A lock wait timeout causes InnoDB to roll
back only the single statement that was waiting for the lock
and encountered the timeout. (To have the entire transaction
roll back, start the server with the
--innodb_rollback_on_timeout
option, available as of MySQL 5.1.15.) You should normally
retry the statement if using the current behavior or the
entire transaction if using
--innodb_rollback_on_timeout .
Both deadlocks and lock wait timeouts are normal on busy
servers and it is necessary for applications to be aware that
they may happen and handle them by retrying. You can make them
less likely by doing as little work as possible between the
first change to data during a transaction and the commit, so
the locks are held for the shortest possible time and for the
smallest possible number of rows. Sometimes splitting work
between different transactions may be practical and helpful.
When a transaction rollback occurs due to a deadlock or lock
wait timeout, it cancels the effect of the statements within
the transaction. But if the start-transaction statement was
START
TRANSACTION or
BEGIN
statement, rollback does not cancel that statement. Further
SQL statements become part of the transaction until the
occurrence of COMMIT ,
ROLLBACK , or
some SQL statement that causes an implicit commit.
A duplicate-key error rolls back the SQL statement, if you
have not specified the IGNORE option in
your statement.
A row too long error rolls back the SQL
statement.
Other errors are mostly detected by the MySQL layer of code
(above the InnoDB storage engine level),
and they roll back the corresponding SQL statement. Locks are
not released in a rollback of a single SQL statement.
During implicit rollbacks, as well as during the execution of an
explicit
ROLLBACK SQL
statement, SHOW PROCESSLIST
displays Rolling back in the
State column for the relevant connection.
13.6.12.1. InnoDB Error Codes
The following is a nonexhaustive list of common
InnoDB -specific errors that you may
encounter, with information about why each occurs and how to
resolve the problem.
1005 (ER_CANT_CREATE_TABLE)
Cannot create table. If the error message refers to error
150, table creation failed because a foreign key constraint
was not correctly formed. If the error message refers to
error –1, table creation probably failed because the
table includes a column name that matched the name of an
internal InnoDB table.
1016 (ER_CANT_OPEN_FILE)
Cannot find the InnoDB table from the
InnoDB data files, although the
.frm file for the table exists. See
Section 13.6.13.4, “Troubleshooting InnoDB Data Dictionary Operations”.
1114 (ER_RECORD_FILE_FULL)
InnoDB has run out of free space in the
tablespace. You should reconfigure the tablespace to add a
new data file.
1205 (ER_LOCK_WAIT_TIMEOUT)
Lock wait timeout expired. Transaction was rolled back.
1206 (ER_LOCK_TABLE_FULL)
The total number of locks exceeds the lock table size. To
avoid this error, increase the value of
innodb_buffer_pool_size .
Within an individual application, a workaround may be to
break a large operation into smaller pieces. For example, if
the error occurs for a large
INSERT , perform several
smaller INSERT operations.
1213 (ER_LOCK_DEADLOCK)
Transaction deadlock. You should rerun the transaction.
1216 (ER_NO_REFERENCED_ROW)
You are trying to add a row but there is no parent row, and
a foreign key constraint fails. You should add the parent
row first.
1217 (ER_ROW_IS_REFERENCED)
You are trying to delete a parent row that has children, and
a foreign key constraint fails. You should delete the
children first.
13.6.12.2. Operating System Error Codes
To print the meaning of an operating system error number, use
the perror program that comes with the MySQL
distribution.
Linux System Error Codes
The following table provides a list of some common Linux
system error codes. For a more complete list, see
Linux
source code.
Windows System Error Codes
The following table provides a list of some common Windows
system error codes. For a complete list, see the
Microsoft
Web site.
13.6.13. InnoDB Performance Tuning and Troubleshooting13.6.13.1. InnoDB Performance Tuning Tips
The followings tips are grouped by category. Some of them can
apply in multiple categories, so it is useful to read them all.
Storage Layout Tips
In InnoDB , having a long PRIMARY
KEY wastes a lot of disk space because its value
must be stored with every secondary index record. (See
Section 13.6.10, “InnoDB Table and Index Structures”.) Create an
AUTO_INCREMENT column as the primary key
if your primary key is long.
Use the VARCHAR data type
instead of CHAR if you are
storing variable-length strings or if the column may contain
many NULL values. A
CHAR(N )
column always takes N characters
to store data, even if the string is shorter or its value is
NULL . Smaller tables fit better in the
buffer pool and reduce disk I/O.
When using COMPACT row format (the
default InnoDB format in MySQL
5.1) and variable-length character sets, such
as utf8 or sjis ,
CHAR(N )
will occupy a variable amount of space, at least
N bytes.
Transaction Management Tips
Wrap several modifications into a single transaction to
reduce the number of flush operations.
InnoDB must flush the log to disk at each
transaction commit if that transaction made modifications to
the database. The rotation speed of a disk is typically at
most 167 revolutions/second (for a 10,000RPM disk), which
constrains the number of commits to the same
167th of a second if the disk
does not “fool” the operating system.
If you can afford the loss of some of the latest committed
transactions if a crash occurs, you can set the
innodb_flush_log_at_trx_commit
parameter to 0. InnoDB tries to flush the
log once per second anyway, although the flush is not
guaranteed. You should also set the value of
innodb_support_xa to 0,
which will reduce the number of disk flushes due to
synchronizing on disk data and the binary log.
Disk I/O Tips
innodb_buffer_pool_size
specifies the size of the buffer pool. If your buffer pool
is small and you have sufficient memory, making the pool
larger can improve performance by reducing the amount of
disk I/O needed as queries access
InnoDB tables. For more
information about the pool, see
Section 7.4.6, “The InnoDB Buffer Pool”.
Beware of big rollbacks of mass inserts:
InnoDB uses the insert buffer to save
disk I/O in inserts, but no such mechanism is used in a
corresponding rollback. A disk-bound rollback can take 30
times as long to perform as the corresponding insert.
Killing the database process does not help because the
rollback starts again on server startup. The only way to get
rid of a runaway rollback is to increase the buffer pool so
that the rollback becomes CPU-bound and runs fast, or to use
a special procedure. See Section 13.6.6.2, “Forcing InnoDB Recovery”.
Beware also of other big disk-bound operations. Use
DROP TABLE and
CREATE TABLE to empty a
table, not DELETE FROM
tbl_name .
In some versions of GNU/Linux and Unix, flushing files to
disk with the Unix fsync() call (which
InnoDB uses by default) and other similar
methods is surprisingly slow. If you are dissatisfied with
database write performance, you might try setting the
innodb_flush_method
parameter to O_DSYNC . The
O_DSYNC flush method seems to perform
slower on most systems, but yours might not be one of them.
When using the InnoDB storage engine on
Solaris 10 for x86_64 architecture (AMD Opteron), it is
important to use direct I/O for
InnoDB -related files. Failure to do so
may cause degradation of InnoDB 's speed
and performance on this platform. To use direct I/O for an
entire UFS file system used for storing
InnoDB -related files, mount it with the
forcedirectio option; see
mount_ufs(1M) . (The default on Solaris
10/x86_64 is not to use this option.)
Alternatively, as of MySQL 5.1.18 you can set
innodb_flush_method =
O_DIRECT if you do not want to affect the entire
file system. This causes InnoDB to call
directio() instead of
fcntl() . However, setting
innodb_flush_method to
O_DIRECT causes InnoDB
to use direct I/O only for data files, not the log files.
When using the InnoDB storage engine with
a large
innodb_buffer_pool_size
value on any release of Solaris 2.6 and up and any platform
(sparc/x86/x64/amd64), a significant performance gain might
be achieved by placing InnoDB data files
and log files on raw devices or on a separate direct I/O UFS
file system using the forcedirectio mount
option as described earlier (it is necessary to use the
mount option rather than setting
innodb_flush_method if you
want direct I/O for the log files). Users of the Veritas
file system VxFS should use the
convosync=direct mount option. You are
advised to perform tests with and without raw partitions or
direct I/O file systems to verify whether performance is
improved on your system.
Other MySQL data files, such as those for
MyISAM tables, should not be placed on a
direct I/O file system. Executables or libraries
must not be placed on a direct I/O file
system.
If the Unix top tool or the Windows
Task Manager shows that the CPU usage percentage with your
workload is less than 70%, your workload is probably
disk-bound. Maybe you are making too many transaction
commits, or the buffer pool is too small. Making the buffer
pool bigger can help, but do not set it equal to more than
80% of physical memory.
Logging Tips
Make your log files big, even as big as the buffer pool.
When InnoDB has written the log files
full, it must write the modified contents of the buffer pool
to disk in a checkpoint. Small log files cause many
unnecessary disk writes. The disadvantage of big log files
is that the recovery time is longer.
Make the log buffer quite large as well (on the order of
8MB).
Bulk Data Loading Tips
When importing data into InnoDB , make
sure that MySQL does not have autocommit mode enabled
because that requires a log flush to disk for every insert.
To disable autocommit during your import operation, surround
it with SET
autocommit and
COMMIT statements:
SET autocommit=0;
... SQL import statements ...
COMMIT;
If you use the mysqldump option
--opt , you get dump files
that are fast to import into an InnoDB
table, even without wrapping them with the
SET
autocommit and
COMMIT statements.
If you have UNIQUE constraints on
secondary keys, you can speed up table imports by
temporarily turning off the uniqueness checks during the
import session:
SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;
For big tables, this saves a lot of disk I/O because
InnoDB can use its insert buffer to write
secondary index records in a batch. Be certain that the data
contains no duplicate keys.
If you have FOREIGN KEY constraints in
your tables, you can speed up table imports by turning the
foreign key checks off for the duration of the import
session:
SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;
For big tables, this can save a lot of disk I/O.
Other Tips
Unlike MyISAM , InnoDB
does not store an index cardinality value in its tables.
Instead, InnoDB computes a cardinality
for a table the first time it accesses it after startup.
With a large number of tables, this might take significant
time. It is the initial table open operation that is
important, so to “warm up” a table for later
use, access it immediately after startup by issuing a
statement such as SELECT 1 FROM
tbl_name LIMIT 1 .
Use the multiple-row INSERT
syntax to reduce communication overhead between the client
and the server if you need to insert many rows:
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
This tip is valid for inserts into any table, not just
InnoDB tables.
If you often have recurring queries for tables that are not
updated frequently, enable the query cache:
[mysqld]
query_cache_type = 1
query_cache_size = 10M
MySQL Enterprise
For optimization recommendations geared to your specific
circumstances, subscribe to the MySQL Enterprise Monitor. For
more information, see
http://www.mysql.com/products/enterprise/advisors.html.
13.6.13.2. SHOW ENGINE INNODB
STATUS and the InnoDB Monitors
InnoDB Monitors provide information about the
InnoDB internal state. This information is
useful for performance tuning. Each Monitor can be enabled by
creating a table with a special name, which causes
InnoDB to write Monitor output periodically.
Also, output for the standard InnoDB Monitor
is available on demand via the
SHOW ENGINE INNODB
STATUS SQL statement.
There are several types of InnoDB Monitors:
The standard InnoDB Monitor displays the
following types of information:
Table and record locks held by each active transaction
Lock waits of a transactions
Semaphore waits of threads
Pending file I/O requests
Buffer pool statistics
Purge and insert buffer merge activity of the main
InnoDB thread
For a discussion of InnoDB lock modes,
see Section 13.6.8.1, “InnoDB Lock Modes”.
To enable the standard InnoDB Monitor for
periodic output, create a table named
innodb_monitor . To obtain Monitor output
on demand, use the
SHOW ENGINE
INNODB STATUS SQL statement to fetch the output to
your client program. If you are using the
mysql interactive client, the output is
more readable if you replace the usual semicolon statement
terminator with \G :
mysql> SHOW ENGINE INNODB STATUS\G
The InnoDB Lock Monitor is like the
standard Monitor but also provides extensive lock
information. To enable this Monitor for periodic output,
create a table named innodb_lock_monitor .
The InnoDB Tablespace Monitor prints a
list of file segments in the shared tablespace and validates
the tablespace allocation data structures. To enable this
Monitor for periodic output, create a table named
innodb_tablespace_monitor .
The InnoDB Table Monitor prints the
contents of the InnoDB internal data
dictionary. To enable this Monitor for periodic output,
create a table named
innodb_table_monitor .
To enable an InnoDB Monitor for periodic
output, use a CREATE TABLE statement to
create the table associated with the Monitor. For example, to
enable the standard InnoDB Monitor, create
the innodb_monitor table:
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
To stop the Monitor, drop the table:
DROP TABLE innodb_monitor;
The CREATE TABLE syntax is just a
way to pass a command to the InnoDB engine
through MySQL's SQL parser: The only things that matter are the
table name innodb_monitor and that it be an
InnoDB table. The structure of the table is
not relevant at all for the InnoDB Monitor.
If you shut down the server, the Monitor does not restart
automatically when you restart the server. You must drop the
Monitor table and issue a new CREATE
TABLE statement to start the Monitor. (This syntax may
change in a future release.)
As of MySQL 5.1.24, the PROCESS
privilege is required to start or stop the
InnoDB Monitor tables.
When you enable InnoDB Monitors for periodic
output, InnoDB writes their output to the
mysqld server standard error output
(stderr ). In this case, no output is sent to
clients. When switched on, InnoDB Monitors
print data about every 15 seconds. Server output usually is
directed to the error log (see Section 5.2.2, “The Error Log”).
This data is useful in performance tuning. On Windows, you must
start the server from a command prompt in a console window with
the --console option if you want
to direct the output to the window rather than to the error log.
InnoDB sends diagnostic output to
stderr or to files rather than to
stdout or fixed-size memory buffers, to avoid
potential buffer overflows. As a side effect, the output of
SHOW ENGINE INNODB
STATUS is written to a status file in the MySQL data
directory every fifteen seconds. The name of the file is
innodb_status.pid ,
where pid is the server process ID.
InnoDB removes the file for a normal
shutdown. If abnormal shutdowns have occurred, instances of
these status files may be present and must be removed manually.
Before removing them, you might want to examine them to see
whether they contain useful information about the cause of
abnormal shutdowns. The
innodb_status.pid
file is created only if the configuration option
innodb_status_file=1 is set.
InnoDB Monitors should be enabled only when
you actually want to see Monitor information because output
generation does result in some performance decrement. Also, if
you enable monitor output by creating the associated table, your
error log may become quite large if you forget to remove the
table later.
For additional information about InnoDB
monitors, see the following resources:
Each monitor begins with a header containing a timestamp and the
monitor name. For example:
================================================
090407 12:06:19 INNODB TABLESPACE MONITOR OUTPUT
================================================
The header for the standard Monitor (INNODB MONITOR
OUTPUT ) is also used for the Lock Monitor because the
latter produces the same output with the addition of extra lock
information.
The following sections describe the output for each Monitor.
13.6.13.2.1. InnoDB Standard Monitor and Lock Monitor Output
The Lock Monitor is the same as the standard Monitor except
that it includes additional lock information. Enabling either
monitor for periodic output by creating the associated
InnoDB table turns on the same output
stream, but the stream includes the extra information if the
Lock Monitor is enabled. For example, if you create the
innodb_monitor and
innodb_lock_monitor tables, that turns on a
single output stream. The stream includes extra lock
information until you disable the Lock Monitor by removing the
innodb_lock_monitor table.
Example InnoDB Monitor output:
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
BACKGROUND THREAD
----------
srv_master_thread loops: 53 1_second, 44 sleeps, 5 10_second, 7 background,
7 flush
srv_master_thread log flush and writes: 48
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 413452, signal count 378357
--Thread 32782 has waited at btr0sea.c line 1477 for 0.00 seconds the
semaphore: X-lock on RW-latch at 41a28668 created in file btr0sea.c line 135
a writer (thread id 32782) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file btr0sea.c line 731
Last time write locked in file btr0sea.c line 1347
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 108462, OS waits 37964; RW-excl spins 681824, OS waits
375485
Spin rounds per wait: 0.00 mutex, 20.00 RW-shared, 0.00 RW-excl
------------------------
LATEST FOREIGN KEY ERROR
------------------------
030709 13:00:59 Transaction:
TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id 34831
inserting
15 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
Foreign key constraint fails for table test/ibtest11a:
,
CONSTRAINT `0_219242` FOREIGN KEY (`A`, `D`) REFERENCES `ibtest11b` (`A`,
`D`) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index PRIMARY tuple:
0: len 4; hex 80000101; asc ....;; 1: len 4; hex 80000005; asc ....;; 2:
len 4; hex 6b68446b; asc khDk;; 3: len 6; hex 0000114e0edc; asc ...N..;; 4:
len 7; hex 00000000c3e0a7; asc .......;; 5: len 4; hex 6b68446b; asc khDk;;
But in parent table test/ibtest11b, in index PRIMARY,
the closest match we can find is record:
RECORD: info bits 0 0: len 4; hex 8000015b; asc ...[;; 1: len 4; hex
80000005; asc ....;; 2: len 3; hex 6b6864; asc khd;; 3: len 6; hex
0000111ef3eb; asc ......;; 4: len 7; hex 800001001e0084; asc .......;; 5:
len 3; hex 6b6864; asc khd;;
------------------------
LATEST DETECTED DEADLOCK
------------------------
030709 12:59:58
*** (1) TRANSACTION:
TRANSACTION 0 290252780, ACTIVE 1 sec, process no 3185, OS thread id 30733
inserting
LOCK WAIT 3 lock struct(s), heap size 320, undo log entries 146
MySQL thread id 21, query id 4553379 localhost heikki update
INSERT INTO alex1 VALUES(86, 86, 794,'aA35818','bb','c79166','d4766t',
'e187358f','g84586','h794',date_format('2001-04-03 12:54:22','%Y-%m-%d
%H:%i'),7
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
symbole trx id 0 290252780 lock mode S waiting
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;
asc aa35818;; 1:
*** (2) TRANSACTION:
TRANSACTION 0 290251546, ACTIVE 2 sec, process no 3190, OS thread id 32782
inserting
130 lock struct(s), heap size 11584, undo log entries 437
MySQL thread id 23, query id 4554396 localhost heikki update
REPLACE INTO alex1 VALUES(NULL, 32, NULL,'aa3572','','c3572','d6012t','',
NULL,'h396', NULL, NULL, 7.31,7.31,7.31,200)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
symbole trx id 0 290251546 lock_mode X locks rec but not gap
Record lock, heap no 324 RECORD: info bits 0 0: len 7; hex 61613335383138;
asc aa35818;; 1:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 48310 n bits 568 table test/alex1 index
symbole trx id 0 290251546 lock_mode X locks gap before rec insert intention
waiting
Record lock, heap no 82 RECORD: info bits 0 0: len 7; hex 61613335373230;
asc aa35720;; 1:
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 0 290328385
Purge done for trx's n:o < 0 290315608 undo n:o < 0 17
Total number of lock structs in row lock hash table 70
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 3491, OS thread id 42002
MySQL thread id 32, query id 4668737 localhost heikki
show innodb status
---TRANSACTION 0 290328384, ACTIVE 0 sec, process no 3205, OS thread id
38929 inserting
1 lock struct(s), heap size 320
MySQL thread id 29, query id 4668736 localhost heikki update
insert into speedc values (1519229,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgjg
jlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjfh
---TRANSACTION 0 290328383, ACTIVE 0 sec, process no 3180, OS thread id
28684 committing
1 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 19, query id 4668734 localhost heikki update
insert into speedcm values (1603393,1, 'hgjhjgghggjgjgjgjgjggjgjgjgjgjgggjgj
gjlhhgghggggghhjhghgggggghjhghghghghghhhhghghghjhhjghjghjkghjghjghjghjfhjf
---TRANSACTION 0 290328327, ACTIVE 0 sec, process no 3200, OS thread id
36880 starting index read
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 27, query id 4668644 localhost heikki Searching rows for
update
update ibtest11a set B = 'kHdkkkk' where A = 89572
------- TRX HAS BEEN WAITING 0 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 65556 n bits 232 table test/ibtest11a index
PRIMARY trx id 0 290328327 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00;
asc supremum.;;
------------------
---TRANSACTION 0 290328284, ACTIVE 0 sec, process no 3195, OS thread id
34831 rollback of SQL statement
ROLLING BACK 14 lock struct(s), heap size 2496, undo log entries 9
MySQL thread id 25, query id 4668733 localhost heikki update
insert into ibtest11a (D, B, C) values (5, 'khDk' ,'khDk')
---TRANSACTION 0 290327208, ACTIVE 1 sec, process no 3190, OS thread id
32782
58 lock struct(s), heap size 5504, undo log entries 159
MySQL thread id 23, query id 4668732 localhost heikki update
REPLACE INTO alex1 VALUES(86, 46, 538,'aa95666','bb','c95666','d9486t',
'e200498f','g86814','h538',date_format('2001-04-03 12:54:22','%Y-%m-%d
%H:%i'),
---TRANSACTION 0 290323325, ACTIVE 3 sec, process no 3185, OS thread id
30733 inserting
4 lock struct(s), heap size 1024, undo log entries 165
MySQL thread id 21, query id 4668735 localhost heikki update
INSERT INTO alex1 VALUES(NULL, 49, NULL,'aa42837','','c56319','d1719t','',
NULL,'h321', NULL, NULL, 7.31,7.31,7.31,200)
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
151671 OS file reads, 94747 OS file writes, 8750 OS fsyncs
25.44 reads/s, 18494 avg bytes/read, 17.55 writes/s, 2.33 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 19, seg size 21,
85004 inserts, 85004 merged recs, 26669 merges
Hash table size 207619, used cells 14461, node heap has 16 buffer(s)
1877.67 hash searches/s, 5121.10 non-hash searches/s
---
LOG
---
Log sequence number 18 1212842764
Log flushed up to 18 1212665295
Last checkpoint at 18 1135877290
0 pending log writes, 0 pending chkp writes
4341 log i/o's done, 1.22 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 84966343; in additional pool allocated 1402624
Buffer pool size 3200
Free buffers 110
Database pages 3074
Modified db pages 2674
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 171380, created 51968, written 194688
28.72 reads/s, 20.72 creates/s, 47.55 writes/s
Buffer pool hit rate 999 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 3004, id 7176, state: purging
Number of rows inserted 3738558, updated 127415, deleted 33707, read 755779
1586.13 inserts/s, 50.89 updates/s, 28.44 deletes/s, 107.88 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
InnoDB Monitor output is limited to 64,000
bytes when produced via the
SHOW ENGINE
INNODB STATUS statement. This limit does not apply
to output written to the server's error output.
Some notes on the output sections:
BACKGROUND
THREAD
The srv_master_thread lines shows work done
by the main background thread. This section is displayed only
by InnoDB Plugin .
SEMAPHORES
This section reports threads waiting for a semaphore and
statistics on how many times threads have needed a spin or a
wait on a mutex or a rw-lock semaphore. A large number of
threads waiting for semaphores may be a result of disk I/O, or
contention problems inside InnoDB .
Contention can be due to heavy parallelism of queries or
problems in operating system thread scheduling. Setting the
innodb_thread_concurrency
system variable smaller than the default value might help in
such situations. The Spin rounds per wait
line (InnoDB Plugin only) shows the number
of spinlock rounds per OS wait for a mutex.
LATEST FOREIGN KEY
ERROR
This section provides information about the most recent
foreign key constraint error. It is not present if no such
error has occurred. The contents include the statement that
failed as well as information about the constraint that failed
and the referenced and referencing tables.
LATEST DETECTED
DEADLOCK
This section provides information about the most recent
deadlock. It is not present if no deadlock has occurred. The
contents show which transactions are involved, the statement
each was attempting to execute, the locks they have and need,
and which transaction InnoDB decided to
roll back to break the deadlock. The lock modes reported in
this section are explained in
Section 13.6.8.1, “InnoDB Lock Modes”.
TRANSACTIONS
If this section reports lock waits, your applications might
have lock contention. The output can also help to trace the
reasons for transaction deadlocks.
FILE I/O
This section provides information about threads that
InnoDB uses to perform various types of
I/O. The first few of these are dedicated to general
InnoDB processing. The contents also
display information for pending I/O operations and statistics
for I/O performance.
On Unix, the number of threads is always 4. On Windows, the
number depends on the setting of the
innodb_file_io_threads system
variable.
INSERT BUFFER AND ADAPTIVE HASH
INDEX
This section shows the status of the InnoDB
insert buffer and adaptive hash index. (See
Section 13.6.10.3, “Insert Buffering”, and
Section 13.6.10.4, “Adaptive Hash Indexes”.) The contents include
the number of operations performed for each, plus statistics
for hash index performance.
LOG
This section displays information about the
InnoDB log. The contents include the
current log sequence number, how far the log has been flushed
to disk, and the position at which InnoDB
last took a checkpoint. (See
Section 13.6.6.3, “InnoDB Checkpoints”.) The section also
displays information about pending writes and write
performance statistics.
BUFFER POOL AND
MEMORY
This section gives you statistics on pages read and written.
You can calculate from these numbers how many data file I/O
operations your queries currently are doing.
For additional information about the operation of the buffer
pool, see Section 7.4.6, “The InnoDB Buffer Pool”.
ROW
OPERATIONS
This section shows what the main thread is doing, including
the number and performance rate for each type of row
operation.
13.6.13.2.2. InnoDB Tablespace Monitor Output
The InnoDB Tablespace Monitor prints
information about the file segments in the shared tablespace
and validates the tablespace allocation data structures. If
you use individual tablespaces by enabling
innodb_file_per_table , the
Tablespace Monitor does not describe those tablespaces.
Example InnoDB Tablespace Monitor output:
================================================
090408 21:28:09 INNODB TABLESPACE MONITOR OUTPUT
================================================
FILE SPACE INFO: id 0
size 13440, free limit 3136, free extents 28
not full frag extents 2: used pages 78, full frag extents 3
first seg id not used 0 23845
SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 14
SEGMENT id 0 2 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 3 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
...
SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2
fragm pages 32; free extents 0; not full extents 0: pages 0
SEGMENT id 0 488 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 17 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
...
SEGMENT id 0 171 space 0; page 2; res 592 used 481; full ext 7
fragm pages 16; free extents 0; not full extents 2: pages 17
SEGMENT id 0 172 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
SEGMENT id 0 173 space 0; page 2; res 96 used 44; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 12
...
SEGMENT id 0 601 space 0; page 2; res 1 used 1; full ext 0
fragm pages 1; free extents 0; not full extents 0: pages 0
NUMBER of file segments: 73
Validating tablespace
Validation ok
---------------------------------------
END OF INNODB TABLESPACE MONITOR OUTPUT
=======================================
The Tablespace Monitor output includes information about the
shared tablespace as a whole, followed by a list containing a
breakdown for each segment within the tablespace.
The tablespace consists of database pages with a default size
of 16KB. The pages are grouped into extents of size 1MB (64
consecutive pages).
The initial part of the output that displays overall
tablespace information has this format:
FILE SPACE INFO: id 0
size 13440, free limit 3136, free extents 28
not full frag extents 2: used pages 78, full frag extents 3
first seg id not used 0 23845
Overall tablespace information includes these values:
id : The tablespace ID. A value of 0
refers to the shared tablespace.
size : The current tablespace size in
pages.
free limit : The minimum page number for
which the free list has not been initialized. Pages at or
above this limit are free.
free extents : The number of free
extents.
not full frag extents , used
pages : The number of fragment extents that are
not completely filled, and the number of pages in those
extents that have been allocated.
full frag extents : The number of
completely full fragment extents.
first seg id not used : The first unused
segment ID.
Individual segment information has this format:
SEGMENT id 0 15 space 0; page 2; res 160 used 160; full ext 2
fragm pages 32; free extents 0; not full extents 0: pages 0
Segment information includes these values:
id : The segment ID.
space , page : The
tablespace number and page within the tablespace where the
segment “inode” is located. A tablespace number
of 0 indicates the shared tablespace.
InnoDB uses inodes to keep track of
segments in the tablespace. The other fields displayed for a
segment (id , res , and so
forth) are derived from information in the inode.
res : The number of pages allocated
(reserved) for the segment.
used : The number of allocated pages in use
by the segment.
full ext : The number of extents allocated
for the segment that are completely used.
fragm pages : The number of initial pages
that have been allocated to the segment.
free extents : The number of extents
allocated for the segment that are completely unused.
not full extents : The number of extents
allocated for the segment that are partially used.
pages : The number of pages used within the
not-full extents.
When a segment grows, it starts as a single page, and
InnoDB allocates the first pages for it
individually, up to 32 pages (this is the fragm
pages value). After that, InnoDB
allocates complete 64-page extents. InnoDB
can add up to 4 extents at a time to a large segment to ensure
good sequentiality of data.
For the example segment shown earlier, it has 32 fragment
pages, plus 2 full extents (64 pages each), for a total of 160
pages used out of 160 pages allocated. The following segment
has 32 fragment pages and one partially full extent using 14
pages for a total of 46 pages used out of 96 pages allocated:
SEGMENT id 0 1 space 0; page 2; res 96 used 46; full ext 0
fragm pages 32; free extents 0; not full extents 1: pages 14
It is possible for a segment that has extents allocated to it
to have a fragm pages value less than 32 if
some of the individual pages have been deallocated subsequent
to extent allocation.
13.6.13.2.3. InnoDB Table Monitor Output
The InnoDB Table Monitor prints the
contents of the InnoDB internal data
dictionary.
The output contains one section per table. The
SYS_FOREIGN and
SYS_FOREIGN_COLS sections are for internal
data dictionary tables that maintain information about foreign
keys. There are also sections for the Table Monitor table and
each user-created InnoDB table. Suppose
that the following two tables have been created in the
test database:
CREATE TABLE parent
(
par_id INT NOT NULL,
fname CHAR(20),
lname CHAR(20),
PRIMARY KEY (par_id),
UNIQUE INDEX (lname, fname)
) ENGINE = INNODB;
CREATE TABLE child
(
par_id INT NOT NULL,
child_id INT NOT NULL,
name VARCHAR(40),
birth DATE,
weight DECIMAL(10,2),
misc_info VARCHAR(255),
last_update TIMESTAMP,
PRIMARY KEY (par_id, child_id),
INDEX (name),
FOREIGN KEY (par_id) REFERENCES parent (par_id)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE = INNODB;
Then the Table Monitor output will look something like this
(reformatted slightly):
===========================================
090420 12:09:32 INNODB TABLE MONITOR OUTPUT
===========================================
--------------------------------------
TABLE: name SYS_FOREIGN, id 0 11, columns 7, indexes 3, appr.rows 1
COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0;
FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0;
REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0;
N_COLS: DATA_INT len 4;
DB_ROW_ID: DATA_SYS prtype 256 len 6;
DB_TRX_ID: DATA_SYS prtype 257 len 6;
INDEX: name ID_IND, id 0 11, fields 1/6, uniq 1, type 3
root page 46, appr.key vals 1, leaf pages 1, size pages 1
FIELDS: ID DB_TRX_ID DB_ROLL_PTR FOR_NAME REF_NAME N_COLS
INDEX: name FOR_IND, id 0 12, fields 1/2, uniq 2, type 0
root page 47, appr.key vals 1, leaf pages 1, size pages 1
FIELDS: FOR_NAME ID
INDEX: name REF_IND, id 0 13, fields 1/2, uniq 2, type 0
root page 48, appr.key vals 1, leaf pages 1, size pages 1
FIELDS: REF_NAME ID
--------------------------------------
TABLE: name SYS_FOREIGN_COLS, id 0 12, columns 7, indexes 1, appr.rows 1
COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0;
POS: DATA_INT len 4;
FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0;
REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0;
DB_ROW_ID: DATA_SYS prtype 256 len 6;
DB_TRX_ID: DATA_SYS prtype 257 len 6;
INDEX: name ID_IND, id 0 14, fields 2/6, uniq 2, type 3
root page 49, appr.key vals 1, leaf pages 1, size pages 1
FIELDS: ID POS DB_TRX_ID DB_ROLL_PTR FOR_COL_NAME REF_COL_NAME
--------------------------------------
TABLE: name test/child, id 0 14, columns 10, indexes 2, appr.rows 201
COLUMNS: par_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;
child_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;
name: DATA_VARCHAR prtype 524303 len 40;
birth: DATA_INT DATA_BINARY_TYPE len 3;
weight: DATA_FIXBINARY DATA_BINARY_TYPE len 5;
misc_info: DATA_VARCHAR prtype 524303 len 255;
last_update: DATA_INT DATA_UNSIGNED DATA_BINARY_TYPE DATA_NOT_NULL len 4;
DB_ROW_ID: DATA_SYS prtype 256 len 6;
DB_TRX_ID: DATA_SYS prtype 257 len 6;
INDEX: name PRIMARY, id 0 17, fields 2/9, uniq 2, type 3
root page 52, appr.key vals 201, leaf pages 5, size pages 6
FIELDS: par_id child_id DB_TRX_ID DB_ROLL_PTR name birth weight misc_info last_update
INDEX: name name, id 0 18, fields 1/3, uniq 3, type 0
root page 53, appr.key vals 210, leaf pages 1, size pages 1
FIELDS: name par_id child_id
FOREIGN KEY CONSTRAINT test/child_ibfk_1: test/child ( par_id )
REFERENCES test/parent ( par_id )
--------------------------------------
TABLE: name test/innodb_table_monitor, id 0 15, columns 4, indexes 1, appr.rows 0
COLUMNS: i: DATA_INT DATA_BINARY_TYPE len 4;
DB_ROW_ID: DATA_SYS prtype 256 len 6;
DB_TRX_ID: DATA_SYS prtype 257 len 6;
INDEX: name GEN_CLUST_INDEX, id 0 19, fields 0/4, uniq 1, type 1
root page 193, appr.key vals 0, leaf pages 1, size pages 1
FIELDS: DB_ROW_ID DB_TRX_ID DB_ROLL_PTR i
--------------------------------------
TABLE: name test/parent, id 0 13, columns 6, indexes 2, appr.rows 299
COLUMNS: par_id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;
fname: DATA_CHAR prtype 524542 len 20;
lname: DATA_CHAR prtype 524542 len 20;
DB_ROW_ID: DATA_SYS prtype 256 len 6;
DB_TRX_ID: DATA_SYS prtype 257 len 6;
INDEX: name PRIMARY, id 0 15, fields 1/5, uniq 1, type 3
root page 50, appr.key vals 299, leaf pages 2, size pages 3
FIELDS: par_id DB_TRX_ID DB_ROLL_PTR fname lname
INDEX: name lname, id 0 16, fields 2/3, uniq 2, type 2
root page 51, appr.key vals 300, leaf pages 1, size pages 1
FIELDS: lname fname par_id
FOREIGN KEY CONSTRAINT test/child_ibfk_1: test/child ( par_id )
REFERENCES test/parent ( par_id )
-----------------------------------
END OF INNODB TABLE MONITOR OUTPUT
==================================
For each table, Table Monitor output contains a section that
displays general information about the table and specific
information about its columns, indexes, and foreign keys.
The general information for each table includes the table name
(in
db_name /tbl_name
format except for internal tables), its ID, the number of
columns and indexes, and an approximate row count.
The COLUMNS part of a table section lists
each column in the table. Information for each column
indicates its name and data type characteristics. Some
internal columns are added by InnoDB , such
as DB_ROW_ID (row ID),
DB_TRX_ID (transaction ID), and
DB_ROLL_PTR (a pointer to the rollback/undo
data).
DATA_xxx :
These symbols indicate the data type. There may be
multiple
DATA_xxx
symbols for a given column.
prtype : The column's
“precise” type. This field includes
information such as the column data type, character set
code, nullability, signedness, and whether it is a binary
string. This field is described in the
innobase/include/data0type.h source
file.
len : The column length in bytes.
Each INDEX part of the table section
provides the name and characteristics of one table index:
name : The index name. If the name is
PRIMARY , the index is a primary key. If
the name is GEN_CLUST_INDEX , the index
is the clustered index that is created automatically if
the table definition doesn't include a primary key or
non-NULL unique index. See
Section 13.6.10.1, “Clustered and Secondary Indexes”.
id : The index ID.
fields : The number of fields in the
index, as a value in
m /n
format:
m is the number of
user-defined columns; that is, the number of columns
you would see in the index definition in a
CREATE TABLE statement.
n is the total number of
index columns, including those added internally. For
the clustered index, the total includes the other
columns in the table definition, plus any columns
added internally. For a secondary index, the total
includes the columns from the primary key that are not
part of the secondary index.
uniq : The number of leading fields that
are enough to determine index values uniquely.
type : The index type. This is a bit
field. For example, 1 indicates a clustered index and 2
indicates a unique index, so a clustered index (which
always contains unique values), will have a
type value of 3. An index with a
type value of 0 is neither clustered
nor unique. The flag values are defined in the
innobase/include/dict0mem.h source
file.
root page : The index root page number.
appr. key vals : The approximate index
cardinality.
leaf pages : The approximate number of
leaf pages in the index.
size pages : The approximate total
number of pages in the index.
FIELDS : The names of the fields in the
index. For a clustered index that was generated
automatically, the field list begins with the internal
DB_ROW_ID (row ID) field.
DB_TRX_ID and
DB_ROLL_PTR are always added internally
to the clustered index, following the fields that comprise
the primary key. For a secondary index, the final fields
are those from the primary key that are not part of the
secondary index.
The end of the table section lists the FOREIGN
KEY definitions that apply to the table. This
information appears whether the table is a referencing or
referenced table.
13.6.13.3. InnoDB General Troubleshooting
The following general guidelines apply to troubleshooting
InnoDB problems:
When an operation fails or you suspect a bug, you should
look at the MySQL server error log (see
Section 5.2.2, “The Error Log”).
Issues relating to the InnoDB data
dictionary include failed CREATE
TABLE statements (orphaned table files), inability
to open .InnoDB files, and
system cannot find the path specified
errors. For information about these sorts of problems and
errors, see
Section 13.6.13.4, “Troubleshooting InnoDB Data Dictionary Operations”.
When troubleshooting, it is usually best to run the MySQL
server from the command prompt, rather than through
mysqld_safe or as a Windows service. You
can then see what mysqld prints to the
console, and so have a better grasp of what is going on. On
Windows, start mysqld with the
--console option to direct
the output to the console window.
Use the InnoDB Monitors to obtain
information about a problem (see
Section 13.6.13.2, “SHOW ENGINE INNODB
STATUS and the InnoDB Monitors”). If the problem is
performance-related, or your server appears to be hung, you
should use the standard Monitor to print information about
the internal state of InnoDB . If the
problem is with locks, use the Lock Monitor. If the problem
is in creation of tables or other data dictionary
operations, use the Table Monitor to print the contents of
the InnoDB internal data dictionary. To
see tablespace information use the Tablespace Monitor.
If you suspect that a table is corrupt, run
CHECK TABLE on that table.
MySQL Enterprise
The MySQL Enterprise Monitor provides a number of advisors
specifically designed for monitoring InnoDB tables. In some
cases, these advisors can anticipate potential problems. For
more information, see
http://www.mysql.com/products/enterprise/advisors.html.
13.6.13.4. Troubleshooting InnoDB Data Dictionary Operations
A specific issue with tables is that the MySQL server keeps data
dictionary information in .frm files it
stores in the database directories, whereas
InnoDB also stores the information into its
own data dictionary inside the tablespace files. If you move
.frm files around, or if the server crashes
in the middle of a data dictionary operation, the locations of
the .frm files may end up out of synchrony
with the locations recorded in the InnoDB
internal data dictionary.
A symptom of an out-of-sync data dictionary is that a
CREATE TABLE statement fails. If
this occurs, you should look in the server's error log. If the
log says that the table already exists inside the
InnoDB internal data dictionary, you have an
orphaned table inside the InnoDB tablespace
files that has no corresponding .frm file.
The error message looks like this:
InnoDB: Error: table test/parent already exists in InnoDB internal
InnoDB: data dictionary. Have you deleted the .frm file
InnoDB: and not used DROP TABLE? Have you used DROP DATABASE
InnoDB: for InnoDB tables in MySQL version <= 3.23.43?
InnoDB: See the Restrictions section of the InnoDB manual.
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will
InnoDB: succeed.
You can drop the orphaned table by following the instructions
given in the error message. If you are still unable to use
DROP TABLE successfully, the
problem may be due to name completion in the
mysql client. To work around this problem,
start the mysql client with the
--skip-auto-rehash
option and try DROP TABLE again.
(With name completion on, mysql tries to
construct a list of table names, which fails when a problem such
as just described exists.)
Another symptom of an out-of-sync data dictionary is that MySQL
prints an error that it cannot open a
.InnoDB file:
ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)
In the error log you can find a message like this:
InnoDB: Cannot find table test/child2 from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?
This means that there is an orphaned .frm
file without a corresponding table inside
InnoDB . You can drop the orphaned
.frm file by deleting it manually.
If MySQL crashes in the middle of an ALTER
TABLE operation, you may end up with an orphaned
temporary table inside the InnoDB tablespace.
Using the Table Monitor, you can see listed a table with a name
that begins with #sql- . You can perform SQL
statements on tables whose name contains the character
“# ” if you enclose the name
within backticks. Thus, you can drop such an orphaned table like
any other orphaned table using the method described earlier. To
copy or rename a file in the Unix shell, you need to put the
file name in double quotes if the file name contains
“# ”.
With innodb_file_per_table
enabled, the following message might occur if the
.frm or .ibd files (or
both) are missing:
InnoDB: in InnoDB data dictionary has tablespace id N ,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
InnoDB: table still exists in the InnoDB internal data dictionary.
If this occurs, try the following procedure to resolve the
problem:
Create a matching .frm file in some
other database directory and copy it to the database
directory where the orphan table is located.
Issue DROP TABLE for the
original table. That should successfully drop the table and
InnoDB should print a warning to the
error log that the .ibd file was
missing.
13.6.14. Restrictions on InnoDB TablesWarning
Do not convert MySQL system tables in the
mysql database from MyISAM
to InnoDB tables! This is an unsupported
operation. If you do this, MySQL does not restart until you
restore the old system tables from a backup or re-generate them
with the mysql_install_db script.
Warning
It is not a good idea to configure InnoDB to
use data files or log files on NFS volumes. Otherwise, the files
might be locked by other processes and become unavailable for
use by MySQL.
A table cannot contain more than 1000 columns.
The InnoDB internal maximum key length is
3500 bytes, but MySQL itself restricts this to 3072 bytes.
Index key prefixes can be up to 767 bytes. See
Section 12.1.13, “CREATE INDEX Syntax”.
The maximum row length, except for variable-length columns
(VARBINARY ,
VARCHAR ,
BLOB and
TEXT ), is slightly less than
half of a database page. That is, the maximum row length is
about 8000 bytes. LONGBLOB and
LONGTEXT
columns must be less than 4GB, and the total row length,
including BLOB and
TEXT columns, must be less than
4GB.
If a row is less than half a page long, all of it is stored
locally within the page. If it exceeds half a page,
variable-length columns are chosen for external off-page
storage until the row fits within half a page, as described in
Section 13.6.11.2, “File Space Management”.
Although InnoDB supports row sizes larger
than 65535 internally, you cannot define a row containing
VARBINARY or
VARCHAR columns with a combined
size larger than 65535:
mysql> CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),
-> c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
-> f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;
ERROR 1118 (42000): Row size too large. The maximum row size for the
used table type, not counting BLOBs, is 65535. You have to change some
columns to TEXT or BLOBs
On some older operating systems, files must be less than 2GB.
This is not a limitation of InnoDB itself,
but if you require a large tablespace, you will need to
configure it using several smaller data files rather than one
or a file large data files.
The combined size of the InnoDB log files
must be less than 4GB.
The minimum tablespace size is 10MB. The maximum tablespace
size is four billion database pages (64TB). This is also the
maximum size for a table.
InnoDB tables do not support
FULLTEXT indexes.
InnoDB tables support spatial data types,
but not indexes on them.
ANALYZE TABLE determines index
cardinality (as displayed in the
Cardinality column of
SHOW INDEX output) by doing
eight random dives to each of the index trees and updating
index cardinality estimates accordingly. Because these are
only estimates, repeated runs of ANALYZE
TABLE may produce different numbers. This makes
ANALYZE TABLE fast on
InnoDB tables but not 100% accurate because
it does not take all rows into account.
For InnoDB Plugin , the number of random
dives can be changed by modifying the
innodb_stats_sample_pages
system variable. For more information, see the
InnoDB
Plugin manual.
MySQL uses index cardinality estimates only in join
optimization. If some join is not optimized in the right way,
you can try using ANALYZE
TABLE . In the few cases that
ANALYZE TABLE does not produce
values good enough for your particular tables, you can use
FORCE INDEX with your queries to force the
use of a particular index, or set the
max_seeks_for_key system
variable to ensure that MySQL prefers index lookups over table
scans. See Section 5.1.4, “Server System Variables”, and
Section B.5.6, “Optimizer-Related Issues”.
SHOW TABLE STATUS does not give
accurate statistics on InnoDB tables,
except for the physical size reserved by the table. The row
count is only a rough estimate used in SQL optimization.
InnoDB does not keep an internal count of
rows in a table. (In practice, this would be somewhat
complicated due to multi-versioning.) To process a
SELECT COUNT(*) FROM t statement,
InnoDB must scan an index of the table,
which takes some time if the index is not entirely in the
buffer pool. If your table does not change often, using the
MySQL query cache is a good solution. To get a fast count, you
have to use a counter table you create yourself and let your
application update it according to the inserts and deletes it
does. SHOW TABLE STATUS also
can be used if an approximate row count is sufficient. See
Section 13.6.13.1, “InnoDB Performance Tuning Tips”.
On Windows, InnoDB always stores database
and table names internally in lowercase. To move databases in
a binary format from Unix to Windows or from Windows to Unix,
you should create all databases and tables using lowercase
names.
For an AUTO_INCREMENT column, you must
always define an index for the table, and that index must
contain just the AUTO_INCREMENT column. In
MyISAM tables, the
AUTO_INCREMENT column may be part of a
multi-column index.
While initializing a previously specified
AUTO_INCREMENT column on a table,
InnoDB sets an exclusive lock on the end of
the index associated with the
AUTO_INCREMENT column. In accessing the
auto-increment counter, InnoDB uses a
specific table lock mode AUTO-INC where the
lock lasts only to the end of the current SQL statement, not
to the end of the entire transaction. Other clients cannot
insert into the table while the AUTO-INC
table lock is held; see
Section 13.6.4.3, “AUTO_INCREMENT Handling in InnoDB ”.
When you restart the MySQL server, InnoDB
may reuse an old value that was generated for an
AUTO_INCREMENT column but never stored
(that is, a value that was generated during an old transaction
that was rolled back).
When an AUTO_INCREMENT column runs out of
values, InnoDB wraps a
BIGINT to
-9223372036854775808 and BIGINT
UNSIGNED to 1 . However,
BIGINT values have 64 bits, so
if you were to insert one million rows per second, it would
still take nearly three hundred thousand years before
BIGINT reached its upper bound.
With all other integer type columns, a duplicate-key error
results. This is similar to how MyISAM
works, because it is mostly general MySQL behavior and not
about any storage engine in particular.
DELETE FROM
tbl_name does not
regenerate the table but instead deletes all rows, one by one.
Under some conditions, TRUNCATE
tbl_name for an
InnoDB table is mapped to DELETE
FROM tbl_name . See
Section 12.2.10, “TRUNCATE TABLE Syntax”.
In MySQL 5.1, the MySQL LOCK
TABLES operation acquires two locks on each table if
innodb_table_locks = 1 (the default). In
addition to a table lock on the MySQL layer, it also acquires
an InnoDB table lock. Older versions of
MySQL did not acquire InnoDB table locks;
the old behavior can be selected by setting
innodb_table_locks = 0 . If no
InnoDB table lock is acquired,
LOCK TABLES completes even if
some records of the tables are being locked by other
transactions.
All InnoDB locks held by a transaction are
released when the transaction is committed or aborted. Thus,
it does not make much sense to invoke
LOCK TABLES on
InnoDB tables in
autocommit = 1 mode, because
the acquired InnoDB table locks would be
released immediately.
Sometimes it would be useful to lock further tables in the
course of a transaction. Unfortunately,
LOCK TABLES in MySQL performs
an implicit COMMIT and
UNLOCK
TABLES . An InnoDB variant of
LOCK TABLES has been planned
that can be executed in the middle of a transaction.
The LOAD TABLE FROM MASTER
statement for setting up replication slave servers does not
work for InnoDB tables. A workaround is to
alter the table to MyISAM on the master,
then do the load, and after that alter the master table back
to InnoDB . Do not do this if the tables use
InnoDB -specific features such as foreign
keys.
The default database page size in InnoDB is
16KB. By recompiling the code, you can set it to values
ranging from 8KB to 64KB. You must update the values of
UNIV_PAGE_SIZE and
UNIV_PAGE_SIZE_SHIFT in the
univ.i source file.
Note
Changing the page size is not a supported operation and
there is no guarantee that
InnoDB will function normally
with a page size other than 16KB. Problems compiling or
running InnoDB may occur. In particular,
ROW_FORMAT=COMPRESSED in the
InnoDB Plugin assumes that the page size
is at most 16KB and uses 14-bit pointers.
A version of InnoDB built for
one page size cannot use data files or log files from a
version built for a different page size.
Currently, cascaded foreign key actions to not activate
triggers.
You cannot create a table with a column name that matches the
name of an internal InnoDB column (including
DB_ROW_ID , DB_TRX_ID ,
DB_ROLL_PTR , and
DB_MIX_ID ). In versions of MySQL before
5.1.10 this would cause a crash, since 5.1.10 the server will
report error 1005 and refers to error –1 in the error
message. This limitation applies only to use of the names in
uppercase.
InnoDB has a limit of 1023 concurrent
transactions that have created undo records by modifying data.
Workarounds include keeping transactions as small and fast as
possible, delaying changes until near the end of the
transaction, and using stored routines to reduce client-server
latency delays. Applications should commit transactions before
doing time-consuming client-side operations.
13.7. The IBMDB2I Storage Engine
The IBMDB2I storage engine is designed as a fully
featured transaction-capable storage engine that enables MySQL to
store its data in DB2 tables running on IBM i. With the
IBMDB2I storage engine, data can be shared
between MySQL applications and applications coded for native DB2 for
i interfaces.
IBMDB2I provides ACID-compliant transactions,
support for foreign key constraints, full crash recovery,
radix-tree-based indexes, and the unique ability to allow DB2 for i
applications to see and update table data in real time.
More information about the storage engine and its interaction with
DB2 for i can be found in IBM's Using DB2 for i as a
Storage Engine for MySQL Redbook publication, at
IBM
DB2 for i a Storage Engine for MySQL Redbook.
The IBMDB2I storage engine was introduced in
MySQL 5.1.33 and considered GA in MySQL 5.1.35.
Because it relies on features specific to the IBM i operating
system running on IBM Power systems, the
IBMDB2I storage engine is only available on
builds of MySQL specifically created for the IBM i operating
system.
IBM i 5.4 or later is required to run
IBMDB2I . Some features may
require IBM i 6.1 or later versions. As well, a set of program
temporary fixes (PTFs) must be installed. Information about the
specific PTF numbers can be obtained from
http://www-912.ibm.com/n_dir/nas4apar.nsf/c79815e083182fec862564c00079d117/67d12878076e4827862574e2003c6d4a?OpenDocument.
The engine is built as a dynamic plugin and so must be installed
by using the following command:
mysql> INSTALL PLUGIN ibmdb2i SONAME ha_ibmdb2i.so; 13.7.2. Configuration OptionsTable 13.6. IBMDB2I Storage Engine
Features
ibmdb2i_assume_exclusive_use
Specifies whether an external interface (such as DB2 for i)
may be altering the data accessible by the
IBMDB2I engine. This is an advisory value
that may improve performance when correctly set to ON. When
the value is ON, IBMDB2I may perform some
internal caching of table statistics. When the value is set to
OFF, IBMDB2I must assume that the table
rows could be inserted and deleted without its direct
knowledge and must call DB2 to obtain accurate statistics
before each operation.
Default Value: OFF
ibmdb2i_async_enabled
Specifies whether buffering between IBMDB2I
and the QSQSRVR jobs responsible for fetching row data should
be done in an asynchronous manner. Asynchronous reads are
enabled by default and provide optimal performance.Under
normal circumstances, this value will never need to be
modified.
Default Value: ON
ibmdb2i_create_index_option
Controls whether additional indexes are created for use by
traditional DB2 for i interfaces. When the value is 0, no
additional indexes will be created. When the value is 1 and
the index created on behalf of MySQL is ASCII-based, an
additional index is created based on EBCDIC hexadecimal
sorting. The additional index may be useful for traditional
DB2 for i interfaces which expect indexes to use EBCDIC-based
sort sequences.
Default Value: 0
ibmdb2i_compat_opt_time_as_duration
Controls how MySQL TIME columns are mapped to DB2 data types
when creating or altering an IBMDB2I table.
When the value is ON , the column is mapped
to an INTEGER type in DB2 and supports the
full range of values defined by MySQL for
TIME types. When the value is
OFF , the column is mapped to a DB2
TIME type and supports values in the range
of '00:00:00' to '23:59:59'. This option is provided to allow
enhanced interoperability with DB2 for i interfaces.
Default Value: OFF
ibmdb2i_system_trace_level
Specifies what kind of debugging information is to be gathered
for QSQSRVR jobs servicing MySQL
connections. Multiple sources of information may be specified
by summing the respective values. Changes to this option only
affect new connections. Valid values include
The most useful sources of information are
DSPJOBLOG , which will capture the job log
for each QSQSRVR job in a spoolfile, and
STRDBG , which will increase the diagnostic
information in each job log.
Default Value: 0
ibmdb2i_compat_opt_allow_zero_date_vals
Specifies whether the storage engine should allow the
0000-00-00 date in
DATETIME , TIMESTAMP and
DATE columns. When the option is 0,
attempts to insert a row containing this zero date into an
IBMDB2I table will fail. As well, a warning
will be generated when creating a column with this zero value
as the default value. When this option is 1, the zero value
will be subsituted with 0001-01-01 when
stored in DB2, and a 0001-01-01 value will
be translated to 0000-00-00 when read from
DB2. Similarly, when a column with a default zero value is
created, the DB2 default value will be '0001-01-01'. Users
must be aware that, when this option is 1, all values of
0001-01-01 in DB2 will be interpreted as
0000-00-00 . This option is primarily added
for compatibility with applications which rely on the zero
date.
Default Value: 0
ibmdb2i_propagate_default_col_vals
Specifies whether DEFAULT value associated
with each column should be propagated to the DB2 definition of
the table when a table is created or altered. The default
value is ON. This ensures that rows inserted from a standard
DB2 interface will use the same default values as when
inserted from MySQL.
Default Value: ON
ibmdb2i_compat_opt_year_as_int
Controls how YEAR columns are stored in DB2. The default is 0
and causes YEAR columns to be created as
CHAR(4) CCSID 1208 columns in DB2. Setting
this option to 1 causes the YEAR columns to be created as
SMALLINT columns. This provides a slight
performance increase and enables indexes that combine a
YEAR column with a character column.
Default Value: 0
ibmdb2i_lob_alloc_size
Controls how much space is allocated by default for reading
data from a BLOB or TEXT
field. If an application consistently uses
BLOB or TEXT fields that
contain more than 2 MB of data, read performance may be
improved if this value is increased. Conversely, an
application which uses smaller BLOB or
TEXT fields may find that the MySQL memory
footprint is reduced if a smaller value is specified for this
option.
Default Value: 2 MB
ibmdb2i_compat_opt_blob_cols
Specifies how MySQL TEXT and
BLOB columns larger than 255 characters are
mapped when creating IBMDB2I tables. When
the value is 0, TEXT columns are mapped to
CLOB or DBCLOB columns
for DB2 for i. This allows the column to contain the maximum
size documented for TEXT columns (64K
characters), but the column can not be included in an index.
When the value is 1, TEXT columns are
mapped to LONG
VARCHAR /VARGRAPHIC columns, and
BLOB columns are mapped to LONG
VARBINARY . This permits indexes to be created over
the column, but it reduces the amount of storage available to
the column below the documented maximum for
TEXT columns. This option was provided to
enable applications which relied on the ability to create
prefix indexes over TEXT columns.
Default Value: 0
ibmdb2i_max_read_buffer_size
Controls the maximum amount of memory allocated for buffering
row data when doing reads from an IBMDB2I
table. This buffering is done independently of any row
buffering done within MySQL proper. Setting this value too low
may reduce read performance, while setting it too high may
increase memory usage and may also reduce read performance.
Default Value: 1 MB
ibmdb2i_max_write_buffer_size
Controls the maximum amount of memory allocated for buffering
row data when inserting multiple rows into an
IBMDB2I table. This buffering is done
independently of any row buffering done within MySQL proper.
Setting this value too low may reduce write performance, while
setting it too high may increase memory usage.
Default Value: 8 MB
ibmdb2i_rdb_name
The name of a local DB2 for i relational database that will
act as a container for all IBMDB2I tables.
This allows an independent auxiliary storage pool (IASP) to be
selected for usage. If no value is specified, the system
database (*SYSBAS) is used.
Default Value: <blank>
ibmdb2i_transaction_unsafe
Controls whether IBMDB2I honors the
transactional commands and isolation levels specified for
MySQL. If the value is OFF, transactions are fully supported.
If the value is ON, transactional behavior is not implemented.
This may provide a moderate performance increase for
applications that do not rely on transactional guarantees.
Default Value: OFF
The values specified for
ibmdb2i_create_index_option ,
ibmdb2i_create_time_columns_as_tod ,
ibmdb2i_map_blob_to_varchar ,
ibmdb2i_compat_opt_allow_zero_date_vals ,
ibmdb2i_propagate_default_col_vals , and
ibmdb2i_propagate_default_col_val will be applied
whenever an IBMDB2I table is created. Tables
are implicitly destroyed and re-created when an
offline ALTER TABLE is
performed on an IBMDB2I table. Therefore it is
highly recommended that the values of these variables be
consistent across the lifetime of a table to prevent an
ALTER TABLE from running under a different set
of options than were used to originally create the table. If this
recommendation is not followed, the ALTER TABLE
may fail upon encountering incompatible data when re-creating the
table.
13.7.3. Creating schemas and tables
IBMDB2I tables are created by specifying the
ENGINE=IBMDB2I option on a CREATE
TABLE or ALTER TABLE statement. These
tables are stored as DB2 for i objects in the QSYS.LIB file
system. Therefore, in contrast to the behavior of other storage
engines, IBMDB2I table and index data does not
reside beneath the datadir directory with other MySQL table data.
When the first IBMDB2I table is created in a
MySQL schema, the corresponding DB2 schema will be created (if it
does not already exist).
Note that--if the user profile running MySQL has sufficient
authority--dropping a MySQL schema will drop a DB2 schema of the
same name even if the schema never contained any
IBMDB2I tables and contains objects not related
to MySQL. Therefore, it is recommended that extreme caution be
used when dropping MySQL schemas when the
IBMDB2I plugin is installed.
13.7.3.1. Identifier names
DB2 for i file objects have both a short (10-character) system
name and an SQL name. The DB2 for i SQL name is always the same
as the MySQL name. However, because MySQL's sensitivity to the
letter case of schema and table names depends on the file system
containing the datadir, mapping the MySQL name to the system
name of the corresponding DB2 for i object must account for
several factors, described below.
When MySQL is operating in a case-sensitive mode (that is,
datadir is in a case-sensitive file-system like (/QOpenSys), the
case of the system name of the IBMDB2I object
reflects the case of the name specified to MySQL. If the object
has all uppercase characters and is equal to or less than 10
characters in length, the system name will be undelimited and in
uppercase.If the object name has mixed or lower-case letters,
the IBM i system name will contain up to 8 characters of the
name in the specified case and will be delimited by surrounding
quotation marks. If the MySQL name has more characters than can
fit in the delimited system name, the system name will be
generated as a mangled name delimited by surrounding quotation
marks.
Examples of this naming behavior are given below:
Table 13.7. Naming Behavior in DB2 Storage Engine MySQL name | DB2 for i SQL name | IBM i System name |
---|
mytable | mytable | "mytable" | mylongertable | mylongertable | "mylo0001" | UPPERTABLE | UPPERTABLE | UPPERTABLE | UPPERLONGTABLE | UPPERLONGTABLE | UPPER00001 | MixedTab | MixedTab | "MixedTab" | MixedlongerTab | MixedlongerTab | "Mixe0001" |
If MySQL is operating in a case-insensitive mode, the IBM i
system name will contain up to 8 characters of the name in lower
case and will be delimited by surrounding quotation marks. If
the MySQL name has more characters than can fit in the delimited
system name, the system name will be generated as a mangled name
delimited by surrounding quotation marks.
Note that IBM i system names are not changed when a RENAME TABLE
command is executed. Only the DB2 for i SQL name changes.
Unlike MySQL, DB2 for i requires that index names be unique to
an entire schema. In order to support MySQL indexes,
IBMDB2I creates the DB2 for i indexes with
modified file names. The generated name is a concatenation of
the index name, three underscores (_), and the table name. For
example, CREATE INDEX idx1 ON tab1 (a, b)
would create a DB2 index named idx___tab1 . If
the ibmdb2i_create_index_option value is set to 1, an additional
index may be created which is named with an additional
H_ marker between the index and table names
(for example, idx___H_tab1). These generated names are then
mangled to create the an IBM i system name, as described above.
If a table is renamed, the indexes will also be renamed.
This index name generation scheme also has implications for the
length of index and table names; to permit the generated name
and allow for delimiting quotes, the combined length of the
index and table names must be less than or equal to 121
characters.
13.7.3.2.1. Data type mapping
When creating a table, IBMDB2I may map the
MySQL types specified on a CREATE TABLE
statement into a corresponding or compatible DB2 for i type.
Examples include the BIT type, which is
stored as a BINARY field, or the
MEDIUMINT type, which is stored as an
INTEGER field. For most data types, this
mapping is transparent. Data types which have restrictions
unique to IBMDB2I are documented in
Section 13.7.7, “Notes and Limitations”.
13.7.4. Database/metadata management
When an IBMDB2I table is created, a File Level
ID (FID) file is created in the database directory. This file has
an extension .FID and contains the last known FID of the
associated DB2 for i physical file. The IBMDB2I
engine uses this FID value to determine whether incompatible
changes have been made to the table by an external (non-MySQL)
interface. If the physical file is altered, DB2 for i
automatically generates a new FID. The next time
IBMDB2I attempts to access the file, it will
detect that the new FID does not match the known FID, and it will
prevent MySQL from using the table.
In some cases, the changes to the physical file that cause the FID
to be updated may not adversely affect access through MySQL and
IBMDB2I . In this case, a user may wish to
override the FID check that IBMDB2I performs. A
user with appropriate permissions may do so simply by deleting the
.FID file associated with the table and
performing a FLUSH TABLE command against the
table. IBMDB2I will then regenerate the file
with the new FID the next time the table is accessed from MySQL.
If triggers or constraints are applied to the table from a native
DB2 interface, they will be respected when accessing the table
from MySQL, but MySQL will have no knowledge of the triggers or
constraints. Likewise, views and indexes can be created over the
tables from a DB2 interface, but the indexes will not be
accessible from MySQL.
13.7.5. Transaction behavior
The IBMDB2I storage engine supports row-level
transaction management. All MySQL isolation levels are supported
by the engine, and where highest performance is required,
transaction support can be disabled globally or per session by
modifying the ibmdb2i_transaction_unsafe configuration option.
IBMDB2I uses the underlying DB2 for i
transaction support to implement MySQL isolation levels. DB2 for i
uses table and row locks to implement the various isolation
levels, as described below:
Table 13.8. IBMDB2I Isolation Levels Isolation level | Read-only (SELECT) | Read-write (UPDATE, DELETE) |
---|
| Lock enforcement | Visibility of uncommitted work on behalf of other connections | Lock enforcement | Visibility of uncommitted work on behalf of other connections |
---|
SERIALIZABLE | Table is locked until end of transaction.Other connections may read rows
while locked. | N/A | Table is locked until end of transaction.Other connections may read rows
while locked. | N/A | REPEATABLE READ | Rows that have been read are locked until end of transaction.Other
connections may read and insert rows while locked. | Rows cannot be read until work is committed. | Rows that have been read are locked until end of transaction.Other
connections may read and insert rows while locked. | Rows cannot be read until work is committed. | READ COMMITTED | Row is locked while cursor is positioned on that row.Other connections
may read and insert rows while locked. | Rows cannot be read until work is committed. | Row is locked while cursor is positioned on that row.Other connections
may read and insert rows while locked. | Rows cannot be read until work is committed. | READ UNCOMMITTED | Row is locked while cursor is positioned on that row.Other connections
may read and insert rows while locked. | Rows can be read before work is committed. | Row is locked while cursor is positioned on that row.Other connections
may read and insert rows while locked. | Rows can be read before work is committed. | transaction_unsafe | No locks | Full access | Table is locked until end of transaction.Other connections may read rows
while locked. | Unrestricted access |
Attempts to access locked rows time out according to the timeout
value associated with the underlying DB2 physical file. This
timeout wait is 30 seconds by default. Refer to
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/dbp/rbafoconcrec.htm
for more information.
Because IBMDB2I does not multi-version rows
under commitment control, row lock contention may occur under
certain scenarios. In particular, when multiple connections
attempt to read overlapping ranges of rows while performing a
statement that does updates, the connections may contend for the
same row locks. This may lead to delays until the row lock timeout
expires. Creating appropriate indexes and increasing query
selectivity to reduce range overlap may help to alleviate this
contention.
13.7.6. Principles and Terminology
AUTO_INCREMENT
The MySQL auto_increment column attribute can be used to generate
a unique identity for new rows.The IBMDB2I
storage engine maps the MySQL auto_increment attribute to the DB2
for i identity attribute
For most MySQL storage engines, the auto_increment value is
determined by adding one to the maximum value stored in the table
for the column.For the IBMDB2I storage engine,
DB2 for i generates the identity value by adding one to the last
generated value. Following are some example MySQL statements to
illustrate the point.
create table t1 (a int auto increment, primary key(a)) engine = ibmdb2i;
insert into t1 values(3);
insert into t1 values(null),(null);
For the first INSERT statement, an explicit value of 3 is
specified for the auto_increment column, so the value 3 is stored
in the inserted row.For the second INSERT statement null is
specified, so generated values 1 and 2 will be stored in the rows.
Duplicate key failures can occur in DB2 for i if a MySQL
application mixes explicit auto_increment values with generated
values within a table. For the example above, if one more record
is inserted into the table for which an auto_increment value is
generated, a duplicate key error will occur because the value 3
(that is, the last generated value plus one) already exists in the
table.To effect the MySQL behavior for auto_increment columns, the
IBMDB2I storage engine will detect a duplicate
key error, alter the restart value for the DB2 identity column to
the maximum value plus one, and retry the failed insert, but only
if the following conditions are true:
The duplicate key error occurred on an index for which the
auto_increment column is a key field
An exclusive (LENR) lock can be acquired on the table
The error occurred on the first or only row of the
INSERT statement.
The IBMDB2I storage engine does not support the
following usage of auto_increment columns:
Any MySQL global or session variable that affects the start,
increment, or offset for generated auto_increment values.
Any MySQL feature that returns the next value to be
used for an auto_increment column.
An auto_increment column on a MySQL partitioned table.
13.7.7. Notes and Limitations
When using multiples instances of MySQL on a single i5 host
you should be aware that the two independent instances will
access the same database within the DB2 environment. This can
lead to namespace collisions and issues. To get round this
limitation, you should configure different IBM i independent
auxilliary storage pools (ISAPs) for each MySQL server, and
then use the ibmdb2i_rdb_name option to
MySQL to configure which IASP should be used for each MySQL
instance.
Indexes over VARBINARY columns may not
provide accurate estimates the optimizer. Queries over such
indexes may produce error 2027 in the error log but will
succeed. This affects only the performance of these queries,
not the correctness of the results.
Setting ibmdb2i_system_trace_level = 16
(STRTRC) may cause unpredictable failures including
error 2088 on some operations. This is unlikely to affect any
users, as this value is recommended only for serviceability
purposes.
IBMDB2I honors the
CASCADE /RESTRICT option
on the DROP TABLE command.
The use of FLUSH TABLES WITH READ LOCK is
discouraged when using IBMDB2I tables. Due
to differences in internal locking implementations,
FLUSH TABLES WITH READ LOCK may produce
deadlocks on IBMDB2I tables.
Row-based replication is supported by
IBMDB2I . Statement-based replication is not
supported.
Schema name lengths are limited to 10 characters in IBM i 5.4
and 30 characters in IBM i 6.1. If the schema name is mixed-
or lower-case, two characters must be subtracted from the
limit to account for surrounding quotes.
The RENAME TABLE command cannot be used to move
IBMDB2I tables from one database to
another.
The maximum length of the internal row format for
IBMDB2I is 32767 bytes. Because the
internal row format may differ from the MySQL row format due
to data mapping differences (see Creating schemas and tables
section), anticipating this limit may be difficult.
The combined length of the index and table names must be less
than or equal to 121 characters.
Indexes over TEXT or BLOB columns with a maximum length
greater than 255 characters are not supported. The
ibmdb2i_map_blob_to_varchar option can be used to work around
this limitation for TEXT fields up to 64K characters.
Specific restrictions apply to certain data types as described
in the following chart:
Table 13.9. Data Type Restrictions in IBMDB2I MySQL data type | Restriction |
---|
LONGBLOB or LONGTEXT | The maximum length of a DB2 BLOB data type is 2GB. | DATE | IBMDB2I does not support the special date value of
'0000-00-00'. The restrictions on
DATE and
DATETIME columns can be removed by
setting the value of
ibmdb2i_compat_opt_allow_zero_date_vals
to 1. | DATETIME | IBMDB2I does not support the special datetime value
of '0000-00-00 00:00:00'. The restrictions on
DATE and
DATETIME columns can be removed by
setting the value of
ibmdb2i_compat_opt_allow_zero_date_vals
to 1. | DECIMAL(p, s) or NUMERIC(p, s) | If p is greater than 63 and s is greater than (p-63), the field
definition is truncated to DECIMAL(63,
s-(p-63)) . If p is greater than 63 and s is
less than or equal to (p-63), the definition is not
supported. | TIME | By default, IBMDB2I only supports times in the range
'00:00:00' to '23:59:59.' See the
ibmdb2i_create_time_columns_as_tod option for more
information. |
These MySQL statements are not supported by the
IBMDB2I Engine:
The ucs2_spanish2_ci and
utf8_spanish2_ci collations are not
supported by IBMDB2I . There are no plans to
support these collations.
The ucs2_swedish_ci and
utf8_swedish_ci collations were added in
MySQL 5.1.35. supported by IBMDB2I . As with
other language-specific unicode collations, the support will
be only be available on IBM i 6.1 and later releases.
13.7.8. Character sets and collations
DB2 for i only supports a single collation per index or foreign
key constraint. A foreign key constraint must have the same
collation as a primary key constraint, if one exists..
Character sets and collations supported by
IBMDB2I are described in the list below. IBM i
6.1 is required for the most comprehensive collation support. Note
that Chinese, Japanese, and Korean character sets are converted to
UTF-16 for storage in DB2 for i;
utf8_general_ci is converted to
UCS2 for storage in DB2 for i.
Table 13.10. Collation Compatibility in IBMDB2I and MySQL MySQL Collation | Supported in IBM i 5.4 | Supported in IBM i 6.1 |
---|
armscii8_general_ci | | | armscii8_bin | | | ascii_general_ci | | Yes | ascii_bin | | Yes | big5_chinese_ci | Yes | Yes | big5_bin | Yes | Yes | cp1250_croatian_ci | | Yes | cp1250_czech_cs | | Yes | cp1250_general_ci | | Yes | cp1250_polish_ci | | Yes | cp1250_bin | | Yes | cp1251_bulgarian_ci | | Yes | cp1251_general_ci | | Yes | cp1251_general_cs | | Yes | cp1251_ukrainian_ci | | | cp1251_bin | | Yes | cp1256_general_ci | | Yes | cp1256_bin | | Yes | cp1257_general_ci | | | cp1257_lithuanian_ci | | | cp1257_bin | | | cp850_general_ci | Yes | Yes | cp850_bin | Yes | Yes | cp852_general_ci | | Yes | cp852_bin | | Yes | cp866_general_ci | | | cp866_bin | | | cp932_japanese_ci | Yes | Yes | cp932_bin | Yes | Yes | dec8_swedish_ci | | | dec8_bin | | | eucjpms_japanese_ci | | | eucjpms_bin | | | euckr_korean_ci | Yes | Yes | euckr_bin | Yes | Yes | gb2312_chinese_ci | Yes | Yes | gb2312_bin | Yes | Yes | gbk_chinese_ci | Yes | Yes | gbk_bin | Yes | Yes | geostd8_general_ci | | | geostd8_bin | | | greek_general_ci | Yes | Yes | greek_bin | Yes | Yes | hebrew_general_ci | Yes | Yes | hebrew_bin | Yes | Yes | hp8_english_ci | | | hp8_bin | | | keybcs2_general_ci | | | keybcs2_bin | | | koi8r_general_ci | | | koi8r_bin | | | koi8u_general_ci | | | koi8u_bin | | | latin1_danish_ci | Yes | Yes | latin1_general_ci | Yes | Yes | latin1_general_cs | Yes | Yes | latin1_german1_ci | Yes | Yes | latin1_german2_ci | | | latin1_spanish_ci | Yes | Yes | latin1_swedish_ci | Yes | Yes | latin1_bin | Yes | Yes | latin2_croatian_ci | Yes | Yes | latin2_czech_cs | Yes | Yes | latin2_general_ci | Yes | Yes | latin2_hungarian_ci | Yes | Yes | latin2_bin | Yes | Yes | latin5_turkish_ci | Yes | Yes | latin5_bin | Yes | Yes | latin7_estonian_cs | | | latin7_general_ci | | | latin7_general_cs | | | latin7_bin | | | macce_general_ci | | Yes | macce_bin | | Yes | macroman_general_ci | | | macroman_bin | | | sjis_japanese_ci | Yes | Yes | sjis_bin | Yes | Yes | swe7_swedish_ci | | | swe7_bin | | | tis620_thai_ci | Yes | Yes | tis620_bin | Yes | Yes | ucs2_czech_ci | | Yes | ucs2_danish_ci | | Yes | ucs2_esperanto_ci | | Yes | ucs2_estonian_ci | | Yes | ucs2_general_ci | Yes | Yes | ucs2_hungarian_ci | | Yes | ucs2_icelandic_ci | | Yes | ucs2_latvian_ci | | Yes | ucs2_lithuanian_ci | | Yes | ucs2_persian_ci | | Yes | ucs2_polish_ci | | Yes | ucs2_roman_ci | | | ucs2_romanian_ci | | Yes | ucs2_slovak_ci | | Yes | ucs2_slovenian_ci | | Yes | ucs2_spanish_ci | | Yes | ucs2_spanish2_ci | | Yes | ucs2_turkish_ci | | Yes | ucs2_unicode_ci | Yes | Yes | ucs2_bin | Yes | Yes | ujis_japanese_ci | Yes | Yes | ujis_bin | Yes | Yes | utf8_czech_ci | | Yes | utf8_danish_ci | | Yes | utf8_esperanto_ci | | Yes | utf8_estonian_ci | | Yes | utf8_general_ci | Yes | Yes | utf8_hungarian_ci | | Yes | utf8_icelandic_ci | | Yes | utf8_latvian_ci | | Yes | utf8_lithuanian_ci | | Yes | utf8_persian_ci | | Yes | utf8_polish_ci | | Yes | utf8_roman_ci | | | utf8_romanian_ci | | Yes | utf8_slovak_ci | | Yes | utf8_slovenian_ci | | Yes | utf8_spanish_ci | | Yes | utf8_spanish2_ci | | Yes | utf8_turkish_ci | | Yes | utf8_unicode_ci | | Yes | utf8_bin | Yes | Yes |
13.7.9. Error codes and trouble-shooting information
Errors reported by the IBMDB2I engine may
originate from two locations. Error values under 2500 originate in
DB2 for i. Error values over 2500 originate in the storage engine
itself. Occasionally, errors are reported by
IBMDB2I but, due to the architecture of MySQL,
cannot be directly exposed to MySQL client interfaces. In these
cases, issuing a SHOW ERRORS command or
referring to the MySQL error log may be necessary to determine the
cause of a failure. Errors which originate in DB2 for i are
usually encountered in the DB2 for i SQL Server Mode job (QSQSRVR)
which services the MySQL client connection; additional information
about failures can often be found by looking in the job log of the
associated QSQSRVR job.
Following is the list of codes and messages for errors that occur
in DB2 for i that are reported by the IBMDB2I
engine. %d and %s represent numbers and strings, respectively,
that are replaced when the message is displayed.
Table 13.11. Error Codes from IBMDB2I Error Code Number | Error Message Text |
---|
0 | Successful | 2016 | Thread ID is too long | 2017 | Error creating a SPACE memory object | 2018 | Error creating a FILE memory object | 2019 | Error creating a SPACE synchronization token | 2020 | Error creating a FILE synchronization token | 2021 | See message %-.7s in joblog for job %-.6s/%-.10s/%-.10s. | 2022 | Error unlocking a synchronization token when closing a connection | 2023 | Invalid action specified for an 'object lock' request | 2024 | Invalid action specified for a savepoint request | 2025 | Partial keys are not supported with an ICU sort sequence | 2026 | Error retrieving an ICU sort key | 2027 | Error converting single-byte sort sequence to UCS-2 | 2028 | An unsupported collation was specified | 2029 | Validation failed for referenced table of foreign key constraint | 2030 | Error extracting table for constraint information | 2031 | Error extracting referenced table for constraint information | 2032 | Invalid action specified for a 'commitment control' request | 2033 | Invalid commitment control isolation level specified on 'open' request | 2034 | Invalid file handle | 2036 | Invalid option specified for returning data on 'read' request | 2037 | Invalid orientation specified for 'read' request | 2038 | Invalid option type specified for 'read' request | 2039 | Invalid isolation level for starting commitment control | 2040 | Error unlocking a synchronization token in module QMYALC | 2041 | Length of space for returned format is not long enough | 2042 | SQL XA transactions are currently unsupported by this interface | 2043 | The associated QSQSRVR job was killed or ended unexpectedly. | 2044 | Error unlocking a synchronization token in module QMYSEI | 2045 | Error unlocking a synchronization token in module QMYSPO | 2046 | Error converting input CCSID from short form to long form | 2048 | Error getting associated CCSID for CCSID conversion | 2049 | Error converting a string from one CCSID to another | 2050 | Error unlocking a synchronization token | 2051 | Error destroying a synchronization token | 2052 | Error locking a synchronization token | 2053 | Error recreating a synchronization token | 2054 | A space handle was not specified for a constraint request | 2055 | An SQL cursor was specified for a delete request | 2057 | Error on delete request because current UFCB for connection is not open | 2058 | An SQL cursor was specified for an object initialization request | 2059 | An SQL cursor was specified for an object override request | 2060 | A space handle was not specified for an object override request | 2061 | An SQL cursor was specified for an information request | 2062 | An SQL cursor was specified for an object lock request | 2063 | An SQL cursor was specified for an optimize request | 2064 | A data handle was not specified for a read request | 2065 | A row number handle was not specified for a read request | 2066 | A key handle was not specified for a read request | 2067 | An SQL cursor was specified for an row estimation request | 2068 | A space handle was not specified for a row estimation request | 2069 | An SQL cursor was specified for a release record request | 2070 | A statement handle was not specified for an 'execute immediate' request | 2071 | A statement handle was not specified for a 'prepare open' request | 2072 | An SQL cursor was specified for an update request | 2073 | The UFCB was not open for read | 2074 | Error on update request because current UFCB for connection is not open | 2075 | A data handle was not specified for an update request | 2076 | An SQL cursor was specified for a write request | 2077 | A data handle was not specified for a write request | 2078 | An unknown function was specified on a process request | 2079 | A share definition was not specified for an 'allocate share' request | 2080 | A share handle was not specified for an 'allocate share' request | 2081 | A use count handle was not specified for an 'allocate share' request | 2082 | A 'records per key' handle was not specified for an information request | 2083 | Error resolving LOB address | 2084 | Length of a LOB space is too small | 2085 | An unknown function was specified for a server request | 2086 | Object authorization failed. See message %-.7s in joblog for job
%-.6s/%-.10s/%-.10s. for more information. | 2088 | Error locking mutex on server | 2089 | Error unlocking mutex on server | 2090 | Error checking for RDB name in RDB Directory | 2091 | Error creating mutex on server | 2094 | Error unlocking mutex | 2095 | Error connecting to server job | 2096 | Error connecting to server job | 2098 | Function check occurred while registering parameter spaces. See job log. | 2101 | End of block | 2102 | The file has changed and might not be compatible with the MySQL table
definition | 2103 | Error giving pipe to server job | 2104 | There are open object locks when attempting to deallocate | 2105 | There is no open lock | 2108 | The maximum value for the auto_increment data type was exceeded | 2109 | Error occurred closing the pipe | 2110 | Error occurred taking a descriptor for the pipe | 2111 | Error writing to pipe | 2112 | Server was interrupted | 2113 | No pipe descriptor exists for reuse | 2114 | Error occurred during an SQL prepare statement | 2115 | Error occurred during an SQL open | 2122 | An unspecified error was returned from the system. | | | | |
Following is the list of error codes and messages that occur
within the IBMDB2I storage engine.
Table 13.12. Error Codes and Messages in IBMDB2I Error Code Number | Error Message Text |
---|
2501 | Error opening codeset conversion from %.64s to %.64s (errno = %d).
Resolution: Alter the
table definition to specify a character set that is
supported by the IBMDB2I engine. | 2502 | Invalid %-.10s name '%-.128s.
Resolution: If a field name, ensure that it is
less than the maximum length of 126 characters.If an index
name, ensure that the length of the index name plus the
length of the table name is less than 121 characters. | 2503 | Unsupported move from '%-.128s' to '%-.128s' on RENAME TABLE statement.
Resolution: Moving a
table from one schema to another is not supported by the
IBMDB2I engine. Create a new table in
the 'to' schema, copy the data to the new table, and then
drop the old table. | 2504 | The %-.64s character set is not supported.
Resolution: Try using another character set. | 2505 | Auto_increment is not allowed for a partitioned table.
Resolution: Remove the
auto_increment attribute from the table or do not
partition the table. | 2506 | Character set conversion error due to unknown encoding scheme %d.
Resolution: Alter the
table definition to ensure that character sets assigned to
columns are supported by the IBMDB2I
engine. | 2508 | Table '%-.128s' was not found by the storage engine.
Resolution: A table
definition exists in MySQL, but the correspondingtable in
DB2 for i is not found. Delete the MySQL table or restore
the DB2 table. | 2509 | Could not resolve to %-.128s in library %-.10s type %-.10s (errno = %d).
Resolution: Restore the
missing IBM i object to the system. | 2510 | Error on _PGMCALL for program %-.10s in library %-.10s (error = %d).
Resolution: This is an
internal error. | 2511 | Error on _ILECALL for API '%.128s' (error = %d).
Resolution: This is an
internal error. | 2512 | Error in iconv() function during character set conversion (errno = %d).
Resolution: Ensure the
text being inserted contains only valid code points for
the associated character set. This may be caused by using
ENCRYPT or COMPRESS on a text field, resulting in binary
data containing invalid characters. | 2513 | Error from Get Encoding Scheme (QTQGESP) API: %d, %d, %d
Resolution: This is an
internal error. | 2514 | Error from Get Related Default CCSID (QTQGRDC) API: %d, %d, %d.
Resolution: This is an
internal error. | 2515 | Data out of range for column '%.192s'.
Resolution: DB2 for i does not support a zero
value for DATE and DATETIME data types. Specify a new
value for the column. | 2516 | Schema name '%.128s' exceeds maximum length of %d characters.
Resolution: Change the
schema name so that its length does not exceed the
maximum. For mixed case or lower case names, allow 2
characters for outer quotes. | 2517 | Multiple collations not supported in a single index.
Resolution: DB2 for i
only supports sort sequences at the table or index level,
so all character-based columns in the primary key or index
must be using the same collation. Alter the table
definition so that all columns use the same collation. | 2518 | Sort sequence was not found.
Resolution: Alter the table to use a collation
that is supported by the IBMDB2I
engine. | 2519 | One or more characters in column %.128s were substituted during
conversion. Resolution:
This is a warning that during the conversion of data from
MySQL to DB2 some characters were replaced with substitute
characters. | 2520 | A decimal column exceeded the maximum precision. Data may be truncated.
Resolution: A decimal
column specified on a CREATE TABLE or ALTER TABLE command
has greater precision than DB2 allows. Data stored in this
column may be truncated. Change the column specification
to have a precision less than or equal to 63. | 2521 | Some data returned by DB2 for table %s could not be converted for MySQL.
Resolution: This is a
warning that some data could not be converted from DB2 to
MySQL. Data was likely inserted into the table using DB2
interfaces that is valid data for DB2 but is invalid for
MySQL. | 2523 | Column %.128s contains characters that cannot be converted.
Resolution: An error
occurred converting data from the MySQL character set to
the associated DB2 coded character set identifier
(CCSID).Omit the incompatible characters or alter the
table to specify a different character set for the column. | 2524 | An invalid name was specified for ibmdb2i_rdb_name.
Resolution: Specify a
valid relational database (RDB) name. | 2525 | A duplicate key was encountered for index '%.128s'.
Resolution: : Specify a
unique key value for the row. Generally, this error occurs
if a unique constraint or index has been applied to the
table from a non-MySQL interface. | 2528 | Some attribute(s) defined for column '%.128s' may not be honored by
accesses from DB2. |
13.8. The MERGE Storage Engine
The MERGE storage engine, also known as the
MRG_MyISAM engine, is a collection of identical
MyISAM tables that can be used as one.
“Identical” means that all tables have identical column
and index information. You cannot merge MyISAM
tables in which the columns are listed in a different order, do not
have exactly the same columns, or have the indexes in different
order. However, any or all of the MyISAM tables
can be compressed with myisampack. See
Section 4.6.5, “myisampack — Generate Compressed, Read-Only MyISAM Tables”. Differences in table options such as
AVG_ROW_LENGTH , MAX_ROWS , or
PACK_KEYS do not matter.
When you create a MERGE table, MySQL creates two
files on disk. The files have names that begin with the table name
and have an extension to indicate the file type. An
.frm file stores the table format, and an
.MRG file contains the names of the tables that
should be used as one. The tables do not have to be in the same
database as the MERGE table itself.
You can use SELECT ,
DELETE ,
UPDATE , and
INSERT on MERGE
tables. You must have SELECT ,
UPDATE , and
DELETE privileges on the
MyISAM tables that you map to a
MERGE table.
Note
The use of MERGE tables entails the following
security issue: If a user has access to MyISAM
table t , that user can create a
MERGE table m that
accesses t . However, if the user's
privileges on t are subsequently
revoked, the user can continue to access
t by doing so through
m .
If you use DROP TABLE with a
MERGE table, you are dropping only the
MERGE specification. The underlying tables are
not affected.
To create a MERGE table, you must specify a
UNION=(list-of-tables )
option that indicates which MyISAM tables to use.
You can optionally specify an INSERT_METHOD
option if you want inserts for the MERGE table to
take place in the first or last table of the
UNION list. Use a value of
FIRST or LAST to cause inserts
to be made in the first or last table, respectively. If you specify
no INSERT_METHOD option or if you specify it with
a value of NO , inserts into the
MERGE table are disallowed and attempts to do so
result in an error.
The following example shows how to create a MERGE
table:
mysql> CREATE TABLE t1 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 (
-> a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> message CHAR(20)) ENGINE=MyISAM;
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql> CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
Note that the a column is indexed as a
PRIMARY KEY in the underlying
MyISAM tables, but not in the
MERGE table. There it is indexed but not as a
PRIMARY KEY because a MERGE
table cannot enforce uniqueness over the set of underlying tables.
After creating the MERGE table, you can issue
queries that operate on the group of tables as a whole:
mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
As of MySQL 5.1.15, the underlying table definitions and indexes
must conform more closely to the definition of the
MERGE table. Conformance is checked when a table
that is part of a MERGE table is opened, not when
the MERGE table is created. If any table fails
the conformance checks, the operation that triggered the opening of
the table will fail. This means that changes to the definitions of
tables within a MERGE may cause a failure when
the MERGE table is accessed. The conformance
checks applied to each table are:
The underlying table must have exactly the same number of
columns as the MERGE table.
The column order in the underlying table must match the column
order in the MERGE table.
Additionally, the specification for each column in the parent
MERGE table and the underlying table are
compared. For each column, MySQL checks:
The column type in the underlying table must equal the
column type of MERGE table.
The column length in the underlying table must equal the
column length of MERGE table.
The column of the underlying table and the
MERGE table can be
NULL .
The underlying table must have at least as many indexes as the
MERGE table. The underlying table may have
more indexes than the MERGE table, but cannot
have fewer.
Note
A known issue exists where indexes on the same columns must be
in identical order, in both the MERGE table
and the underlying MyISAM table. See
Bug#33653.
For each index:
The index type of the underlying table and the
MERGE table must be the same.
The number of index parts (that is, multiple columns within
a compound index) in the index definition for the underlying
table and the MERGE table must be the
same.
For each index part:
Index part lengths must be equal.
Index part types must be equal.
Index part languages must be equal.
Check whether index parts can be
NULL .
To remap a MERGE table to a different collection
of MyISAM tables, you can use one of the
following methods:
DROP the MERGE table and
re-create it.
Use ALTER TABLE tbl_name
UNION=(...) to change the list of underlying tables.
Beginning with MySQL 5.1.24, it is also possible to use
ALTER TABLE ... UNION=() (that is, with an
empty UNION clause) to remove all
of the underlying tables.
Additional Resources
13.8.1. MERGE Table Advantages and Disadvantages
MERGE tables can help you solve the following
problems:
Easily manage a set of log tables. For example, you can put
data from different months into separate tables, compress some
of them with myisampack, and then create a
MERGE table to use them as one.
Obtain more speed. You can split a large read-only table based
on some criteria, and then put individual tables on different
disks. A MERGE table structured this way
could be much faster than using a single large table.
Perform more efficient searches. If you know exactly what you
are looking for, you can search in just one of the split
tables for some queries and use a MERGE
table for others. You can even have many different
MERGE tables that use overlapping sets of
tables.
Perform more efficient repairs. It is easier to repair
individual tables that are mapped to a
MERGE table than to repair a single large
table.
Instantly map many tables as one. A MERGE
table need not maintain an index of its own because it uses
the indexes of the individual tables. As a result,
MERGE table collections are
very fast to create or remap. (You must
still specify the index definitions when you create a
MERGE table, even though no indexes are
created.)
If you have a set of tables from which you create a large
table on demand, you can instead create a
MERGE table on them on demand. This is much
faster and saves a lot of disk space.
Exceed the file size limit for the operating system. Each
MyISAM table is bound by this limit, but a
collection of MyISAM tables is not.
You can create an alias or synonym for a
MyISAM table by defining a
MERGE table that maps to that single table.
There should be no really notable performance impact from
doing this (only a couple of indirect calls and
memcpy() calls for each read).
The disadvantages of MERGE tables are:
You can use only identical MyISAM tables
for a MERGE table.
Some MyISAM features are unavailable in
MERGE tables. For example, you cannot
create FULLTEXT indexes on
MERGE tables. (You can, of course, create
FULLTEXT indexes on the underlying
MyISAM tables, but you cannot search the
MERGE table with a full-text search.)
If the MERGE table is nontemporary, all
underlying MyISAM tables must be
nontemporary. If the MERGE table is
temporary, the MyISAM tables can be any mix
of temporary and nontemporary.
MERGE tables use more file descriptors than
MyISAM tables. If 10 clients are using a
MERGE table that maps to 10 tables, the
server uses (10 ? 10) + 10 file descriptors. (10 data
file descriptors for each of the 10 clients, and 10 index file
descriptors shared among the clients.)
Index reads are slower. When you read an index, the
MERGE storage engine needs to issue a read
on all underlying tables to check which one most closely
matches the given index. To read the next index value, the
MERGE storage engine needs to search the
read buffers to find the next value. Only when one index
buffer is used up does the storage engine need to read the
next index block. This makes MERGE indexes
much slower on eq_ref
searches, but not much slower on
ref searches. For more
information about eq_ref
and ref , see
Section 12.3.2, “EXPLAIN Syntax”.
13.8.2. MERGE Table Problems
The following are known problems with MERGE
tables:
If you use ALTER TABLE to
change a MERGE table to another storage
engine, the mapping to the underlying tables is lost. Instead,
the rows from the underlying MyISAM tables
are copied into the altered table, which then uses the
specified storage engine.
REPLACE does not work as
expected because the MERGE engine cannot
enforce uniqueness over the set of underlying tables. The two
key facts are:
REPLACE can detect unique
key violations only in the underlying table to which it is
going to write (which is determined by the
INSERT_METHOD option). This differs
from violations in the MERGE table
itself.
If REPLACE detects unique
key violation, it will only change the corresponding row
in the underlying table it is writing to; that is, the
first or last table (as determined by the
INSERT_METHOD option.
Similar considerations apply for
INSERT
... ON DUPLICATE KEY UPDATE .
MERGE tables do not support partitioning.
That is, you cannot partition a MERGE
table, nor can any of a MERGE table's
underlying MyISAM tables be partitioned.
You should not use REPAIR
TABLE , OPTIMIZE
TABLE , DROP TABLE ,
ALTER TABLE ,
DELETE without a
WHERE clause, TRUNCATE
TABLE , or ANALYZE
TABLE on any of the tables that are mapped into an
open MERGE table. If you do so, the
MERGE table may still refer to the original
table, which yields unexpected results. To work around this
deficiency, ensure that no MERGE tables
remain open by issuing a
FLUSH TABLES
statement prior to performing any of those operations.
The unexpected results include the possibility that the
operation on the MERGE table will report
table corruption. However, if this occurs after operations on
the underlying MyISAM tables such as those
listed in the previous paragraph (REPAIR
TABLE , OPTIMIZE
TABLE , and so forth), the corruption message is
spurious. To deal with this, issue a
FLUSH TABLES
statement after modifying the MyISAM
tables.
DROP TABLE on a table that is
in use by a MERGE table does not work on
Windows because the MERGE storage engine's
table mapping is hidden from the upper layer of MySQL. Windows
does not allow open files to be deleted, so you first must
flush all MERGE tables (with
FLUSH TABLES )
or drop the MERGE table before dropping the
table.
A MERGE table cannot maintain uniqueness
constraints over the entire table. When you perform an
INSERT , the data goes into the
first or last MyISAM table (as determined
by the INSERT_METHOD option). MySQL ensures
that unique key values remain unique within that
MyISAM table, but not across all the tables
in the collection.
The INSERT_METHOD table option for a
MERGE table indicates which underlying
MyISAM table to use for inserts into the
MERGE table. However, use of the
AUTO_INCREMENT table option for that
MyISAM table has no effect for inserts into
the MERGE table until at least one row has
been inserted directly into the MyISAM
table.
As of MySQL 5.1.15, the definition of the
MyISAM tables and the
MERGE table are checked when the tables are
accessed (for example, as part of a
SELECT or
INSERT statement). The checks
ensure that the definitions of the tables and the parent
MERGE table definition match by comparing
column order, types, sizes and associated indexes. If there is
a difference between the tables then an error will be returned
and the statement will fail.
Because these checks take place when the tables are opened,
any changes to the definition of a single table, including
column changes, column ordering and engine alterations will
cause the statement to fail.
In MySQL 5.1.14 and earlier:
When you create or alter MERGE table,
there is no check to ensure that the underlying tables are
existing MyISAM tables and have
identical structures. When the MERGE
table is used, MySQL checks that the row length for all
mapped tables is equal, but this is not foolproof. If you
create a MERGE table from dissimilar
MyISAM tables, you are very likely to
run into strange problems.
Similarly, if you create a MERGE table
from non-MyISAM tables, or if you drop
an underlying table or alter it to be a
non-MyISAM table, no error for the
MERGE table occurs until later when you
attempt to use it.
Because the underlying MyISAM tables
need not exist when the MERGE table is
created, you can create the tables in any order, as long
as you do not use the MERGE table until
all of its underlying tables are in place. Also, if you
can ensure that a MERGE table will not
be used during a given period, you can perform maintenance
operations on the underlying tables, such as backing up or
restoring them, altering them, or dropping and recreating
them. It is not necessary to redefine the
MERGE table temporarily to exclude the
underlying tables while you are operating on them.
The order of indexes in the MERGE table and
its underlying tables should be the same. If you use
ALTER TABLE to add a
UNIQUE index to a table used in a
MERGE table, and then use
ALTER TABLE to add a nonunique
index on the MERGE table, the index
ordering is different for the tables if there was already a
nonunique index in the underlying table. (This happens because
ALTER TABLE puts
UNIQUE indexes before nonunique indexes to
facilitate rapid detection of duplicate keys.) Consequently,
queries on tables with such indexes may return unexpected
results.
If you encounter an error message similar to ERROR
1017 (HY000): Can't find file:
'mm .MRG' (errno: 2) it
generally indicates that some of the base tables do not use
the MyISAM storage engine. Confirm that all
of these tables are MyISAM .
The maximum number of rows in a MERGE table
is 264 (~1.844E+19; the same as for
a MyISAM table), provided that the server
was built using the
--with-big-tables option.
(All standard MySQL 5.1 standard binaries are
built with this option; for more information, see
Section 2.3.2, “Typical configure Options”.) It is not possible to
merge multiple MyISAM tables into a single
MERGE table that would have more than this
number of rows.
The MERGE storage engine does not support
INSERT DELAYED statements.
Use of underlying MyISAM tables of
differing row formats with a parent MERGE
table is currently known to fail. See Bug#32364.
As of MySQL 5.1.20, if a MERGE table cannot
be opened or used because of a problem with an underlying
table, CHECK TABLE displays
information about which table caused the problem.
As of MySQL 5.1.23, you cannot change the union list of a
nontemporary MERGE table when
LOCK TABLES is in effect. The
following does not work:
CREATE TABLE m1 ... ENGINE=MRG_MYISAM ...;
LOCK TABLES t1 WRITE, t2 WRITE, m1 WRITE;
ALTER TABLE m1 ... UNION=(t1,t2) ...;
However, you can do this with a temporary
MERGE table.
As of MySQL 5.1.23, you cannot create a
MERGE table with CREATE ...
SELECT , neither as a temporary
MERGE table, nor as a nontemporary
MERGE table. For example:
CREATE TABLE m1 ... ENGINE=MRG_MYISAM ... SELECT ...;
Attempts to do this result in an error:
tbl_name is not BASE
TABLE .
13.9. The MEMORY (HEAP ) Storage Engine
The MEMORY storage engine creates tables with
contents that are stored in memory. Formerly, these were known as
HEAP tables. MEMORY is the
preferred term, although HEAP remains supported
for backward compatibility.
Table 13.13. MEMORY Storage Engine
Features Storage limits | RAM | Transactions | No | Locking granularity | Table | MVCC | No | Geospatial datatype support | No | Geospatial indexing support | No | B-tree indexes | Yes | Hash indexes | Yes | Full-text search indexes | No | Clustered indexes | No | Data caches | N/A | Index caches | N/A | Compressed data | No | Encrypted data[a] | Yes | Cluster database support | No | Replication support[b] | Yes | Foreign key support | No | Backup / point-in-time recovery[c] | Yes | Query cache support | Yes | Update statistics for data dictionary | Yes | | |
Each MEMORY table is associated with one disk
file. The file name begins with the table name and has an extension
of .frm to indicate that it stores the table
definition.
To specify explicitly that you want to create a
MEMORY table, indicate that with an
ENGINE table option:
CREATE TABLE t (i INT) ENGINE = MEMORY;
As indicated by the name, MEMORY tables are
stored in memory. They use hash indexes by default, which makes them
very fast, and very useful for creating temporary tables. However,
when the server shuts down, all rows stored in
MEMORY tables are lost. The tables themselves
continue to exist because their definitions are stored in
.frm files on disk, but they are empty when the
server restarts.
This example shows how you might create, use, and remove a
MEMORY table:
mysql> CREATE TABLE test ENGINE=MEMORY
-> SELECT ip,SUM(downloads) AS down
-> FROM log_table GROUP BY ip;
mysql> SELECT COUNT(ip),AVG(down) FROM test;
mysql> DROP TABLE test;
MEMORY tables have the following characteristics:
Space for MEMORY tables is allocated in small
blocks. Tables use 100% dynamic hashing for inserts. No overflow
area or extra key space is needed. No extra space is needed for
free lists. Deleted rows are put in a linked list and are reused
when you insert new data into the table.
MEMORY tables also have none of the problems
commonly associated with deletes plus inserts in hashed tables.
MEMORY tables can have up to 32 indexes per
table, 16 columns per index and a maximum key length of 500
bytes.
The MEMORY storage engine implements both
HASH and BTREE indexes.
You can specify one or the other for a given index by adding a
USING clause as shown here:
CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
General characteristics of B-tree and hash indexes are described
in Section 7.4.4, “How MySQL Uses Indexes”.
You can have nonunique keys in a MEMORY
table. (This is an uncommon feature for implementations of hash
indexes.)
If you have a hash index on a MEMORY table
that has a high degree of key duplication (many index entries
containing the same value), updates to the table that affect key
values and all deletes are significantly slower. The degree of
this slowdown is proportional to the degree of duplication (or,
inversely proportional to the index cardinality). You can use a
BTREE index to avoid this problem.
Columns that are indexed can contain NULL
values.
MEMORY tables use a fixed-length row storage
format.
MEMORY tables cannot contain
BLOB or
TEXT columns.
MEMORY includes support for
AUTO_INCREMENT columns.
You can use INSERT DELAYED with
MEMORY tables. See
Section 12.2.5.2, “INSERT DELAYED Syntax”.
MEMORY tables are shared among all clients
(just like any other non-TEMPORARY table).
MEMORY table contents are stored in memory,
which is a property that MEMORY tables share
with internal temporary tables that the server creates on the
fly while processing queries. However, the two types of tables
differ in that MEMORY tables are not subject
to storage conversion, whereas internal temporary tables are:
If an internal temporary table becomes too large, the server
automatically converts it to an on-disk table, as described
in Section 7.5.10, “How MySQL Uses Internal Temporary Tables”.
MEMORY tables are never converted to disk
tables.
The maximum size of MEMORY tables is
limited by the
max_heap_table_size system
variable, which has a default value of 16MB. To have larger
(or smaller) MEMORY tables, you must
change the value of this variable. The value in effect at
the time a MEMORY table is created is the
value used for the life of the table. (If you use
ALTER TABLE or
TRUNCATE TABLE , the value in
effect at that time becomes the new maximum size for the
table. A server restart also sets the maximum size of
existing MEMORY tables to the global
max_heap_table_size value.)
You can set the size for individual tables as described
later in this section.
The server needs sufficient memory to maintain all
MEMORY tables that are in use at the same
time.
Memory used by a MEMORY table is not
reclaimed if you delete individual rows from the table. Memory
is only reclaimed when the entire table is deleted. Memory that
was previously used for rows that have been deleted will be
re-used for new rows only within the same table. To free up the
memory used by rows that have been deleted you should use
ALTER TABLE ENGINE=MEMORY to force a table
rebuild.
To free all the memory used by a MEMORY table
when you no longer require its contents, you should execute
DELETE or
TRUNCATE TABLE , or remove the
table altogether using DROP
TABLE .
If you want to populate a MEMORY table when
the MySQL server starts, you can use the
--init-file option. For example,
you can put statements such as
INSERT INTO ...
SELECT or
LOAD DATA
INFILE into this file to load the table from a
persistent data source. See Section 5.1.2, “Server Command Options”,
and Section 12.2.6, “LOAD DATA INFILE
Syntax”.
If you are using replication, the master server's
MEMORY tables become empty when it is shut
down and restarted. However, a slave is not aware that these
tables have become empty, so it returns out-of-date content if
you select data from them. When a MEMORY
table is used on the master for the first time since the master
was started, a DELETE statement
is written to the master's binary log automatically, thus
synchronizing the slave to the master again. Note that even with
this strategy, the slave still has outdated data in the table
during the interval between the master's restart and its first
use of the table. However, if you use the
--init-file option to populate
the MEMORY table on the master at startup, it
ensures that this time interval is zero.
The memory needed for one row in a MEMORY
table is calculated using the following expression:
SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) ? 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) ? 2)
+ ALIGN(length_of_row +1, sizeof(char*))
ALIGN() represents a round-up factor to cause
the row length to be an exact multiple of the
char pointer size.
sizeof(char*) is 4 on 32-bit machines and 8
on 64-bit machines.
As mentioned earlier, the
max_heap_table_size system variable
sets the limit on the maximum size of MEMORY
tables. To control the maximum size for individual tables, set the
session value of this variable before creating each table. (Do not
change the global
max_heap_table_size value unless
you intend the value to be used for MEMORY tables
created by all clients.) The following example creates two
MEMORY tables, with a maximum size of 1MB and
2MB, respectively:
mysql> SET max_heap_table_size = 1024*1024;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.01 sec)
mysql> SET max_heap_table_size = 1024*1024*2;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t2 (id INT, UNIQUE(id)) ENGINE = MEMORY;
Query OK, 0 rows affected (0.00 sec)
Both tables will revert to the server's global
max_heap_table_size value if the
server restarts.
You can also specify a MAX_ROWS table option in
CREATE TABLE statements for
MEMORY tables to provide a hint about the number
of rows you plan to store in them. This does not allow the table to
grow beyond the max_heap_table_size
value, which still acts as a constraint on maximum table size. For
maximum flexibility in being able to use
MAX_ROWS , set
max_heap_table_size at least as
high as the value to which you want each MEMORY
table to be able to grow.
Additional Resources
13.10. The EXAMPLE Storage Engine
The EXAMPLE storage engine is a stub engine that
does nothing. Its purpose is to serve as an example in the MySQL
source code that illustrates how to begin writing new storage
engines. As such, it is primarily of interest to developers.
To enable the EXAMPLE storage engine if you build
MySQL from source, invoke configure with the
--with-example-storage-engine option.
To examine the source for the EXAMPLE engine,
look in the storage/example directory of a
MySQL source distribution.
When you create an EXAMPLE table, the server
creates a table format file in the database directory. The file
begins with the table name and has an .frm
extension. No other files are created. No data can be stored into
the table. Retrievals return an empty result.
mysql> CREATE TABLE test (i INT) ENGINE = EXAMPLE;
Query OK, 0 rows affected (0.78 sec)
mysql> INSERT INTO test VALUES(1),(2),(3);
ERROR 1031 (HY000): Table storage engine for 'test' doesn't »
have this option
mysql> SELECT * FROM test;
Empty set (0.31 sec)
The EXAMPLE storage engine does not support
indexing.
13.11. The FEDERATED Storage Engine
The FEDERATED storage engine enables data to be
accessed from a remote MySQL database on a local server without
using replication or cluster technology. When using a
FEDERATED table, queries on the local server are
automatically executed on the remote (federated) tables. No data is
stored on the local tables.
To include the FEDERATED storage engine if you
build MySQL from source, invoke configure with
the --with-federated-storage-engine option.
Beginning with MySQL 5.1.26, the FEDERATED
storage engine is not enabled by default in the running server; to
enable FEDERATED , you must start the MySQL server
binary using the --federated option.
To examine the source for the FEDERATED engine,
look in the storage/federated directory of a
MySQL source distribution.
MySQL Enterprise
MySQL Enterprise subscribers will find MySQL Knowledge Base
articles about the FEDERATED storage engine at
FEDERATED Storage Engine. Access to the Knowledge Base
collection of articles is one of the advantages of subscribing to
MySQL Enterprise. For more information, see
http://www.mysql.com/products/enterprise/advisors.html.
13.11.1. FEDERATED Storage Engine Overview
When you create a table using one of the standard storage engines
(such as MyISAM , CSV or
InnoDB ), the table consists of the table
definition and the associated data. When you create a
FEDERATED table, the table definition is the
same, but the physical storage of the data is handled on a remote
server.
A FEDERATED table consists of two elements:
A remote server with a database table,
which in turn consists of the table definition (stored in the
.frm file) and the associated table. The
table type of the remote table may be any type supported by
the remote mysqld server, including
MyISAM or InnoDB .
A local server with a database table,
where the table definition matches that of the corresponding
table on the remote server. The table definition is stored
within the .frm file. However, there is
no data file on the local server. Instead, the table
definition includes a connection string that points to the
remote table.
When executing queries and statements on a
FEDERATED table on the local server, the
operations that would normally insert, update or delete
information from a local data file are instead sent to the remote
server for execution, where they update the data file on the
remote server or return matching rows from the remote server.
The basic structure of a FEDERATED table setup
is shown in Figure 13.2, “FEDERATED Table Structure”.
When a client issues an SQL statement that refers to a
FEDERATED table, the flow of information
between the local server (where the SQL statement is executed) and
the remote server (where the data is physically stored) is as
follows:
The storage engine looks through each column that the
FEDERATED table has and constructs an
appropriate SQL statement that refers to the remote table.
The statement is sent to the remote server using the MySQL
client API.
The remote server processes the statement and the local server
retrieves any result that the statement produces (an
affected-rows count or a result set).
If the statement produces a result set, each column is
converted to internal storage engine format that the
FEDERATED engine expects and can use to
display the result to the client that issued the original
statement.
The local server communicates with the remote server using MySQL
client C API functions. It invokes
mysql_real_query() to send the
statement. To read a result set, it uses
mysql_store_result() and fetches
rows one at a time using
mysql_fetch_row() .
13.11.2. How to Create FEDERATED Tables
To create a FEDERATED table you should follow
these steps:
Create the table on the remote server. Alternatively, make a
note of the table definition of an existing table, perhaps
using the SHOW CREATE TABLE
statement.
Create the table on the local server with an identical table
definition, but adding the connection information that links
the local table to the remote table.
For example, you could create the following table on the remote
server:
CREATE TABLE test_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;
To create the local table that will be federated to the remote
table, there are two options available. You can either create the
local table and specify the connection string (containing the
server name, login, password) to be used to connect to the remote
table using the CONNECTION , or you can use an
existing connection that you have previously created using the
CREATE SERVER statement.
Important
When you create the local table it must
have an identical field definition to the remote table.
Note
You can improve the performance of a
FEDERATED table by adding indexes to the
table on the host, even though the tables will not actually be
created locally. The optimization will occur because the query
sent to the remote server will include the contents of the
WHERE clause will be sent to the remote
server and executed locally. This reduces the network traffic
that would otherwise request the entire table from the server
for local processing.
13.11.2.1. Creating a FEDERATED Table Using
CONNECTION
To use the first method, you must specify the
CONNECTION string after the engine type in a
CREATE TABLE statement. For
example:
CREATE TABLE federated_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';
Note
CONNECTION replaces the
COMMENT used in some previous versions of
MySQL.
The CONNECTION string contains the
information required to connect to the remote server containing
the table that will be used to physically store the data. The
connection string specifies the server name, login credentials,
port number and database/table information. In the example, the
remote table is on the server remote_host ,
using port 9306. The name and port number should match the host
name (or IP address) and port number of the remote MySQL server
instance you want to use as your remote table.
The format the connection string is as follows:
scheme ://user_name [:password ]@host_name [:port_num ]/db_name /tbl_name
Where:
scheme — is a recognized
connection protocol. Only mysql is
supported as the scheme value at
this point.
user_name — the user name
for the connection. This user must have been created on the
remote server, and must have suitable privileges to perform
the required actions (SELECT ,
INSERT ,
UPDATE , and so forth) on the
remote table.
password — (optional) the
corresponding password for
user_name .
host_name — the host name
or IP address of the remote server.
port_num — (optional) the
port number for the remote server. The default is 3306.
db_name — the name of the
database holding the remote table.
tbl_name — the name of the
remote table. The name of the local and the remote table do
not have to match.
Sample connection strings:
CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'
13.11.2.2. Creating a FEDERATED Table Using
CREATE SERVER
If you are creating a number of FEDERATED
tables on the same server, or if you want to simplify the
process of creating FEDERATED tables, you can
use the CREATE SERVER statement
to define the server connection parameters, just as you would
with the CONNECTION string.
The format of the CREATE SERVER
statement is:
CREATE SERVER
server_name
FOREIGN DATA WRAPPER wrapper_name
OPTIONS (option [, option ] ...)
The server_name is used in the
connection string when creating a new
FEDERATED table.
For example, to create a server connection identical to the
CONNECTION string:
CONNECTION='mysql://fed_user@remote_host:9306/federated/test_table';
You would use the following statement:
CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
OPTIONS (USER 'fed_user', HOST 'remote_host', PORT 9306, DATABASE 'federated');
To create a FEDERATED table that uses this
connection, you still use the CONNECTION
keyword, but specify the name you used in the
CREATE SERVER statement.
CREATE TABLE test_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='fedlink/test_table';
The connection name in this example contains the name of the
connection (fedlink ) and the name of the
table (test_table ) to link to, separated by a
slash. If you specify only the connection name without a table
name, the table name of the local table is used instead.
For more information on CREATE
SERVER , see Section 12.1.16, “CREATE SERVER Syntax”.
The CREATE SERVER statement
accepts the same arguments as the CONNECTION
string. The CREATE SERVER
statement updates the rows in the
mysql.servers table. See the following table
for information on the correspondence between parameters in a
connection string, options in the CREATE
SERVER statement, and the columns in the
mysql.servers table. For reference, the
format of the CONNECTION string is as
follows:
scheme ://user_name [:password ]@host_name [:port_num ]/db_name /tbl_name
13.11.3. FEDERATED Storage Engine Notes and Tips
You should be aware of the following points when using the
FEDERATED storage engine:
The following items indicate features that the
FEDERATED storage engine does and does not
support:
The remote server must be a MySQL server. Support by
FEDERATED for other database engines may be
added in the future.
The remote table that a FEDERATED table
points to must exist before you try to
access the table through the FEDERATED
table.
It is possible for one FEDERATED table to
point to another, but you must be careful not to create a
loop.
A FEDERATED table does not support indexes
per se. Because access to the table is handled remotely, it is
the remote table that supports the indexes. Care should be
taken when creating a FEDERATED table since
the index definition from an equivalent
MyISAM or other table may not be supported.
For example, creating a FEDERATED table
with an index prefix on
VARCHAR ,
TEXT or
BLOB columns will fail. The
following definition in MyISAM is valid:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=MYISAM;
The key prefix in this example is incompatible with the
FEDERATED engine, and the equivalent
statement will fail:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED
CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';
If possible, you should try to separate the column and index
definition when creating tables on both the remote server and
the local server to avoid these index issues.
Internally, the implementation uses
SELECT ,
INSERT ,
UPDATE , and
DELETE , but not
HANDLER .
The FEDERATED storage engine supports
SELECT ,
INSERT ,
UPDATE ,
DELETE ,
TRUNCATE TABLE , and indexes. It
does not support ALTER TABLE ,
or any Data Definition Language statements that directly
affect the structure of the table, other than
DROP TABLE . The current
implementation does not use prepared statements.
FEDERATED accepts
INSERT
... ON DUPLICATE KEY UPDATE statements, but if a
duplicate-key violation occurs, the statement fails with an
error.
Performance on a FEDERATED table when
performing bulk inserts (for example, on a
INSERT INTO ...
SELECT ... statement) is slower than with other
table types because each selected row is treated as an
individual INSERT statement on
the FEDERATED table.
Transactions are not supported.
Before MySQL 5.1.21, for a multiple-row insert into a
FEDERATED table that refers to a remote
transactional table, if the insert failed for a row due to
constraint failure, the remote table would contain a partial
commit (the rows preceding the failed one) instead of rolling
back the statement completely. This occurred because the rows
were treated as individual inserts.
As of MySQL 5.1.21, FEDERATED performs
bulk-insert handling such that multiple rows are sent to the
remote table in a batch. This provides a performance
improvement and enables the remote table to perform
improvement. Also, if the remote table is transactional, it
enables the remote storage engine to perform statement
rollback properly should an error occur. This capability has
the following limitations:
The size of the insert cannot exceed the maximum packet
size between servers. If the insert exceeds this size, it
is broken into multiple packets and the rollback problem
can occur.
Bulk-insert handling does not occur for
INSERT
... ON DUPLICATE KEY UPDATE .
There is no way for the FEDERATED engine to
know if the remote table has changed. The reason for this is
that this table must work like a data file that would never be
written to by anything other than the database system. The
integrity of the data in the local table could be breached if
there was any change to the remote database.
When using a CONNECTION string, you cannot
use an '@' character in the password. You can get round this
limitation by using the CREATE
SERVER statement to create a server connection.
The insert_id and
timestamp options are not
propagated to the data provider.
Any DROP TABLE statement issued
against a FEDERATED table drops only the
local table, not the remote table.
FEDERATED tables do not work with the query
cache.
User-defined partitioning is not supported for
FEDERATED tables. Beginning with MySQL
5.1.15, it is no longer possible to create such tables at all.
Some of these limitations may be lifted in future versions of the
FEDERATED handler.
13.11.4. FEDERATED Storage Engine Resources
The following additional resources are available for the
FEDERATED storage engine:
13.12. The ARCHIVE Storage Engine
The ARCHIVE storage engine is used for storing
large amounts of data without indexes in a very small footprint.
Table 13.14. ARCHIVE Storage Engine
Features Storage limits | None | Transactions | No | Locking granularity | Row | MVCC | No | Geospatial datatype support | Yes | Geospatial indexing support | No | B-tree indexes | No | Hash indexes | No | Full-text search indexes | No | Clustered indexes | No | Data caches | No | Index caches | No | Compressed data | Yes | Encrypted data[a] | Yes | Cluster database support | No | Replication support[b] | Yes | Foreign key support | No | Backup / point-in-time recovery[c] | Yes | Query cache support | Yes | Update statistics for data dictionary | Yes | | |
The ARCHIVE storage engine is included in MySQL
binary distributions. To enable this storage engine if you build
MySQL from source, invoke configure with the
--with-archive-storage-engine option.
To examine the source for the ARCHIVE engine,
look in the storage/archive directory of a
MySQL source distribution.
You can check whether the ARCHIVE storage engine
is available with the SHOW ENGINES
statement.
When you create an ARCHIVE table, the server
creates a table format file in the database directory. The file
begins with the table name and has an .frm
extension. The storage engine creates other files, all having names
beginning with the table name. The data file has an extension of
.ARZ . (Prior to MySQL 5.1.15, a metadata file
with an extension of .ARM is created as well.)
An .ARN file may appear during optimization
operations.
The ARCHIVE engine supports
INSERT and
SELECT , but not
DELETE ,
REPLACE , or
UPDATE . It does support
ORDER BY operations,
BLOB columns, and basically all but
spatial data types (see Section 11.13.4.1, “MySQL Spatial Data Types”).
The ARCHIVE engine uses row-level locking.
As of MySQL 5.1.6, the ARCHIVE engine supports
the AUTO_INCREMENT column attribute. The
AUTO_INCREMENT column can have either a unique or
nonunique index. Attempting to create an index on any other column
results in an error. The ARCHIVE engine also
supports the AUTO_INCREMENT table option in
CREATE TABLE and
ALTER TABLE statements to specify the
initial sequence value for a new table or reset the sequence value
for an existing table, respectively.
As of MySQL 5.1.6, the ARCHIVE engine ignores
BLOB columns if they are not
requested and scans past them while reading. Formerly, the following
two statements had the same cost, but as of 5.1.6, the second is
much more efficient than the first:
SELECT a, b, blob_col FROM archive_table;
SELECT a, b FROM archive_table;
Storage: Rows are compressed as
they are inserted. The ARCHIVE engine uses
zlib lossless data compression (see
http://www.zlib.net/). You can use
OPTIMIZE TABLE to analyze the table
and pack it into a smaller format (for a reason to use
OPTIMIZE TABLE , see later in this
section). The engine also supports CHECK
TABLE . There are several types of insertions that are
used:
An INSERT statement just pushes
rows into a compression buffer, and that buffer flushes as
necessary. The insertion into the buffer is protected by a lock.
A SELECT forces a flush to occur,
unless the only insertions that have come in were
INSERT DELAYED (those flush as
necessary). See Section 12.2.5.2, “INSERT DELAYED Syntax”.
A bulk insert is visible only after it completes, unless other
inserts occur at the same time, in which case it can be seen
partially. A SELECT never causes
a flush of a bulk insert unless a normal insert occurs while it
is loading.
Retrieval: On retrieval, rows are
uncompressed on demand; there is no row cache. A
SELECT operation performs a complete
table scan: When a SELECT occurs, it
finds out how many rows are currently available and reads that
number of rows. SELECT is performed
as a consistent read. Note that lots of
SELECT statements during insertion
can deteriorate the compression, unless only bulk or delayed inserts
are used. To achieve better compression, you can use
OPTIMIZE TABLE or
REPAIR TABLE . The number of rows in
ARCHIVE tables reported by
SHOW TABLE STATUS is always accurate.
See Section 12.5.2.5, “OPTIMIZE TABLE Syntax”,
Section 12.5.2.6, “REPAIR TABLE Syntax”, and
Section 12.5.5.38, “SHOW TABLE STATUS Syntax”.
Additional Resources
13.13. The CSV Storage Engine
The CSV storage engine stores data in text files
using comma-separated values format.
To enable the CSV storage engine if you build
MySQL from source, invoke configure with the
--with-csv-storage-engine option.
To examine the source for the CSV engine, look in
the storage/csv directory of a MySQL source
distribution.
When you create a CSV table, the server creates a
table format file in the database directory. The file begins with
the table name and has an .frm extension. The
storage engine also creates a data file. Its name begins with the
table name and has a .CSV extension. The data
file is a plain text file. When you store data into the table, the
storage engine saves it into the data file in comma-separated values
format.
mysql> CREATE TABLE test (i INT NOT NULL, c CHAR(10) NOT NULL)
-> ENGINE = CSV;
Query OK, 0 rows affected (0.12 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
+------+------------+
| i | c |
+------+------------+
| 1 | record one |
| 2 | record two |
+------+------------+
2 rows in set (0.00 sec)
Starting with MySQL 5.1.9, creating a CSV table also creates a
corresponding Metafile that stores the state of the table and the
number of rows that exist in the table. The name of this file is the
same as the name of the table with the extension
CSM .
If you examine the test.CSV file in the
database directory created by executing the preceding statements,
its contents should look like this:
"1","record one"
"2","record two"
This format can be read, and even written, by spreadsheet
applications such as Microsoft Excel or StarOffice Calc.
13.13.1. Repairing and Checking CSV Tables
Functionality introduced in version
5.1.9
The CSV storage engines supports the CHECK and
REPAIR commands to verify and if possible
repair a damaged CSV table.
When running the CHECK command, the CSV file
will be checked for validity by looking for the correct field
separators, escaped fields (matching quotes and/or missing
quotes), the correct number of fields compared to the table
definition and the existence of a corresponding CSV metafile. The
first invalid row discovered will report an error. Checking a
valid table produces output like that shown below:
mysql> check table csvtest;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.csvtest | check | status | OK |
+--------------+-------+----------+----------+
1 row in set (0.00 sec)
A check on a corrupted table returns a fault:
mysql> check table csvtest;
+--------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.csvtest | check | error | Corrupt |
+--------------+-------+----------+----------+
1 row in set (0.01 sec)
If the check fails, the table is marked as crashed (corrupt). Once
a table has been marked as corrupt, it is automatically repaired
when you next run CHECK or execute a
SELECT statement. The corresponding
corrupt status and new status will be displayed when running
CHECK :
mysql> check table csvtest;
+--------------+-------+----------+----------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+----------------------------+
| test.csvtest | check | warning | Table is marked as crashed |
| test.csvtest | check | status | OK |
+--------------+-------+----------+----------------------------+
2 rows in set (0.08 sec)
To repair a table you can use REPAIR , this
copies as many valid rows from the existing CSV data as possible,
and then replaces the existing CSV file with the recovered rows.
Any rows beyond the corrupted data are lost.
mysql> repair table csvtest;
+--------------+--------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| test.csvtest | repair | status | OK |
+--------------+--------+----------+----------+
1 row in set (0.02 sec) Warning
Note that during repair, only the rows from the CSV file up to
the first damaged row are copied to the new table. All other
rows from the first damaged row to the end of the table are
removed, even valid rows.
Important
The CSV storage engine does not support
indexing.
Partitioning is not supported for tables using the
CSV storage engine. Beginning with MySQL
5.1.12, it is no longer possible to create partitioned
CSV tables. (See Bug#19307)
Beginning with MySQL 5.1.23, tables using the
CSV storage engine can no longer be created
with NULL columns. However, for backward
compatibility, you can continue to use such tables that were
created in previous MySQL releases. (Bug#32050)
13.14. The BLACKHOLE Storage Engine
The BLACKHOLE storage engine acts as a
“black hole” that accepts data but throws it away and
does not store it. Retrievals always return an empty result:
mysql> CREATE TABLE test(i INT, c CHAR(10)) ENGINE = BLACKHOLE;
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO test VALUES(1,'record one'),(2,'record two');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM test;
Empty set (0.00 sec)
To enable the BLACKHOLE storage engine if you
build MySQL from source, invoke configure with
the --with-blackhole-storage-engine option.
To examine the source for the BLACKHOLE engine,
look in the sql directory of a MySQL source
distribution.
When you create a BLACKHOLE table, the server
creates a table format file in the database directory. The file
begins with the table name and has an .frm
extension. There are no other files associated with the table.
The BLACKHOLE storage engine supports all kinds
of indexes. That is, you can include index declarations in the table
definition.
You can check whether the BLACKHOLE storage
engine is available with the SHOW
ENGINES statement.
Inserts into a BLACKHOLE table do not store any
data, but if the binary log is enabled, the SQL statements are
logged (and replicated to slave servers). This can be useful as a
repeater or filter mechanism. Suppose that your application requires
slave-side filtering rules, but transferring all binary log data to
the slave first results in too much traffic. In such a case, it is
possible to set up on the master host a “dummy” slave
process whose default storage engine is
BLACKHOLE , depicted as follows:
The master writes to its binary log. The “dummy”
mysqld process acts as a slave, applying the
desired combination of replicate-do-* and
replicate-ignore-* rules, and writes a new,
filtered binary log of its own. (See
Section 16.1.3, “Replication and Binary Logging Options and Variables”.) This filtered log is
provided to the slave.
The dummy process does not actually store any data, so there is
little processing overhead incurred by running the additional
mysqld process on the replication master host.
This type of setup can be repeated with additional replication
slaves.
INSERT triggers for
BLACKHOLE tables work as expected. However,
because the BLACKHOLE table does not actually
store any data, UPDATE and
DELETE triggers are not activated:
The FOR EACH ROW clause in the trigger definition
does not apply because there are no rows.
Other possible uses for the BLACKHOLE storage
engine include:
Verification of dump file syntax.
Measurement of the overhead from binary logging, by comparing
performance using BLACKHOLE with and without
binary logging enabled.
BLACKHOLE is essentially a
“no-op” storage engine, so it could be used for
finding performance bottlenecks not related to the storage
engine itself.
The BLACKHOLE storage engine does not support
INSERT DELAYED ,
LOCK TABLES , or
UNLOCK TABLES
statements prior to MySQL 5.1.19.
As of MySQL 5.1.4, the BLACKHOLE engine is
transaction-aware, in the sense that committed transactions are
written to the binary log and rolled-back transactions are not.
Column Filtering
When using row-based replication,
(binlog_format=ROW ), a slave where the last
columns are missing from a table is supported, as described in the
section Section 16.3.1.5, “Replication with Differing Table Definitions on Master and Slave”.
This filtering works on the slave side, that is, the columns are
copied to the slave before they are filtered out. There are at least
two cases where it is not desirable to copy the columns to the
slave:
If the data is confidential, so the slave server should not have
access to it.
If the master has many slaves, filtering before sending to the
slaves may reduce network traffic.
Master column filtering can be achieved using the
BLACKHOLE engine. This is carried out in a way
similar to how master table filtering is achieved - by using the
BLACKHOLE engine and the option
--replicate-[do|ignore]-table .
The setup for the master is:
CREATE TABLE t1 (public_col_1, ..., public_col_N,
secret_col_1, ..., secret_col_M) ENGINE=MyISAM;
The setup for the trusted slave is:
CREATE TABLE t1 (public_col_1, ..., public_col_N) ENGINE=BLACKHOLE;
The setup for the untrusted slave is:
CREATE TABLE t1 (public_col_1, ..., public_col_N) ENGINE=MyISAM;
|
|