Buffer PoolThe buffer pool is an area in main memory where
InnoDBcaches table and index data as data is accessed. The buffer pool allows frequently used data to be processed directly from memory, which speeds up processing. On dedicated database servers, up to 80% of physical memory is often assigned to the
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; data that is rarely used is aged out of the cache, using a variation of the LRU algorithm.
The change buffer is a special data structure that caches changes to secondary index pages when affected pages are not in the buffer pool. The buffered changes, which may result from
DELETEoperations (DML), are merged later when the pages are loaded into the buffer pool by other read operations.
Redo Log Buffer
The redo log buffer is the memory area that holds data to be written to the redo log. Redo log buffer size is defined by the
innodb_log_buffer_sizeconfiguration option. The redo log buffer is periodically flushed to the log file on disk. A large redo log buffer enables large transactions to run without the need to write redo log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O.
innodb_flush_log_at_trx_commitoption controls how the contents of the redo log buffer are written to the log file. The
innodb_flush_log_at_timeoutoption controls redo log flushing frequency.
InnoDBsystem tablespace contains the
InnoDBdata dictionary (metadata for
InnoDB-related objects) and is the storage area for the doublewrite buffer, the change buffer, and undo logs. The system tablespace also contains table and index data for any user-created tables that are created in the system tablespace. The system tablespace is considered a shared tablespace since it is shared by multiple tables.
The system tablespace is represented by one or more data files. By default, one system data file, named
ibdata1, is created in the MySQL
datadirectory. The size and number of system data files is controlled by the
InnoDB Data DictionaryThe
InnoDBdata dictionary is comprised of internal system tables that contain metadata used to keep track of objects such as tables, indexes, and table columns. The metadata is physically located in the
InnoDBsystem tablespace. For historical reasons, data dictionary metadata overlaps to some degree with information stored in
InnoDBtable metadata files (
doublewrite BufferThe doublewrite buffer is a storage area located in the system tablespace where
InnoDBwrites pages that are flushed from the
InnoDBbuffer pool, before the pages are written to their proper positions in the data file. Only after flushing and writing pages to the doublewrite buffer, does
InnoDBwrite pages to their proper positions. If there is an operating system, storage subsystem, or mysqld process crash in the middle of a page write,
InnoDBcan 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 doublewrite buffer itself as a large sequential chunk, with a single
fsync()call to the operating system.
The doublewrite buffer is enabled by default in most cases. To disable the doublewrite buffer, set
If system tablespace files (ibdata files”) are located on Fusion-io devices that support atomic writes, doublewrite buffering is automatically disabled and Fusion-io atomic writes are used for all data files. Because the doublewrite buffer setting is global, doublewrite buffering is also disabled for data files residing on non-Fusion-io hardware. This feature is only supported on Fusion-io hardware and is only enabled for Fusion-io NVMFS on Linux. To take full advantage of this feature, an
An undo log is a collection of undo log records associated with a single transaction. An undo log record contains information about how to undo the latest change by a transaction to a clustered index record. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from the undo log records. Undo logs exist within undo log segments, which are contained within rollback segments. By default, rollback segments are physically part of the system tablespace. However, rollback segments can reside in separate undo tablespaces. For more information, see Section 15.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”. For information about multi-versioning, see Section 15.3, “InnoDB Multi-Versioning”.
InnoDBsupports 128 rollback segments, 32 of which are reserved as non-redo rollback segments for temporary table transactions. Each transaction that updates a temporary table (excluding read-only transactions) is assigned two rollback segments, one redo-enabled rollback segment and one non-redo rollback segment. Read-only transactions are only assigned non-redo rollback segments, as read-only transactions are only permitted to modify temporary tables.
This leaves 96 available rollback segments, each of which supports up to 1023 concurrent data-modifying transactions, for a total limit of approximately 96K concurrent data-modifying transactions. The 96K limit assumes that transactions do not modify temporary tables. If all data-modifying transactions also modify temporary tables, the total limit is approximately 32K concurrent data modifying transactions. For more information about rollback segments that are reserved for temporary table transactions, see Section 126.96.36.199, “InnoDB Temporary Table Undo Logs”.
innodb_undo_logsoption defines the number of rollback segments used by
A file-per-table tablespace is a single-table tablespace that is created in its own data file rather than in the system tablespace. Tables are created in file-per-table tablespaces when the
innodb_file_per_tableoption is enabled. Otherwise,
InnoDBtables are created in the system tablespace. Each file-per-table tablespace is represented by a single
.ibddata file, which is created in the database directory by default.
File per-table tablespaces support
COMPRESSEDrow formats which support features such as off-page storage for variable length data and table compression. For information about these features, and about other advantages of file-per-table tablespaces, see Section 15.7.4, “InnoDB File-Per-Table Tablespaces”.
InnoDBtablespace created using
CREATE TABLESPACEsyntax. General tablespaces can be created outside of the MySQL data directory, are capable of holding multiple tables, and support tables of all row formats.
Tables are added to a general tablespace using
... TABLESPACE [=]or
For more information, see Section 15.7.9, “InnoDB General Tablespaces”.
An undo tablespace comprises one or more files that contain undo logs. Undo logs exist within undo log segments, which are contained within rollback segments. By default, rollback segments are physically part of the system tablespace. However, rollback segments can reside in separate undo tablespaces. An undo tablespace is created when the undo log is separated from the system tablespace using the
For more information, see Section 15.7.7, “Storing InnoDB Undo Logs in Separate Tablespaces”.
The temporary tablespace is a tablespace for non-compressed
InnoDBtemporary tables and related objects. The configuration option,
innodb_temp_data_file_path, defines a relative path for the temporary tablespace data file. If
innodb_temp_data_file_pathis not defined, a single auto-extending 12MB data file named
ibtmp1is created in the data directory. The temporary tablespace is recreated on each server start and receives a dynamically generated space ID, which helps avoid conflicts with existing space IDs. The temporary tablespace cannot reside on a raw device. Startup is refused if the temporary tablespace cannot be created.
The temporary tablespace is removed on normal shutdown or on an aborted initialization. The temporary tablespace is not removed when a crash occurs. In this case, the database administrator may remove the temporary tablespace manually or restart the server with the same configuration, which removes and recreates the temporary tablespace.
Temporary table undo logs are used for temporary tables and related objects. This type of undo log is not a redo log, as temporary tables are not recovered during crash recovery and do not require redo logs. Temporary table undo logs are, however, used for rollback while the server is running. This special type of non-redo undo log benefits performance by avoiding redo logging I/O for temporary tables and related objects. Temporary table undo logs reside in the temporary tablespace. The default temporary tablespace file,
ibtmp1, is located in the data directory by default and is always recreated on server startup. A user defined location for the temporary tablespace file can be specified by setting
32 rollback segments are reserved for temporary table undo logs for transactions that modify temporary tables and related objects, which means that the maximum number of rollback segments available for data-modifying transactions that generate undo records is 96. With 96 available rollback segments, the limit on concurrent data-modifying transactions is 96K. For more information see Section 15.3, “InnoDB Multi-Versioning” andSection 15.8.8, “Limits on InnoDB Tables”.
The redo log is a disk-based data structure used during crash recovery to correct data written by incomplete transactions. During normal operations, the redo log encodes requests to change
InnoDBtable data that result from SQL statements or low-level API calls. Modifications that did not finish updating the data files before an unexpected shutdown are replayed automatically during initialization, and before the connections are accepted. For information about the role of the redo log in crash recovery, see Section 15.18.2, “InnoDB Recovery”.
By default, the redo log is physically represented on disk as a set of files, named
ib_logfile1. MySQL writes to the redo log files in a circular fashion. Data in the redo log is encoded in terms of records affected; this data is collectively referred to as redo. The passage of data through the redo log is represented by an ever-increasing LSN value.