These terms are commonly used in information about the InnoDB storage engine.
A
An acronym standing for atomicity, consistency, isolation, and durability. These properties are all desirable in a database system, and are all closely tied to the notion of a transaction. The transactional features of InnoDB adhere to the ACID principles.
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
The database remains in a consistent state at all times -- after each commit or rollback, and while transactions are in progress.
Transactions are protected (isolated) from each other while they are in progress; they cannot interfere with each other or see each other's uncommitted data. This isolation is achieved through the notion of locking. (Expert users can adjust the isolation level, trading off less protection in favor of increased performance, when they can be sure that the transactions really do not interfere with each other.)
The results of transactions are durable: once a commit operation succeeds, the changes made by that transaction are safe from power failures, system crashes, race conditions, or other potential dangers that many non-database applications are vulnerable to. Durability typically involves writing to disk storage, with a certain amount of redundancy to protect against power failures or software crashes during write operations. (In InnoDB, the doublewrite buffer assists with durability.)
See also commit.
See also doublewrite buffer.
See also isolation level.
See also locking.
See also rollback.
See also transaction.
An algorithm, first introduced in the InnoDB Plugin, that smooths out the I/O overhead introduced by checkpoints. Instead of flushing all modified pages from the buffer pool to the data files at once, the InnoDB storage engine periodically flushes small sets of modified pages. The adaptive flushing algorithm extends this process by estimating the optimal rate to perform these periodic flushes, based on the rate of flushing and how fast redo information is generated.
See also buffer pool.
See also checkpoint.
See also data files.
See also flush.
See also plugin.
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 a hash index, it builds one automatically.
The hash index is always built based on an existing B-tree index on the table. InnoDB can build a hash index on a prefix of any length of the key defined for the B-tree, depending on the pattern of searches that InnoDB observes for the B-tree index. A hash index can be partial; the whole B-tree index does not need to be cached in the buffer pool. InnoDB builds hash indexes on demand for those pages of the index that are often accessed.
In a sense, the adaptive hash index configures InnoDB at runtime to take advantage of ample main memory, coming closer to the architecture of main-memory databases.
See also B-tree.
Acronym for asynchronous I/O. You might see this acronym in InnoDB messages or keywords.
See also asynchronous I/O.
The code name for the original InnoDB file format. It supports the redundant and compact row formats, but not the newer dynamic and compressed row formats available in the Barracuda file format.
You can select the file format to use through the
innodb_file_format option.
The default InnoDB file format switches to Barracuda in MySQL 5.5.5.
See also Barracuda.
See also compact row format.
See also compressed row format.
See also dynamic row format.
See also file format.
See also ibdata file.
See also innodb_file_format.
See also redundant row format.
See also row format.
A set of functions or procedures. An API provides a stable set of names and types for functions, procedures, parameters, and return values.
When a backup produced by the MySQL
Enterprise Backup product does not include the most
recent changes that occurred while the backup was underway, the
process of updating the backup files to include those changes is
known as the apply step. It is
specified by the --apply-log option of the
ibbackup or innobackup
commands.
Before the changes are applied, we refer to the files as a raw backup. After the changes are applied, we refer to the files as a prepared backup. The changes are recorded in the ibbackup_logfile file; once the apply step is finished, this file is no longer necessary.
See also hot backup.
See also ibbackup_logfile.
See also MySQL Enterprise Backup.
See also prepared backup.
See also raw backup.
Archive storage engine metadata. Files with this extension are
always included in backups produced by the
innobackup command of the
MySQL Enterprise Backup
product.
See also .ARZ file.
See also innobackup command.
See also MySQL Enterprise Backup.
Archive storage engine data. Files with this extension are
always included in backups produced by the
innobackup command of the
MySQL Enterprise Backup
product.
See also .ARM file.
See also innobackup command.
See also MySQL Enterprise Backup.
A type of I/O operation that allows other processing to proceed before the I/O is completed. Also known as non-blocking I/O and abbreviated as AIO. InnoDB uses this type of I/O for certain operations that can run in parallel without affecting the reliability of the database, such as reading pages into the buffer pool that have not actually been requested, but might be needed soon.
Historically, InnoDB has used asynchronous I/O on Windows
systems only. Starting with the InnoDB Plugin 1.1, InnoDB uses
asynchronous I/O on Linux systems. This change introduces a
dependency on libaio. On other Unix-like
systems, InnoDB uses synchronous I/O only.
See also buffer pool.
See also non-blocking I/O.
A property of a table column (specified by the
AUTO_INCREMENT keyword) that automatically
adds an ascending sequence of values in the column. InnoDB
supports auto-increment only for primary
key columns.
It saves work for the developer, not to have to produce new unique values when inserting new rows. It provides useful information for the query optimizer, because the column is known to be not null and with unique values. The values from such a column can be used as lookup keys in various contexts, and because they are auto-generated there is no reason to ever change them; for this reason, primary key columns are often specified as auto-incrementing.
Auto-increment columns can be problematic with statement-based
replication, because replaying the statements on a slave might
not produce the same set of column values as on the master, due
to timing issues. When you have an auto-incrementing primary
key, you can use statement-based replication only with the
setting innodb_autoinc_lock_mode=1. If you
have innodb_autoinc_lock_mode=2, which allows
higher concurrency for insert operations, use
row-based replication rather
than statement-based
replication. The setting
innodb_autoinc_lock_mode=0 is the previous
(traditional) default setting and should not be used except for
compatibility purposes.
See also auto-increment locking.
See also innodb_autoinc_lock_mode.
See also primary key.
See also row-based replication.
See also statement-based replication.
The convenience of an
auto-increment primary key
involves some tradeoff with concurrency. In the simplest case,
if one transaction is inserting values into the table, any other
transactions must wait to do their own inserts into that table,
so that rows inserted by the first transaction receive
consecutive primary key values. InnoDB includes optimizations,
and the innodb_autoinc_lock_mode option, so
that you can choose how to trade off between predictable
sequences of auto-increment values and maximum
concurrency for insert
operations.
See also auto-increment.
See also concurrency.
See also innodb_autoinc_lock_mode.
A setting that causes a commit operation after each SQL statement. This mode is not usually recommended for working with InnoDB tables, where transactions typically span several statements. It is more appropriate for working with MyISAM tables where transactions are not applicable, or in specialized situations with InnoDB to minimize overhead from locking and generation of undo data.
See also commit.
See also locking.
See also SQL.
See also transaction.
See also undo.
B
A tree data structure that is popular for use in database
indexes. The structure is kept sorted at all times, enabling
fast lookup for exact matches (equals operator) and ranges (for
example, greater than, less than, and BETWEEN
operators). This type of index is available for most storage
engines, such as InnoDB and MyISAM.
Because B-tree nodes can have many children, a B-tree is not the same as a binary tree, which is limited to 2 children per node.
Contrast with hash index, which is only available in the MEMORY storage engine. The MEMORY storage engine can also use B-tree indexes, and you should choose B-tree indexes for MEMORY tables if some queries use range operators.
See also hash_index.
Identifiers within MySQL SQL statements must be quoted using the
backtick character (`) if they contain
special characters or reserved words. For example, to refer to a
table named FOO#BAR or a column named
SELECT, you would specify the identifiers as
`FOO#BAR` and `SELECT`.
Since the backticks provide an extra level of safety, they are
used extensively in program-generated SQL statements, where the
identifier names might not be known in advance.
Many other database systems use double quotation marks
(") around such special names. For
portability, you can enable ANSI_QUOTES mode
in MySQL and use double quotation marks instead of backticks to
qualify identifier names.
See also SQL.
The code name for an InnoDB file
format that supports compression for table data. This
file format was first introduced in the InnoDB Plugin. It
supports the dynamic and
compressed row formats. You can
select it through the innodb_file_format
option.
The default InnoDB file format switches to Barracuda in MySQL 5.5.5.
The MySQL Enterprise Backup product version 3.5 and above supports backing up tablespaces that use the Barracuda file format.
See also Antelope.
See also compact row format.
See also compressed row format.
See also dynamic row format.
See also file format.
See also ibdata file.
See also innodb_file_format.
See also row format.
An early stage in the life of a software product, when it is available only for evaluation, typically without a definite release number or a number less than 1. InnoDB does not use the beta designation, preferring an early adopter phase that can extend over several point releases, leading to a GA release.
See also early adopter.
See also GA.
A file containing a record of all statements that attempt to change table data. These statements can be replayed to bring slave servers up to date in a replication scenario, or to bring a database up to date after restoring table data from a backup. The binary logging feature can be turned on and off, although we recommend always enabling it if you use replication or perform backups.
You can examine the contents of the binary log, or replay those
statements during replication or recovery, by using the
mysqlbinlog command.
For the MySQL Enterprise Backup
product, the file name of the binary log and the current
position within the file are important details. To record this
information for the master server when taking a backup in a
replication context, you can specify the
--slave-info option.
Prior to MySQL 5.0, a similar capability was available, known as the update log. In MySQL 5.0 and higher, the binary log replaces the update log.
See also binlog.
An informal name for the binary log file. For example, you might see this abbreviation used in certain messages or forum discussions.
See also binary log.
A portion of a system that is constrained in size or capacity, that has the effect of limiting overall throughput. For example, a memory area might be smaller than necessary; access to a single required resource might prevent multiple CPU cores from running simultaneously; or waiting for disk I/O to complete might prevent the CPU from running at full capacity.
See also buffer pool.
See also concurrency.
A memory or disk area used for temporary storage. Data is buffered in memory so that it can be written to disk efficiently, with a few large I/O operations rather than many small ones. Data is buffered on disk for greater reliability, so that it can be recovered even when a crash or other failure occurs at the worst possible time. The main types of buffers used by InnoDB are the buffer pool, the doublewrite buffer, and the insert buffer.
See also buffer pool.
See also doublewrite buffer.
See also insert buffer.
The memory area that holds cached InnoDB data, read from both tables and indexes. For efficiency of high-volume read operations, the buffer pool is divided into pages that can potentially hold multiple rows. For efficiency of cache management, the buffer pool is implemented as a linked list of pages, so that data that is rarely used can be aged out of the cache, using a variation of the LRU algorithm. On systems with large memory, you can improve concurrency by dividing the buffer pool into multiple instances.
See also LRU.
See also page.
The built-in InnoDB storage engine within MySQL is the original form of distribution for the storage engine. Contrast with the InnoDB Plugin. Starting with MySQL 5.5, the InnoDB Plugin is merged back into the MySQL code base as the built-in InnoDB storage engine (known as InnoDB 1.1).
See also plugin.
The relationships and sequences of actions that form the basis of business software, used to run a commercial company. Sometimes these rules are dictated by law, other times by company policy. Careful planning ensures that the relationships encoded and enforced by the database, and the actions performed through application logic, accurately reflect the real policies of the company and can handle real-life situations.
For example, an employee leaving a company might trigger a sequence of actions from the human resources department. The human resources database might also need the flexibility to represent data about a person who has been hired, but not yet started work. Closing an account at an online service might result in data being removed from a database, or the data might be moved or flagged so that it could be recovered if the account is re-opened. A company might establish policies regarding salary maximums, minimums, and adjustments, in addition to basic sanity checks such as the salary not being a negative number. A retail database might not allow a purchase with the same serial number to be returned more than once, or might not allow credit card purchases above a certain value, while a database used to detect fraud might allow these kinds of things.
See also relational.
See also trigger.
C
The general term for any memory area that stores copies of data for frequent or high-speed retrieval. In InnoDB, the primary kind of cache structure is the buffer pool.
See also buffer.
See also buffer pool.
The number of different values in a table column. When queries refer to columns that have an associated index, the cardinality of each column influences which access method is most efficient. If the values in a column have a very uneven distribution, the cardinality might not be a good way to determine the best query plan. (http://bugs.mysql.com/bug.php?id=36513)
Cardinality can also apply to the number of distinct values present in multiple columns, as in a composite index.
See also composite index.
See also index.
See also random dive.
See also selectivity.
The general term for the features involving the insert buffer, consisting of insert buffering, delete buffering, and purge buffering. Index changes resulting from SQL statements, which could normally involve random I/O operations, are held back and performed periodically by a background thread. This sequence of operations can write the disk blocks for a series of index values more efficiently than if each value were written to disk immediately.
See also delete buffering.
See also insert buffer.
See also insert buffering.
See also purge buffering.
As changes are made to data pages that are cached in the buffer pool, those changes are written to the data files sometime later, a process known as flushing. The checkpoint is a record of the latest changes (represented by an LSN value) that have been successfully written to the data files.
See also buffer pool.
See also data files.
See also flush.
See also LSN.
In a foreign key relationship,
a child table is one whose rows refer (or point) to rows in
another table with an identical value for a specific column. The
corresponding row in the parent
table must exist before the row can be created in the
child table. The values in the child table can prevent delete or
update operations on the parent table, or can cause automatic
deletion or updates in the child table, based on the ON
CASCADE option used when creating the foreign key.
See also foreign key.
See also parent table.
A type of program that sends requests to a server, and
interprets or processes the results. The client software might
run only some of the time (such as a mail or chat program), and
might run interactively (such as the mysql
command processor).
See also mysql.
See also server.
The InnoDB term for a primary key index. InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. For best performance, choose the primary key columns carefully based on the most performance-critical queries. Because modifying the columns of the clustered index is an expensive operation, choose primary columns that are rarely or never updated.
In the Oracle Database product, this type of table is known as an index-organized table.
See also index.
See also primary key.
See also secondary index.
A backup taken while the database is shut down. For busy applications and web sites, this might not be practical, and you might prefer a warm backup or a hot backup.
See also hot backup.
See also warm backup.
An index on a single column.
See also composite index.
See also index.
When an index is created with a length specification, such as
CREATE INDEX idx ON t1 (c1(N)), only the
first N characters of the column value are stored in the index.
Keeping the index prefix small makes the index compact, and the
memory and disk I/O savings help performance. (Although making
the index prefix too small can hinder query optimization by
making rows with different values appear to the query optimizer
to be duplicates.)
For columns containing binary values or long text strings, where sorting is not a major consideration and storing the entire value in the index would waste space, the index automatically uses the first N (typically 768) characters of the value to do lookups and sorts.
See also index.
A SQL statement that ends a transaction, making permanent any changes made by the transaction. It is the opposite of rollback, which undoes any changes made in the transaction.
InnoDB uses an optimistic mechanism for commits, so that changes can be written to the data files before the commit actually occurs. This technique makes the commit itself faster, with the tradeoff that more work is required in case of a rollback.
By default, MySQL uses the autocommit setting, which automatically issues a commit following each SQL statement.
See also autocommit.
See also optimistic.
See also rollback.
See also SQL.
See also transaction.
The default InnoDB row format since MySQL 5.0.3. It has a more compact representation for nulls and variable-length fields than the prior default (redundant row format).
Because of the B-tree indexes that make row lookups so fast in InnoDB, there is little if any performance benefit to keeping all rows the same size.
See also redundant row format.
See also row format.
An index that includes multiple columns.
See also index.
See also index prefix.
A set of files produced by the MySQL
Enterprise Backup product, where each
tablespace is compressed. The
compressed files are renamed with a .ibz file
extension.
Applying compression right at the start of the backup process helps to avoid storage overhead during the compression process, and to avoid network overhead when transferring the backup files to another server. The process of applying the binary log takes longer, and requires uncompressing the backup files.
See also apply.
See also binary log.
See also compression.
See also hot backup.
See also MySQL Enterprise Backup.
See also tablespace.
A row format introduced in the InnoDB Plugin, available as part of the Barracuda file format. Large fields are stored away from the page that holds the rest of the row data, as in dynamic row format. Both index pages and the large fields are compressed, yielding memory and disk savings. Depending on the structure of the data, the decrease in memory and disk usage might or might not outweigh the performance overhead of uncompressing the data as it is used.
See also Barracuda.
See also dynamic row format.
See also row format.
A feature with wide-ranging benefits from using less disk space, performing less I/O, and using less cache memory. InnoDB products include more than one feature related to compression: table data can be kept in a compressed format during database operation; data can be compressed as part of a backup operation with the MySQL Enterprise Backup product.
When InnoDB table data is compressed, the compression applies to the table itself, any associated index data, and the pages loaded into the buffer pool. Compression does not apply to pages in the undo buffer.
The table compression feature requires using MySQL 5.5 or higher, or the InnoDB Plugin in MySQL 5.1 or earlier, and creating the table using the Barracuda file format and compressed row format, with the innodb_file_per_table setting turned on.
The compression feature of the MySQL
Enterprise Backup product makes a compressed copy of
each tablespace, changing the extension from
.ibd to .ibz. Compressing
the backup data allows you to keep more backups on hand, and
reduces the time to transfer backups to a different server. The
data is uncompressed during the restore operation. When a
compressed backup operation processes a table that is already
compressed, it skips the compression step for that table,
because compressing again would result in little or no space
savings.
See also Barracuda.
See also buffer pool.
See also compressed row format.
See also hot backup.
See also index.
See also plugin.
See also table.
See also undo buffer.
See composite index.
The ability of multiple operations (in database terminology, transactions) to run simultaneously, without interfering with each other. Concurrency is also involved with performance, because ideally the protection for multiple simultaneous transactions works with a minimum of performance overhead, using efficient mechanisms for locking.
See also ACID.
See also locking.
See also transaction.
The file that holds the option
values used by MySQL at startup. Traditionally, on Linux and
UNIX this file is named my.cnf, and on
Windows it is named my.ini. You can set a
number of options related to InnoDB under the
[mysqld] section of the file.
When you use the MySQL Enterprise Backup product, you typically use two configuration files: one that specifies where the data comes from and how it is structured (which could be the original configuration file for your real server), and a stripped-down one containing only a small set of options that specify where the backup data goes and how it is structured. The configuration files used with the MySQL Enterprise Backup product must contain certain options that are typically left out of regular configuration files, so you might need to add some options to your existing configuration file for use with MySQL Enterprise Backup.
See also my.cnf.
See also option file.
A read operation that uses snapshot information to present query results based on a point in time, regardless of changes performed by other transactions running at the same time. If queried data has been changed by another transaction, the original data is reconstructed based on the contents of the undo log. This technique avoids some of the locking issues that can reduce concurrency by forcing transactions to wait for other transactions to finish.
With the repeatable read isolation level, the snapshot is based on the time when the first read operation is performed. With the read committed isolation level, the snapshot is reset to the time of each consistent read operation.
Consistent read is the default mode in which InnoDB processes
SELECT statements in
READ COMMITTED and
REPEATABLE READ isolation
levels. Because a consistent read does not set any locks on the
tables it accesses, other sessions are free to modify those
tables while a consistent read is being performed on the table.
See also ACID.
See also concurrency.
See also isolation level.
See also locking.
See also MVCC.
See also read committed.
See also read uncommitted.
See also repeatable read.
See also serializable read.
See also transaction.
See also undo log.
An automatic test that can block database changes to prevent data from becoming inconsistent. (In computer science terms, a kind of assertion related to an invariant condition.) Constraints are a crucial component of the ACID philosophy, to maintain data consistency. Constraints supported by MySQL include foreign key constraints and unique constraints.
See also ACID.
See also foreign key.
See also relational.
See also unique constraint.
An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transactions, until that transaction ends.
Any column index or composite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.
See also column index.
See also composite index.
See also index.
See also secondary index.
The cleanup activities that occur when InnoDB is started again after a crash. Changes that were committed before the crash, but not yet written into the tablespace files, are reconstructed from the doublewrite buffer. When the database is shut down normally, this type of activity is performed during shutdown by the purge operation.
During normal operation, committed data can be stored in the insert buffer for a period of time before being written to the tablespace files. There is always a tradeoff between keeping the tablespace files up-to-date, which introduces performance overhead during normal operation, and buffering the data, which can make shutdown and crash recovery take longer.
See also doublewrite buffer.
See also insert buffer.
See also purge.
An internal data structure that is used to represent the result
set of a query, or other operation that performs a search using
a SQL WHERE clause. It works like an iterator
in other high-level languages, producing each value from the
result set as requested.
Although usually SQL handles the processing of cursors for you, you might delve into the inner workings when dealing with performance-critical code.
See also query.
D
See DDL.
A set of tables, controlled by the InnoDB storage engine, that keeps track of InnoDB-related objects such as tables, indexes, and table columns. These tables are part of the InnoDB system tablespace.
Because the MySQL Enterprise Backup product always backs up the system tablespace, all backups include the contents of the data dictionary.
See also hot backup.
See also system tablespace.
The files that physically contain the InnoDB table and index data. There can be a one-to-many relationship between data files and tables, as in the case of the system tablespace, which can hold multiple InnoDB tables as well as the data dictionary. There can also be a one-to-one relationship between data files and tables, as when the file-per-table setting is enabled, causing each newly created table to be stored in a separate tablespace.
See also data dictionary.
See also file-per-table.
See also system tablespace.
See also tablespace.
See DML.
A database system or application that primarily runs large queries. The read-only or read-mostly data might be organized in denormalized form for query efficiency. Contrast with OLTP.
See also OLTP.
See also query.
An InnoDB database is largely defined by its data files.
For long-time MySQL users, a database is a familiar notion. Users coming from an Oracle background will find that the MySQL meaning of a database is closer to what Oracle calls a schema.
See also data files.
Data definition language, a set of
SQL statements for manipulating
the database itself rather than individual table rows. Includes
all forms of the CREATE,
ALTER, and DROP
statements. Also includes the TRUNCATE
statement, because it works differently than a DELETE
FROM statement,
even though the ultimate effect is similar.
table_name
DDL statements automatically commit the current transaction; they cannot be rolled back.
See also commit.
See also rollback.
See also SQL.
See also transaction.
A situation where different transactions are unable to proceed, because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither will ever release the locks it holds.
A deadlock can occur when the transactions acquire locks on
multiple tables, but in the opposite order. A deadlock can also
occur when statements such as UPDATE or
SELECT ... FOR UPDATE lock ranges of index
records and gaps, with each
transaction acquiring some locks but not others due to a timing
issue.
To reduce the possibility of deadlocks, use transactions rather
than LOCK TABLE statements; keep transactions
that insert or update data small enough that they do not stay
open for long periods of time; when different transactions
update multiple tables or large ranges of rows, use the same
order of operations (such as SELECT ... FOR
UPDATE) in each transaction; create indexes on the
columns used in SELECT ... FOR UPDATE and
UPDATE ... WHERE statements. The possibility
of deadlocks is not affected by the
isolation level, because the
isolation level changes the behavior of read operations, while
deadlocks occur because of write operations.
If a deadlock does occur, InnoDB detects the condition and
rolls back one of the
transactions (the victim).
Thus, even if your application logic is perfectly correct, you
must still handle the case where a transaction must be retried.
To monitor how frequently deadlocks occur, use the command
SHOW ENGINE INNODB STATUS.
See also concurrency.
See also gap.
See also isolation level.
See also lock.
See also locking.
See also rollback.
See also transaction.
See also victim.
A mechanism that automatically detects when a deadlock occurs, and automatically rolls back one of the transactions involved (the victim).
See also deadlock.
See also rollback.
See also transaction.
See also victim.
When InnoDB processes a DELETE statement, the
rows are immediately marked for deletion and no longer are
returned by queries. The storage is reclaimed sometime later,
during the periodic garbage collection known as the
purge operation, performed by a
separate thread.
See also purge.
The technique of storing index changes due to
DELETE operations in the
insert buffer rather than
writing them immediately, so that the physical writes can be
performed to minimize random I/O. (Because delete operations are
a two-step process, this operation buffers the write that
normally marks an index record for deletion.) It is one of the
types of change buffering; the
others are insert buffering and
purge buffering.
See also change buffering.
See also insert buffer.
See also insert buffering.
See also purge buffering.
See also index.
A page in the InnoDB buffer pool that has been updated in memory, where the changes are not yet written (flushed) to the data files.
See also buffer pool.
See also flush.
See also page.
See also write combining.
An operation that retrieves unreliable data, data that was updated by another transaction but not yet committed. It is only possible with the isolation level known as read uncommitted.
This kind of operation does not adhere to the ACID principle of database design. It is considered very risky, because the data could be rolled back, or updated further before being committed; then, the transaction doing the dirty read would be using data that was never confirmed as accurate.
Its polar opposite is consistent read, where InnoDB goes to great lengths to ensure that a transaction does not read information updated by another transaction, even if the other transaction commits in the meantime.
See also ACID.
See also commit.
See also consistent read.
See also isolation level.
See also read committed.
See also read uncommitted.
See also rollback.
A kind of database that primarily organizes data on disk storage (hard drives or equivalent). Data is brought back and forth between disk and memory to be operated upon. It is the opposite of an in-memory database. InnoDB is disk-based.
See also in-memory database.
Data manipulation language, a set of
SQL statements for performing
insert, update, and delete operations. The
SELECT statement is sometimes considered as a
DML statement, because the SELECT ... FOR
UPDATE form is subject to the same considerations for
locking as
INSERT, UPDATE, and
DELETE.
DML statements operate in the context of a transaction, so their effects can be committed or rolled back as a single unit.
See also commit.
See also locking.
See also rollback.
See also SQL.
See also transaction.
InnoDB uses a novel file flush technique called doublewrite. Before writing pages to a data file, InnoDB first writes them to a contiguous area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer have 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 crash recovery.
Although data is always written twice, the doublewrite buffer
does not require twice as much I/O overhead or twice as many I/O
operations. Data is written to the buffer itself as a large
sequential chunk, with a single fsync call to
the operating system.
The doublewrite buffer can be turned off by specifying the
option innodb_doublewrite=0.
See also crash recovery.
See also purge.
A row format introduced in the InnoDB Plugin, available as part
of the Barracuda file format.
Because TEXT and BLOB
fields are stored outside of the rest of the page that holds the
row data, it is very efficient for rows that include large
objects, resulting in fewer I/O operations when the large fields
do not need to be accessed.
See also Barracuda.
See also row format.
E
A stage similar to beta, when a software product is typically evaluated for performance, functionality, and compatibility in a non-mission-critical setting. InnoDB uses the early adopter designation rather than beta, through a succession of point releases leading up to a GA release.
See also beta.
See also GA.
A product that allows the capabilities of the InnoDB storage engine to be embedded within an application program, the same as any other library, without the need for a full MySQL installation. Instead of using SQL, the application manipulates data using C API calls. It is intended for applications that require maximal performance and minimal configuration and setup, such as in the embedded systems market.
See also cursor.
The process of removing an item from a cache or other temporary storage area. Often, but not always, uses the LRU algorithm to determine which item to remove.
See also LRU.
A kind of lock that prevents any other transaction from locking the same row. Depending on the transaction isolation level, this kind of lock might block other transactions from writing to the same row, or might also block other transactions from reading the same row. The default InnoDB isolation level, REPEATABLE READ, enables higher concurrency by allowing transactions to read rows that have exclusive locks, a technique known as consistent read.
See also concurrency.
See also consistent read.
See also isolation level.
See also lock.
See also repeatable read.
See also shared lock.
See also transaction.
F
A capability first introduced in the InnoDB Plugin, that speeds up creation of secondary indexes by avoiding the need to completely rewrite the associated table. The speedup applies to dropping secondary indexes also.
Because index maintenance can add performance overhead to many
data transfer operations, consider doing operations such as
ALTER TABLE ... ENGINE=INNODB or
INSERT INTO ... SELECT * FROM ... without any
secondary indexes in place, and creating the indexes afterward.
Even if you do not use the InnoDB Plugin as your primary storage engine, you can take advantage of this capability by enabling the Plugin temporarily, just to create or drop indexes, and then switch back to the built-in InnoDB storage engine for normal use.
See also index.
See also secondary index.
A shutdown procedure that is required before installation of the InnoDB Plugin. From the MySQL command line, issue the following command before performing the shutdown:
SET GLOBAL innodb_fast_shutdown=0;
To make this type of shutdown the default, specify by the
configuration parameter
innodb_fast_shutdown=0.
See also shutdown.
See also slow shutdown.
The format used by InnoDB for its data files named
ibdata1, ibdata2, and so
on. Each file format supports one or more row formats.
See also Antelope.
See also Barracuda.
See also ibdata file.
See also row format.
A general name for the setting controlled by the
innodb_file_per_table option. For each table
created while this setting is in effect, the data is stored in a
separate file rather than in the system tablespace. When table
data is stored in a separate file, you have more flexibility to
choose non-default file formats
and row formats, which are
required for features such as data compression. The
TRUNCATE TABLE operation is also much faster,
and the reclaimed space can be used by the operating system
rather than remaining reserved for InnoDB.
This mode is the default setting in MySQL 5.5.5 and higher.
The MySQL Enterprise Backup product is more flexible for tables that are in their own files. For example, tables can be excluded from a backup, but only if they are in separate files. Thus, this setting is suitable for tables that are backed up less frequently or on a different schedule.
See also file format.
See also ibdata file.
See also innodb_file_per_table.
See also row format.
See also system tablespace.
This row format is used by the MyISAM storage engine, not by
InnoDB. If you create an InnoDB table with the option
row_format=fixed, InnoDB translates this
option to use the compact row
format instead, although the fixed
value might still show up in output such as SHOW TABLE
STATUS reports.
See also compact row format.
See also row format.
To write changes to the database files, that had been buffered in a memory area or a temporary disk storage area. The InnoDB storage structures that are periodically flushed include the redo log, the undo log, and the buffer pool.
Flushing can happen because a memory area becomes full and the system needs to free some space, because a commit operation means the changes from a transaction can be finalized, or because a shutdown operation means that all outstanding work should be finalized.
See also buffer pool.
See also commit.
See also redo log.
See also undo log.
An internal InnoDB data structure that tracks pages in the buffer pool that have been changed and need to be written back out to disk. This data structure is updated frequently by InnoDB's internal mini-transactions, and so is protected by its own mutex to allow concurrent access to the buffer pool.
See also buffer pool.
See also mini-transaction.
See also mutex.
See also page.
A type of pointer relationship, between rows in separate InnoDB tables. The foreign key relationship is defined on one column in both the parent table and the child table.
In addition to enabling fast lookup of related information, foreign keys help to enforce referential integrity, by preventing any of these pointers from becoming invalid as data is inserted, updated, and deleted. This enforcement mechanism is a type of constraint. A row that points to another table cannot be inserted if the associated foreign key value does not exist in the other table. If a row is deleted or its foreign key value changed, and rows in another table point to that foreign key value, the foreign key can be set up to prevent the deletion, cause the corresponding column values in the other table to become null, or automatically delete the corresponding rows in the other table.
One of the stages in designing a normalized database is to identify data that is duplicated, separate that data into a new table, and set up a foreign key relationship so that the multiple tables can be queried like a single table, using a join operation.
See also child table.
See also foreign key constraint.
See also join.
See also normalized.
See also NULL.
See also parent table.
See also relational.
The type of constraint that
maintains database consistency through a
foreign key relationship.
Unlike other kinds of constraints that prevent data from being
inserted or updated, foreign key constraints can also cause data
in child rows to be deleted,
changed to different values, or set to
null, based on the ON
CASCADE option specified when creating the foreign
key.
See also child table.
See also constraint.
See also foreign key.
See also NULL.
The file containing the metadata, such as the table definition, of a MySQL table.
For backups, you must always keep the full set of
.FRM files along with the backup data, to be
able to restore tables that are altered or dropped after the
backup. Files with this extension are always included in backups
produced by the innobackup
command of the MySQL Enterprise
Backup product. If you use the
ibbackup command instead, you
must copy the .FRM files yourself.
Although each InnoDB table has a .FRM file,
InnoDB maintains its own table metadata in the system
tablespace; the .FRM files are not needed for
InnoDB to operate on InnoDB tables.
These files are backed up by the MySQL
Enterprise Backup product. These files must not be
modified by an ALTER TABLE operation while
the backup is taking place, which is why backups that include
non-InnoDB tables perform a FLUSH TABLES WITH READ
LOCK operation to freeze such activity while backing
up the .FRM files. Restoring a backup can
result in .FRM files being created, changed,
or removed to match the state of the database at the time of the
backup.
See also ibbackup command.
See also innobackup command.
See also MySQL Enterprise Backup.
An operation that requires reading the entire contents of a table, rather than just selected portions using an index. Typically performed either with small lookup tables, or in data warehousing situations with large tables where all available data is aggregated and analyzed. How frequently these operations occur, and the sizes of the tables relative to available memory, have implications for the algorithms used in query optimization and managing the buffer pool.
The purpose of indexes is to allow lookups for specific values or ranges of values within a large table, thus avoiding full table scans when practical.
See also buffer pool.
See also index.
See also LRU.
G
"Generally available", the stage when a software product leaves beta and is available for sale, official support, and production use.
See also beta.
See also early adopter.
A place in an InnoDB index data
structure where new values could be inserted. When you lock a
set of rows with a statement such as SELECT ... FOR
UPDATE, InnoDB can create locks that apply to the gaps
as well as the actual values in the index. For example, if you
select all values greater than 10 for update, a gap lock
prevents another transaction from inserting a new value that is
greater than 10.
Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.
See also concurrency.
See also index.
See also infimum record.
See also isolation level.
See also supremum record.
An InnoDB optimization that performs some
low-level I/O operations (log
write) once for a set of
commit operations, rather than
flushing and syncing separately for each commit.
When the binlog is enabled, you typically also set the
configuration option sync_binlog=0, because
group commit for the binary log is only supported if it is set
to 0.
See also commit.
See also log write.
See also plugin.
See also XA.
H
A type of index intended for
queries that use equality operators rather than range operators
such as greater-than or BETWEEN. It is
available in only the MEMORY storage engine. Although hash
indexes are the default for MEMORY tables for historic reasons,
for general-purpose queries, B-tree indexes are often a better
choice.
The InnoDB storage engine includes a variant of this index type, the adaptive hash index, that is constructed automatically if needed based on runtime conditions.
See also adaptive hash index.
See also B-tree.
See also index.
A periodic message that is sent to indicate that a system is functioning properly. In a replication context, if the master stops sending such messages, one of the slaves can take its place. Similar techniques can be used between the servers in a cluster environment, to confirm that all of them are operating properly.
See also master database.
See also replication.
See also slave database.
A condition where a row, table, or internal data structure is accessed so frequently, in a context that requires some form of locking or mutual exclusion, that it results in a performance or scalability issue.
Although “hot” typically indicates an undesirable condition, a hot backup is the preferred kind of backup.
See also hot backup.
A backup taken while the database and is running and applications are reading and writing to it. The backup involves more than simply copying data files: it must include any data that was inserted or updated while the backup was in process; it must exclude any data that was deleted while the backup was in process; and it must ignore any changes that were not committed.
The Oracle product that performs hot backups, of InnoDB tables especially but also tables from MyISAM and other storage engines, is known as MySQL Enterprise Backup.
The hot backup process consists of two stages. The initial copying of the data files produces a raw backup. The apply step incorporates any changes to the database that happened while the backup was running. Applying the changes produces a prepared backup; these files are ready to be restored whenever necessary.
See also apply.
See also MySQL Enterprise Backup.
See also prepared backup.
See also raw backup.
I
The set of files managed by InnoDB within a MySQL database: the system tablespace, any file-per-table tablespaces, and the (typically 2) log files. Used sometimes in detailed discussions of InnoDB file structures and formats, to avoid ambiguity between the meanings of database between different DBMS products, and the non-InnoDB files that may be part of a MySQL database.
See also database.
See also schema.
A set of files, typically named ib_logfile0
and ib_logfile1, that form the
redo log. These files record
statements that attempt to change data in InnoDB tables. These
statements are replayed automatically to correct data written by
incomplete transactions, on startup following a crash.
This data can not be used for manual recovery; for that type of operation, use the binary log.
See also binary log.
See also redo log.
The fundamental command-line tool of the MySQL Enterprise Backup product. It performs a hot backup operation for InnoDB tables. You use this command directly if all your data is in InnoDB tables, if all the important data that you need to back up is in InnoDB tables, or if you are running on the Windows platform. If you also need to back up tables from MyISAM or other storage engines, you use the innobackup command instead (available on UNIX and Linux systems only).
See also hot backup.
See also innobackup command.
See also MySQL Enterprise Backup.
A supplemental backup file created by the
MySQL Enterprise Backup product
during a hot backup operation.
It contains information about any data changes that occurred
while the backup was running. The initial backup files,
including ibbackup_logfile, are known as a
raw backup, because the changes
that occurred during the backup operation are not yet
incorporated. After you perform the
apply step to the raw backup
files, the resulting files do include those final data changes,
and are known as a prepared
backup. At this stage, the
ibbackup_logfile file is no longer necessary.
See also apply.
See also hot backup.
See also MySQL Enterprise Backup.
See also prepared backup.
See also raw backup.
Each InnoDB tablespace created
using the file-per-table
setting has a filename with a .ibd extension.
This extension does not apply to the
system tablespace, which is
made up of files named ibdata1,
ibdata2, and so on.
See also file-per-table.
See also ibz file.
See also system tablespace.
See also tablespace.
A set of files with names such as ibdata1,
ibdata2, and so on, that make up the InnoDB
system tablespace. These files
contain metadata about InnoDB tables, and can contain some or
all of the table data also (depending on whether the
file-per-table option is in effect when each table is created).
See also Antelope.
See also Barracuda.
See also file format.
See also file-per-table.
See also system tablespace.
When the MySQL Enterprise
Backup product performs a
compressed backup, it
transforms each tablespace file
that is created using the
file-per-table setting from a
.ibd extension to a .ibz
extension.
The compression applied during backup is distinct from the compressed row format that keeps table data compressed during normal operation. A compressed backup operation skips the compression step for a tablespace that is already in compressed row format, as compressing a second time would slow down the backup but produce little or no space savings.
See also compressed backup.
See also compressed row format.
See also file-per-table.
See also ibd file.
See also MySQL Enterprise Backup.
See also tablespace.
See also locking.
A type of database system that maintains data in memory, to avoid overhead due to disk I/O and translation between disk blocks and memory areas. Some in-memory databases sacrifice durability (the "D" in the ACID design philosophy) and are vulnerable to hardware, power, and other types of failures, making them more suitable for read-only operations. Other in-memory databases do use durability mechanisms such as logging changes to disk or using non-volatile memory.
See also ACID.
See also disk-based.
A type of hot backup, performed by the MySQL Enterprise Backup product, that only saves data changed since some point in time. Having a full backup and a succession of incremental backups lets you reconstruct backup data over a long period, without the storage overhead of keeping several full backups on hand. You can restore the full backup and then apply each of the incremental backups in succession, or you can keep the full backup up-to-date by applying each incremental backup to it, then perform a single restore operation.
The granularity of changed data is at the page level. A page might actually cover more than one row. Each changed page is included in the backup.
See also hot backup.
See also MySQL Enterprise Backup.
See also page.
A data structure that provides a fast lookup capability for rows of a table, typically by forming a tree structure representing all the values of a particular column or set of columns.
See also B-tree.
See also clustered index.
See also column index.
See also composite index.
See also covering index.
See also partial index.
See also primary key.
See also secondary index.
In an index that applies to multiple columns (known as a composite index), the initial or leading columns of the index. A query that references the first 1, 2, 3, and so on columns of a composite index can use the index, even if the query does not reference all the columns in the index.
See also composite index.
See also index.
A pseudo-record in an index,
representing the gap below the
smallest value in that index. If a transaction has a statement
such as SELECT ... FOR UPDATE ... WHERE col <
10;, and the smallest value in the column is 5, it is
a lock on the infimum record that prevents other transactions
from inserting even smaller values such as 0, -10, and so on.
See also gap.
See also pseudo-record.
See also supremum record.
The actual name of the schema
that contains the data
dictionary. To examine information (metadata) about
the database, you can query tables such as
INFORMATION_SCHEMA.TABLES and
INFORMATION_SCHEMA.COLUMNS.
The InnoDB Plugin introduced
some tables into the information schema that are specific to
InnoDB, such as INNODB_LOCKS and
INNODB_TRX. You use these tables not to see
how the database is structured, but to get real-time information
about the workings of the InnoDB storage engine that can help
with performance monitoring, tuning, and troubleshooting. In
particular, these tables provide information about the InnoDB
features related to
compression, and transactions
and their associated locks.
See also compression.
See also data dictionary.
See also locking.
See also schema.
See also transaction.
A command-line tool of the MySQL
Enterprise Backup product. It performs a
hot backup operation for tables
from InnoDB, MyISAM, and other storage engines. It calls the
ibbackup command to back up the
InnoDB tables; if you only need to back up InnoDB tables, it can
be more efficient to call ibbackup directly.
This command is not available on the Windows platform.
See also hot backup.
See also ibbackup command.
See also MySQL Enterprise Backup.
A storage engine for MySQL that combines high performance with transactional capability (that is, reliability, robustness, and concurrent access). Tables created under the InnoDB storage engine are ideally suited for hot backups.
See also ACID.
See also hot backup.
See also storage engine.
See also transaction.
A licensed backup product, superceded in MySQL 5.1 and above by MySQL Enterprise Backup.
See also MySQL Enterprise Backup.
The option that controls the algorithm used for
auto-increment locking. When
you have an auto-incrementing primary key, you can use
statement-based replication only with the setting
innodb_autoinc_lock_mode=1. This setting is
known as consecutive lock mode,
because multi-row inserts within a transaction receive
consecutive auto-increment values. If you have
innodb_autoinc_lock_mode=2, which allows
higher concurrency for insert operations, use row-based
replication rather than statement-based replication. This
setting is known as interleaved
lock mode, because multiple multi-row insert statements running
at the same time can receive autoincrement values that are
interleaved. The setting
innodb_autoinc_lock_mode=0 is the previous
(traditional) default setting and should not be used except for
compatibility purposes.
See also auto-increment locking.
A setting that determines the file format for all tablespaces created after you specify a value for this option. To create tablespaces other than the system tablespace, you must use the file-per-table option also.
See also Antelope.
See also Barracuda.
See also file format.
See also file-per-table.
See also innodb_file_per_table.
See also system tablespace.
See also tablespace.
The option that allows you to use the file-per-table setting, which stores newly created tables in their own data files, outside the system tablespace. This option is needed to take full advantage of many other features, such as such as table compression in the InnoDB Plugin, or backups of named tables in MySQL Enterprise Backup.
This option was once static, but can now be set using the
SET GLOBAL command.
See also data files.
See also file-per-table.
See also system tablespace.
A setting that provides an alternative to automatic deadlock detection. Rolls back any transaction that waits more than a specified time to acquire a lock. Especially useful if deadlocks are caused by updates to multiple tables controlled by different storage engines; such deadlocks are not detected automatically.
See also deadlock.
See also deadlock detection.
See also victim.
See also locking.
The option that controls whether InnoDB operates in strict mode, where conditions that are normally treated as warnings, cause errors instead (and the underlying statements fail).
This mode is the default setting in MySQL 5.5.5 and higher.
See also strict mode.
See also locking.
One of the primary DML operations in SQL. The performance of inserts is a key factor in data warehouse systems that load millions of rows into tables, and OLTP systems where many concurrent connections might insert rows into the same table, in arbitrary order. If insert performance is important to you, you should learn about InnoDB features such as the insert buffer used in change buffering, and auto-increment columns.
See also auto-increment.
See also change buffering.
See also DML.
See also insert buffer.
See also SQL.
A special index data structure that records changes to
pages in secondary indexes.
These values could result from SQL INSERT
UPDATE, or DELETE
statements. The set of features involving the insert buffer is
known collectively as change
buffering, consisting of insert
buffering, delete
buffering, and purge
buffering.
Changes are only recorded in the insert buffer when the relevant page from the secondary index is not in the buffer pool. When the relevant index page is brought into the buffer pool while associated changes are still in the insert buffer, the changes for that page are applied in the buffer pool using the data from the insert buffer. Periodically, the purge operation that runs during times when the system is mostly idle, or during a slow shutdown, writes the new index pages to disk. The purge operation can write the disk blocks for a series of index values more efficiently than if each value were written to disk immediately.
Physically, the insert buffer is part of the system tablespace, so that the index changes remain buffered across database restarts. The changes are only applied when the pages are brought into the buffer pool due to some other read operation.
To see information about the current data in the insert buffer,
issue the SHOW INNODB STATUS command.
See also buffer pool.
See also change buffering.
See also delete buffering.
See also insert buffering.
See also page.
See also purge.
See also purge buffering.
See also system tablespace.
The technique of storing secondary index changes due to
INSERT operations in the
insert buffer rather than
writing them immediately, so that the physical writes can be
performed to minimize random I/O. It is one of the types of
change buffering; the others
are delete buffering and
purge buffering.
Insert buffering is not used if the secondary index is unique, because the uniqueness of new values cannot be verified before the new entries are written out. Other kinds of change buffering do work for unique indexes.
See also change buffering.
See also delete buffering.
See also insert buffer.
See also purge buffering.
See also gap.
See also intention lock.
See also lock.
See also locking.
See intention lock.
A kind of lock that applies to the table level, used to indicate what kind of lock the transaction intends to acquire on rows in the table. Different transactions can acquire different kinds of intention locks on the same table, but the first transaction to acquire an intention exclusive (IX) lock on a table prevents other transactions from acquiring any S or X locks on the table. Conversely, the first transaction to acquire an intention shared (IS) lock on a table prevents other transactions from acquiring any X locks on the table. The two-phase process allows the lock requests to be resolved in order, without blocking locks and corresponding operations that are compatible.
See also insert-intention gap lock.
See also lock.
See also lock mode.
See also locking.
See intention lock.
One of the foundations of database processing. Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability, consistency, and reproducibility of results when multiple transactions are making changes and performing queries at the same time.
From highest amount of consistency and protection to the least, the isolation levels supported by InnoDB are: serializable read, repeatable read, consistent read, and read uncommitted.
With the built-in InnoDB storage engine and the InnoDB Plugin, many users can keep the default isolation level (repeatable read) for all operations. Expert users might choose the read committed level as they push the boundaries of scalability with OLTP processing, or during data warehousing operations where minor inconsistencies do not affect the aggregate results of large amounts of data. The levels on the edges (serializable read and read uncommitted) change the processing behavior to such an extent that they are rarely used.
See also ACID.
See also consistent read.
See also read uncommitted.
See also repeatable read.
See also serializable read.
See also transaction.
J
A query that retrieves data from more than one table, by referencing columns in the tables that hold identical values. These columns are frequently part of a foreign key relationship.
See also foreign key.
See also query.
K
An option to specify the size of data pages within a table that uses compressed row format. The default is 8 kilobytes. Lower values risk hitting internal limits that depend on the combination of row size and compression percentage.
See also compressed row format.
L
A lightweight structure used by InnoDB to implement a lock for its own internal memory structures, typically held for a brief time measured in milliseconds or microseconds. A general term that includes both mutexes (for exclusive access) and rw-locks (for shared access). Certain latches are the focus of performance tuning within the InnoDB storage engine, such as the data dictionary mutex. Statistics about latch use and contention are available through the Performance Schema interface.
See also data dictionary.
See also lock.
See also locking.
See also mutex.
See also Performance Schema.
See also rw-lock.
The InnoDB buffer pool is represented as a list of memory pages. The list is reordered as new pages are accessed and enter the buffer pool, as pages within the buffer pool are accessed again and are considered newer, and as pages that are not accessed for a long time are replaced in the buffer pool.
See also buffer pool.
See also eviction.
See also LRU.
See also sublist.
The high-level notion of an object that controls access to a resource, such as a table, row, or internal data structure, as part of a locking strategy. For intensive performance tuning, you might delve into the actual structures that implement locks, such as mutexes and latches.
See also latch.
See also lock mode.
See also locking.
See also mutex.
See also Pthreads.
An operation used in some database systems that converts many row locks into a single table lock, saving memory space but reducing concurrent access to the table. InnoDB uses a space-efficient representation for row locks, so that lock escalation is not needed.
See also locking.
See also row lock.
See also table lock.
A shared (S) lock allows a transaction to read a row. Multiple transactions can acquire an S lock on that same row at the same time.
An exclusive (X) lock allows a transaction to update or delete a row. No other transaction can acquire any kind of lock on that same row at the same time.
Intention locks apply to the table level, and are used to indicate what kind of lock the transaction intends to acquire on rows in the table. Different transactions can acquire different kinds of intention locks on the same table, but the first transaction to acquire an intention exclusive (IX) lock on a table prevents other transactions from acquiring any S or X locks on the table. Conversely, the first transaction to acquire an intention shared (IS) lock on a table prevents other transactions from acquiring any X locks on the table. The two-phase process allows the lock requests to be resolved in order, without blocking locks and corresponding operations that are compatible.
See also intention lock.
See also lock.
See also locking.
See also deadlock.
See also lock.
See also locking.
The system of protecting a transaction from seeing or changing data that is being queried or changed by other transactions. The locking strategy must balance reliability and consistency of database operations (the principles of the ACID philosophy) against the performance needed for good concurrency. Fine-tuning the locking strategy often involves choosing an isolation level and ensuring all your database operations are safe and reliable for that isolation level.
See also ACID.
See also concurrency.
See also isolation level.
See also latch.
See also lock.
See also mutex.
See also transaction.
See also group commit.
A backup that reproduces table
structure and data, without copying the actual data files. For
example, the mysqldump command produces a
logical backup, because its output contains statements such as
CREATE TABLE and INSERT
that can re-create the data. Contrast with
physical backup.
See also physical backup.
A prefix added to InnoDB configuration options when installing the InnoDB Plugin after server startup, so any new configuration options not recognized by the current level of MySQL do not cause a startup failure. MySQL processes configuration options that start with this prefix, but gives a warning rather than a failure if the part after the prefix is not a recognized option. For details, see the MySQL documentation.
An acronym meaning “least recently used”, a common
method for managing storage areas. The items that have not been
used recently are removed, when space is needed to cache newer
items. InnoDB uses the LRU mechanism by default to manage the
pages within the
buffer pool, but makes
exceptions in cases where a page might be read only a single
time, such as during a full-table scan. The amount that the
buffer cache policy differs from the strict LRU algorithm is
governed by the options innodb_old_blocks_pct
and innodb_old_blocks_time.
See also buffer pool.
See also eviction.
Acronym for log sequence number. This arbitrary, ever-increasing value represents a point in time corresponding to operations recorded in the redo log. (This point in time is regardless of transaction boundaries; it can fall in the middle of one or more transactions.) It is used internally by InnoDB during crash recovery and for managing the buffer pool.
In the MySQL Enterprise Backup
product, you can specify an LSN to represent the point in time
from which to take an incremental
backup. The relevant LSN is displayed by the output
of the ibbackup command. Once you have the
LSN corresponding to the time of a full backup, you can specify
that value to take a subsequent incremental backup, whose output
contains another LSN for the next incremental backup.
See also hot backup.
See also incremental backup.
See also redo log.
M
In a replication scenario, the database that processes the initial insert, update, and delete requests for data. These changes are propagated to, and repeated on, other databases known as slave databases.
See also replication.
See also slave database.
See also thread.
The technique of initially bringing blocks into the buffer pool
not at the "newest" end of the list, but instead somewhere in
the middle. The exact location of the mid-point can vary, based
on the setting of the innodb_old_blocks_pct
option. The intent is that blocks that are only read once, such
as during a full table scan, can be aged out of the buffer pool
sooner than with a strict LRU algorithm.
See also buffer pool.
See also full table scan.
See also LRU.
An internal phase of InnoDB processing, when making changes to the data dictionary during a DDL operation. The changes to the data dictionary are made without affecting the transaction that is being processed by MySQL.
See also data dictionary.
See also DDL.
See also transaction.
See also lock monitor.
A file containing references to other tables, used by the MERGE
storage engine. Files with this extension are always included in
backups produced by the innobackup command of
the MySQL Enterprise Backup
product.
See also innobackup command.
See also MySQL Enterprise Backup.
A type of processor that can take advantage of multi-threaded programs, such as the MySQL server.
See MVCC.
Informal abbreviation for “mutex variable”. (Mutex itself is short for “mutual exclusion”.) The low-level object that InnoDB uses to represent and enforce exclusive-access locks to internal in-memory data structures. Once the lock is acquired, any other process, thread, and so on is prevented from acquiring the same lock. Contrast with rw-locks, which allow shared access. Mutexes and rw-locks are known collectively as latches.
See also latch.
See also lock.
See also Performance Schema.
See also Pthreads.
See also rw-lock.
Acronym for multiversion concurrency control. This technique allows InnoDB transactions with certain isolation levels perform consistent read operations; that is, to query rows that are being updated by other transactions, and see the values from before those updates occurred. This is a powerful technique to increase concurrency, by allowing queries to proceed without waiting due to locks held by the other transactions.
This technique is not universal in the database world. Some other database products, and some other storage engines within MySQL, do not support it.
See also ACID.
See also concurrency.
See also consistent read.
See also isolation level.
See also lock.
The name, on UNIX or Linux systems, of the MySQL option file.
See also my.ini.
See also option file.
The name, on Windows systems, of the MySQL option file.
See also my.cnf.
See also option file.
The files that MySQL uses to store the data for MyISAM tables.
Files with this extension are always included in backups
produced by the innobackup command of the
MySQL Enterprise Backup
product.
See also innobackup command.
See also .MYI files.
See also MySQL Enterprise Backup.
The files that MySQL uses to store the indexes for MyISAM
tables. Files with this extension are always included in backups
produced by the innobackup command of the
MySQL Enterprise Backup
product.
See also innobackup command.
See also .MYD files.
See also MySQL Enterprise Backup.
The mysql program is the command-line
interpreter for the MySQL database. It processes SQL statements,
and also MySQL-specific commands such as SHOW
TABLES.
See also mysqld.
A licensed product, superceding InnoDB Hot Backup, that performs hot backups of MySQL databases. It offers the most efficiency and flexibility when backing up InnoDB tables, but can also back up MyISAM and other kinds of tables.
See also hot backup.
See also InnoDB.
The mysqld program is the database engine for
the MySQL database. It runs as a UNIX daemon or Windows service,
constantly waiting for requests and performing maintenance work
in the background.
See also mysql.
See also hot backup.
N
An industry term that means the same as asynchronous I/O.
See also asynchronous I/O.
The occurrence when a query retrieves data, and a later query within the same transaction retrieves what should be the same data, but the queries return different results (changed by another transaction committing in the meantime).
This kind of operation goes against the ACID principle of database design. Within a transaction, data should be consistent, with predictable and stable relationships.
Among different isolation levels, non-repeatable reads are prevented by the serializable read and repeatable read levels, and allowed by the consistent read, and read uncommitted levels.
See also ACID.
See also consistent read.
See also isolation level.
See also locking.
See also read uncommitted.
See also repeatable read.
See also serializable read.
See also transaction.
The condition where data is split into multiple tables, and duplicate values condensed into single rows represented by an ID, to avoid storing, querying, and updating redundant or lengthy values.
For example, an address might be given a unique ID, so that a census database could represent the relationship lives at this address by associating that ID with each member of a family, rather than storing multiple copies of a complex value such as 123 Main Street, Anytown, USA.
For another example, although a simple address book application might store each phone number in the same table as a person's name and address, a phone company database might give each phone number a special ID, and store the numbers and IDs in a separate table. This normalized representation could simplify large-scale updates when area codes split apart.
Normalization is not always recommended. Data that is primarily queried, and only updated by deleting entirely and reloading, is often kept in fewer, larger tables with redundant copies of duplicate values. This data representation is referred to as denormalized, and is frequently found in data warehousing applications.
See also foreign key.
See also relational.
A special value in SQL,
indicating the absence of data. Any arithmetic operation or
equality test involving a NULL value, in turn
produces a NULL result. (Thus it is similar
to the IEEE floating-point concept of NaN,
not a number.) Any aggregate
calculation such as AVG() ignores rows with
NULL values, when determining how many rows
to divide by. The only test that works with
NULL values uses the SQL idioms IS
NULL or IS NOT NULL.
NULL values play a part in index operations,
because for performance a database must minimize the overhead of
keeping track of missing data values, Typically,
NULL values are not represented in an index,
because a query that tests an indexed column using a standard
comparison operator could never match a row with a
NULL value for that column. For the same
reason, unique indexes do not prevent NULL
values; those values simply are not represented in the index.
Declaring a NOT NULL constraint on a column
provides reassurance that there are no rows left out of the
index, allowing for better query optimization (accurate counting
of rows and estimation of whether to use the index).
Because the primary key must be
able to uniquely identify every row in the table, a
single-column primary key cannot contain any
NULL values, and a multi-column primary key
cannot contain any rows with NULL values in
all columns.
Although the Oracle database allows a NULL
value to be concatenated with a string, InnoDB treats the result
of such an operation as NULL.
See also index.
See also primary key.
See also SQL.
O
A column containing variable-length data (such as
BLOB and VARCHAR) that are
too long to fit on a B-tree
page. The data is stored in overflow
pages.
See also B-tree.
See also overflow page.
Acronym for "Online Transaction Processing". A database system, or a database application, that runs a workload with many transactions, with frequent writes as well as reads, typically affecting small amounts of data at a time. For example, an airline reservation system or an application that processes bank deposits. The data might be organized in normalized form for a balance between DML (insert/update/delete) efficiency and query efficiency. Contrast with data warehouse.
See also data warehouse.
See also DML.
See also query.
See also transaction.
A file containing database configuration information. Files with
this extension are always included in backups produced by the
innobackup command of the
MySQL Enterprise Backup
product.
See also innobackup command.
See also MySQL Enterprise Backup.
A methodology that guides low-level implementation decisions for a relational database system. The requirements of performance and concurrency in a relational database mean that operations must be started or dispatched quickly. The requirements of consistency and referential integrity mean that any operation could fail: a transaction might be rolled back, a DML operation could violate a constraint, a request for a lock could cause a deadlock, a network error could cause a timeout. An optimistic strategy is one that assumes most requests or attempts will succeed, so that relatively little work is done to prepare for the failure case. When this assumption is true, the database does little unnecessary work; when requests do fail, extra work must be done to clean up and undo changes.
InnoDB uses optimistic strategies for operations such as locking and commits. For example, data changed by a transaction can be written to the data files before the commit occurs, making the commit itself very fast, but requiring more work to undo the changes if the transaction is rolled back.
The opposite of an optimistic strategy is a pessimistic one, where a system is optimized to deal with operations that are unreliable and frequently unsuccessful. This methodology is rare in a database system, because so much care goes into choosing reliable hardware, networks, and algorithms.
See also commit.
See also locking.
See also pessimistic.
A configuration parameter for MySQL.
In the context of the InnoDB storage engine (either the built-in
InnoDB or the InnoDB Plugin), each option name starts with the
prefix innodb_.
See also built-in.
See also option file.
See also plugin.
Traditionally, on Linux and UNIX this file is named
my.cnf, and on Windows it is named
my.ini.
See also configuration file.
See also my.cnf.
Separately allocated disk pages
that hold variable-length columns (such as
BLOB and VARCHAR) that are
too long to fit on a B-tree
page. The associated columns are known as
off-page columns.
See also B-tree.
See also off-page column.
See also page.
P
A unit representing how much data InnoDB transfers at any one time between disk (the data files) and memory (the buffer pool). A page can contain one or more rows, depending on how much data is in each row. If a row does not fit entirely into a single page, InnoDB sets up additional pointer-style data structures so that the information about the row can be stored in one page.
One way to fit more data in each page is to use compressed row format.
See also buffer pool.
See also compressed row format.
See also data files.
See also page size.
Currently, this value is fixed at 16 kilobytes. This is considered a reasonable compromise: large enough to hold the data for most rows, yet small enough to minimize the performance overhead of transferring unneeded data to memory. Other values are not tested or supported.
See also page.
A file containing partition definitions. Files with this
extension are always included in backups produced by the
innobackup command of the
MySQL Enterprise Backup
product.
See also innobackup command.
See also MySQL Enterprise Backup.
The table in a foreign key
relationship that holds the initial column values pointed to
from the child table. The
consequences of deleting, or updating rows in the parent table
depend on the ON UPDATE and ON
DELETE clauses in the foreign key definition. Rows
with corresponding values in the child table could be
automatically deleted or updated in turn, or those columns could
be set to NULL, or the operation could be
prevented.
See also child table.
See also foreign key.
An index that represents only
part of a column value, typically the first N characters (the
prefix) of a long
VARCHAR value.
See also index.
See also index prefix.
The performance_schema schema, in MySQL 5.5
and up, presents a set of tables that you can query to get
detailed information about the performance characteristics of
many internal parts of the MySQL server.
See also latch.
See also mutex.
See also rw-lock.
A methodology that sacrifices performance or concurrency in favor of safety. It is appropriate if a high proportion of requests or attempts might fail, or if the consequences of a failed request are severe. InnoDB uses what is known as a pessimistic locking strategy, to minimize the chance of deadlocks. At the application level, you might avoid deadlocks by usiing a pessimistic strategy of acquiring all locks needed by a transaction at the very beginning.
Many built-in database mechanisms use the opposite optimistic methodology.
See also deadlock.
See also locking.
See also optimistic.
A row that appears in the result set of a query, but not in the
result set of an earlier query. For example, if a query is run
twice within a transaction, and in the meantime, another
transaction commits after inserting a new row or updating a row
so that it matches the WHERE clause of the
query.
This occurrence is known as a phantom read. It is harder to guard against than a non-repeatable read, because locking all the rows from the first query result set does not prevent the changes that cause the phantom to appear.
Among different isolation levels, phantom reads are prevented by the serializable read level, and allowed by the repeatable read, consistent read, and read uncommitted levels.
See also consistent read.
See also isolation level.
See also non-repeatable read.
See also read uncommitted.
See also repeatable read.
See also serializable read.
See also transaction.
A backup that copies the actual
data files. For example, the MySQL
Enterprise Backup command produces a physical backup,
because its output contains data files that can be used directly
by the mysqld server. Contrast with
logical backup.
See also logical backup.
See also MySQL Enterprise Backup.
Acronym for point-in-time recovery.
See also point-in-time recovery.
While the InnoDB storage engine is included with the MySQL database, it is also separately installable as a plugin. The InnoDB Plugin offers performance and functional enhancements over the built-in InnoDB storage engine in MySQL. The plugin can replace the built-in InnoDB storage engine, and can be upgraded independently of the full database server. The flexibility to upgrade can help to roll out performance improvements and new features with less migration and testing effort than switching to a whole new database release.
See also built-in.
The process of restoring a backup to recreate the state of the database at a specific date and time. Commonly abbreviated PITR. Because it is unlikely that the specified time corresponds exactly to the time of a backup, this technique usually requires a combination of a physical backup and a logical backup. For example, with the MySQL Enterprise Backup product, you restore the last backup that you took before the specified point in time, then replay changes from the binary log between the time of the backup and the PITR time.
See also logical backup.
See also MySQL Enterprise Backup.
See also physical backup.
See also PITR backup.
See also locking.
See index prefix.
A set of backup files, produced by the MySQL Enterprise Backup product, after all the stages of applying binary logs and incremental backups are finished. The resulting files are ready to be restored. Prior to the apply steps, the files are known as a raw backup.
See also binary log.
See also hot backup.
See also incremental backup.
See also MySQL Enterprise Backup.
See also raw backup.
See also restore.
A set of columns -- and by implication, the index based on this
set of columns -- that can uniquely identify every row in a
table. As such, it must be a unique index that does not contain
any NULL values.
InnoDB requires that every table has such an index (also called the clustered index or cluster index), and organizes the table storage based on the column values of the primary key.
See also clustered index.
See also index.
An artificial record in an index, used for locking key values or ranges that do not currently exist.
See also infimum record.
See also locking.
See also supremum record.
The POSIX threads standard, which defines an API for threading and locking operations on UNIX and Linux systems. On UNIX and Linux systems, InnoDB uses this implementation for mutexes.
See also mutex.
A type of garbage collection performed by a separate thread,
running on a periodic schedule. The purge includes these
actions: removing obsolete values from indexes; physically
removing rows that were marked for deletion by previous
DELETE statements.
See also crash recovery.
See also delete.
See also doublewrite buffer.
The technique of storing index changes due to
DELETE operations in the
insert buffer rather than
writing them immediately, so that the physical writes can be
performed to minimize random I/O. (Because delete operations are
a two-step process, this operation buffers the write that
normally purges an index record that was previously marked for
deletion.) It is one of the types of
change buffering; the others
are insert buffering. and
delete buffering
See also change buffering.
See also delete buffering.
See also insert buffer.
See also insert buffering.
A thread within the InnoDB process that is dedicated to performing the periodic purge operation.
See also purge.
See also thread.
Q
An operation that reads information from one or more tables in a database. Depending on the organization of data and the parameters of the query, the lookup might be optimized by consulting an index.
Normally with MySQL, queries are coded using SQL. (Even if you are using some other language for the main application logic.)
See also database.
See also index.
See also SQL.
See also table.
R
Acronym for "Redundant Array of Inexpensive Drives". Spreading I/O operations across multiple drives enables greater concurrency at the hardware level, and improves the efficiency of low-level write operations that otherwise would be performed in sequence.
See also concurrency.
A technique for quickly estimating the number of different values in a column (the column's cardinality). InnoDB samples pages at random from the index and uses that data to estimate the number of different values. This operation occurs when each table is first opened.
Originally, the number of sampled pages was fixed at 8; now, it
is determined by the setting of the
innodb_stats_sample_pages parameter.
The way the random pages are picked depends on the setting of the innodb_use_legacy_cardinality_algorithm parameter. The default setting (OFF) has better randomness than in older releases.
See also cardinality.
The initial set of backup files produced by the MySQL Enterprise Backup product, before the changes reflected in the binary log and any incremental backups are applied. At this stage, the files are not ready to restore. After these changes are applied, the files are known as a prepared backup.
See also binary log.
See also hot backup.
See also ibbackup_logfile.
See also incremental backup.
See also MySQL Enterprise Backup.
See also prepared backup.
See also restore.
An isolation level that uses a locking strategy that relaxes some of the protection between transactions, in the interest of performance. Transactions cannot see uncommitted data from other transactions, but they can see data that is committed by another transaction after the current transaction started. Thus, a transaction never sees any bad data, but the data that it does see may depend to some extent on the timing of other transactions.
When a transaction with this isolation level performs
UPDATE ... WHERE or DELETE ...
WHERE operations, other transactions might have to
wait. The transaction can perform SELECT ... FOR
UPDATE, and LOCK IN SHARE MODE
operations without making other transactions wait.
See also ACID.
See also isolation level.
See also locking.
See also repeatable read.
See also serializable read.
See also transaction.
The isolation level that provides the least amount of protection between transactions. Queries employ a locking strategy that allows them to proceed in situations where they would normally wait for another transaction. However, this extra performance comes at the cost of less reliable results, including data that has been changed by other transactions and not committed yet (known as dirty read). Use this isolation level only with great caution, and be aware that the results might not be consistent or reproducible, depending on what other transactions are doing at the same time. Typically, transactions with this isolation level do only queries, not insert, update, or delete operations.
See also ACID.
See also dirty read.
See also isolation level.
See also locking.
See also transaction.
A type of I/O request that prefetches pages into the buffer cache asynchronously, in anticipation that these pages will be needed soon.
See also locking.
A set of files, typically named ib_logfile0
and ib_logfile1, that record statements that
attempt to change data in InnoDB tables. These statements are
replayed automatically to correct data written by incomplete
transactions, on startup following a crash.
See also ib_logfile.
The oldest InnoDB row format. Prior to MySQL 5.0.3, it was the only row format available in InnoDB. In My SQL 5.0.3 and later, the default is compact row format. You can still specify redundant row format for compatibility with older InnoDB tables.
See also compact row format.
See also row format.
The technique of maintaining data always in a consistent format, part of the ACID philosophy. In particular, data in different tables is kept in synch through the use of foreign keys, which can prevent changes from happening or automatically propagate those changes to all related tables.
See also ACID.
See also foreign key.
An important aspect of modern database systems.
In a mathematical context, the relations within a database are
derived from set theory. For example, the OR
and AND operators of a
WHERE clause represent the notions of union
and intersection.
In everyday terms, the database encodes and enforces relationships such as one-to-one, one-to-many, many-to-one, and uniqueness. For example, a person might have zero, one, or many phone numbers in an address database; a single phone number might be associated with several family members. In a financial database, a person might be required to have exactly one taxpayer ID, and any taxpayer ID could only be associated with one person.
At the database level, these relationships are expressed through
SQL features such as columns within a table, unique and
NOT NULL constraints, foreign keys, and
different kinds of join operations. Complex relationships
typically involve data split between more than one table. Often,
the data is normalized, so that
duplicate values in one-to-many relationships are stored only
once.
See also ACID.
See also foreign key.
See also normalized.
The default isolation level for InnoDB. It prevents any rows that are queried from being changed by other transactions, thus blocking non-repeatable reads but not phantom reads. It uses a moderately strict locking strategy so that all queries within a transaction see data from the same snapshot, that is, the data as it was at the time the transaction started.
When a transaction with this isolation level performs
UPDATE ... WHERE, DELETE ...
WHERE, SELECT ... FOR UPDATE, and
LOCK IN SHARE MODE operations, other
transactions might have to wait.
See also ACID.
See also consistent read.
See also isolation level.
See also locking.
See also serializable read.
See also transaction.
The practice of sending changes from a master database, to one or more slave databases, so that all databases have the same data. This technique has a wide range of uses, such as load-balancing for better scalability, disaster recovery, and testing software upgrades and configuration changes. The changes can be sent between the database by methods called row-based replication and statement-based replication.
See also master database.
See also row-based replication.
See also slave database.
See also statement-based replication.
The process of putting a set of backup files from the
MySQL Enterprise Backup product
in place for use by MySQL. This operation can be performed to
fix a corrupted database, to return to some earlier point in
time, or (in a replication
context) to set up a new slave
database. In the MySQL
Enterprise Backup product, this operation is
performed by the --copy-back option of the
innobackup command.
See also hot backup.
See also innobackup command.
See also MySQL Enterprise Backup.
See also prepared backup.
See also replication.
See also slave database.
A SQL statement that ends a transaction, undoing any changes made by the transaction. It is the opposite of commit, which makes permanent any changes made in the transaction.
By default, MySQL uses the autocommit setting, which automatically issues a commit following each SQL statement. You must change this setting before you can use the rollback technique.
See also ACID.
See also commit.
See also transaction.
The storage area containing the undo log, part of the system tablespace.
See also system tablespace.
See also undo log.
The disk storage format for a row from an InnoDB table. As InnoDB gains new capabilities such as compression, new row formats are introduced to support the resulting improvements in storage efficiency and performance.
Each table has its own row format, specified through the
ROW_FORMAT option. To see the row format for
each InnoDB table, issue the command SHOW TABLE
STATUS. Because all the tables in the system
tablespace share the same row format, to take advantage of other
row formats typically requires setting the
innodb_file_per_table option, so that each
table is stored in a separate tablespace.
See also compact row format.
See also compressed row format.
See also dynamic row format.
See also fixed row format.
See also redundant row format.
A lock that prevents a row from being accessed in an incompatible way by another transaction.
See also lock.
See also lock mode.
See also transaction.
This form of replication is safe to use for all settings of the innodb_autoinc_lock_mode option.
See also auto-increment locking.
See also innodb_autoinc_lock_mode.
See also replication.
See also statement-based replication.
The low-level object that InnoDB uses to represent and enforce shared-access locks to internal in-memory data structures. Once the lock is acquired, any other process, thread, and so on can read the data structure, but no one else can write to it. Contrast with mutexes, which enforce exclusive access. Mutexes and rw-locks are known collectively as latches.
See also latch.
See also lock.
See also mutex.
See also Performance Schema.
S
Savepoints help to implement nested transactions. They can be used to provide scope to operations on tables that are part of a larger transaction. For example, scheduling a trip in a reservation system might involve booking several different flights; if a desired flight is unavailable, you might roll back the changes involved in booking that one leg, without rolling back the earlier flights that were successfully booked.
See also rollback.
See also transaction.
The ability to add more work and issue more simultaneous requests to a system, without a sudden drop in performance due to exceeding the limits of system capacity. Software architecture, hardware configuration, application coding, and type of workload all play a part in scalability. When the system reaches its maximum capacity, popular techniques for increasing scalability are scale up (increasing the capacity of existing hardware or software) and scale out (adding new hardware or more instances of server software).
Conceptually, a schema is a set of interrelated database objects, such as tables, table columns, data types of the columns, indexes, foreign keys, and so on. These objects are connected through SQL syntax, because the columns make up the tables, the foreign keys refer to tables and columns, and so on. Ideally, they are also connected logically, working together as part of a unified application or flexible framework.
In MySQL, physically, a schema is analogous to a database. Typically, all the objects for a MySQL application are contained within a single database.
Some other database products draw a distinction. For example, in the Oracle Database product, a schema represents only a part of a database: the tables and other objects owned by a single user.
See also database.
See also ib-file set.
See also information schema.
A type of InnoDB index that represents a subset of table columns. An InnoDB table can have zero, one, or many secondary indexes. (Contrast with the clustered index, which is required for each InnoDB table, and stores the data for all the table columns.)
A secondary index can be used to satisfy queries that only require values from the indexed columns. For more complex queries, it can be used to identify the relevant rows in the table, which are then retrieved through lookups using the clustered index.
Creating and dropping secondary indexes has traditionally
involved significant overhead from copying all the data in the
InnoDB table. The fast index
creation feature of the InnoDB Plugin makes both
CREATE INDEX and DROP
INDEX statements much faster for InnoDB secondary
indexes.
See also clustered index.
See also fast index creation.
See also index.
A property of data distribution, the number of distinct values
in a column divided by the number of records in the table. High
selectivity means that the column values are relatively unique,
and can retrieved efficiently through an index. If you (or the
query optimizer) can predict that a test in a
WHERE clause only matches a small number (or
proportion) of rows in a table, the overall query tends to be
efficient if it evaluates that test first, using an index.
See also cardinality.
A type of read operation used for UPDATE
statements, that is a combination of read
committed and consistent
read. When an UPDATE statement
examines a row that is already locked, InnoDB returns the latest
committed version to MySQL so that MySQL can determine whether
the row matches the WHERE condition of the
UPDATE. If the row matches (must be updated),
MySQL reads the row again, and this time InnoDB either locks it
or waits for a lock on it. This type of read operation can only
happen when the transaction has the read committed
isolation level, or when the
innodb_locks_unsafe_for_binlog option is
enabled.
See also consistent read.
See also isolation level.
See also read committed.
The isolation level that uses the most conservative locking strategy, to prevent any other transactions from inserting or changing data that was read by this transaction, until it is finished. This way, the same query can be run over and over within a transaction, and be certain to retrieve the same set of results each time. Any attempt to change data that was committed by another transaction since the start of the current transaction, cause the current transaction to wait.
This is the default isolation level specified by the SQL standard. In practice, this degree of strictness is rarely needed, so the default isolation level for InnoDB is the next most strict, repeatable read.
See also ACID.
See also consistent read.
See also isolation level.
See also locking.
See also repeatable read.
See also transaction.
A type of program that runs continuously, waiting to receive and act upon requests from another program (the client). Because often an entire computer is dedicated to running one or more server programs (such as a database server, a web server, an application server, or some combination of these), the term server can also refer to the computer that runs the server software.
See also client.
See also mysqld.
A kind of lock that allows other transactions to read the locked object, and to also acquire other shared locks on it, but not to write to it. The opposite of exclusive lock.
See also exclusive lock.
See also lock.
See also transaction.
Another way of referring to the system tablespace.
See also system tablespace.
The process of stopping the InnoDB storage engine. This process can do various cleanup operations, so it is slow to shut down but fast to start up later; or it can skip the cleanup operations, so it is fast to shut down but must do the cleanup the next time it starts.
The shutdown mode is controlled by the
innodb_fast_shutdown option.
See also fast shutdown.
See also slow shutdown.
A slave database, frequently shortened to "slave", is a database in a replication scenario that receives changes from another database (the master database) and applies those same changes. Thus it maintains the same contents as the master, although it might lag somewhat behind.
In MySQL, slave databases are commonly used in disaster recovery, to take the place of a master database that fails. They are also commonly used for testing software upgrades and new settings, to ensure that database configuration changes do not cause problems with performance or reliability.
See also master database.
See also replication.
An option for the Hot Backup product that reduces the I/O overhead of the backup operation. (The backup takes longer as a result.)
A type of shutdown that does additional flushing operations
before completing. Specified by the configuration parameter
innodb_fast_shutdown=0.
Although the shutdown itself can take longer, that time will be
saved on the subsequent startup.
See also fast shutdown.
See also shutdown.
See also tablespace.
The Structured Query Language that is standard for performing database operations. Often divided into the categories DDL, DML, and queries.
See also DDL.
See also DML.
See also query.
Acronym for “solid-state drive”. A type of storage device with different performance characteristics than a traditional hard drive: smaller storage capacity, faster for random reads, no moving parts, and with a number of considerations affecting write performance.
See also disk-based.
This form of replication requires some care with the setting for the innodb_autoinc_lock_mode option, to avoid potential timing problems with auto-increment locking.
See also auto-increment locking.
See also innodb_autoinc_lock_mode.
See also replication.
See also row-based replication.
A component of the MySQL database that performs the low-level work of storing, updating, and querying data. InnoDB is one such storage engine. Different storage engines are designed with different tradeoffs between factors such as memory usage versus disk usage, read speed versus write speed, and speed versus robustness.
The MySQL Enterprise Backup product is optimized for backing up tables produced by the InnoDB storage engine. It can also back up tables produced by MyISAM and other storage engines.
See also InnoDB.
See also MySQL Enterprise Backup.
The general name for the setting controlled by the
innodb_strict_mode option. Turning on this
setting causes certain conditions that are normally treated as
warnings, to be considered errors. For example, certain invalid
combinations of options related to file
format and row
format, that normally produce a warning and continue
with default values, now cause the CREATE
TABLE operation to fail.
MySQL also has something called strict mode.
See also innodb_strict_mode.
Within the list structure that represents the buffer pool, pages that are relatively old and relatively new are represented by different portions of the list. A set of parameters control the size of these portions and the dividing point between the new and old pages.
See also buffer pool.
See also eviction.
See also list.
See also LRU.
A pseudo-record in an index,
representing the gap above the
largest value in that index. If a transaction has a statement
such as SELECT ... FOR UPDATE ... WHERE col >
10;, and the largest value in the column is 20, it is
a lock on the supremum record that prevents other transactions
from inserting even larger values such as 50, 100, and so on.
See also gap.
See also infimum record.
See also pseudo-record.
By default, this single data file stores all the table data for a database, as well as all the metadata for InnoDB-related objects (the data dictionary).
Turning on the innodb_file_per_table option causes each newly created table to be stored in its own tablespace, reducing the size of, and dependencies on, the system tablespace.
Keeping all table data in the system tablespace has implications for the MySQL Enterprise Backup product (backing up one large file rather than several smaller files), and prevents you from using certain InnoDB features that require the newer Barracuda file format. on the
See also Barracuda.
See also data dictionary.
See also file format.
See also file-per-table.
See also ibdata file.
See also innodb_file_per_table.
See also tablespace.
T
Although a table is a distinct, addressable object in the context of SQL, for backup purposes we are often concerned with whether the table is part of the system tablespace, or was created under the file-per-table setting and so resides in its own tablespace.
See also clustered index.
See also file-per-table.
See also system tablespace.
See also table.
A lock that prevents any other
transaction from accessing a
table. InnoDB makes considerable effort to make such locks
unnecessary, by using techniques such as
row locks and
consistent reads for processing
DML statements and
queries. You can create such a
lock through SQL using the LOCK TABLE
statement; one of the steps in migrating from other database
systems or MySQL storage engines is to remove such statements
wherever practical.
See also consistent read.
See also DML.
See also lock.
See also locking.
See also query.
See also row lock.
See also table.
See also transaction.
See also monitor.
A data file that can hold data for one or more tables. The
system tablespace contains the
tables that make up the data
dictionary, and by default holds all the other InnoDB
tables. Turning on the innodb_file_per_table
option allows newly created tables to each have their own
tablespace, with a separate data file for each table.
Tablespaces created by the built-in InnoDB storage engine are upward compatible with the InnoDB Plugin. Tablespaces created by the InnoDB Plugin are downward compatible with the built-in InnoDB storage engine, if they use the Antelope file format.
See also Antelope.
See also Barracuda.
See also compressed row format.
See also data dictionary.
See also file-per-table.
See also ibdata file.
See also innodb_file_per_table.
See also system tablespace.
A representation of the data
dictionary metadata for a table, within the InnoDB
tablespace. This metadata can
be checked against the .frm
file for consistency when the table is opened, to
diagnose errors resulting from out-of-date
.frm files. This information is present for
InnoDB tables that are part of the system
tablespace, as well as for tables that have their own
.ibd file because of the
file-per-table option.
See also data dictionary.
See also file-per-table.
See also .FRM file.
See also ibd file.
See also system tablespace.
See also tablespace.
See also monitor.
A table whose data does not need to be truly permanent. For example, temporary tables might be used as storage areas for intermediate results in complicated calculations or transformations; this intermediate data would not need to be recovered after a crash. Database products can take various shortcuts to improve the performance of operations on temporary tables, by being less scrupulous about writing data to disk and other measures to protect the data across restarts.
Sometimes, the data itself is removed automatically at a set time, such as when the transaction ends or when the session ends. With some database products, the table itself is removed automatically too.
See also table.
See also master thread.
See also Pthreads.
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.
Database transactions, as implemented by InnoDB, have properties that are collectively known by the acronym ACID, for atomicity, consistency, isolation, and durability.
See also ACID.
See also commit.
See also isolation level.
See also lock.
See also rollback.
An internal field associated with each row. This field is physically changed by INSERT, UPDATE, and DELETE operations to record which transaction has locked the row.
See also implicit row lock.
A feature that allows a
tablespace to be moved from one
database to another. Traditionally, this has not been possible
for InnoDB tablespaces because all table data was part of the
system tablespace. Now, with
the file-per-table setting, an
InnoDB table can reside in its own .ibd
file that you can copy individually. Also, the
original .ibd file contains information such
as the space ID that ties the
tablespace to its original database. The transportable
tablespace feature involves rewriting some information inside
the tablespace to make it compatible with the destination
database.
See also file-per-table.
See also ibd file.
See also space ID.
See also system tablespace.
See also tablespace.
A file containing trigger parameters. Files with this extension
are always included in backups produced by the
innobackup command of the
MySQL Enterprise Backup
product.
See also innobackup command.
See also MySQL Enterprise Backup.
See also .TRN file.
See also stored procedure.
A file containing trigger namespace information. Files with this
extension are always included in backups produced by the
innobackup command of the
MySQL Enterprise Backup
product.
See also innobackup command.
See also MySQL Enterprise Backup.
See also .TRG file.
Resources for troubleshooting InnoDB reliability and performance issues include: the Information Schema tables.
A DDL operation that removes
the entire contents of a table. Although conceptually it has the
same result as a DELETE statement with no
WHERE clause, it operates differently behind
the scenes: InnoDB creates a new empty table, drops the old
table, then renames the new table to take the place of the old
one. Because this is a DDL operation, it cannot be
rolled back.
If the table being truncated contains foreign keys that
reference another table, the truncation operation uses a slower
method of operation, deleting one row at a time so that
corresponding rows in the referenced table can be deleted as
needed by any ON DELETE CASCADE clause.
See also DDL.
See also rollback.
A technical term designating an ordered set of elements. It is an abstract notion, used in formal discussions of database theory. In the database field, tuples are usually represented by the columns of a table row. They could also be represented by the result sets of queries, for example, queries that retrieved only some columns of a table, or columns from joined tables.
See also cursor.
An operation that is part of a distributed transaction, under the XA specification. (Sometimes abbreviated as 2PC.) When multiple databases participate in the transaction, either all databases commit the changes, or all databases roll back the changes.
See also commit.
See also rollback.
See also transaction.
See also XA.
See also XA.
U
Data that is maintained throughout the life of a transaction, recording all changes so that they can be undone in case of a rollback operation.
See also rollback.
See also transaction.
See also undo log.
See undo log.
A storage area that holds copies of data modified by active transactions. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from this storage area. This area is physically part of the system tablespace. It is split into separate portions, the insert undo buffer and the update undo buffer. Collectively, these parts are also known as the rollback segment, a familiar term for Oracle DBAs.
See also consistent read.
See also rollback segment.
See also system tablespace.
See also transaction.
A kind of constraint that asserts that a column can not contain any duplicate values. In terms of relational algebra, it is used to specify 1-to-1 relationships. For efficiency in checking whether a value can be inserted, (that is, the value does not already exist in the column), a unique constraint is supported by an underlying unique index.
See also constraint.
See also relational.
See also unique index.
An index on a column or set of columns that have a unique constraint. Because the index is known not to contain any duplicate values, certain kinds of lookups and count operations are more efficient than in the normal kind of index. Most of the lookups against this type of index are simply to determine if a certain value exists or not. The number of values in the index is the same as the number of rows in the table, or at least the number of rows with non-null values for the associated columns.
See also cardinality.
See also unique constraint.
See also unique key.
The set of columns (one or more) comprising a
unique index. When you can
define a WHERE condition that matches exactly
one row, and the query can use an associated unique index, the
lookup and error handling can be performed very efficiently.
See also cardinality.
See also unique constraint.
See also unique index.
V
The transaction that is automatically chosen to be rolled back when a deadlock is detected. InnoDB rolls back the transaction that has updated the fewest rows.
See also deadlock.
See also deadlock detection.
See also innodb_lock_wait_timeout.
W
When an operation, such as acquiring a
lock,
mutex, or
latch, cannot be completed
immediately, InnoDB pauses and tries again. The mechanism for
pausing is elaborate enough that this operation has its own
name, the wait. Individual
threads are paused using a combination of internal InnoDB
scheduling, operating system wait() calls,
and short-duration spin loops.
On systems with heavy load and many transactions, you might use
the output from the SHOW INNODB STATUS
command to determine whether threads are spending too much time
waiting, and if so, how you can improve
concurrency.
See also concurrency.
See also latch.
See also lock.
See also mutex.
A backup taken while the database is running, but that restricts some database operations during the backup process. For example, tables might become read-only. For busy applications and web sites, you might prefer a hot backup.
See also cold backup.
See also hot backup.
To run a system under a typical workload for some time after startup, so that the buffer pool and other memory regions are filled as they would be under normal conditions. Typically done during performance testing, to ensure consistent results across multiple runs; otherwise, performance might be artificially low during the first run.
See also buffer pool.
The built-in InnoDB storage engine and the InnoDB Plugin are supported on all the same Microsoft Windows versions as MySQL.
The MySQL Enterprise Backup
product is available on Windows, although the
innobackup command is not part of the Windows
edition of the product.
The combination and volume of SQL and other database operations, performed by a database application during typical or peak usage. You can subject the database to a particular workload during performance testing to identify bottlenecks, or during capacity planning.
See also bottleneck.
See also SQL.
An optimization technique that reduces write operations when dirty pages are flushed from the InnoDB buffer pool. If a row in a page is updated multiple times, or multiple rows on the same page are updated, all of those changes are stored to the data files in a single write operation rather than one write for each change.
See also buffer pool.
See also dirty page.
See also flush.
X
A standard interface for coordinating distributed transactions, allowing multiple databases to participate in a transaction while maintaining ACID compliance.
XA Distributed Transaction support is turned on by default. If you are not using this feature, note that it adds an extra fsync for each transaction, which may adversely affect performance.
See also commit.
See also transaction.
See also two-phase commit.
See also two-phase locking.
This is the User’s Guide for the InnoDB storage engine 1.1 for MySQL 5.5, generated on 2010-12-23 (revision: 24527) .
© 2010, Oracle Corporation and/or its affiliates
