[+/-]
InnoDB
Contact InformationInnoDB
ConfigurationInnoDB
Startup Options and System VariablesInnoDB
TablesInnoDB
Data and Log
FilesInnoDB
DatabaseInnoDB
Database to Another MachineInnoDB
Transaction Model and LockingInnoDB
Multi-VersioningInnoDB
Table and Index StructuresInnoDB
Disk I/O and File Space ManagementInnoDB
Error HandlingInnoDB
Performance Tuning and TroubleshootingInnoDB
Tables
InnoDB
is a transaction-safe (ACID compliant)
storage engine for MySQL that has commit, rollback, and
crash-recovery capabilities to protect user data.
InnoDB
row-level locking (without escalation to
coarser granularity locks) and Oracle-style consistent nonlocking
reads increase multi-user concurrency and performance.
InnoDB
stores user data in clustered indexes to
reduce I/O for common queries based on primary keys. To maintain
data integrity, InnoDB
also supports
FOREIGN KEY
referential-integrity constraints.
You can freely mix InnoDB
tables with tables from
other MySQL storage engines, even within the same statement.
To determine whether your server supports InnoDB
use the SHOW ENGINES
statement. See
Section 12.5.5.17, “SHOW ENGINES
Syntax”.
Table 13.3. InnoDB
Storage Engine
Features
Storage limits | 64TB | Transactions | Yes | Locking granularity | Row |
MVCC | Yes | Geospatial datatype support | Yes | Geospatial indexing support | No |
B-tree indexes | Yes | Hash indexes | No | Full-text search indexes | No |
Clustered indexes | Yes | Data caches | Yes | Index caches | Yes |
Compressed data | Yes[a] | Encrypted data[b] | Yes | Cluster database support | No |
Replication support[c] | Yes | Foreign key support | Yes | Backup / point-in-time recovery[d] | Yes |
Query cache support | Yes | Update statistics for data dictionary | Yes | ||
[a] Compressed InnoDB tables are supported only by InnoDB Plugin. [b] Implemented in the server (via encryption functions), rather than in the storage engine. [c] Implemented in the server, rather than in the storage engine [d] Implemented in the server, rather than in the storage engine |
InnoDB
has been designed for maximum performance
when processing large data volumes. Its CPU efficiency is probably
not matched by any other disk-based relational database engine.
The InnoDB
storage engine maintains its own
buffer pool for caching data and indexes in main memory.
InnoDB
stores its tables and indexes in a
tablespace, which may consist of several files (or raw disk
partitions). This is different from, for example,
MyISAM
tables where each table is stored using
separate files. InnoDB
tables can be very large
even on operating systems where file size is limited to 2GB.
The Windows Essentials installer makes InnoDB
the
MySQL default storage engine on Windows, if the server being
installed supports InnoDB
.
InnoDB
is used in production at numerous large
database sites requiring high performance. The famous Internet news
site Slashdot.org runs on InnoDB
. Mytrix, Inc.
stores more than 1TB of data in InnoDB
, and
another site handles an average load of 800 inserts/updates per
second in InnoDB
.
InnoDB
is published under the same GNU GPL
License Version 2 (of June 1991) as MySQL. For more information on
MySQL licensing, see
http://www.mysql.com/company/legal/licensing/.
Additional resources
A forum dedicated to the InnoDB
storage
engine is available at http://forums.mysql.com/list.php?22.
Innobase Oy also hosts several forums, available at http://forums.innodb.com.
At the 2008 MySQL User Conference, Innobase announced
availability of an InnoDB
Plugin for MySQL.
This plugin for MySQL exploits the “pluggable storage
engine” architecture of MySQL, to permit users to replace
the “built-in” version of InnoDB
in MySQL 5.1.
As of MySQL 5.1.38, the InnoDB Plugin
is
included in MySQL 5.1 releases, in addition to the
built-in version of InnoDB
that has been
included in previous releases. This version of the
InnoDB Plugin
is 1.0.4 and is considered of
Beta quality.
The InnoDB Plugin
offers new features,
improved performance and scalability, enhanced reliability and
new capabilities for flexibility and ease of use. Among the
features of the InnoDB Plugin
are “Fast
index creation,” table and index compression, file format
management, new INFORMATION_SCHEMA
tables,
capacity tuning, multiple background I/O threads, and group
commit.
For information about these features, see the InnoDB
Plugin
Manual at
http://www.innodb.com/products/innodb_plugin/plugin-documentation.
For general information about using InnoDB
in
MySQL, see Section 13.6, “The InnoDB
Storage Engine”.
The InnoDB Plugin
is included in source and
binary distributions, except RHEL3, RHEL4, SuSE 9 (x86, x86_64,
ia64), and generic Linux RPM packages.
To use the InnoDB Plugin
, you must disable
the built-in version of InnoDB
that is also
included and instruct the server to use InnoDB
Plugin
instead. To accomplish this, use the following
lines in your my.cnf
file:
[mysqld] ignore-builtin-innodb plugin-load=innodb=ha_innodb_plugin.so
For the plugin-load
option,
innodb
is the name to associate with the
plugin and ha_innodb_plugin.so
is the name
of the shared object library that contains the plugin code. The
extension of .so
applies for Unix (and
similar) systems. For HP-UX on HPPA (11.11) or Windows, the
extension should be .sl
or
.dll
, respectively, rather than
.so
.
If the server has problems finding the plugin when it starts up,
specify the pathname to the plugin directory. For example, if
plugins are located in the lib/mysql/plugin
directory under the MySQL installation directory and you have
installed MySQL at /usr/local/mysql
, use
these lines in your my.cnf
file:
[mysqld] ignore-builtin-innodb plugin-load=innodb=ha_innodb_plugin.so plugin_dir=/usr/local/mysql/lib/mysql/plugin
The previous examples show how to activate the storage engine
part of InnoDB Plugin,
but the plugin also
implements several InnoDB-related
INFORMATION_SCHEMA
tables. (For information
about these tables, see
http://www.innodb.com/doc/innodb_plugin-1.0/innodb-information-schema.html.)
To enable these tables, include additional
pairs in the value of the
name
=library
plugin-load
option:
[mysqld] ignore-builtin-innodb plugin-load=innodb=ha_innodb_plugin.so ;innodb_trx=ha_innodb_plugin.so ;innodb_locks=ha_innodb_plugin.so ;innodb_cmp=ha_innodb_plugin.so ;innodb_cmp_reset=ha_innodb_plugin.so ;innodb_cmpmem=ha_innodb_plugin.so ;innodb_cmpmem_reset=ha_innodb_plugin.so
The plugin-load
option value as
shown here is formatted on multiple lines for display purposes
but should be written in my.cnf
using a
single line without spaces in the option value. On Windows,
substitute .dll
for each instance of the
.so
extension.
After the server starts up, verify that InnoDB
Plugin
has been loaded by using the
SHOW PLUGINS
statement. For
example, if you have loaded the storage engine and the
INFORMATION_SCHEMA
tables, the output should
include lines similar to these:
mysql> SHOW PLUGINS;
+---------------------+--------+--------------------+---------------------...
| Name | Status | Type | Library ...
+---------------------+--------+--------------------+---------------------...
...
| InnoDB | ACTIVE | STORAGE ENGINE | ha_innodb_plugin.so ...
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | ha_innodb_plugin.so ...
+---------------------+--------+--------------------+---------------------...
If you build MySQL from a source distribution, InnoDB
Plugin
is one of the storage engines that is built by
default. Build MySQL the way you normally do; for example, by
using the instructions at Section 2.10, “MySQL Installation Using a Source Distribution”.
After the build completes, you should find the plugin shared
object file under the storage/innodb_plugin
directory, and make install should install it
in the plugin directory. Configure MySQL to use InnoDB
Plugin
as described earlier for binary distributions.
If you use gcc, InnoDB
Plugin
cannot be compiled with gcc
3.x; you must use gcc 4.x instead.
InnoDB Hot Backup enables you to back up a
running MySQL database, including InnoDB
and
MyISAM
tables, with minimal disruption to
operations while producing a consistent snapshot of the
database. When InnoDB Hot Backup is copying
InnoDB
tables, reads and writes to both
InnoDB
and MyISAM
tables
can continue. During the copying of MyISAM
tables, reads (but not writes) to those tables are permitted. In
addition, InnoDB Hot Backup supports creating
compressed backup files, and performing backups of subsets of
InnoDB
tables. In conjunction with MySQL’s
binary log, users can perform point-in-time recovery.
InnoDB Hot Backup is commercially licensed by
Innobase Oy. For a more complete description of InnoDB
Hot Backup, see
http://www.innodb.com/hot-backup/features/ or
download the documentation from
http://www.innodb.com/doc/hot_backup/manual.html.
You can order trial, term, and perpetual licenses from Innobase
at http://www.innodb.com/hot-backup/order/.
User Comments
Add your own comment.