Table of Contents
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:
The original storage engine was ISAM
, which
managed nontransactional tables. This engine has been replaced
by MyISAM
and should no longer be used. It is
deprecated in MySQL 4.1, and is removed in subsequent MySQL
release series.
In MySQL 3.23.0, the MyISAM
and
HEAP
storage engines were introduced.
MyISAM
is an improved replacement for
ISAM
. The HEAP
storage
engine provides in-memory tables. The MERGE
storage engine was added in MySQL 3.23.25. It enables a
collection of identical MyISAM
tables to be
handled as a single table. All three of these storage engines
handle nontransactional tables, and all are included in MySQL by
default. Note that the HEAP
storage engine
has been renamed the MEMORY
engine.
The InnoDB
and BDB
storage
engines that handle transaction-safe tables were introduced in
later versions of MySQL 3.23. Both are available in source
distributions as of MySQL 3.23.34a. BDB
is
included in MySQL-Max binary distributions on those operating
systems that support it. InnoDB
also is
included in MySQL-Max binary distributions for MySQL 3.23.
Beginning with MySQL 4.0, InnoDB
is included
by default in all MySQL binary distributions. In source
distributions, you can enable or disable either engine by
configuring MySQL as you like.
The EXAMPLE
storage engine was added in MySQL
4.1.3. It is a “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.
NDBCLUSTER
is the storage engine
used by MySQL Cluster to implement tables that are partitioned
over many computers. It is available in source code
distributions as of MySQL 4.1.2 and binary distributions as of
MySQL-Max 4.1.3.
MySQL Cluster is covered in a separate chapter of this Manual. See Chapter 15, MySQL Cluster, for more information.
The ARCHIVE
storage engine was added in MySQL
4.1.3. It is used for storing large amounts of data without
indexes in a very small footprint.
The CSV
storage engine was added in MySQL
4.1.4. This engine stores data in text files using
comma-separated values format.
The BLACKHOLE
storage engine was added in
MySQL 4.1.11. This engine accepts but does not store data and
retrievals always return an empty set.
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 available and currently
set as the default storage engine.
mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: HEAP
Support: YES
Comment: Alias for MEMORY
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 4. row ***************************
Engine: MERGE
Support: YES
Comment: Collection of identical MyISAM tables
*************************** 5. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Alias for MERGE
...
This chapter describes each of the MySQL storage engines except for
NDBCLUSTER
, which is covered in
Chapter 15, MySQL Cluster.
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.
When you create a new table, you can specify which storage engine to
use by adding an ENGINE
or
TYPE
table option to the
CREATE TABLE
statement:
CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY;
ENGINE
is the preferred term, but cannot be used
before MySQL 4.0.18. TYPE
is available beginning
with MySQL 3.23.0, the first version of MySQL for which multiple
storage engines were available. TYPE
is supported
for backward compatibility but is deprecated.
If you omit the ENGINE
or TYPE
option, the default storage engine is used. Normally, this is
MyISAM
, but you can change it by using the
--default-storage-engine
or
--default-table-type
server startup
option, or by setting the default-storage-engine
or default-table-type
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
or
table_type
variable:
SET storage_engine=MYISAM; SET table_type=BDB;
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.3.4.6, “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; ALTER TABLE t TYPE = BDB;
See Section 12.1.5, “CREATE TABLE Syntax”, and Section 12.1.2, “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)
.
(Before MySQL, MyISAM
is always used for
unavailable storage engines.) type 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. In MySQL 4.1, a warning is generated when 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.
A database may contain tables of different types. That is, tables need not all be created with the same storage engine.
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.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.
Note that to use the InnoDB
storage engine in
MySQL 3.23, you must configure at least the
innodb_data_file_path
startup
option. In 4.0 and up, InnoDB
uses default
configuration values if you specify none. See
Section 13.2.3, “InnoDB Configuration”.
Nontransaction-safe tables have several advantages of their own, all of which occur because there is no transaction overhead:
Much faster
Lower disk space requirements
Less memory required to perform updates
MyISAM
is the default storage engine as of
MySQL 3.23. It is based on the ISAM
storage
engine but has many useful extensions.
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;
The older term TYPE
is supported as a synonym
for ENGINE
for backward compatibility, but
ENGINE
is the preferred term from MySQL 4.0.18
on and TYPE
is deprecated.
Normally, the ENGINE
or TYPE
option is unnecessary; MyISAM
is the default
storage engine unless the default has been changed. To ensure that
MyISAM
is used in situations where the default
might have been changed, specify the storage engine 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.2, “myisamchk — MyISAM Table-Maintenance Utility”, and
Section 4.6.4, “myisampack — Generate Compressed, Read-Only MyISAM Tables”.
The following characteristics of the MyISAM
storage engine are improvements over the older
ISAM
engine:
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 permit better index compression.
Large files (up to 63-bit file length) are supported on file systems and operating systems that support large files.
The maximum number of indexes per table is 64 (32 before MySQL 4.1.2). This can be changed by changing the source and recompiling. The maximum number of columns per index is 16.
The maximum key length is 1000 bytes (500 before MySQL 4.1.2). This can 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.
Index files are usually much smaller with
MyISAM
than with ISAM
.
This means that MyISAM
normally uses less
system resources than ISAM
, but needs more
CPU time when inserting data into a compressed index.
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/UPDATE
. This makes
AUTO_INCREMENT
columns faster (at least
10%). Values at the top of the sequence are not reused after
being deleted as they are with ISAM
. (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.6.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.5, “CREATE TABLE Syntax”.
NULL
values are permitted in indexed
columns. This takes 0-1 bytes per key.
As of MySQL 4.1, each character column can have a different character set.
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, not only for whole keys as in
ISAM
.
myisampack can pack
BLOB
and
VARCHAR
columns;
pack_isam cannot.
MyISAM
also supports the following features,
which MySQL will be able to use in the near future:
A forum dedicated to the MyISAM
storage
engine is available at
http://forums.mysql.com/list.php?21.
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”.
Set the mode for automatic recovery of crashed
MyISAM
tables.
Don't flush key buffers between writes for any
MyISAM
table.
If you do this, you should not access
MyISAM
tables from another program
(such as from another MySQL server or with
myisamchk) when the tables are in use.
Doing so risks index corruption. Using
--external-locking
does not
eliminate this risk.
The following system variables affect the behavior of
MyISAM
tables. For additional information,
see Section 5.1.3, “Server System Variables”.
The size of the tree cache used in bulk insert optimization.
This is a limit per thread!
myisam_max_extra_sort_file_size
Used to help MySQL to decide when to use the slow but safe key cache index creation method.
This parameter is given in megabytes before MySQL 4.0.3, and in bytes as of 4.0.3.
The maximum size of the temporary file that MySQL is
permitted to use while re-creating a
MyISAM
index (during
REPAIR TABLE
,
ALTER TABLE
, or
LOAD DATA
INFILE
). If the file size would be larger than
this value, the index is created using the key cache
instead, which is slower. This variable was added in MySQL
3.23.37.
The value is given in megabytes before 4.0.3 and in bytes thereafter.
Set the size of the buffer used when recovering tables.
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.
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
.
You should have a cron script that
automatically moves these files from the database directories to
backup media.
tbl_name-datetime
.BAK
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.
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.4, “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.5, “CREATE TABLE Syntax”, for information about
ROW_FORMAT
.
You can decompress (unpack) compressed MyISAM
tables using myisamchk
--unpack
; see
Section 4.6.2, “myisamchk — MyISAM Table-Maintenance Utility”, for more information.
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.
Static-format tables have these characteristics:
CHAR
and
BINARY
columns are
space-padded to the column width. This is also true for
NUMERIC
and
DECIMAL
columns.
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.
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.
Compressed storage format is a read-only format that is generated with the myisampack tool.
All MySQL distributions as of version 3.23.19 include
myisampack by default. (This version is
when MySQL was placed under the GPL.) For earlier versions,
myisampack was included only with licenses
or support agreements, but the server still can read tables
that were compressed with myisampack.
Compressed tables can be uncompressed with
myisamchk. (For the ISAM
storage engine, compressed tables can be created with
pack_isam and uncompressed with
isamchk.)
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.
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.
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.
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.4.2.3, “CHECK TABLE Syntax”,
Section 12.4.2.6, “REPAIR TABLE Syntax”, and
Section 4.6.2, “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
Section 18.4, “Porting to Other Systems”.
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:
A MyISAM
table is copied without first
issuing LOCK TABLES
and
FLUSH
TABLES
.
MySQL has crashed between an update and the final close. (Note that the table may still be okay, because MySQL always issues writes for everything between each statement.)
A table was modified by myisamchk --recover or myisamchk --update-state at the same time that it was in use by mysqld.
Multiple mysqld servers are using the
table and one server performed a
REPAIR TABLE
or
CHECK TABLE
on the table
while it was in use by another server. In this setup, it
is safe to use CHECK TABLE
,
although you might get the warning from other servers.
However, REPAIR TABLE
should be avoided because when one server replaces the
data file with a new one, this is not known to the other
servers.
In general, it is a bad idea to share a data directory among multiple servers. See Section 5.7, “Running Multiple MySQL Servers on the Same Machine”, for additional discussion.
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.4.5.10, “SHOW ENGINES Syntax”.
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.
Starting from MySQL 4.1.5, the improved Windows installer makes
InnoDB
the MySQL default storage engine on
Windows.
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/.
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.
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/.
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
Beginning with MySQL 4.0, InnoDB
is enabled by
default, so the following information applies only to MySQL 3.23.
InnoDB
tables are included in the MySQL source
distribution starting from 3.23.34a and are activated in the
MySQL-Max binaries of the 3.23 series. For Windows, the MySQL-Max
binaries are included in the standard distribution.
If you have downloaded a binary version of MySQL that includes
support for InnoDB
, simply follow the
instructions of the MySQL manual for installing a binary version
of MySQL. If you have MySQL 3.23 installed, the simplest way to
install MySQL-Max is to replace the executable
mysqld server with the corresponding executable
from the MySQL-Max distribution. MySQL and MySQL-Max differ only
in the server executable. See
Section 2.8, “Installing MySQL from Generic Binaries on Other Unix-Like Systems”, and
Section 5.2, “The mysqld-max Extended MySQL Server”.
To compile the MySQL source code with InnoDB
support, download MySQL 3.23.34a or newer from
http://www.mysql.com/ and configure MySQL with the
--with-innodb
option. See
Section 2.9, “Installing MySQL from Source”.
To use InnoDB
tables with MySQL 3.23, you must
specify configuration parameters in the
[mysqld]
section of the
my.cnf
option file. On Windows, you can use
my.ini
instead. If you do not configure
InnoDB
in the option file,
InnoDB
does not start. (From MySQL 4.0 on,
InnoDB
uses default parameters if you do not
specify any. However, to get best performance, it is still
recommended that you use parameters appropriate for your system,
as discussed in Section 13.2.3, “InnoDB Configuration”.)
In MySQL 3.23, you must specify at the minimum an
innodb_data_file_path
value to
configure the InnoDB
data files. For example,
to configure InnoDB
to use a single 500MB data
file, place the following setting in the
[mysqld]
section of your option file:
[mysqld] innodb_data_file_path=ibdata1:500M
InnoDB
creates the ibdata1
file in the MySQL data directory by default. To specify the
location explicitly, specify an
innodb_data_home_dir
setting. See
Section 13.2.3, “InnoDB Configuration”.
To enable InnoDB
tables in MySQL 3.23, see
Section 13.2.2, “InnoDB in MySQL 3.23”.
From MySQL 4.0 on, the InnoDB
storage engine is
enabled by default. If you do not want to use
InnoDB
tables, start the server with the
--skip-innodb
option to disable the InnoDB
storage engine. In
this case, the server will not start if the default storage engine
is set to InnoDB
. Use
--default-storage-engine
to set the
default to some other engine if necessary.
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 4.0 and
above create an auto-extending 10MB data file named
ibdata1
and two 5MB log files named
ib_logfile0
and
ib_logfile1
in the MySQL data directory. (In
MySQL 4.0.0 and 4.0.1, the data file is 64MB and not
auto-extending.) In MySQL 3.23, InnoDB
does not
start if you provide no configuration options. 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.
The examples shown here are representative. See
Section 13.2.4, “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 MySQL 4.0 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.
autoextend
is available starting from MySQL
3.23.50 and 4.0.2.
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.2.6, “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 permits 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
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
. The
example assumes the use of MySQL-Max 3.23.50 or later or MySQL
4.0.2 or later because it uses 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.
Also, the small archived InnoDB
log file
ib_arch_log_0000000000
that
InnoDB
creates automatically ends up in the
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 4.1\bin
, you can
start it like this:
C:\> "C:\Program Files\MySQL\MySQL Server 4.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.2.3.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
Section 2.3.4.14, “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
or
C:\WINNT
). 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 = /ibdata/ibdata1:2000M;/dr2/ibdata/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 # # innodb_log_arch_dir must be the same as innodb_log_group_home_dir # (starting from 4.0.6, you can omit it) innodb_log_arch_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.2.3.2, “Using Raw Devices for the Shared Tablespace”.
On 32-bit GNU/Linux x86, you must be careful not to set memory
usage too high. glibc
may permit 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 Oracle Corporation.) and in the worst case
also uses sort_buffer_size + read_buffer_size
additional memory.
In MySQL 4.1, by compiling MySQL yourself, you can use up to
64GB of physical memory in 32-bit Windows. See the description
for
innodb_buffer_pool_awe_mem_mb
in Section 13.2.4, “InnoDB Startup Options and System Variables”.
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
There is a known bug in versions prior to 4.1.8 that manifests
itself if you specify
innodb_file_per_table
in
my.cnf
. If you shut down
mysqld, then records may disappear from the
secondary indexes of a table. See Bug #7496 for more
information and workarounds. This is fixed in 4.1.9, but
another bug (Bug #8021) bit the Windows version in 4.1.9, and
in the Windows version of 4.1.9 you must put the line
innodb_flush_method=unbuffered
to your
my.cnf
or my.ini
to
get mysqld to work.
Starting from MySQL 4.1.1, 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.
If you need to downgrade to 4.0, you must make table dumps and
re-create the whole InnoDB
tablespace. If you
have not created new InnoDB
tables under
MySQL 4.1.1 or later, and need to downgrade quickly, you can
also do a direct downgrade to the MySQL 4.0.18 or later in the
4.0 series. Before doing the direct downgrade to 4.0.x, you have
to end all client connections to the mysqld
server that is to be downgraded, and let it run the purge and
insert buffer merge operations to completion, so that
SHOW INNODB STATUS
shows the main
thread in the state waiting for server
activity
. Then you can shut down
mysqld and start 4.0.18 or later in the 4.0
series.
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
file in the database directory where the table belongs. This is
similar to what the tbl_name
.ibdMyISAM
storage engine
does, but MyISAM
divides the table into a
data file and an
tbl_name
.MYD
index file. For tbl_name
.MYIInnoDB
, the data and the
indexes are stored together in the .ibd
file. The
file is still created as usual.
tbl_name
.frm
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.
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 TABLEdb1.tbl_name
TOdb2.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 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.
Starting from MySQL 3.23.41, 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. (Starting from MySQL
3.23.44, 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
permits
changes to be made.
On Windows, starting from 4.1.1, 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 enable 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
permit 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 permit access to root
.
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. The log directory also contains a small
file named ib_arch_log_0000000000
. That
file resulted from the database creation, after which
InnoDB
switched off log archiving. 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
Starting from MySQL 4.1.1, you can add the option
innodb_file_per_table
to
my.cnf
to make InnoDB
store each table to its own .ibd
file in
the same MySQL database directory where the
.frm
file is created. See
Section 13.2.3.1, “Using Per-Table Tablespaces”.
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.
This section describes the InnoDB
-related
command options and system variables. System variables that take a
numeric value can be specified as
--
on the command line or as
var_name
=value
in option files. Many of the system variables can be changed at
runtime (see Section 5.1.4.2, “Dynamic System Variables”). (Before
MySQL 4.0.2, system variable values should be specified using
var_name
=value
--set-variable
syntax.) For more information on
specifying options and system variables, see
Section 4.2.3, “Specifying Program Options”.
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.
Enables the InnoDB
storage engine, if the
server was compiled with InnoDB
support.
To disable InnoDB
, use
--skip-innodb
.
In this case, the server will not start if the default storage
engine is set to InnoDB
. Use
--default-storage-engine
to set
the default to some other engine if necessary.
Controls whether InnoDB
creates a file
named
innodb_status.
in the MySQL data directory. If enabled,
<pid>
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.
This option is available as of MySQL 4.0.21.
Disable the InnoDB
storage engine. See the
description of --innodb
.
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.
The increment size (in MB) for extending the size of an
auto-extending shared tablespace file when it becomes full.
The default value is 8. This variable does not affect the
per-table tablespace files that are created if you use
innodb_file_per_table=1
.
Those files are auto-extending regardless of the value of
innodb_autoextend_increment
.
The initial extensions are by small amounts, after which
extensions occur in increments of 4MB.
This variable is available starting from MySQL 4.0.24 and 4.1.5. As of MySQL 4.0.24 and 4.1.6, it can be changed at runtime as a global system variable.
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
innobase/os/os0proc.c
source file.
This variable is available as of MySQL 4.1.0. 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.
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.5.2, “The InnoDB Buffer Pool”
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 On some operating systems, files
must be less than 2GB. If you do not specify
innodb_data_file_path
, the
default behavior starting from 4.0 is to create a single 10MB
auto-extending data file named ibdata1
.
Starting from 3.23.44, you can set the file size larger 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.2.3, “InnoDB Configuration”.
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, in which case you can
use absolute file paths in
innodb_data_file_path
.
The InnoDB
shutdown mode. The default value
is 1 as of MySQL 3.23.50, 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.
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. This variable is available as
of MySQL 3.23.37.
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.2.3.1, “Using Per-Table Tablespaces”. This variable
is available as of MySQL 4.1.1.
There is a bug in versions <= 4.1.8 if you specify
innodb_file_per_table
in
my.cnf
! If you shut down
mysqld, records may disappear from the
secondary indexes of a table. See Bug #7496 for more
information and workarounds. This is fixed in 4.1.9, but
another bug (Bug #8021) bit the Windows version in 4.1.9,
and in the Windows version of 4.1.9, you must put the line
innodb_flush_method=unbuffered
in your
my.cnf
or my.ini
to get mysqld to work.
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 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 this variable is 1 (prior to MySQL 4.0.13, the default is 0).
A value of 1 is 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
,
sync_binlog=1
, and
innodb-safe-binlog
in your master server
my.cnf
file.
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.
Defines the method used to
flush data to the
InnoDB
data
files and log
files, which can affect I/O throughput. This variable
is only configurable on Unix and Linux systems. On Windows
systems, the flush method is always
async_unbuffered
and cannot be changed.
This variable is available as of MySQL 3.23.40.
The innodb_flush_method
options include:
fdatasync
: InnoDB
uses the fsync()
system call to flush
both the data and log files. fsync
is
the default setting. Starting from MySQL 3.23.41,
InnoDB
uses fsync()
instead of fdatasync()
.
O_DSYNC
: InnoDB
uses
O_SYNC
to open and flush the log files,
and fsync()
to flush the data files.
InnoDB
does not use
O_DSYNC
directly because there have
been problems with it on many varieties of Unix.
O_DIRECT
: InnoDB
uses O_DIRECT
(or
directio()
on Solaris) to open the data
files, and uses fsync()
to flush both
the data and log files. This option is available on some
GNU/Linux versions starting from MySQL 4.0.14.
How each settings affects performance depends on hardware
configuration and workload. Benchmark your particular
configuration to decide which setting to use, or whether to
keep the default setting. Examine the
Innodb_data_fsyncs
status
variable to see the overall number of
fsync()
calls for each setting. The mix of
read and write operations in your workload can affect how a
setting performs. For example, on a system with a hardware
RAID controller and battery-backed write cache,
O_DIRECT
can help to avoid double buffering
between the InnoDB
buffer pool and the
operating system's file system cache. On some systems where
InnoDB
data and log files are located on a
SAN, the default value or O_DSYNC
might be
faster for a read-heavy workload with mostly
SELECT
statements. Always test this
parameter with hardware and workload that reflect your
production environment.
The crash recovery mode. Possible values are from 0 to 6. The meanings of these values are described in Section 13.2.7.2, “Forcing InnoDB Recovery”.
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. This variable is
available starting from MySQL 3.23.44.
The timeout in seconds an InnoDB
transaction may wait for a lock before being rolled back. The
default is 50 seconds.
A lock wait for 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.2.9.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.3.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.2.9.5, “Avoiding the Phantom Problem Using Next-Key Locking”.
innodb_locks_unsafe_for_binlog
is available as of MySQL 4.1.4.
The directory where fully written log files would be archived
if we used log archiving. The value of this variable should
currently be set the same as
innodb_log_group_home_dir
.
Starting from MySQL 4.0.6, there is no need to set this
variable.
Whether to log InnoDB
archive files. This
variable is unused. Recovery from a backup is done by MySQL
using its own log files, so there is no need to archive
InnoDB
log files. The default for this
variable is 0.
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
enables 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.
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.
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.
The directory path to the InnoDB
log files.
It must have the same value as
innodb_log_arch_dir
. 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.
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. Available starting from 4.0.13 and 4.1.1.
This variable controls how to delay
INSERT
,
UPDATE
, and
DELETE
operations when the
purge operations are lagging (see
Section 13.2.10, “InnoDB Multi-Versioning”). The default value
of this variable is 0, meaning that there are no delays.
innodb_max_purge_lag
is
available as of MySQL 4.0.22 and 4.1.6.
The InnoDB
transaction system maintains a
list of transactions that have index records delete-marked 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 permissible to have 100MB of unpurged
InnoDB
table rows.
The lag value is displayed as the history list length in the
TRANSACTIONS
section of InnoDB Monitor
output. For example, if the output includes the following
lines, the lag value is 20:
------------ TRANSACTIONS ------------ Trx id counter 0 290328385 Purge done for trx's n:o < 0 290315608 undo n:o < 0 17 History list length 20
The number of identical copies of log groups to keep for the database. This should be set to 1.
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. This variable
is available as of MySQL 4.1.1.
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-safe-binlog
If this option is given, then after a crash recovery by
InnoDB
, mysqld truncates
the binary log after the last not-rolled-back transaction in
the log. The option also causes InnoDB
to
print an error if the binary log is smaller or shorter than it
should be. See Section 5.3.4, “The Binary Log”.
Starting from MySQL 4.0.20, and 4.1.2,
InnoDB
honors LOCK
TABLES
. 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
tries to keep the number of
operating system threads concurrently inside
InnoDB
less than or equal to the limit
given by this variable. The default value is 8. If you have
low performance and SHOW INNODB
STATUS
reveals many threads waiting for semaphores,
you may have thread thrashing and should try setting this
variable lower or higher. If you have a computer with many
processors and disks, you can try setting the value higher to
better utilize the resources of your computer. A recommended
value is 2 times the number of CPUs plus the number of disks.
A value of 500 or greater disables the concurrency checking.
This variable is available starting from MySQL 3.23.44 and
4.0.1.
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). This variable was
added in MySQL 4.1.3.
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 older term TYPE
is supported as a synonym
for ENGINE
for backward compatibility, but
ENGINE
is the preferred term and
TYPE
is deprecated.
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 Comment
section in
the output of SHOW TABLE STATUS
.
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.
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
and ROLLBACK
.
If you want to leave autocommit on, you can begin your
transactions within
START
TRANSACTION
and end them with
COMMIT
or
ROLLBACK
.
Before MySQL 4.0.11, you have to use the keyword
BEGIN
instead
of START
TRANSACTION
. The following example shows two
transactions. The first is committed and the second is rolled
back.
shell>mysql test
mysql>CREATE TABLE customer (a INT, b CHAR (20), INDEX (A))
->TYPE=InnoDB;
Query OK, 0 rows affected (0.00 sec) mysql>BEGIN;
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.
To convert a non-InnoDB
table to use
InnoDB
use ALTER
TABLE
:
ALTER TABLE t1 TYPE=InnoDB;
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 ... TYPE=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, starting from MySQL 3.23.52, 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
permits 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.2.7.2, “Forcing InnoDB Recovery”.
If you want all your (nonsystem) tables to be created as
InnoDB
tables, you can, starting from the
MySQL 3.23.43, add the line
default-table-type=innodb
to the
[mysqld]
section of your server option file.
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.
Beginning with MySQL 4.1.12, InnoDB
supports
the AUTO_INCREMENT =
table option in
N
ALTER TABLE
statements, to set
the initial counter value or alter the current counter value.
The same is true as of MySQL 4.1.14 for
CREATE TABLE
. The effect of this
option is canceled by a server restart, for reasons discussed
earlier in this section.
Starting from MySQL 3.23.44, InnoDB
features
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
, ...) REFERENCEStbl_name
(index_col_name
,...) [ON DELETEreference_option
] [ON UPDATEreference_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
versions older than MySQL 4.1.2, 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
permits 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
clause is given,
the symbol
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. ON DELETE
CASCADE
is supported starting from MySQL 3.23.50
and ON UPDATE CASCADE
is supported
starting from 4.0.8. 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.
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. ON DELETE SET
NULL
is available starting from MySQL 3.23.50 and
ON UPDATE SET NULL
is available starting
from 4.0.8.
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 will not be permitted to proceed if there is a related
foreign key value in the referenced table. Starting from
4.0.18 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) ) TYPE=INNODB; CREATE TABLE child (id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) TYPE=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)) TYPE=INNODB; CREATE TABLE customer (id INT NOT NULL, PRIMARY KEY (id)) TYPE=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)) TYPE=INNODB;
Starting from MySQL 3.23.50, InnoDB
enables
you to add a new foreign key constraint to a table by using
ALTER TABLE
:
ALTER TABLEtbl_name
ADD [CONSTRAINT [symbol
]] FOREIGN KEY [index_name
] (index_col_name
, ...) REFERENCEStbl_name
(index_col_name
,...) [ON DELETEreference_option
] [ON UPDATEreference_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.
Starting from MySQL 4.0.13, InnoDB
supports
the use of ALTER TABLE
to drop
foreign keys:
ALTER TABLEtbl_name
DROP FOREIGN KEYfk_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. (A
constraint name can be given as of MySQL 4.0.18.) Otherwise, the
fk_symbol
value is internally
generated by InnoDB
when the foreign key is
created. To find out the symbol 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 ) TYPE=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.
Starting from MySQL 3.23.50, the InnoDB
parser permits table and column identifiers in a
FOREIGN KEY ... REFERENCES ...
clause to be
quoted within backticks. (Alternatively, double quotation marks
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.
Before MySQL 3.23.50, ALTER TABLE
or CREATE INDEX
should not be
used in connection with tables that have foreign key constraints
or that are referenced in foreign key constraints: Any
ALTER TABLE
removes all foreign
key constraints defined for the table. You should not use
ALTER TABLE
with the referenced
table, either. Instead, use DROP
TABLE
and CREATE TABLE
to modify the schema. When MySQL does an
ALTER TABLE
it may internally use
RENAME TABLE
, and that confuses
the foreign key constraints that refer to the table. In MySQL, a
CREATE INDEX
statement is
processed as an ALTER TABLE
, so
the same considerations apply.
Starting from MySQL 3.23.50, InnoDB
returns
the foreign key definitions of a table 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 FROMdb_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 as of
MySQL 4.1.1. This avoids problems with tables having to be
reloaded in a particular order when the dump is reloaded. For
earlier versions, you can disable the variable manually within
mysql when loading the dump file like this:
mysql>SET foreign_key_checks = 0;
mysql>SOURCE
mysql>dump_file_name
;SET foreign_key_checks = 1;
This enables 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 permit 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.
foreign_key_checks
is available
starting from MySQL 3.23.52 and 4.0.3.
InnoDB
does not permit 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. Starting from MySQL
4.0.13, you can use SHOW INNODB
STATUS
to display a detailed explanation of the latest
InnoDB
foreign key error in the server.
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. Starting from MySQL 3.23.50,
InnoDB
does not check foreign key
constraints on those foreign key or referenced key values that
contain a NULL
column.
InnoDB
essentially implements the semantics
defined by MATCH SIMPLE
, which permit a
foreign key to be all or partially NULL
. In
that case, the (child table) row containing such a foreign key
is permitted to be inserted, and does not match any row in the
referenced (parent) table.
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 permit 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 from 4.0.13. A self-referential
ON DELETE CASCADE
has been possible since
ON DELETE
was implemented. Since 4.0.21,
cascading operations may not be nested more than 15 levels.
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
whole 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 using a foreign key.
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.2.7, “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.
There are minor limitations in InnoDB
replication:
LOAD TABLE FROM MASTER
does
not work for InnoDB
type tables. There
are workarounds: 1) dump the table on the master and import
the dump file into the slave, or 2) use ALTER TABLE
on
the master before setting up replication with tbl_name
TYPE=MyISAMLOAD
TABLE
, and then use tbl_name
FROM
MASTERALTER
TABLE
to alter the master table back to the
InnoDB
type afterward. However, this
should not be done for tables that have foreign key
definitions because the definitions will be lost.
Before MySQL 4.0.6, SLAVE STOP
did not
respect the boundary of a multiple-statement transaction. An
incomplete transaction would be rolled back, and the next
SLAVE START
would only execute the
remaining part of the half transaction. That would cause
replication to fail.
Before MySQL 4.0.6, a slave crash in the middle of a
multiple-statement transaction would cause the same problem
as SLAVE STOP
.
Before MySQL 4.0.11, replication of the SET
foreign_key_checks = 0
statement does not work
properly.
Most of these limitations can be eliminated by using more recent server versions for which the limitations do not apply.
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.3.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.
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.
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.
From MySQL 3.23.50 and 4.0.2, 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. Starting with MySQL 4.0.24 and 4.1.5,
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, 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.
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, “Establishing a 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.5, “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.
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.
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
statements or
tbl_name
InnoDB
background operations to crash or
assert, or even cause InnoDB
roll-forward
recovery to crash. In such cases, starting from MySQL 3.23.44,
you can use the
innodb_force_recovery
option to
force the InnoDB
storage engine to start up,
and you can also prevent 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
Before MySQL 4.0, use this syntax instead:
[mysqld] set-variable = innodb_force_recovery=4
innodb_force_recovery
is 0 by
default (normal startup without forced recovery) The permissible
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
jump over
corrupt index records and pages, which helps in dumping
tables.
tbl_name
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.
Starting from MySQL 3.23.53 and 4.0.4, you can
SELECT
from tables to dump them,
or DROP
or CREATE
a table
even if forced recovery is used. If you know that a certain
table is causing a crash in rollback, you can drop it. You can
use this also 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.
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.
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.2.7, “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.
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 permitted
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.2.13, “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
. As of MySQL
4.0.5, InnoDB
offers all four transaction
isolation levels described by the SQL standard:
READ UNCOMMITTED
,
READ COMMITTED
,
REPEATABLE READ
, and
SERIALIZABLE
. Before 4.0.5,
only REPEATABLE READ
and
SERIALIZABLE
were available.
Before MySQL 3.23.50, SET
TRANSACTION
had no effect on InnoDB
tables.
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.3.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.2.9.4, “InnoDB Record, Gap, and Next-Key Locks”.
InnoDB
implements standard row-level locking
where there are two types of locks:
A shared (S
) lock permits a
transaction to read a row.
An exclusive (X
) lock permits a
transaction to update or delete a row.
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 permits
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
):
Intention shared (IS
):
Transaction T
intends to set
S
locks on individual rows in
table t
.
Intention exclusive (IX
):
Transaction T
intends to set
X
locks on those rows.
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.
X | IX | S | IS | |
---|---|---|---|---|
X | Conflict | Conflict | Conflict | Conflict |
IX | Conflict | Compatible | Conflict | Compatible |
S | Conflict | Conflict | Compatible | Compatible |
IS | Conflict | Compatible | Compatible | Compatible |
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.
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.2.9.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.
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.
To implement reading and incrementing the counter, first perform
a locking read of the counter using FOR
UPDATE
, and then increment the counter. For example:
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.
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.
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.2.11.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.2.9.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. (This does not include
the case that the search condition includes only some columns of
a multiple-column unique index; in that case, gap locking does
occur.) 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.
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 enables you to “lock” the nonexistence of something in your table.
Gap locking can be disabled as discussed in Section 13.2.9.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.
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.2.9.4, “InnoDB Record, Gap, and Next-Key Locks”. The transaction
isolation level also can affect which locks are set; see
Section 12.3.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.2.9.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.
INSERT
... ON DUPLICATE KEY UPDATE
differs from a simple
INSERT
in that an exclusive
next-key lock rather than a shared lock is placed on the row
to be updated when a duplicate-key error occurs.
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 to be replaced.
INSERT INTO T SELECT ... FROM S WHERE ...
sets an exclusive index record without a gap lock on each
row inserted into T
. It does the search
on rows from S
as a consistent read (no
locks), but sets shared next-key locks on
S
if MySQL binary logging is turned on.
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.2.9, “The InnoDB Transaction Model and Locking”.
Before MySQL 3.23.50, SHOW TABLE
STATUS
applied to a table with an
AUTO_INCREMENT
column sets an exclusive
row-level lock to the high end of the
AUTO_INCREMENT
index. This means also
that SHOW TABLE STATUS
could
cause a deadlock of transactions, something that may
surprise users. Starting from MySQL 3.23.50,
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.
Beginning with MySQL 4.0.20 and 4.1.2,
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.2.9.8, “Deadlock Detection and Rollback”. See also Section 13.2.15, “Restrictions on InnoDB Tables”.
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.2.13, “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.3.3, “Statements That Cause an Implicit Commit”.
InnoDB
automatically detects transaction
deadlocks and rolls back a transaction or transactions to break
the deadlock. Starting from MySQL 4.0.5,
InnoDB
tries to pick small transactions to
roll back, the size of a transaction being determined by the
number of rows inserted, updated, or deleted. Prior to 4.0.5,
InnoDB
always rolled back the transaction
whose lock request was the last one to build a deadlock, that
is, a cycle in the “waits-for” graph of
transactions.
Beginning with MySQL 4.0.20 and 4.1.2, 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. Before
that, InnoDB
cannot detect deadlocks where a
table lock set by a MySQL LOCK
TABLES
statement is involved, or if a lock set by
another storage engine than InnoDB
is
involved. You have to 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.
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 INNODB STATUS
to
determine the cause of the latest deadlock. That can help
you to tune your application to avoid deadlocks. This
strategy can be used as of MySQL 3.23.52 and 4.0.3,
depending on your MySQL series. From 4.1.2 on, use
SHOW ENGINE
INNODB STATUS
.
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 that are short in duration 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 permit 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.
In applications that use autocommit =
1
and MySQL's LOCK
TABLES
statement, InnoDB
's
internal table locks that were present from 4.0.20 to 4.0.23
can cause deadlocks. Starting from 4.0.22, you can set
innodb_table_locks = 0
in
my.cnf
to fall back to the old behavior
and remove the problem. 4.0.24 does not set
InnoDB
table locks if
autocommit = 1
.
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 would carry 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
for the purge thread. Starting with MySQL 4.0.22 and 4.1.6, the
innodb_max_purge_lag
system
variable exists for exactly this purpose. See
Section 13.2.4, “InnoDB Startup Options and System Variables”, for more information.
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. It is also the reason
why DROP DATABASE
did not work for
InnoDB
type tables before MySQL 3.23.44.
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.
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.
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.
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.
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.2.7.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.2.10, “InnoDB Multi-Versioning”).
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.
Rows in InnoDB
tables 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
truncates trailing spaces
from VARCHAR
columns. Note
that MySQL may internally convert
CHAR
columns to
VARCHAR
. See
Section 12.1.5.2, “Silent Column Specification Changes”.
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.
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.
The data files that you define in the configuration file form
the InnoDB
tablespace. The files are
logically 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.2.10, “InnoDB Multi-Versioning”.)
To see information about the tablespace, use the Tablespace Monitor. See Section 13.2.14.2, “SHOW ENGINE INNODB STATUS and the InnoDB Monitors”.
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.
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
TYPE=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.
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 or a timeout in a lock wait causes
InnoDB
to roll back the whole transaction.
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 such implicit rollbacks, as well as during the explicit
ROLLBACK
SQL
statement, SHOW PROCESSLIST
displays "Rolling back" in the State
column for
the connection (starting from MySQL 4.1.8).
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.2.14.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.
To print the meaning of an operating system error number, use the perror program that comes with the MySQL distribution.
The following table provides a list of some common Linux system error codes. For a more complete list, see Linux source code.
1 (EPERM)
Operation not permitted
2 (ENOENT)
No such file or directory
3 (ESRCH)
No such process
4 (EINTR)
Interrupted system call
5 (EIO)
I/O error
6 (ENXIO)
No such device or address
7 (E2BIG)
Arg list too long
8 (ENOEXEC)
Exec format error
9 (EBADF)
Bad file number
10 (ECHILD)
No child processes
11 (EAGAIN)
Try again
12 (ENOMEM)
Out of memory
13 (EACCES)
Permission denied
14 (EFAULT)
Bad address
15 (ENOTBLK)
Block device required
16 (EBUSY)
Device or resource busy
17 (EEXIST)
File exists
18 (EXDEV)
Cross-device link
19 (ENODEV)
No such device
20 (ENOTDIR)
Not a directory
21 (EISDIR)
Is a directory
22 (EINVAL)
Invalid argument
23 (ENFILE)
File table overflow
24 (EMFILE)
Too many open files
25 (ENOTTY)
Inappropriate ioctl for device
26 (ETXTBSY)
Text file busy
27 (EFBIG)
File too large
28 (ENOSPC)
No space left on device
29 (ESPIPE)
Illegal seek
30 (EROFS)
Read-only file system
31 (EMLINK)
Too many links
The following table provides a list of some common Windows system error codes. For a complete list, see the Microsoft Web site.
1 (ERROR_INVALID_FUNCTION)
Incorrect function.
2 (ERROR_FILE_NOT_FOUND)
The system cannot find the file specified.
3 (ERROR_PATH_NOT_FOUND)
The system cannot find the path specified.
4 (ERROR_TOO_MANY_OPEN_FILES)
The system cannot open the file.
5 (ERROR_ACCESS_DENIED)
Access is denied.
6 (ERROR_INVALID_HANDLE)
The handle is invalid.
7 (ERROR_ARENA_TRASHED)
The storage control blocks were destroyed.
8 (ERROR_NOT_ENOUGH_MEMORY)
Not enough storage is available to process this command.
9 (ERROR_INVALID_BLOCK)
The storage control block address is invalid.
10 (ERROR_BAD_ENVIRONMENT)
The environment is incorrect.
11 (ERROR_BAD_FORMAT)
An attempt was made to load a program with an incorrect format.
12 (ERROR_INVALID_ACCESS)
The access code is invalid.
13 (ERROR_INVALID_DATA)
The data is invalid.
14 (ERROR_OUTOFMEMORY)
Not enough storage is available to complete this operation.
15 (ERROR_INVALID_DRIVE)
The system cannot find the drive specified.
16 (ERROR_CURRENT_DIRECTORY)
The directory cannot be removed.
17 (ERROR_NOT_SAME_DEVICE)
The system cannot move the file to a different disk drive.
18 (ERROR_NO_MORE_FILES)
There are no more files.
19 (ERROR_WRITE_PROTECT)
The media is write protected.
20 (ERROR_BAD_UNIT)
The system cannot find the device specified.
21 (ERROR_NOT_READY)
The device is not ready.
22 (ERROR_BAD_COMMAND)
The device does not recognize the command.
23 (ERROR_CRC)
Data error (cyclic redundancy check).
24 (ERROR_BAD_LENGTH)
The program issued a command but the command length is incorrect.
25 (ERROR_SEEK)
The drive cannot locate a specific area or track on the disk.
26 (ERROR_NOT_DOS_DISK)
The specified disk or diskette cannot be accessed.
27 (ERROR_SECTOR_NOT_FOUND)
The drive cannot find the sector requested.
28 (ERROR_OUT_OF_PAPER)
The printer is out of paper.
29 (ERROR_WRITE_FAULT)
The system cannot write to the specified device.
30 (ERROR_READ_FAULT)
The system cannot read from the specified device.
31 (ERROR_GEN_FAILURE)
A device attached to the system is not functioning.
32 (ERROR_SHARING_VIOLATION)
The process cannot access the file because it is being used by another process.
33 (ERROR_LOCK_VIOLATION)
The process cannot access the file because another process has locked a portion of the file.
34 (ERROR_WRONG_DISK)
The wrong diskette is in the drive. Insert %2 (Volume Serial Number: %3) into drive %1.
36 (ERROR_SHARING_BUFFER_EXCEEDED)
Too many files opened for sharing.
38 (ERROR_HANDLE_EOF)
Reached the end of the file.
39 (ERROR_HANDLE_DISK_FULL)
The disk is full.
87 (ERROR_INVALID_PARAMETER)
The parameter is incorrect. (If this error occurs on MySQL
4.1.9 on Windows and you have set
innodb_file_per_table
in a
server option file, this is Bug #8021, and a workaround is
to add the line
innodb_flush_method=unbuffered
to the
file as well.)
112 (ERROR_DISK_FULL)
The disk is full.
123 (ERROR_INVALID_NAME)
The file name, directory name, or volume label syntax is incorrect.
1450 (ERROR_NO_SYSTEM_RESOURCES)
Insufficient system resources exist to complete the requested service.
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.2.11, “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(
column always takes N
)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.
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.
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.5.2, “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.2.7.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
(Relevant from 3.23.39 up.) 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.
(Verified using MySQL 4.1, assumed for other MySQL versions,
given that this is a platform architecture issue.) 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.)
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. 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, starting from MySQL 3.23.52 and 4.0.3, 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, starting from MySQL 3.23.52 and 4.0.3, you can
speed up table imports by turning the foreign key checks off
for a while in 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 (available as of MySQL 4.0):
[mysqld] query_cache_type = 1 query_cache_size = 10M
In MySQL 4.0, the query cache works only with autocommit enabled. This restriction is removed in MySQL 4.1.1 and up.
Starting from MySQL 3.23.42, 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, starting from MySQL 4.1.2, output for the
standard InnoDB
Monitor is available on
demand using the
SHOW ENGINE INNODB
STATUS
SQL statement. (From MySQL 3.23.52 and 4.0.3
until 4.1.1, you can use the SHOW 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.2.9.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
. The Table Monitor
is available as of MySQL 3.23.44.
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) TYPE=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.)
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.3.1, “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.
Beginning with MySQL 4.0.19, InnoDB
sends
diagnostic output to stderr or files instead of stdout or
fixed-size memory buffers, to avoid potential buffer overflow
errors. As a side effect, the output of
SHOW INNODB STATUS
is written to
a status file in the MySQL data directory every fifteen seconds.
The name of the file is
innodb_status.
,
where pid
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. Beginning with MySQL 4.0.21, the
innodb_status.
file is created only if the configuration option
pid
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:
Mark Leith: InnoDB Table and Tablespace Monitors
MySQL Performance Blog: SHOW INNODB STATUS walk through
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.
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 INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
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
------------------------
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
History list length 20
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 using 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:
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.
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.2.9.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.2.11.3, “Insert Buffering”, and
Section 13.2.11.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.2.7.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.5.2, “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.
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.
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:04:38 INNODB TABLE MONITOR OUTPUT =========================================== -------------------------------------- TABLE: name SYS_FOREIGN, id 0 11, columns 8, indexes 3, appr.rows 1 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; FOR_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; N_COLS: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, id 0 11, fields 1/6, 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, 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, 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 8, indexes 1, appr.rows 1 COLUMNS: ID: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; POS: DATA_INT len 4 prec 0; FOR_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; REF_COL_NAME: DATA_VARCHAR DATA_ENGLISH len 0 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name ID_IND, id 0 14, fields 2/6, 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 11, indexes 2, appr.rows 238 COLUMNS: par_id: DATA_INT len 4 prec 0; child_id: DATA_INT len 4 prec 0; name: DATA_VARCHAR prtype 1 len 40 prec 0; birth: DATA_INT len 3 prec 0; weight: type 11 len 12 prec 0; misc_info: DATA_VARCHAR prtype 1 len 255 prec 0; last_update: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, id 0 17, fields 2/9, type 3 root page 52, appr.key vals 238, leaf pages 6, size pages 7 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, 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 5, indexes 1, appr.rows 0 COLUMNS: i: DATA_INT len 4 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name GEN_CLUST_INDEX, id 0 19, fields 0/4, type 1 root page 56, 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 7, indexes 2, appr.rows 223 COLUMNS: par_id: DATA_INT len 4 prec 0; fname: DATA_CHAR prtype 2 len 20 prec 0; lname: DATA_CHAR prtype 2 len 20 prec 0; DB_ROW_ID: DATA_SYS DATA_ROW_ID len 6 prec 0; DB_TRX_ID: DATA_SYS DATA_TRX_ID len 6 prec 0; DB_ROLL_PTR: DATA_SYS DATA_ROLL_PTR len 7 prec 0; INDEX: name PRIMARY, id 0 15, fields 1/5, type 3 root page 50, appr.key vals 223, 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, 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
format except for internal tables), its ID, the number of
columns and indexes, and an approximate row count.
db_name
/tbl_name
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_
:
These symbols indicate the data type. There may be
multiple
xxx
DATA_
symbols for a given column.
xxx
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.
prec
: The precision of the type.
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.2.11.1, “Clustered and Secondary Indexes”.
id
: The index ID.
fields
: The number of fields in the
index, as a value in
format:
m
/n
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.
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.
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.3.1, “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.2.14.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.2.14.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.
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 use
DROP DATABASE
in MySQL versions
before 3.23.44, or 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-...
. Starting from
MySQL 4.0.6, you can perform SQL statements also 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 quotation marks
if the file name contains “#
”.
Older MySQL versions did not permit accessing any table with a
name containing “#
”. The
solution in older MySQL versions is to use a special
InnoDB
mechanism available starting from
MySQL 3.23.48. When you have an orphaned table
#sql-id
inside the tablespace, you can
cause InnoDB
to rename it to
rsql-id_recover_innodb_tmp_table
with the
following statement:
CREATE TABLE `rsql-id_recover_innodb_tmp_table`(...) TYPE=InnoDB;
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.
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.
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 1024 bytes.
Index key prefixes can be up to 767 bytes (255 bytes before MySQL 4.1.2). See Section 12.1.4, “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.2.12.2, “File Space Management”.
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 do not support spatial data
types.
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.
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.3, “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.2.14.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.
Before MySQL 4.1.12, InnoDB
does not
support the AUTO_INCREMENT
table option for
setting the initial sequence value in an
ALTER TABLE
statement. Before
MySQL 4.1.14, the same is true for CREATE
TABLE
. To set the value with
InnoDB
, insert a dummy row with a value one
less and delete that dummy row, or insert the first row with
an explicit value specified.
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.2.5.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
does not
regenerate the table but instead deletes all rows, one by one.
tbl_name
Under some conditions, TRUNCATE
for an
tbl_name
InnoDB
table is mapped to DELETE
FROM
and does
not reset the tbl_name
AUTO_INCREMENT
counter. See
Section 12.1.10, “TRUNCATE TABLE Syntax”.
Before MySQL 4.0.14 or 4.1.0, if you tried to create a unique index on a prefix of a column you got an error:
CREATE TABLE T (A CHAR(20), B INT, UNIQUE (A(5))) TYPE = InnoDB;
If you created a nonunique index on a prefix of a column,
InnoDB
created an index over the whole
column. These restrictions were removed in MySQL 4.0.14.
Before MySQL 4.0.20 or 4.1.2, the MySQL
LOCK TABLES
operation does not
know about InnoDB
row-level locks set by
completed SQL statements. This means that you can get a table
lock on a table even if there still exist transactions by
other users who have row-level locks on the same table. Thus,
your operations on the table may have to wait if they collide
with these locks of other users. Also a deadlock is possible.
However, this does not endanger transaction integrity, because
the row-level locks set by InnoDB
always
take care of the integrity. Also, a table lock prevents other
transactions from acquiring more row-level locks (in a
conflicting lock mode) on the table.
Beginning with MySQL 4.0.20 and 4.1.2, 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 do not acquire InnoDB
table locks.
Beginning with MySQL 4.0.22 and 4.1.7, 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.
Before MySQL 3.23.52, replication always ran with autocommit enabled. Therefore consistent reads in the slave would also see partially processed transactions, and thus the read would not be really consistent in the slave. This restriction was removed in MySQL 3.23.52.
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.
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.
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.
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
4.1.19 this would cause a crash, since 4.1.19 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 and delaying changes until near the end of the
transaction. Applications should commit transactions before
doing time-consuming client-side operations.
The MERGE
storage engine was introduced in
MySQL 3.23.25. It is also known as the
MRG_MyISAM
engine.
A MERGE
table 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.4, “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
underlying MyISAM
tables that should be used as
one. (Originally, all used tables had to be in the same database
as the MERGE
table. This restriction has been
lifted as of MySQL 4.1.1.)
You can use SELECT
,
DELETE
,
UPDATE
, and (as of MySQL 4.0)
INSERT
on MERGE
tables. You must have SELECT
,
DELETE
, and
UPDATE
privileges on the
MyISAM
tables that you map to a
MERGE
table.
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 this behavior
is undesirable, you can start the server with the new
--skip-merge
option to disable
the MERGE
storage engine. This option is
available as of MySQL 4.1.21.
Use of DROP TABLE
with a
MERGE
table drops only the
MERGE
specification. The underlying tables are
not affected.
To create a MERGE
table, you must specify a
UNION=(
option that indicates which list-of-tables
)MyISAM
tables to
use. You can optionally specify an
INSERT_METHOD
option to control how inserts
into the MERGE
table take place. Use a value of
FIRST
or LAST
to cause
inserts to be made in the first or last underlying 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 not permitted
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;
The older term TYPE
is supported as a synonym
for ENGINE
for backward compatibility, but
ENGINE
is the preferred term from MySQL 4.0.18
on and TYPE
is deprecated.
Note that column a
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.
(Similarly, a column with a UNIQUE
index in the
underlying tables should be indexed in the
MERGE
table but not as a
UNIQUE
index.)
After creating the MERGE
table, you can use it
to 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 |
+---+---------+
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
to change the list of underlying tables.
tbl_name
UNION=(...)
As of MySQL 4.1.23, the underlying table definitions and indexes
must conform more closely than previously 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 fails. 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 and the MERGE
table
must have the same number of columns.
The column order in the underlying table and the
MERGE
table must match.
Additionally, the specification for each corresponding column
in the parent MERGE
table and the
underlying tables are compared and must satisfy these checks:
The column type in the underlying table and the
MERGE
table must be equal.
The column length in the underlying table and the
MERGE
table must be equal.
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.
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.
Each index must satisfy these checks:
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
.
For information about the table checks applied prior to MySQL 4.1.23, see Section 13.3.2, “MERGE Table Problems”.
A forum dedicated to the MERGE
storage
engine is available at
http://forums.mysql.com/list.php?93.
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
underlying 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 smaller 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 from 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 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 a given index value. 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.7.2, “EXPLAIN Syntax”.
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.
Before MySQL 4.1.1, all underlying tables and the
MERGE
table itself had to be in the same
database.
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.
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 over all the
underlying tables in the collection.
Because the MERGE
engine cannot enforce
uniqueness over the set of underlying tables,
REPLACE
does not work as
expected. 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 a
unique key violation, it will change only 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
.
You should not use ANALYZE
TABLE
, REPAIR
TABLE
, OPTIMIZE
TABLE
, ALTER TABLE
,
DROP TABLE
,
DELETE
without a
WHERE
clause, or
TRUNCATE 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 and yield unexpected
results. To work around this problem, ensure that no
MERGE
tables remain open by issuing a
FLUSH
TABLES
statement prior to performing any of the
named operations.
The unexpected results include the possibility that the
operation on the MERGE
table will report
table corruption. If this occurs after one of the named
operations on the underlying MyISAM
tables, 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 permit 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.
Before MySQL 3.23.49, DELETE FROM
used
without a merge_table
WHERE
clause only clears the
mapping for the table. That is, it incorrectly empties the
.MRG
file rather than deleting records
from the mapped tables.
Using RENAME TABLE
on an
active MERGE
table may corrupt the table.
This is fixed in MySQL 4.1.x.
As of MySQL 4.1.23, 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, an error is returned and
the statement fails. 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.
Prior to MySQL 4.1.23, table checks are applied as follows:
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:
'tbl_name
.MRG' (errno:
2), it generally indicates that some of the
underlying 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 232 (~4.295E+09; the
same as for a MyISAM
table). 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.
The MEMORY
storage engine creates tables with
contents that are stored in memory. Before MySQL 4.1,
MEMORY
tables are called
HEAP
tables. As of 4.1,
MEMORY
is the preferred term, although
HEAP
remains supported for backward
compatibility.
The MEMORY
storage engine associate each table
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 that you want to create a MEMORY
table, indicate that with an ENGINE
table
option:
CREATE TABLE t (i INT) ENGINE = MEMORY;
The older term TYPE
is supported as a synonym
for ENGINE
for backward compatibility, but
ENGINE
is the preferred term from MySQL 4.0.18
on and TYPE
is deprecated.
As indicated by the engine 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 TYPE=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 permit up to 32 indexes per
table and 16 columns per index. Previously, the maximum key
length supported by this storage engine was 255 bytes; as of
MySQL 4.1.13, MEMORY
tables support a
maximum key length of 500 bytes. (See
Section C.1.13, “Changes in MySQL 4.1.13 (2005-07-15)”.)
Before MySQL 4.1, the MEMORY
storage engine
supports only hash indexes. From MySQL 4.1 on, hash indexes
are still the default, but you can specify explicitly that a
MEMORY
table index should be a
HASH
or BTREE
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;
For general characteristics of B-tree and hash indexes, see Section 7.4.3, “How MySQL Uses Indexes”.
If a MEMORY
table hash index 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.
MEMORY
tables can have nonunique keys.
(This is an uncommon feature for implementations of hash
indexes.)
As of MySQL 4.0.2, columns that are indexed can contain
NULL
values.
MEMORY
tables use a fixed-length
row-storage format. Variable-length types such as
VARCHAR
are stored using a
fixed length.
MEMORY
supports
AUTO_INCREMENT
columns as of MySQL 4.1.0.
As of MySQL 4.1, MEMORY
supports
INSERT DELAYED
. See
Section 12.2.4.2, “INSERT DELAYED Syntax”.
Non-TEMPORARY
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:
MEMORY
tables are never converted to
disk tables. If an internal temporary table becomes too
large, the server automatically converts it to on-disk
storage, as described in
Section 7.7.4, “How MySQL Uses Internal Temporary 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 for CREATE TABLE
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.
To free memory used by a MEMORY
table when
you no longer require its contents, you should execute
DELETE
or
TRUNCATE TABLE
to remove all
rows, 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.5, “LOAD DATA INFILE Syntax”.
A server's MEMORY
tables become empty when
it is shut down and restarted. However, if the server is a
replication master, its slave are not aware that these tables
have become empty, so they returns out-of-date content if you
select data from these tables. To handle this, as of MySQL
4.0.18, when a MEMORY
table is used on a
master for the first time since it was started, a
DELETE FROM
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 enable 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.
A forum dedicated to the MEMORY
storage
engine is available at
http://forums.mysql.com/list.php?92.
Sleepycat Software has provided MySQL with the Berkeley DB
transactional storage engine. This storage engine typically is
called BDB
for short. BDB
tables may have a greater chance of surviving crashes and are also
capable of COMMIT
and
ROLLBACK
operations on transactions.
Support for the BDB
storage engine is included
in MySQL source distributions, which come with a
BDB
distribution that is patched to make it
work with MySQL. You cannot use an unpatched version of
BDB
with MySQL.
As of MySQL 5.1, BDB
is not supported.
For general information about Berkeley DB, please visit the Sleepycat Web site, http://www.sleepycat.com/.
Currently, we know that the BDB
storage
engine works with the following operating systems:
Linux 2.x Intel
Sun Solaris (SPARC and x86)
FreeBSD 4.x/5.x (x86, sparc64)
IBM AIX 4.3.x
SCO OpenServer
SCO UnixWare 7.1.x
Windows NT/2000/XP
The BDB
storage engine does
not work with the following operating
systems:
Linux 2.x Alpha
Linux 2.x AMD64
Linux 2.x IA-64
Linux 2.x s390
Mac OS X
The preceding lists are not complete. We update them as we receive more information.
If you build MySQL from source with support for
BDB
tables, but the following error occurs
when you start mysqld, it means that the
BDB
storage engine is not supported for your
architecture:
bdb: architecture lacks fast mutexes: applications cannot be threaded Can't init databases
In this case, you must rebuild MySQL without
BDB
support or start the server with the
--skip-bdb
option.
If you have downloaded a binary version of MySQL that includes
support for Berkeley DB, simply follow the usual binary
distribution installation instructions. (MySQL-Max distributions
include BDB
support.)
If you build MySQL from source, you can enable
BDB
support by invoking
configure with the
--with-berkeley-db
option in addition to any
other options that you normally use. Download a distribution for
MySQL 3.23.34 or newer, change location into its top-level
directory, and run this command:
shell> ./configure --with-berkeley-db [other-options
]
For more information, see Section 5.2, “The mysqld-max Extended MySQL Server”, Section 2.8, “Installing MySQL from Generic Binaries on Other Unix-Like Systems”, and Section 2.9, “Installing MySQL from Source”.
The following options to mysqld can be used
to change the behavior of the BDB
storage
engine. For more information, see
Section 5.1.2, “Server Command Options”.
The base directory for BDB
tables. This
should be the same directory that you use for
--datadir
.
The BDB
lock detection method. The option
value should be DEFAULT
,
OLDEST
, RANDOM
, or
YOUNGEST
.
The BDB
log file directory.
Do not start Berkeley DB in recover mode.
Don't synchronously flush the BDB
logs.
This option is deprecated as of MySQL 4.0.18; use
--skip-sync-bdb-logs
instead (see the description for
--sync-bdb-logs
).
Start Berkeley DB in multi-process mode. (Do not use
DB_PRIVATE
when initializing Berkeley
DB.)
The BDB
temporary file directory.
Disable the BDB
storage engine.
Synchronously flush the BDB
logs. This
option is enabled by default. Use
--skip-sync-bdb-logs
to disable it. This option was added in MySQL 4.0.18.
If you use the --skip-bdb
option,
MySQL does not initialize the Berkeley DB library and this saves
a lot of memory. However, if you use this option, you cannot use
BDB
tables. If you try to create a
BDB
table, MySQL uses the default storage
engine instead.
Normally, you should start mysqld without the
--bdb-no-recover
option if you
intend to use BDB
tables. However, this may
cause problems when you try to start mysqld
if the BDB
log files are corrupted. See
Section 2.10.2.3, “Starting and Troubleshooting the MySQL Server”.
With the bdb_max_lock
variable,
you can specify the maximum number of locks that can be active
on a BDB
table. The default is 10,000. You
should increase this if errors such as the following occur when
you perform long transactions or when mysqld
has to examine many rows to execute a query:
bdb: Lock table is out of available locks Got error 12 from ...
You may also want to change the
binlog_cache_size
and
max_binlog_cache_size
variables
if you are using large multiple-statement transactions. See
Section 5.3.4, “The Binary Log”.
See also Section 5.1.3, “Server System Variables”.
Each BDB
table is stored on disk in two
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, and a
.db
file contains the table data and
indexes.
To specify explicitly that you want a BDB
table, indicate that with an ENGINE
table
option:
CREATE TABLE t (i INT) ENGINE = BDB;
The older term TYPE
is supported as a synonym
for ENGINE
for backward compatibility, but
ENGINE
is the preferred term from MySQL
4.0.18 on and TYPE
is deprecated.
BerkeleyDB
is a synonym for
BDB
in the ENGINE
table
option.
The BDB
storage engine provides transactional
tables. The way you use these tables depends on the autocommit
mode:
If you are running with autocommit enabled (which is the
default), changes to BDB
tables are
committed immediately and cannot be rolled back.
If you are running with autocommit disabled, changes do not
become permanent until you execute a
COMMIT
statement. Instead of
committing, you can execute
ROLLBACK
to forget the changes.
You can start a transaction with the
START
TRANSACTION
or
BEGIN
statement to suspend autocommit, or with SET
autocommit = 0
to disable autocommit explicitly.
For more information about transactions, see Section 12.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Syntax”.
The BDB
storage engine has the following
characteristics:
BDB
tables can have up to 31 indexes per
table, 16 columns per index, and a maximum key size of 1024
bytes (500 bytes before MySQL 4.0).
MySQL requires a primary key in each BDB
table so that each row can be uniquely identified. If you
don't create one explicitly by declaring a PRIMARY
KEY
, MySQL creates and maintains a hidden primary
key for you. The hidden key has a length of five bytes and
is incremented for each insert attempt. This key does not
appear in the output of SHOW CREATE
TABLE
or DESCRIBE
.
The primary key is faster than any other index, because it is stored together with the row data. The other indexes are stored as the key data plus the primary key, so it is important to keep the primary key as short as possible to save disk space and get better speed.
This behavior is similar to that of
InnoDB
, where shorter primary keys save
space not only in the primary index but in secondary indexes
as well.
If all columns that you access in a BDB
table are part of the same index or part of the primary key,
MySQL can execute the query without having to access the
actual row. In a MyISAM
table, this can
be done only if the columns are part of the same index.
Sequential scanning is slower for BDB
tables than for MyISAM
tables because the
data in BDB
tables is stored in B-trees
and not in a separate data file.
Key values are not prefix- or suffix-compressed like key
values in MyISAM
tables. In other words,
key information takes a little more space in
BDB
tables compared to
MyISAM
tables.
There are often holes in the BDB
table to
permit you to insert new rows in the middle of the index
tree. This makes BDB
tables somewhat
larger than MyISAM
tables.
SELECT COUNT(*) FROM
is slow for
tbl_name
BDB
tables, because no row count is
maintained in the table.
The optimizer needs to know the approximate number of rows
in the table. MySQL solves this by counting inserts and
maintaining this in a separate segment in each
BDB
table. If you don't issue a lot of
DELETE
or
ROLLBACK
statements, this number should be accurate enough for the
MySQL optimizer. However, MySQL stores the number only on
close, so it may be incorrect if the server terminates
unexpectedly. It should not be fatal even if this number is
not 100% correct. You can update the row count by using
ANALYZE TABLE
or
OPTIMIZE TABLE
. See
Section 12.4.2.1, “ANALYZE TABLE Syntax”, and
Section 12.4.2.5, “OPTIMIZE TABLE Syntax”.
Internal locking in BDB
tables is done at
the page level.
LOCK TABLES
works on
BDB
tables as with other tables. If you
do not use LOCK TABLES
, MySQL
issues an internal multiple-write lock on the table (a lock
that does not block other writers) to ensure that the table
is properly locked if another thread issues a table lock.
To support transaction rollback, the BDB
storage engine maintains log files. For maximum performance,
you can use the --bdb-logdir
option to place the BDB
logs on a
different disk than the one where your databases are
located.
MySQL performs a checkpoint each time a new
BDB
log file is started, and removes any
BDB
log files that are not needed for
current transactions. You can also use
FLUSH LOGS
at any time to checkpoint the Berkeley DB tables.
For disaster recovery, you should use table backups plus MySQL's binary log. See Section 6.2, “Database Backup Methods”.
If you delete old log files that are still in use,
BDB
is not able to do recovery at all
and you may lose data if something goes wrong.
Applications must always be prepared to handle cases where
any change of a BDB
table may cause an
automatic rollback and any read may fail with a deadlock
error.
If you get a full disk with a BDB
table,
you get an error (probably error 28) and the transaction
should roll back. This contrasts with
MyISAM
and ISAM
tables, for which mysqld waits for
sufficient free disk space before continuing.
The following list indicates restrictions that you must observe
when using BDB
tables:
Each BDB
table stores in its
.db
file the path to the file as it was
created. This is done to enable detection of locks in a
multi-user environment that supports symlinks. As a
consequence of this, it is not possible to move
BDB
table files from one database
directory to another.
When making backups of BDB
tables, you
must either use mysqldump or else make a
backup that includes the files for each
BDB
table (the .frm
and .db
files) as well as the
BDB
log files. The BDB
storage engine stores unfinished transactions in its log
files and requires them to be present when
mysqld starts. The BDB
logs are the files in the data directory with names of the
form
log.
(ten digits).
NNNNNNNNNN
If a column that permits NULL
values has
a unique index, only a single NULL
value
is permitted. This differs from other storage engines, which
permit multiple NULL
values in unique
indexes.
If the following error occurs when you start
mysqld after upgrading, it means that the
current version of BDB
doesn't support
the old log file format:
bdb: Ignoring log file: .../log.NNNNNNNNNN
:
unsupported log version #
In this case, you must delete all BDB
logs from your data directory (the files that have names of
the form
log.
)
and restart mysqld. We also recommend
that you then use mysqldump --opt to dump
your NNNNNNNNNN
BDB
tables, drop the tables, and
restore them from the dump file.
If autocommit mode is disabled and you drop a
BDB
table that is referenced in another
transaction, you may get error messages of the following
form in your MySQL error log:
001119 23:43:56 bdb: Missing log fileid entry 001119 23:43:56 bdb: txn_abort: Log undo failed for LSN: 1 3644744: Invalid
This is not fatal, but the fix is not trivial. Avoid
dropping BDB
tables except while
autocommit mode is enabled.
The EXAMPLE
storage engine was added in MySQL
4.1.3. It 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.
The EXAMPLE
storage engine is included in
MySQL-Max binary distributions. To enable this 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 sql/examples
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.
The ARCHIVE
storage engine was added in MySQL
4.1.3. It is used for storing large amounts of data without
indexes in a very small footprint.
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 sql
directory of a MySQL source
distribution.
You can check whether the ARCHIVE
storage
engine is available with this statement:
mysql> SHOW VARIABLES LIKE 'have_archive';
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 and metadata files
have extensions of .ARZ
and
.ARM
, respectively. 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 16.4.1, “MySQL Spatial Data Types”). The
ARCHIVE
engine uses row-level locking.
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). 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.4.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.4.2.5, “OPTIMIZE TABLE Syntax”,
Section 12.4.2.6, “REPAIR TABLE Syntax”, and
Section 12.4.5.23, “SHOW TABLE STATUS Syntax”.
A forum dedicated to the ARCHIVE
storage
engine is available at
http://forums.mysql.com/list.php?112.
The CSV
storage engine was added in MySQL
4.1.4. This engine stores data in text files using comma-separated
values format. It is unavailable on Windows until MySQL 5.1.
The CSV
storage engine is included in MySQL-Max
binary distributions. To enable this 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 sql/examples
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)
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.
The CSV
storage engine does not support
indexing.
The BLACKHOLE
storage engine was added in MySQL
4.1.11. This 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)
The BLACKHOLE
storage engine is included in
MySQL-Max binary distributions. To enable this 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 this statement:
mysql> SHOW VARIABLES LIKE 'have_blackhole_engine';
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 14.8, “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.
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 original storage engine in MySQL was the
ISAM
engine. It was the only storage engine
available until MySQL 3.23, when the improved
MyISAM
engine was introduced as the default.
ISAM
is deprecated. As of MySQL 4.1, it is
included in the source but not enabled in binary distributions. It
is not available in MySQL 5.0. Embedded MySQL server versions do
not support ISAM
tables by default.
Due to the deprecated status of ISAM
, and
because MyISAM
is an improvement over
ISAM
, you are advised to convert any remaining
ISAM
tables to MyISAM
as
soon as possible. To convert an ISAM
table to a
MyISAM
table, use an ALTER
TABLE
statement:
mysql> ALTER TABLE tbl_name
TYPE = MYISAM;
For more information about MyISAM
, see
Section 13.1, “The MyISAM Storage Engine”.
Each ISAM
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 definition. The
data file has an .ISD
extension. The index
file has an .ISM
extension.
ISAM
uses B-tree indexes.
You can check or repair ISAM
tables with the
isamchk utility. See
Section 6.6.1, “Using myisamchk for Crash Recovery”.
ISAM
has the following properties:
Compressed and fixed-length keys
Fixed and dynamic record length
16 indexes per table, with 16 key parts per key
Maximum key length 256 bytes (default)
Data values are stored in machine format; this is fast, but machine/OS dependent
Many of the properties of MyISAM
tables are
also true for ISAM
tables. However, there are
also many differences. The following list describes some of the
ways that ISAM
is distinct from
MyISAM
:
Not binary portable across OS/platforms.
Can't handle tables larger than 4GB.
Only supports prefix compression on strings.
Smaller (more restrictive) key limits.
Dynamic tables become more fragmented.
Doesn't support MERGE
tables.
Tables are checked and repaired with isamchk rather than with myisamchk.
Tables are compressed with pack_isam rather than with myisampack.
Cannot be used with the BACKUP
TABLE
or RESTORE
TABLE
backup-related statements.
Cannot be used with the CHECK
TABLE
, REPAIR TABLE
,
OPTIMIZE TABLE
, or
ANALYZE TABLE
table-maintenance
statements.
No support for full-text searching or spatial data types.
No support for multiple character sets per table.
Indexes cannot be assigned to specific key caches.