Alden Hosting provides professional, efficient, and reliable business-class Web hosting and Website Design services.
|
||
|
Alden Hosting provides professional, efficient, and reliable business-class Web hosting and Website Design services. |
|
|
JAVA, JSP, SERVLETS, TOMCAT, SERVLETS MANAGER, |
|||||||||||||||||||||||||||||||
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. A lock is not granted to a requesting transaction 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.
In InnoDB, all user activity occurs inside a
transaction. If the autocommit mode is enabled, each SQL
statement forms a single transaction on its own. By default,
MySQL starts new connections with autocommit enabled.
If the autocommit mode is switched off with SET
AUTOCOMMIT = 0, then we can consider that a user
always has a transaction open. An SQL 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 users. A
ROLLBACK statement, on the other hand,
cancels all modifications made by the current transaction. Both
statements release all InnoDB locks that were
set during the current transaction.
If the connection has autocommit enabled, the user can still
perform a multiple-statement transaction by starting it with an
explicit START TRANSACTION or
BEGIN statement and ending it with
COMMIT or ROLLBACK.
In terms of the SQL:1992 transaction isolation levels, the
InnoDB default is REPEATABLE
READ. InnoDB offers all four
transaction isolation levels described by the SQL standard. You
can set the default isolation level for all connections by using
the --transaction-isolation option on the
command line or in an option file. For example, you can set the
option in the [mysqld] section of an option
file like this:
[mysqld]
transaction-isolation = {READ-UNCOMMITTED | READ-COMMITTED
| REPEATABLE-READ | SERIALIZABLE}
A user can change the isolation level for a single session or
for all new incoming connections with the SET
TRANSACTION statement. Its syntax is as follows:
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED
| REPEATABLE READ | SERIALIZABLE}
Note that there are hyphens in the level names for the
--transaction-isolation option, but not for the
SET TRANSACTION statement.
The default behavior is to set the isolation level for the next
(not started) transaction. If you use the
GLOBAL keyword, the statement sets the
default transaction level globally for all new connections
created from that point on (but not for existing connections).
You need the SUPER privilege to do this.
Using the SESSION keyword sets the default
transaction level for all future transactions performed on the
current connection.
Any client is free to change the session isolation level (even in the middle of a transaction), or the isolation level for the next transaction.
You can determine the global and session transaction isolation
levels by checking the value of the
tx_isolation system variable with these
statements:
SELECT @@global.tx_isolation; SELECT @@tx_isolation;
In row-level locking, InnoDB 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 users
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 only locks a gap before some index
record. Next-key locking for searches or index scans can be
disabled by enabling the
innodb_locks_unsafe_for_binlog system
variable.
A detailed description of each isolation level in
InnoDB follows:
READ UNCOMMITTED
SELECT statements are performed in a
non-locking fashion, but a possible earlier version of a
record might be used. Thus, using this isolation level, such
reads are not consistent. This is also called a “dirty
read.” Otherwise, this isolation level works like
READ COMMITTED.
READ COMMITTED
A somewhat Oracle-like isolation level. All SELECT
... FOR UPDATE and SELECT ... LOCK IN
SHARE MODE statements lock only the index records,
not the gaps before them, and thus allow the free insertion
of new records next to locked records.
UPDATE and DELETE
statements using a unique index with a unique search
condition lock only the index record found, not the gap
before it. In range-type UPDATE and
DELETE statements,
InnoDB must set next-key or gap locks and
block insertions by other users to the gaps covered by the
range. This is necessary because “phantom rows”
must be blocked for MySQL replication and recovery to work.
Consistent reads behave as in Oracle: Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. See Section 14.2.10.4, “Consistent Non-Locking Read”.
REPEATABLE READ
This is the default isolation level of
InnoDB. SELECT ... FOR
UPDATE, SELECT ... LOCK IN SHARE
MODE, UPDATE, and
DELETE statements that use a unique index
with a unique search condition lock only the index record
found, not the gap before it. With other search conditions,
these operations employ next-key locking, locking the index
range scanned with next-key or gap locks, and block new
insertions by other users.
In consistent reads, there is an important difference from
the READ COMMITTED isolation level: All
consistent reads within the same transaction read the same
snapshot established by the first read. This convention
means that if you issue several plain
SELECT statements within the same
transaction, these SELECT statements are
consistent also with respect to each other. See
Section 14.2.10.4, “Consistent Non-Locking Read”.
SERIALIZABLE
This level is like REPEATABLE READ, but
InnoDB implicitly commits all plain
SELECT statements to SELECT ...
LOCK IN SHARE MODE.
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 see the changes made by
those 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. Note that the
exception to the rule causes the following anomaly: if you
update some rows in a table, a SELECT will
see the latest version of the updated rows, while it sees the
old version of other rows. If other users simultaneously update
the same table, the anomaly means that you may see the table in
a state that never existed in the database.
If you are running with the default REPEATABLE
READ isolation 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.
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 users are free to modify those
tables at the same time a consistent read is being performed on
the table.
Note that consistent read does not work over DROP
TABLE and over ALTER TABLE.
Consistent read does not work over DROP TABLE
because MySQL can't 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 read is not convenient. For
example, you might want to add a new row into your table
child, and make sure that the child has a
parent in table parent. The following example
shows how to implement referential integrity in your application
code.
Suppose that you use a consistent read to read the table
parent and indeed see the parent of the child
in the table. Can you safely add the child row to table
child? No, because it may happen that
meanwhile some other user deletes the parent row from the table
parent 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;
Performing a read in share mode means that we read the latest
available data, and set a shared mode lock on the rows we read.
A shared mode lock prevents others from updating or deleting the
row we have read. Also, if the latest data belongs to a yet
uncommitted transaction of another client connection, we wait
until that transaction commits. After we see that the preceding
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. Obviously, using a consistent read or
a shared mode read to read the present value of the counter is
not a good idea 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 attempting to
update the counter.
In this case, there are two good ways to implement the reading
and incrementing of the counter: (1) update the counter first by
incrementing it by 1 and only after that read it, or (2) read
the counter first with a lock mode FOR
UPDATE, and increment after that. The latter approach
can be implemented as follows:
SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes SET counter_field = counter_field + 1;
A SELECT ... FOR UPDATE reads the latest
available data, setting exclusive locks on each row it reads.
Thus, it sets the same locks a searched SQL
UPDATE would set on the rows.
The preceding description is merely an example of how
SELECT ... FOR UPDATE works. In MySQL, the
specific task of generating a unique identifier actually can be
accomplished using only a single access to the table:
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1); SELECT LAST_INSERT_ID();
The SELECT statement merely retrieves the
identifier information (specific to the current connection). It
does not access any table.
Locks set by IN SHARE MODE and FOR
UPDATE reads are released when the transaction is
committed or rolled back.
Locking of rows for update using SELECT FOR
UPDATE only applies when
AUTOCOMMIT is switched off. If
AUTOCOMMIT is on, then the rows matching
the specification are not locked.
In row-level locking, InnoDB uses an
algorithm called next-key locking.
InnoDB performs the row-level locking in such
a way that when it searches or scans an index of a table, it
sets shared or exclusive locks on the index records it
encounters. Thus, the row-level locks are actually index record
locks.
The next-key locks that InnoDB sets on index
records also affect the “gap” before that index
record. If a user has a shared or exclusive lock on record
R in an index, another user cannot insert a
new index record immediately before R in the
index order. (A gap lock refers to a lock that only locks a gap
before some index record.)
This next-key locking of gaps is done to prevent the so-called
“phantom problem.” Suppose that you want to read
and lock all children from the child table
having an identifier value greater than 100, with the intention
of updating some column in the selected rows later:
SELECT * FROM child WHERE id > 100 FOR UPDATE;
Suppose that there is an index on the id
column. The query scans that index starting from the first
record where id is bigger than 100. If the
locks set on the index records would not lock out inserts made
in the gaps, a new row might meanwhile be inserted to the table.
If you execute the same SELECT within the
same transaction, you would see a new row in the result set
returned by the query. This is contrary to the isolation
principle of transactions: A transaction should be able to run
so that the data it has read does not change during the
transaction. If we regard a set of rows as a data item, the new
“phantom” child would violate this isolation
principle.
When InnoDB scans an index, it can also lock
the gap after the last record in the index. Just that happens in
the previous example: The locks set by InnoDB
prevent any insert to the table where id
would be bigger than 100.
You can use next-key locking to implement a uniqueness check in your application: If you read your data in share mode and do not see a duplicate for a row you are going to insert, then you can safely insert your row and know that the next-key lock set on the successor of your row during the read prevents anyone meanwhile inserting a duplicate for your row. Thus, the next-key locking allows you to “lock” the non-existence of something in your 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.
User A User 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
In this example, user 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.
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;
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 if 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 record locks are normally
next-key locks that also block inserts to the “gap”
immediately before the record.
If the locks to be set are exclusive, InnoDB
always retrieves also the clustered index record and sets a lock
on it.
If you do not have indexes suitable for your statement and MySQL has to scan the whole 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 need to scan many rows.
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, this 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 read
encounters.
SELECT ... FROM ... FOR UPDATE sets
exclusive next-key locks on all index records the read
encounters.
INSERT INTO ... VALUES (...) sets an
exclusive lock on the inserted row. Note that this lock is
not a next-key lock and does not prevent other users from
inserting to the gap before the inserted row. If a
duplicate-key error occurs, a shared lock on the duplicate
index record is set.
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. Note that other
clients cannot insert into the table while the
AUTO-INC table lock is held; see
Section 14.2.10.2, “InnoDB and AUTOCOMMIT”.
InnoDB fetches the value of a previously
initialized AUTO_INCREMENT column without
setting any locks.
INSERT INTO T SELECT ... FROM S WHERE ...
sets an exclusive (non-next-key) lock on each row inserted
into T. InnoDB sets
shared next-key locks on S, unless
innodb_locks_unsafe_for_binlog is
enabled, in which case it does the search on
S as a consistent read.
InnoDB has to set locks in the latter
case: In roll-forward recovery from a backup, every SQL
statement has to be executed in exactly the same way it was
done originally.
CREATE TABLE ... SELECT ... performs the
SELECT as a consistent read or with
shared locks, as in the previous item.
REPLACE is done like an insert if there
is no collision on a unique key. Otherwise, an exclusive
next-key lock is placed on the row that has to be updated.
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.
If a FOREIGN KEY constraint is defined on
a table, any insert, update, or delete that requires the
constraint condition to be checked sets shared record-level
locks on the records that it looks at to check the
constraint. InnoDB also sets these locks
in the case where the constraint fails.
LOCK TABLES sets table locks, but it is
the higher MySQL layer above the InnoDB
layer that sets these locks. InnoDB is
aware of table locks if
innodb_table_locks=1 (the default) and
AUTOCOMMIT=0, and the MySQL layer above
InnoDB knows about row-level locks.
Otherwise, InnoDB's automatic deadlock
detection cannot detect deadlocks where such table locks are
involved. Also, because the higher MySQL layer does not know
about row-level locks, it is possible to get a table lock on
a table where another user currently has row-level locks.
However, this does not endanger transaction integrity, as
discussed in Section 14.2.10.10, “Deadlock Detection and Rollback”.
See also Section 14.2.16, “Restrictions on InnoDB Tables”.
By default, MySQL begins each client connection with autocommit
mode enabled. When autocommit is enabled, MySQL does a commit
after each SQL statement if that statement did not return an
error. If an SQL statement returns an error, the commit or
rollback behavior depends on the error. See
Section 14.2.15, “InnoDB Error Handling”.
If you have the autocommit mode off and close a connection without explicitly committing the final transaction, MySQL rolls back that transaction.
Each of the following statements (and any synonyms for them)
implicitly end a transaction, as if you had done a
COMMIT before executing the statement:
ALTER TABLE, BEGIN,
CREATE INDEX, DROP
INDEX, DROP TABLE,
LOAD MASTER DATA, LOCK
TABLES, LOAD DATA INFILE,
RENAME TABLE, SET
AUTOCOMMIT=1, START
TRANSACTION, UNLOCK TABLES.
Beginning with MySQL 5.0.8, The CREATE
TABLE, CREATE DATABASE
DROP DATABASE, and TRUNCATE
TABLE statements cause an implicit commit.
Beginning with MySQL 5.0.13, the ALTER
FUNCTION, ALTER PROCEDURE,
CREATE FUNCTION, CREATE
PROCEDURE, DROP FUNCTION, and
DROP PROCEDURE statements cause an
implicit commit. Beginning with MySQL 5.0.15, the
ALTER VIEW, CREATE
TRIGGER, CREATE USER,
CREATE VIEW, DROP
TRIGGER, DROP USER,
DROP VIEW, and RENAME
USER statements cause an implicit commit.
UNLOCK TABLES commits a transaction only
if any tables currently have been locked with LOCK
TABLES. This does not occur for UNLOCK
TABLES following FLUSH TABLES WITH READ
LOCK because the latter statement does not acquire
table-level locks.
The CREATE TABLE statement in
InnoDB is processed as a single
transaction. This means that a ROLLBACK
from the user does not undo CREATE TABLE
statements the user made during that transaction.
CREATE TABLE and DROP
TABLE do not commit a transaction if the
TEMPORARY keyword is used. (This does not
apply to other operations on temporary tables such as
CREATE INDEX, which do cause a commit.)
Prior to MySQL 5.0.26, LOAD DATA INFILE
also caused an implicit commit for InnoDB
tables (as was true for all storage engines).
Transactions cannot be nested. This is a consequence of the
implicit COMMIT performed for any current
transaction when you issue a START
TRANSACTION statement or one of its synonyms.
Statements that cause implicit commit cannot be used in an XA
transaction while the transaction is in an
ACTIVE state.
The BEGIN statement differs from the use of
the BEGIN keyword that starts a
BEGIN ... END compound statement. The latter
does not cause an implicit commit. See
Section 17.2.5, “BEGIN ... END Compound Statement Syntax”.
InnoDB automatically detects a deadlock of
transactions and rolls back a transaction or transactions to
break the deadlock. InnoDB tries to pick
small transactions to roll back, where the size of a transaction
is determined by the number of rows inserted, updated, or
deleted.
InnoDB is aware of table locks if
innodb_table_locks=1 (the default) and
AUTOCOMMIT=0, and the MySQL layer above it
knows about row-level locks. Otherwise,
InnoDB cannot detect deadlocks where a table
lock set by a MySQL LOCK TABLES statement or
a lock set by a storage engine other than
InnoDB is involved. You must resolve these
situations by setting the value of the
innodb_lock_wait_timeout system variable.
When InnoDB performs a complete rollback of a
transaction, all locks set by the transaction are released.
However, if just a single SQL statement is rolled back as a
result of an error, some of the locks set by the statement may
be preserved. This happens because InnoDB
stores row locks in a format such that it cannot know afterward
which lock was set by which statement.
Deadlocks are a classic problem in transactional databases, but they are not dangerous unless they are so frequent that you cannot run certain transactions at all. Normally, you must write your applications so that they are always prepared to re-issue a transaction if it gets rolled back because of a deadlock.
InnoDB uses automatic row-level locking. You
can get deadlocks even in the case of transactions that just
insert or delete a single row. That is because these operations
are not really “atomic”; they automatically set
locks on the (possibly several) index records of the row
inserted or deleted.
You can cope with deadlocks and reduce the likelihood of their occurrence with the following techniques:
Use SHOW ENGINE INNODB STATUS to
determine the cause of the latest deadlock. That can help
you to tune your application to avoid deadlocks.
Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.
Commit your transactions often. Small transactions are less prone to collision.
If you are using locking reads (SELECT ... FOR
UPDATE or ... LOCK IN SHARE
MODE), try using a lower isolation level such as
READ COMMITTED.
Access your tables and rows in a fixed order. Then transactions form well-defined queues and do not deadlock.
Add well-chosen indexes to your tables. Then your queries
need to scan fewer index records and consequently set fewer
locks. Use EXPLAIN SELECT to determine
which indexes the MySQL server regards as the most
appropriate for your queries.
Use less locking. If you can afford to allow a
SELECT to return data from an old
snapshot, do not add the clause FOR
UPDATE or LOCK IN SHARE MODE to
it. Using the READ COMMITTED isolation
level is good here, because each consistent read within the
same transaction reads from its own fresh snapshot.
If nothing else helps, serialize your transactions with
table-level locks. The correct way to use LOCK
TABLES with transactional tables, such as
InnoDB tables, is to set
AUTOCOMMIT = 0 and not to call
UNLOCK TABLES until after 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 deadlocks are avoided.
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 the LOCK TABLES
command, MySQL does not set InnoDB table
locks if AUTOCOMMIT=1.
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 14.2.13, “InnoDB Table and Index Structures”.) Create an
AUTO_INCREMENT column as the primary key if
your primary key is long.
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.
Wrap several modifications into one transaction.
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, 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.
Make your log files big, even as big as the buffer pool. When
InnoDB has written the log files full, it
has to write the modified contents of the buffer pool to disk
in a checkpoint. Small log files cause many unnecessary disk
writes. The drawback of big log files is that the recovery
time is longer.
Make the log buffer quite large as well (on the order of 8MB).
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.
When using row_format=compact (the default
InnoDB record format in MySQL
5.0) and variable-length character sets, such as
utf8 or sjis,
CHAR( will
occupy a variable amount of space, at least
N)N bytes.
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. Although
O_DSYNC seems to be slower on most systems,
yours might not be one of them.
When using the InnoDB storage engine on
Solaris 10 for x86_64 architecture (AMD Opteron), it is
important to mount any filesystems used for storing
InnoDB-related files using the
forcedirectio option. (The default on
Solaris 10/x86_64 is not to use this
option.) Failure to use forcedirectio
causes a serious degradation of InnoDB's
speed and performance on this platform.
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 can be
achieved by placing InnoDB data files and
log files on raw devices or on a separate direct I/O UFS
filesystem (using mount option
forcedirectio; see
mount_ufs(1M)). Users of the Veritas
filesystem VxFS should use the mount option
convosync=direct.
Other MySQL data files, such as those for
MyISAM tables, should not be placed on a
direct I/O filesystem. Executables or libraries must
not be placed on a direct I/O filesystem.
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.
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 14.2.8.1, “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
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 have UNIQUE constraints on secondary
keys, you can speed up table imports by temporarily turning
off the uniqueness checks during the import session:
SET UNIQUE_CHECKS=0;
... import operation ...
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. UNIQUE_CHECKS
allows but does not require storage engines to ignore
duplicate keys.
If you have FOREIGN KEY constraints in your
tables, you can speed up table imports by turning the foreign
key checks off for the duration of the import session:
SET FOREIGN_KEY_CHECKS=0;
... import operation ...
SET FOREIGN_KEY_CHECKS=1;
For big tables, this can save a lot of disk I/O.
If you often have recurring queries for tables that are not updated frequently, use the query cache:
[mysqld] query_cache_type = ON query_cache_size = 10M
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, you might want
to use it immediately after start up by issuing a statement
such as SELECT 1 FROM
.
tbl_name LIMIT 1
MySQL Enterprise For optimization recommendations geared to your specific circumstances subscribe to the MySQL Network Monitoring and Advisory Service. For more information see http://www.mysql.com/products/enterprise/advisors.html.
InnoDB includes InnoDB
Monitors that print information about the
InnoDB internal state. You can use the
SHOW ENGINE INNODB STATUS SQL statement at
any time to fetch the output of the standard
InnoDB Monitor to your SQL client. This
information is useful in performance tuning. (If you are using
the mysql interactive SQL client, the output
is more readable if you replace the usual semicolon statement
terminator with \G.) For a discussion of
InnoDB lock modes, see
Section 14.2.10.1, “InnoDB Lock Modes”.
mysql> SHOW ENGINE INNODB STATUS\G
Another way to use InnoDB Monitors is to let
them periodically write data to the standard output of the
mysqld server. 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 .err log in the
MySQL data directory. 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.
Monitor output includes 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
To cause the standard InnoDB Monitor to write
to the standard output of mysqld, use the
following SQL statement:
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
The monitor can be stopped by issuing the following statement:
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.)
You can use innodb_lock_monitor in a similar
fashion. This is the same as innodb_monitor,
except that it also provides a great deal of lock information. A
separate innodb_tablespace_monitor prints a
list of created file segments existing in the tablespace and
validates the tablespace allocation data structures. In
addition, there is innodb_table_monitor with
which you can print the contents of the
InnoDB internal data dictionary.
A sample of InnoDB Monitor output:
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Status:
=====================================
030709 13:00:59 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 18 seconds
----------
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
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
============================
Some notes on the output:
If the TRANSACTIONS section reports lock
waits, your applications may have lock contention. The
output can also help to trace the reasons for transaction
deadlocks.
The SEMAPHORES 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
innodb_thread_concurrency smaller than
the default value can help in such situations.
The BUFFER POOL AND MEMORY 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.
The ROW OPERATIONS section shows what the
main thread is doing.
InnoDB sends diagnostic output to
stderr or to files rather than to
stdout or fixed-size memory buffers, to avoid
potential buffer overflows. As a side effect, the output of
SHOW ENGINE INNODB STATUS is written to a
status file in the MySQL data directory every fifteen seconds.
The name of the file is
innodb_status.,
where pidpid is the server process ID.
InnoDB removes the file for a normal
shutdown. If abnormal shutdowns have occurred, instances of
these status files may be present and must be removed manually.
Before removing them, you might want to examine them to see
whether they contain useful information about the cause of
abnormal shutdowns. The
innodb_status.
file is created only if the configuration option
pidinnodb_status_file=1 is set.
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 two fields to each row
stored in the database. A 6-byte 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 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.
InnoDB uses the information in the rollback
segment to perform the undo operations needed in a transaction
rollback. It also uses the information to build earlier versions
of a row for a consistent read.
Undo logs in the rollback segment are divided into insert and
update undo logs. Insert undo logs are needed only in transaction
rollback and can be discarded as soon as the transaction commits.
Update undo logs are used also in consistent reads, but they can
be discarded only after there is no transaction present for which
InnoDB has assigned a snapshot that in a
consistent read could need the information in the update undo log
to build an earlier version of a database row.
You must remember to commit your transactions regularly, including
those transactions that issue only consistent reads. Otherwise,
InnoDB cannot discard data from the update undo
logs, and the rollback segment may grow too big, filling up your
tablespace.
The physical size of an undo log record in the rollback segment is typically smaller than the corresponding inserted or updated row. You can use this information to calculate the space need for your rollback segment.
In the InnoDB multi-versioning scheme, a row is
not physically removed from the database immediately when you
delete it with an SQL statement. Only when
InnoDB can discard the update undo log record
written for the deletion can it also physically remove the
corresponding row and its index records from the database. This
removal operation is called a purge, and it is quite fast, usually
taking the same order of time as the SQL statement that did the
deletion.
In a scenario where the user inserts and deletes rows in smallish
batches at about the same rate in the table, it is possible that
the purge thread starts to lag behind, and the table grows bigger
and bigger, making everything disk-bound and very slow. Even if
the table carries just 10MB of useful data, it may grow to occupy
10GB with all the “dead” rows. In such a case, it
would be good to throttle new row operations, and allocate more
resources to the purge thread. The
innodb_max_purge_lag system variable exists for
exactly this purpose. See Section 14.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 both an .frm file or files, and the
corresponding entries inside the InnoDB data
dictionary. This is the reason why you cannot move
InnoDB tables between databases simply by
moving the .frm files.
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, the index of the primary key is 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
there is no such index in the table, InnoDB
internally generates a clustered index where the rows are ordered
by the row 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 when compared to the traditional solution. (In many database systems, data storage uses a different page from the index record.)
In InnoDB, the records in non-clustered indexes
(also called secondary indexes) contain the primary key value for
the row. InnoDB uses this primary key value to
search for the row from the clustered index. Note that if the
primary key is long, the secondary indexes use more space.
InnoDB compares CHAR and
VARCHAR strings of different lengths such that
the remaining length in the shorter string is treated as if padded
with spaces.
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.
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, the insertions to the clustered index do not require random reads from a disk.
On the other hand, secondary indexes are usually non-unique, 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 to a non-unique 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 to 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 14.2.8.1, “Forcing InnoDB Recovery”).
The 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 14.2.12, “Implementation of 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.
Note that 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.
The physical record structure for InnoDB tables is dependent on
the MySQL version and the optional ROW_FORMAT
option used when the table was created. For InnoDB tables in
MySQL earlier than 5.0.3, only the REDUNDANT
row format was available. For MySQL 5.0.3 and later, the default
is to use the COMPACT row format, but you can
use the REDUNDANT format to retain
compatibility with older versions of InnoDB tables.
Records in InnoDB ROW_FORMAT=REDUNDANT 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 field for the transaction ID and a seven-byte field for the roll pointer.
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 contains also all the fields defined for the clustered index key.
A record contains also 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.
An SQL NULL value reserves 1 or 2 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. The
motivation behind reserving the fixed space for
NULL values is that it 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.
Records in InnoDB ROW_FORMAT=COMPACT tables
have the following characteristics:
Each index record contains a five-byte header that may be preceded by a variable-length header. The header is used to link together consecutive records, and also in row-level locking.
The record header contains a bit vector for indicating
NULL columns. The bit vector occupies
(n_nullable+7)/8 bytes. Columns that are
NULL will not occupy other space than the
bit in this vector.
For each non-NULL variable-length field,
the record header contains the length of the column in one
or two bytes. Two bytes will only be needed if part of the
column is stored externally or the maximum length exceeds
255 bytes and the actual length exceeds 127 bytes.
The record header is followed by the data contents of the
columns. Columns that are NULL are
omitted.
Records in the clustered index contain fields for all user-defined columns. In addition, there is a six-byte field for the transaction ID and a seven-byte field for the roll pointer.
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 contains also all the fields defined for the clustered index key.
Internally, InnoDB stores fixed-length, fixed-width
character columns such as CHAR(10) in a
fixed-length format. InnoDB truncates trailing spaces from
VARCHAR columns.
Internally, InnoDB attempts to store UTF-8
CHAR( columns in
n)n bytes by trimming trailing spaces. In
ROW_FORMAT=REDUNDANT, such columns occupy
3*n bytes. The motivation behind
reserving the minimum space n is that it
in many cases enables an update of the column 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 tablespace of InnoDB. The files are
simply concatenated to form the tablespace. There is no striping
in use. Currently, you cannot define where within the tablespace
your tables are allocated. However, in a newly created
tablespace, InnoDB allocates space starting
from the first data file.
The tablespace consists of database pages with a default size of
16KB. The pages are grouped into extents of 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.
Two segments are allocated for each index in
InnoDB. One is for non-leaf 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.
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 to a large segment up to 4 extents at a time to ensure
good sequentiality of 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 14.2.12, “Implementation of Multi-Versioning”.)
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.
A 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 10 to 50MB/s, which can be used to estimate how fast a table scan should run.
It can speed up index scans if you periodically perform a
“null” ALTER TABLE operation:
ALTER TABLE tbl_name ENGINE=INNODB
That causes MySQL to rebuild the table. 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 to 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 the rollback of that
statement. InnoDB sometimes rolls back only
part of the statement, or the whole transaction. The following
items describe how InnoDB performs error
handling:
If you run out of file space in the tablespace, a MySQL
Table is full error occurs and
InnoDB rolls back the SQL statement.
A transaction deadlock causes InnoDB to
roll back the entire transaction. In the case of a lock wait
timeout, InnoDB also rolls back the entire
transaction before MySQL 5.0.13; as of 5.0.13,
InnoDB rolls back only the most recent SQL
statement.
When a transaction rollback occurs due to a deadlock or lock
wait timeout, it cancels the effect of the statements within
the transaction. But if the start-transaction statement was
START TRANSACTION or
BEGIN statement, rollback does not cancel
that statement. Further SQL statements become part of the
transaction until the occurrence of COMMIT,
ROLLBACK, or some SQL statement that causes
an implicit commit.
A duplicate-key error rolls back the SQL statement, if you
have not specified the IGNORE option in
your statement.
A row too long error rolls back the SQL
statement.
Other errors are mostly detected by the MySQL layer of code
(above the InnoDB storage engine level),
and they roll back the corresponding SQL statement. Locks are
not released in a rollback of a single SQL statement.
During implicit rollbacks, as well as during the execution of an
explicit ROLLBACK SQL command, SHOW
PROCESSLIST displays Rolling back in
the State column for the relevant connection.
The following is a non-exhaustive 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
errno 150, table creation failed because
a foreign key constraint was not correctly formed. If the
error message refers to errno -1, table
creation probably failed because the table included 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 14.2.17.1, “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.
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 Windows
and you have enabled
innodb_file_per_table in a server option
file, 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 filename, directory name, or volume label syntax is incorrect.
1450 (ERROR_NO_SYSTEM_RESOURCES)
Insufficient system resources exist to complete the requested service.
Warning: Do
not convert MySQL system tables in the
mysql database from
MyISAM to InnoDB tables!
This is an unsupported operation. If you do this, MySQL does
not restart until you restore the old system tables from a
backup or re-generate them with the
mysql_install_db script.
Warning: If is not a good
idea to configure InnoDB to use datafiles
or logfiles 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 internal maximum key length is 3500 bytes, but MySQL itself restricts this to 1024 bytes.
The maximum row length, except for VARCHAR,
BLOB and TEXT columns,
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 also BLOB and
TEXT columns, must be less than 4GB.
InnoDB stores the first 768 bytes of a
VARCHAR, BLOB, or
TEXT column in the row, and the rest into
separate pages.
Although InnoDB supports row sizes larger
than 65535 internally, you cannot define a row containing
VARCHAR columns with a combined size larger
than 65535:
mysql>CREATE TABLE t (a VARCHAR(8000), b VARCHAR(10000),->c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),->f VARCHAR(10000), g VARCHAR(10000)) ENGINE=InnoDB;ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
On some older operating systems, files must be less than 2GB.
This is not a limitation of InnoDB itself,
but if you require a large tablespace, you will need to
configure it using several smaller data files rather than one
or a file large data files.
The combined size of the InnoDB log files
must be less than 4GB.
The minimum tablespace size is 10MB. The maximum tablespace size is four billion database pages (64TB). This is also the maximum size for a table.
InnoDB tables do not support
FULLTEXT indexes.
InnoDB tables do not support spatial data
types before MySQL 5.0.16. As of 5.0.16,
InnoDB supports spatial types, but not
indexes on them.
ANALYZE TABLE determines index cardinality
(as displayed in the Cardinality column of
SHOW INDEX output) by doing ten random
dives to each of the index trees and updating index
cardinality estimates accordingly. Note that 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 as it
doesn't 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 doesn't 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.2.3, “System Variables”, and
Section B.1.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. 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. If your table
does not change often, using the MySQL query cache is a good
solution. SHOW TABLE STATUS also can be
used if an approximate row count is sufficient. See
Section 14.2.11, “InnoDB Performance Tuning Tips”.
On Windows, InnoDB always stores database
and table names internally in lowercase. To move databases in
binary format from Unix to Windows or from Windows to Unix,
you should always use explicitly lowercase names when creating
databases and tables.
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.
In MySQL 5.0 before MySQL 5.0.3,
InnoDB does not support the
AUTO_INCREMENT table option for setting the
initial sequence value in a CREATE TABLE or
ALTER TABLE statement. 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. Note that other clients
cannot insert into the table while the
AUTO-INC table lock is held; see
Section 14.2.10.2, “InnoDB and AUTOCOMMIT”.
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 do note that
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_nameInnoDB table is mapped to DELETE
FROM and doesn't
reset the tbl_nameAUTO_INCREMENT counter. See
Section 13.2.9, “TRUNCATE Syntax”.
In MySQL 5.0, the MySQL LOCK
TABLES operation acquires two locks on each table if
innodb_table_locks=1 (the default). In
addition to a table lock on the MySQL layer, it also acquires
an InnoDB table lock. Older versions of
MySQL did not acquire InnoDB table locks;
the old behavior can be selected by setting
innodb_table_locks=0. If no
InnoDB table lock is acquired,
LOCK TABLES completes even if some records
of the tables are being locked by other transactions.
All InnoDB locks held by a transaction are
released when the transaction is committed or aborted. Thus,
it does not make much sense to invoke LOCK
TABLES on InnoDB tables in
AUTOCOMMIT=1 mode, because the acquired
InnoDB table locks would be released
immediately.
Sometimes it would be useful to lock further tables in the
course of a transaction. Unfortunately, LOCK
TABLES in MySQL performs an implicit
COMMIT and UNLOCK
TABLES. An InnoDB variant of
LOCK TABLES has been planned that can be
executed in the middle of a transaction.
The LOAD TABLE FROM MASTER statement for
setting up replication slave servers does not work for
InnoDB tables. A workaround is to alter the
table to MyISAM on the master, do then 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.
Currently, triggers are not activated by cascaded foreign key actions.
You cannot create a table with a column name that matches the
name of an internal InnoDB column (including
DB_ROW_ID, DB_TRX_ID,
DB_ROLL_PTR and
DB_MIX_ID). In versions of MySQL before
5.0.21 this would cause a crash, since 5.0.21 the server will
report error 1005 and refers to errno -1 in
the error message.
As of MySQL 5.0.19, InnoDB does not ignore
trailing spaces when comparing BINARY or
VARBINARY column values. See
Section 11.4.2, “The BINARY and VARBINARY Types” and
Section E.1.11, “Changes in release 5.0.19 (04 March 2006)”.
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, which is the file in the data
directory that has a suffix of .err.
When troubleshooting, it is usually best to run the MySQL
server from the command prompt, rather than through the
mysqld_safe wrapper 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, you must start the server with the
--console option to direct the output to
the console window.
Use the InnoDB Monitors to obtain
information about a problem (see
Section 14.2.11.1, “SHOW ENGINE INNODB STATUS and the
InnoDB Monitors”). If the problem is
performance-related, or your server appears to be hung, you
should use innodb_monitor to print
information about the internal state of
InnoDB. If the problem is with locks, use
innodb_lock_monitor. If the problem is in
creation of tables or other data dictionary operations, use
innodb_table_monitor to print the contents
of the InnoDB internal data dictionary.
If you suspect that a table is corrupt, run CHECK
TABLE on that table.
MySQL Enterprise The MySQL Network Monitoring and Advisory Service provides a number of advisors specifically designed for monitoring InnoDB tables. In some cases, these advisors can anticipate potential problems. For more information see http://www.mysql.com/products/enterprise/advisors.html.
A specific issue with tables is that the MySQL server keeps data
dictionary information in .frm files it
stores in the database directories, whereas
InnoDB also stores the information into its
own data dictionary inside the tablespace files. If you move
.frm files around, or if the server crashes
in the middle of a data dictionary operation, the locations of
the .frm files may end up out of synchrony
with the locations recorded in the InnoDB
internal data dictionary.
A symptom of an out-of-sync data dictionary is that a
CREATE TABLE statement fails. If this occurs,
you should look in the server's error log. If the log says that
the table already exists inside the InnoDB
internal data dictionary, you have an orphaned table inside the
InnoDB tablespace files that has no
corresponding .frm file. The error message
looks like this:
InnoDB: Error: table test/parent already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL version <= 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and moving the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed.
You can drop the orphaned table by following the instructions
given in the error message. If you are still unable to use
DROP TABLE successfully, the problem may be
due to name completion in the mysql client.
To work around this problem, start the mysql
client with the --skip-auto-rehash option and
try DROP TABLE again. (With name completion
on, mysql tries to construct a list of table
names, which fails when a problem such as just described
exists.)
Another symptom of an out-of-sync data dictionary is that MySQL
prints an error that it cannot open a
.InnoDB file:
ERROR 1016: Can't open file: 'child2.InnoDB'. (errno: 1)
In the error log you can find a message like this:
InnoDB: Cannot find table test/child2 from the internal data dictionary InnoDB: of InnoDB though the .frm file for the table exists. Maybe you InnoDB: have deleted and recreated InnoDB data files but have forgotten InnoDB: to delete the corresponding .frm files of InnoDB tables?
This means that there is an orphaned .frm
file without a corresponding table inside
InnoDB. You can drop the orphaned
.frm file by deleting it manually.
If MySQL crashes in the middle of an ALTER
TABLE operation, you may end up with an orphaned
temporary table inside the InnoDB tablespace.
Using innodb_table_monitor you can see listed
a table whose name is #sql-.... You can
perform SQL statements on tables whose name contains the
character ‘#’ if you enclose the
name within backticks. Thus, you can drop such an orphaned table
like any other orphaned table using the method described
earlier. Note that to copy or rename a file in the Unix shell,
you need to put the file name in double quotes if the file name
contains ‘#’.
The MERGE storage engine, also known as the
MRG_MyISAM engine, is a collection of identical
MyISAM tables that can be used as one.
“Identical” means that all tables have identical column
and index information. You cannot merge MyISAM
tables in which the columns are listed in a different order, do not
have exactly the same columns, or have the indexes in different
order. However, any or all of the MyISAM tables
can be compressed with myisampack. See
Section 8.7, “myisampack — Generate Compressed, Read-Only MyISAM Tables”. Differences in table options such as
AVG_ROW_LENGTH, MAX_ROWS, or
PACK_KEYS do not matter.
When you create a MERGE table, MySQL creates two
files on disk. The files have names that begin with the table name
and have an extension to indicate the file type. An
.frm file stores the table format, and an
.MRG file contains the names of the tables that
should be used as one. The tables do not have to be in the same
database as the MERGE table itself.
Starting with MySQL 5.0.36 the underlying table definitions and
indexes must conform more closely to the definition of the
MERGE table. Conformance will be checked when the
merged tables are opened, not when the MERGE
table is created. This means that changes to the definitions of
tables within a MERGE may cause a failure when
the MERGE table is accessed.
You can use SELECT, DELETE,
UPDATE, and INSERT on
MERGE tables. You must have
SELECT, UPDATE, and
DELETE privileges on the
MyISAM tables that you map to a
MERGE table.
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 5.0.24.
If you DROP the MERGE table,
you are dropping only the MERGE specification.
The underlying tables are not affected.
To create a MERGE table, you must specify a
UNION=(
clause that indicates which list-of-tables)MyISAM tables you
want to use as one. You can optionally specify an
INSERT_METHOD option if you want inserts for the
MERGE table to take place in the first or last
table of the UNION list. Use a value of
FIRST or LAST to cause inserts
to be made in the first or last table, respectively. If you do not
specify an INSERT_METHOD option or if you specify
it with a value of NO, attempts to insert rows
into the MERGE table 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 and
TYPE is deprecated.
Note that the a column is indexed as a
PRIMARY KEY in the underlying
MyISAM tables, but not in the
MERGE table. There it is indexed but not as a
PRIMARY KEY because a MERGE
table cannot enforce uniqueness over the set of underlying tables.
In MySQL 5.0.36 and higher, when a table that is part of a
MERGE table is opened, the following checks are
applied before opening each table. If any table fails the
conformance checks, then the operation that triggered the opening of
the table will fail. The conformance checks applied to each table
are:
Table must have exactly the same amount of columns that
MERGE table has.
Column order in the MERGE table must match
the column order in the underlying tables.
Additionally, the specification for each column in the parent
MERGE table and the underlying table are
compared. For each column, MySQL checks:
Column type in the underlying table equals the column type
of MERGE table.
Column length in the underlying table equals the column
length of MERGE table.
Column of underlying table and column of
MERGE table can be
NULL.
Underlying table must have at least the same amount of keys that
merge table has. The underlying table may have morekeys than the
MERGE table, but cannot have less.
For each key:
Check if the key type of underlying table equals the key type of merge table.
Check if number of key parts (i.e. multiple columns within a compound key) in the underlying table key definition equals the number of key parts in merge table key definition.
For each key part:
Check if key part lengths are equal.
Check if key part types are equal.
Check if key part languages are equal.
Check if key part can be NULL.
After creating the MERGE table, you can issue
queries that operate on the group of tables as a whole:
mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
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=(...)
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 big read-only table based on
some criteria, and then put individual tables on different
disks. A MERGE table on this could be much
faster than using the big table.
Perform more efficient searches. If you know exactly what you
are looking for, you can search in just one of the split tables
for some queries and use a MERGE table for
others. You can even have many different
MERGE tables that use overlapping sets of
tables.
Perform more efficient repairs. It is easier to repair
individual tables that are mapped to a MERGE
table than to repair a single large table.
Instantly map many tables as one. A MERGE
table need not maintain an index of its own because it uses the
indexes of the individual tables. As a result,
MERGE table collections are
very fast to create or remap. (Note that
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 should instead create a MERGE
table on them on demand. This is much faster and saves a lot of
disk space.
Exceed the file size limit for the operating system. Each
MyISAM table is bound by this limit, but a
collection of MyISAM tables is not.
You can create an alias or synonym for a
MyISAM table by defining a
MERGE table that maps to that single table.
There should be no really notable performance impact from doing
this (only a couple of indirect calls and
memcpy() calls for each read).
The disadvantages of MERGE tables are:
You can use only identical MyISAM tables for
a MERGE table.
You cannot use a number of MyISAM features in
MERGE tables. For example, you cannot create
FULLTEXT indexes on MERGE
tables. (You can, of course, create FULLTEXT
indexes on the underlying MyISAM tables, but
you cannot search the MERGE table with a
full-text search.)
If the MERGE table is non-temporary, all
underlying MyISAM tables must be
non-temporary, too. If the MERGE table is
temporary, the MyISAM tables can be any mix
of temporary and non-temporary.
MERGE tables use more file descriptors. 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.)
Key reads are slower. When you read a key, the
MERGE storage engine needs to issue a read on
all underlying tables to check which one most closely matches
the given key. To read the next key, the
MERGE storage engine needs to search the read
buffers to find the next key. Only when one key buffer is used
up does the storage engine need to read the next key block. This
makes MERGE keys much slower on
eq_ref searches, but not much slower on
ref searches. See Section 7.2.1, “Optimizing Queries with EXPLAIN”,
for more information about eq_ref and
ref.
Additional resources
A forum dedicated to the MERGE storage engine
is available at http://forums.mysql.com/list.php?93.
The following are known problems with MERGE
tables:
If you use ALTER TABLE to change a
MERGE table to another storage engine, the
mapping to the underlying tables is lost. Instead, the rows
from the underlying MyISAM tables are
copied into the altered table, which then uses the specified
storage engine.
REPLACE does not work.
You cannot use REPAIR TABLE,
OPTIMIZE TABLE, DROP
TABLE, ALTER TABLE,
DELETE without a WHERE
clause, TRUNCATE TABLE, or ANALYZE
TABLE on any of the tables that are mapped into an
open MERGE table. If you do so, the
MERGE table may still refer to the original
table, which yields unexpected results. The easiest way to
work around this deficiency is to ensure that no
MERGE tables remain open by issuing a
FLUSH TABLES statement prior to performing
any of those operations.
The unexpected results include the possibility that the
operation on the MERGE table will report
table corruption. However, if this occurs after operations on
the underlying MyISAM tables such as those
listed in the previous paragraph (REPAIR
TABLE, OPTIMIZE TABLE, and so
forth), the corruption message is spurious. To deal with this,
issue a FLUSH TABLES statement after
modifying the MyISAM tables.
DROP TABLE on a table that is in use by a
MERGE table does not work on Windows
because the MERGE storage engine's table
mapping is hidden from the upper layer of MySQL. Windows does
not allow open files to be deleted, so you first must flush
all MERGE tables (with FLUSH
TABLES) or drop the MERGE table
before dropping the table.
A MERGE table cannot maintain uniqueness
constraints over the entire table. When you perform an
INSERT, the data goes into the first or
last MyISAM table (depending on the value
of the INSERT_METHOD option). MySQL ensures
that unique key values remain unique within that
MyISAM table, but not across all the tables
in the collection.
In MySQL 5.0.36 and later, the definition of the
MyISAM tables and the
MERGE table are checked when the tables are
accessed (for example, as part of a SELECT
or INSERT statement). The checks ensure
that the definitions of the tables and the parent
MERGE table definition match by comparing
column order, types, sizes and associated indexes. If there is
a difference between the tables then an error will be returned
and the statement will fail.
Because these checks take place when the tables are opened, any changes to the definition of a single, including column changes, ocolumn ordering and engine alterations will cause the statement to fail.
In MySQL 5.0.35 and earlier:
When you create or alter MERGE table,
there is no check to ensure that the underlying tables are
existing MyISAM tables and have
identical structures. When the MERGE
table is used, MySQL checks that the row length for all
mapped tables is equal, but this is not foolproof. If you
create a MERGE table from dissimilar
MyISAM tables, you are very likely to
run into strange problems.
Similarly, if you create a MERGE table
from non-MyISAM tables, or if you drop
an underlying table or alter it to be a
non-MyISAM table, no error for the
MERGE table occurs until later when you
attempt to use it.
Because the underlying MyISAM tables
need not exist when the MERGE table is
created, you can create the tables in any order, as long
as you do not use the MERGE table until
all of its underlying tables are in place. Also, if you
can ensure that a MERGE table will not
be used during a given period, you can perform maintenance
operations on the underlying tables, such as backing up or
restoring them, altering them, or dropping and recreating
them. It is not necessary to redefine the
MERGE table temporarily to exclude the
underlying tables while you are operating on them.
The order of indexes in the MERGE table and
its underlying tables should be the same. If you use
ALTER TABLE to add a
UNIQUE index to a table used in a
MERGE table, and then use ALTER
TABLE to add a non-unique index on the
MERGE table, the index ordering is
different for the tables if there was already a non-unique
index in the underlying table. (This happens because
ALTER TABLE puts UNIQUE
indexes before non-unique 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:
' it
generally indicates that some of the base tables are not using
the MyISAM storage engine. Confirm that all tables are MyISAM.
mm.MRG' (errno: 2)
There is a limit of 232
(~4.295E+09)) rows to a MERGE table, just
as there is with a MyISAM, it is therefore
not possible to merge multiple MyISAM
tables that exceed this limitation. However, you build MySQL
with the --with-big-tables option then the
row limitation is increased to
(232)2
(1.844E+19) rows. See Section 2.4.14.2, “Typical configure Options”.
Beginning with MySQL 5.0.4 all standard binaries are built
with this option.
The MERGE storage engine does not support
INSERT DELAYED statements.
As of MySQL 5.0.44, if a MERGE table cannot be
opened or used because of a problem with an underlying table,
CHECK TABLE displays information about which
table caused the problem.
The MEMORY storage engine creates tables with
contents that are stored in memory. Formerly, these were known as
HEAP tables. MEMORY is the
preferred term, although HEAP remains supported
for backward compatibility.
Each MEMORY table is associated with one disk
file. The filename begins with the table name and has an extension
of .frm to indicate that it stores the table
definition.
To specify explicitly that you want to create a
MEMORY table, indicate that with an
ENGINE table option:
CREATE TABLE t (i INT) ENGINE = MEMORY;
The older term TYPE is supported as a synonym for
ENGINE for backward compatibility, but
ENGINE is the preferred term and
TYPE is deprecated.
As indicated by the name, MEMORY tables are
stored in memory. They use hash indexes by default, which makes them
very fast, and very useful for creating temporary tables. However,
when the server shuts down, all rows stored in
MEMORY tables are lost. The tables themselves
continue to exist because their definitions are stored in
.frm files on disk, but they are empty when the
server restarts.
This example shows how you might create, use, and remove a
MEMORY table:
mysql>CREATE TABLE test ENGINE=MEMORY->SELECT ip,SUM(downloads) AS down->FROM log_table GROUP BY ip;mysql>SELECT COUNT(ip),AVG(down) FROM test;mysql>DROP TABLE test;
MEMORY tables have the following characteristics:
Space for MEMORY tables is allocated in small
blocks. Tables use 100% dynamic hashing for inserts. No overflow
area or extra key space is needed. No extra space is needed for
free lists. Deleted rows are put in a linked list and are reused
when you insert new data into the table.
MEMORY tables also have none of the problems
commonly associated with deletes plus inserts in hashed tables.
MEMORY tables can have up to 32 indexes per
table, 16 columns per index and a maximum key length of 500
bytes.
The MEMORY storage engine implements both
HASH and BTREE indexes.
You can specify one or the other for a given index by adding a
USING clause as shown here:
CREATE TABLE lookup
(id INT, INDEX USING HASH (id))
ENGINE = MEMORY;
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
General characteristics of B-tree and hash indexes are described in Section 7.4.5, “How MySQL Uses Indexes”.
You can have non-unique keys in a MEMORY
table. (This is an uncommon feature for implementations of hash
indexes.)
If you have a hash index on a MEMORY table
that has a high degree of key duplication (many index entries
containing the same value), updates to the table that affect key
values and all deletes are significantly slower. The degree of
this slowdown is proportional to the degree of duplication (or,
inversely proportional to the index cardinality). You can use a
BTREE index to avoid this problem.
Columns that are indexed can contain NULL
values.
MEMORY tables use a fixed-length row storage
format.
MEMORY tables cannot contain
BLOB or TEXT columns.
MEMORY includes support for
AUTO_INCREMENT columns.
You can use INSERT DELAYED with
MEMORY tables. See
Section 13.2.4.2, “INSERT DELAYED Syntax”.
MEMORY tables are shared among all clients
(just like any other non-TEMPORARY table).
MEMORY table contents are stored in memory,
which is a property that MEMORY tables share
with internal 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 tables are:
If an internal table becomes too large, the server
automatically converts it to an on-disk table. The size
limit is determined by the value of the
tmp_table_size system variable.
MEMORY tables are never converted to disk
tables. To ensure that you don't accidentally do anything
foolish, you can set the
max_heap_table_size system variable to
impose a maximum size on MEMORY tables.
For individual tables, you can also specify a
MAX_ROWS table option in the
CREATE TABLE statement.
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,
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.2.2, “Command Options”, and
Section 13.2.5, “LOAD DATA INFILE Syntax”.
If you are using replication, the master server's
MEMORY tables become empty when it is shut
down and restarted. However, a slave is not aware that these
tables have become empty, so it returns out-of-date content if
you select data from them. When a MEMORY
table is used on the master for the first time since the master
was started, a DELETE statement is written to
the master's binary log automatically, thus synchronizing the
slave to the master again. Note that even with this strategy,
the slave still has outdated data in the table during the
interval between the master's restart and its first use of the
table. However, if you use the --init-file
option to populate the MEMORY table on the
master at startup, it ensures that this time interval is zero.
The memory needed for one row in a MEMORY
table is calculated using the following expression:
SUM_OVER_ALL_BTREE_KEYS(max_length_of_key+ sizeof(char*) × 4) + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2) + ALIGN(length_of_row+1, sizeof(char*))
ALIGN() represents a round-up factor to cause
the row length to be an exact multiple of the
char pointer size.
sizeof(char*) is 4 on 32-bit machines and 8
on 64-bit machines.
Additional resources
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 a non-patched version of BDB with MySQL.
We at MySQL AB work in close cooperation with Sleepycat to keep the quality of the MySQL/BDB interface high. (Even though Berkeley DB is in itself very tested and reliable, the MySQL interface is still considered gamma quality. We continue to improve and optimize it.)
When it comes to support for any problems involving
BDB tables, we are committed to helping our users
locate the problem and create reproducible test cases. Any such test
case is forwarded to Sleepycat, which in turn helps us find and fix
the problem. As this is a two-stage operation, any problems with
BDB tables may take a little longer for us to fix
than for other storage engines. However, we anticipate no
significant difficulties with this procedure because the Berkeley DB
code itself is used in many applications other than MySQL.
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
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
Note: 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.
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 MySQL
5.0 distribution, change location into its top-level
directory, and run this command:
shell> ./configure --with-berkeley-db [other-options]
For more information, Section 2.4.13, “Installing MySQL from tar.gz Packages on Other
Unix-Like Systems”, and
Section 2.4.14, “MySQL Installation Using a Source Distribution”.
The following options to mysqld can be used to
change the behavior of the BDB storage engine.
For more information, see Section 5.2.2, “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; 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.
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.4.15.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.11.3, “The Binary Log”.
See also Section 5.2.3, “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 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 13.4.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.
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's 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
allow 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_nameBDB 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 13.5.2.1, “ANALYZE TABLE Syntax”, and
Section 13.5.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 5.9.1, “Database Backups”.
Warning: 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 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 allows NULL values has a
unique index, only a single NULL value is
allowed. This differs from other storage engines, which allow
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
NNNNNNNNNNBDB 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. Until the
problem is fixed, we recommend that you not drop
BDB tables except while autocommit mode is
enabled.
The EXAMPLE storage engine is a stub engine that
does nothing. Its purpose is to serve as an example in the MySQL
source code that illustrates how to begin writing new storage
engines. As such, it is primarily of interest to developers.
The EXAMPLE storage engine is included in MySQL
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 FEDERATED storage engine is available
beginning with MySQL 5.0.3. It is a storage engine that accesses
data in tables of remote databases rather than in local tables.
The FEDERATED storage engine is included in MySQL
binary distributions. To enable this storage engine if you build
MySQL from source, invoke configure with the
--with-federated-storage-engine option.
To examine the source for the FEDERATED engine,
look in the sql directory of a source
distribution for MySQL 5.0.3 or newer.
Additional resources
A forum dedicated to the FEDERATED storage
engine is available at http://forums.mysql.com/list.php?105.
When you create a FEDERATED 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, because the actual data is
in a remote table. This differs from the way that storage engines
for local tables work.
For local database tables, data files are local. For example, if
you create a MyISAM table named
users, the MyISAM handler
creates a data file named users.MYD. A handler
for local tables reads, inserts, deletes, and updates data in
local data files, and rows are stored in a format particular to
the handler. To read rows, the handler must parse data into
columns. To write rows, column values must be converted to the row
format used by the handler and written to the local data file.
With the MySQL FEDERATED storage engine, there
are no local data files for a table (for example, there is no
.MYD file). Instead, a remote database stores
the data that normally would be in the table. The local server
connects to a remote server, and uses the MySQL client API to
read, delete, update, and insert data in the remote table. Data
retrieval is initiated via a SELECT * FROM
SQL statement. To
read the result, rows are fetched one at a time by using the
tbl_namemysql_fetch_row() C API function, and then
converting the columns in the SELECT result set
to the format that the FEDERATED handler
expects.
The flow of information is as follows:
SQL calls issued locally
MySQL handler API (data in handler format)
MySQL client API (data converted to SQL calls)
Remote database -> MySQL client API
Convert result sets (if any) to handler format
Handler API -> Result rows or rows-affected count to local
The procedure for using FEDERATED tables is
very simple. Normally, you have two servers running, either both
on the same host or on different hosts. (It is possible for a
FEDERATED table to use another table that is
managed by the same server, although there is little point in
doing so.)
First, you must have a table on the remote server that you want to
access by using a FEDERATED table. Suppose that
the remote table is in the federated database
and is defined like this:
CREATE TABLE test_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;
The example uses a MyISAM table, but the table
could use any storage engine.
Next, create a FEDERATED table on the local
server for accessing the remote table:
CREATE TABLE federated_table (
id INT(20) NOT NULL AUTO_INCREMENT,
name VARCHAR(32) NOT NULL DEFAULT '',
other INT(20) NOT NULL DEFAULT '0',
PRIMARY KEY (id),
INDEX name (name),
INDEX other_key (other)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://root@remote_host:9306/federated/test_table';
(Before MySQL 5.0.13, use COMMENT rather than
CONNECTION.)
The structure of this table must be exactly the same as that of
the remote table, except that the ENGINE table
option should be FEDERATED and the
CONNECTION table option is a connection string
that indicates to the FEDERATED engine how to
connect to the remote server.
The FEDERATED engine creates only the
test_table.frm file in the
federated database.
The remote host information indicates the remote server to which
your local server connects, and the database and table information
indicates which remote table to use as the data source. In this
example, the remote server is indicated to be running as
remote_host on port 9306, so there must be a
MySQL server running on the remote host and listening to port
9306.
The general form of the connection string in the
CONNECTION option is as follows:
scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name
Only mysql is supported as the
scheme value at this point; the
password and port number are optional.
Here are some example connection strings:
CONNECTION='mysql://username:password@hostname:port/database/tablename' CONNECTION='mysql://username@hostname/database/tablename' CONNECTION='mysql://username:password@hostname/database/tablename'
The use of CONNECTION for specifying the
connection string is non-optimal and is likely to change in
future. Keep this in mind for applications that use
FEDERATED tables. Such applications are likely
to need modification if the format for specifying connection
information changes.
Because any password given in the connection string is stored as
plain text, it can be seen by any user who can use SHOW
CREATE TABLE or SHOW TABLE STATUS for
the FEDERATED table, or query the
TABLES table in the
INFORMATION_SCHEMA database.
The following items indicate features that the
FEDERATED storage engine does and does not
support:
In the first version, the remote server must be a MySQL
server. Support by FEDERATED for other
database engines may be added in the future.
The remote table that a FEDERATED table
points to must exist before you try to
access the table through the FEDERATED
table.
It is possible for one FEDERATED table to
point to another, but you must be careful not to create a
loop.
There is no support for transactions.
A FEDERATED table does not support indexes
per-se, since the access to the table is handled remotely, it
is the remote table that supports the indexes. Care should be
taken when creating a FEDERATED table since
the index definition from an equivalent
MyISAM or other table may not be supported.
For example, creating a FEDERATED table
with an index prefix on VARCHAR,
TEXT or BLOB columns
will fail. The following definition in
MyISAM is valid:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=MYISAM;
The key prefix in this example is incompatible with the
FEDERATED engine, and the equivalent
statement will fail:
CREATE TABLE `T1`(`A` VARCHAR(100),UNIQUE KEY(`A`(30))) ENGINE=FEDERATED CONNECTION='MYSQL://127.0.0.1:3306/TEST/T1';
If possible, you should try to separate the column and index definition when creating tables on both the remote server and the local server to avoid these index issues.
Performance on a FEDERATED table when
performing bulk inserts (for example, on a INSERT
INTO ... SELECT ... statement) is slower than with
other table types because each selected row is treated as an
individual INSERT statement on the
federated table.
There is no way for the FEDERATED engine to
know if the remote table has changed. The reason for this is
that this table must work like a data file that would never be
written to by anything other than the database. The integrity
of the data in the local table could be breached if there was
any change to the remote database.
The FEDERATED storage engine supports
SELECT, INSERT,
UPDATE, DELETE, and
indexes. It does not support ALTER TABLE,
or any Data Definition Language statements other than
DROP TABLE. The current implementation does
not use Prepared statements.
Any DROP TABLE statement issued against a
FEDERATED table will only drop the local
table, not the remote table.
The implementation uses SELECT,
INSERT, UPDATE, and
DELETE, but not HANDLER.
FEDERATED tables do not work with the query
cache.
Some of these limitations may be lifted in future versions of the
FEDERATED handler.
The ARCHIVE storage engine 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). Beginning with MySQL 5.0.15, the engine also
supports CHECK TABLE. There are several types of
insertions that are used:
An INSERT statement just pushes rows into a
compression buffer, and that buffer flushes as necessary. The
insertion into the buffer is protected by a lock. A
SELECT forces a flush to occur, unless the
only insertions that have come in were INSERT
DELAYED (those flush as necessary). See
Section 13.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 13.5.2.5, “OPTIMIZE TABLE Syntax”,
Section 13.5.2.6, “REPAIR TABLE Syntax”, and
Section 13.5.4.24, “SHOW TABLE STATUS Syntax”.
Additional resources
A forum dedicated to the ARCHIVE storage
engine is available at http://forums.mysql.com/list.php?112.
The CSV storage 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
binary distributions (except on Windows). 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, c CHAR(10)) 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 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
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. For example, 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 6.8, “Replication Startup Options”.) This filtered log is
provided to the slave.
The dummy process does not actually store any data, so there is little processing overhead incurred by running the additional mysqld process on the replication master host. This type of setup can be repeated with additional replication slaves.
INSERT triggers for BLACKHOLE
tables work as expected. However, because the
BLACKHOLE table does not actually store any data,
UPDATE and DELETE triggers are
not activated: The FOR EACH ROW clause in the
trigger definition does not apply because there are no rows.
Other possible uses for the BLACKHOLE storage
engine include:
Verification of dump file syntax.
Measurement of the overhead from binary logging, by comparing
performance using BLACKHOLE with and without
binary logging enabled.
BLACKHOLE is essentially a
“no-op” storage engine, so it could be used for
finding performance bottlenecks not related to the storage
engine itself.
Alden Hosting offers private JVM (Java Virtual Machine), Java Server Pages (JSP), Servlets, and Servlets Manager with our Web Hosting Plans WEB 4 PLAN and WEB 5 PLAN , WEB 6 PLAN .
At Alden Hosting we eat and breathe Java! We are the industry leader in providing affordable, quality and efficient Java web hosting in the shared hosting marketplace. All our sites run on our Java hosing platform configured for optimum performance using Java, Tomcat, MySQL, Apache and web application frameworks such as Struts, Hibernate, Cocoon, Ant, etc.
We offer only one type of Java hosting - Private Tomcat. Hosting accounts on the Private Tomcat environment get their very own Tomcat server. You can start and re-start your entire Tomcat server yourself.
 |
|
http://alden-servlet-Hosting.com
JSP at alden-servlet-Hosting.com
Servlets at alden-servlet-Hosting.com
Servlet at alden-servlet-Hosting.com
Tomcat at alden-servlet-Hosting.com
MySQL at alden-servlet-Hosting.com
Java at alden-servlet-Hosting.com
sFTP at alden-servlet-Hosting.com
http://alden-tomcat-Hosting.com
JSP at alden-tomcat-Hosting.com
Servlets at alden-tomcat-Hosting.com
Servlet at alden-tomcat-Hosting.com
Tomcat at alden-tomcat-Hosting.com
MySQL at alden-tomcat-Hosting.com
Java at alden-tomcat-Hosting.com
sFTP at alden-tomcat-Hosting.com
http://alden-sftp-Hosting.com
JSP at alden-sftp-Hosting.com
Servlets at alden-sftp-Hosting.com
Servlet at alden-sftp-Hosting.com
Tomcat at alden-sftp-Hosting.com
MySQL at alden-sftp-Hosting.com
Java at alden-sftp-Hosting.com
sFTP at alden-sftp-Hosting.com
http://alden-jsp-Hosting.com
JSP at alden-jsp-Hosting.com
Servlets at alden-jsp-Hosting.com
Servlet at alden-jsp-Hosting.com
Tomcat at alden-jsp-Hosting.com
MySQL at alden-jsp-Hosting.com
Java at alden-jsp-Hosting.com
sFTP at alden-jsp-Hosting.com
http://alden-java-Hosting.com
JSp at alden-java-Hosting.com
Servlets at alden-java-Hosting.com
Servlet at alden-java-Hosting.com
Tomcat at alden-java-Hosting.com
MySQL at alden-java-Hosting.com
Java at alden-java-Hosting.com
sFTP at alden-java-Hosting.com
JSP
Servlets
Tomcat
mysql
Java
JSP
Servlets
Tomcat
mysql
Java
JSP
Servlets
Tomcat
mysql
Java
JSP
Servlets
Tomcat
mysql
Java
JSP at JSP.aldenWEBhosting.com
Servlets at servlets.aldenWEBhosting.com
Tomcat at Tomcat.aldenWEBhosting.com
mysql at mysql.aldenWEBhosting.com
Java at Java.aldenWEBhosting.com
Web Hosts Portal
Web Links
Web Links JSP
Web Links servlet
Tomcat Docs
Web Links
Web Links JSP
Web Links servlet
Web Hosting
Tomcat Docs
JSP Solutions Web Links
JSP Solutions Web Hosting
Servlets Solutions Web Links
Servlets Solutions Web Hosting
Web Links
Web Links
.
.
.
.
.
.
.
.
.
.
jsp hosting
servlets hosting
web hosting
web sites designed
cheap web hosting
web site hosting
myspace web hosting
