The InnoDB doublewrite buffer was implemented to recover from half-written pages. This can happen when there's a power failure while InnoDB is writing a page to disk. On reading that page, InnoDB can discover the corruption from the mismatch of the page checksum. However, in order to recover, an intact copy of the page would be needed.

The double write buffer provides such a copy.

Whenever InnoDB flushes a page to disk, it is first written to the double write buffer. Only when the buffer is safely flushed to disk will InnoDB write the page to the final destination. When recovering, InnoDB scans the double write buffer and for each valid page in the buffer checks if the page in the data file is valid too.

Doublewrite Buffer Settings

To turn off the doublewrite buffer, set the system variable to 0. This is safe on filesystems that write pages atomically - that is, a page write fully succeeds or fails. But with other filesystems, it is not recommended for production systems. An alternative option is atomic writes. See for more details.

_{This page is licensed: CC BY-SA / Gnu FDL}

The InnoDB Monitor refers to particular kinds of monitors included in MariaDB and since the early versions of MySQL.

There are four types: the standard InnoDB monitor, the InnoDB Lock Monitor, InnoDB Tablespace Monitor and the InnoDB Table Monitor.

Standard InnoDB Monitor

The standard InnoDB Monitor returns extensive InnoDB information, particularly lock, semaphore, I/O and buffer activity:

To enable the standard InnoDB Monitor, from , set the innodb_status_output system variable to 1. Before , running the following statement was the method used:

To disable the standard InnoDB monitor, either set the system variable to zero, or, before , drop the table

The CREATE TABLE and DROP TABLE method of enabling and disabling the InnoDB Monitor has been deprecated, and may be removed in a future version of MariaDB.

For a description of the output, see .

InnoDB Lock Monitor

The InnoDB Lock Monitor displays additional lock information.

To enable the InnoDB Lock Monitor, the standard InnoDB monitor must be enabled. Then, from , set the system variable to 1. Before , running the following statement was the method used:

To disable the standard InnoDB monitor, either set the system variable to zero, or, before , drop the table

The CREATE TABLE and DROP TABLE method of enabling and disabling the InnoDB Lock Monitor has been deprecated, and may be removed in a future version of MariaDB.

InnoDB Tablespace Monitor

The InnoDB Tablespace Monitor is deprecated, and may be removed in a future version of MariaDB.

Enabling the Tablespace Monitor outputs a list of file segments in the shared tablespace to the error log, and validates the tablespace allocation data structures.

To enable the Tablespace Monitor, run the following statement:

To disable it, drop the table:

InnoDB Table Monitor

The InnoDB Table Monitor is deprecated, and may be removed in a future version of MariaDB.

Enabling the Table Monitor outputs the contents of the InnoDB internal data dictionary to the error log every fifteen seconds.

To enable the Table Monitor, run the following statement:

To disable it, drop the table:

SHOW ENGINE INNODB STATUS

The statement can be used to obtain the standard InnoDB Monitor output when required, rather than sending it to the error log. It will also display the InnoDB Lock Monitor information if the system variable is set to 1.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

When both innodb_flush_log_at_trx_commit=1 (the default) is set and the binary log is enabled, there is now one less sync to disk inside InnoDB during commit (2 syncs shared between a group of transactions instead of 3).

Durability of commits is not decreased — this is because even if the server crashes before the commit is written to disk by InnoDB, it are recovered from the binary log at next server startup (and it is guaranteed that sufficient information is synced to disk so that such a recovery is always possible).

Switching to Old Flushing Behavior

The old behavior, with 3 syncs to disk per (group) commit (and consequently lower performance), can be selected with the new option. There is normally no benefit to doing this, however there are a couple of edge cases to be aware of.

Non-durable Binary Log Settings

If is set and the is enabled, but is set, then commits are not guaranteed durable inside InnoDB after commit. This is because if is set and if the server crashes, then transactions that were not flushed to the binary log prior to the crash are missing from the binary log.

In this specific scenario, can be set to ensure that transactions are durable in InnoDB, even if they are not necessarily durable from the perspective of the binary log.

One should be aware that if is set, then a crash is nevertheless likely to cause transactions to be missing from the binary log. This will cause the binary log and InnoDB to be inconsistent with each other. This is also likely to cause any to become inconsistent, since transactions are replicated through the . Thus it is recommended to set . With the improvements introduced in , this setting has much less penalty in recent versions compared to older versions of MariaDB and MySQL.

Recent Transactions Missing from Backups

and only see transactions that have been flushed to the . With the improvements, there may be a small delay (defined by the system variable) between when a commit happens and when the commit are included in a backup.

Note that the backup will still be fully consistent with itself and the . This problem is normally not an issue in practice. A backup usually takes a long time to complete (relative to the 1 second or so that is normally set to), and a backup usually includes a lot of transactions that were committed during the backup. With this in mind, it is not generally noticeable if the backup does not include transactions that were committed during the last 1 second or so of the backup process. It is just mentioned here for completeness.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

Starting with MariaDB Enterprise Server 10.5 and MariaDB Community Server 10.5, the InnoDB I/O Threads were replaced by the asynchronous I/O functionality in the InnoDB Background Thread Pool.

Feature Summary

Basic Configuration

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Sometimes there is a requirement that when some data is deleted, it is really gone. This might be the case when one stores user's personal information or some other sensitive data. Normally though, when a row is deleted, the space is only marked as free on the page. It may eventually be overwritten, but there is no guarantee when that will happen. A copy of the deleted rows may also be present in the log files.

Support for data scrubbing: Background threads periodically scan tablespaces and logs and remove all data that should be deleted. The number of background threads for tablespace scans is set by . Log scrubbing happens in a separate thread.

To configure scrubbing one can use the following variables:

Setting a Table's File Format

A table's tablespace is tagged with the lowest InnoDB file format that supports the table's . So, even if the Barracuda file format is enabled, tables that use the COMPACT or REDUNDANT row formats are tagged with the Antelope file format in the table.

Overview

MariaDB Enterprise Server uses the InnoDB storage engine by default. InnoDB is a general purpose transactional storage engine that is performant, ACID-compliant, and well-suited for most workloads.

Benefits

Locks are acquired by a transaction to prevent concurrent transactions from modifying, or even reading, some rows or ranges of rows. This is done to make sure that concurrent write operations never collide.

supports a number of lock modes.

Shared and Exclusive Locks

The two standard row-level locks are share locks(S) and exclusive locks(X).

A shared lock is obtained to read a row, and allows other transactions to read the locked row, but not to write to the locked row. Other transactions may also acquire their own shared locks.

Benchmarks attached to MDEV-19514 show that the change buffer sometimes reduces performance, and in the best case seem to bring a few per cent improvement to throughput. However, such improvement could come with a price: If the buffered changes are never merged (MDEV-19514, motivated by the reduction of random crashes and the removal of an innodb_force_recovery option that can inflict further corruption), then the InnoDB system tablespace can grow out of control (MDEV-21952).

INSERT, UPDATE, and DELETE statements can be particularly heavy operations to perform, as all indexes need to be updated after each change. For this reason these changes are often buffered.

Pages are modified in the , and not immediately on disk. After all the records that cover the changes to a data page have been written to the InnoDB redo log, the changed page may be written (''flushed'') to a data file. Pages that have been modified in memory and not yet flushed are called dirty pages.

The Change Buffer is an optimization that allows some data to be modified even though the data page does not exist in the buffer pool. Instead of modifying the data in its final destination, we would insert a record into a special Change Buffer that resides in the system tablespace. When the page is read into the buffer pool for any reason, the buffered changes are applied to it.

The Change Buffer only contains changes to secondary index leaf pages.

In the days of old, only inserted rows could be buffered, so this buffer was called Insert Buffer. The old name still appears in several places, for example in the output of .

Inserts to UNIQUE secondary indexes cannot be buffered unless is used. This may sometimes allow duplicates to be inserted into the UNIQUE secondary index. Much of the time, the UNIQUE constraint would be checked because the change buffer could only be used if the index page is not located in the buffer pool.

When rows are deleted, a flag is set, thus rows are not immediately deleted. Delete-marked records may be purged after the transaction has been committed and any read views that were created before the commit have been closed. Delete-mark and purge buffering of any secondary indexes is allowed.

ROLLBACK never makes use of the change buffer; it would force a merge of any changes that were buffered during the execution of the transaction.

The Change Buffer is an optimization because:

• Some random-access page reads are transformed into modifications of change buffer pages.

• A change buffer page can be modified several times in memory and be flushed to disk only once.

• Dirty pages are flushed together, so the number of IO operations is lower.

If the server crashes or is shut down, the Change Buffer might not be empty. The Change Buffer resides in the InnoDB system tablespace, covered by the write-ahead log, so they can be applied at server restart. A shutdown with will merge all buffered changes.

There is no background task that merges the change buffer to the secondary index pages. Changes are only merged on demand.

The main server system variable here is , which determines which form of change buffering, if any, to use.

The following settings are available:

• inserts

  • Only buffer insert operations

• deletes

  • Only buffer delete operations

Modifying the value of this variable only affects the buffering of new operations. The merging of already buffered changes is not affected.

The system variable determines the maximum size of the change buffer, expressed as a percentage of the buffer pool.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

Page Flushing with InnoDB Page Cleaner Threads

InnoDB page cleaner threads flush dirty pages from the InnoDB buffer pool. These dirty pages are flushed using a least-recently used (LRU) algorithm.

innodb_max_dirty_pages_pct

The variable specifies the maximum percentage of unwritten (dirty) pages in the . If this percentage is exceeded, flushing will take place.

innodb_max_dirty_pages_pct_lwm

The variable determines the low-water mark percentage of dirty pages that will enable preflushing to lower the dirty page ratio. The value 0 (the default) means that there are no separate background flushing so long as:

• the share of dirty pages does not exceed

• the last checkpoint age (LSN difference since the latest checkpoint) does not exceed (minus some safety margin)

• the is not running out of space, which could trigger eviction flushing

To make flushing more eager, set to a higher value, for example SET GLOBAL innodb_max_dirty_pages_pct_lwm=0.001;

Page Flushing with Multiple InnoDB Page Cleaner Threads

The system variable makes it possible to use multiple InnoDB page cleaner threads. It is deprecated and ignored now as the original reasons for splitting the buffer pool have mostly gone away.

The number of InnoDB page cleaner threads can be configured by setting the system variable. The system variable can be set in a server in an prior to starting up the server:

The system variable can be changed dynamically with :

This system variable's default value is either 4 or the configured value of the system variable, whichever is lower.

Page Flushing with a Single InnoDB Page Cleaner Thread

Since the original reasons for splitting the buffer pool have mostly gone away, only a single InnoDB page cleaner thread is supported.

Page Flushing with Multi-threaded Flush Threads

InnoDB's multi-thread flush feature can be enabled by setting the system variable. The number of threads cane be configured by setting the system variable. This system variable can be set in a server in an prior to starting up the server:

The system variable's default value is 8. The maximum value is 64. In multi-core systems, it is recommended to set its value close to the configured value of the system variable. However, it is also recommended to use your own benchmarks to find a suitable value for your particular application.

Note

InnoDB's multi-thread flush feature is deprecated. Use multiple InnoDB page cleaner threads instead.

Configuring the InnoDB I/O Capacity

Increasing the amount of I/O capacity available to InnoDB can also help increase the performance of page flushing.

The amount of I/O capacity available to InnoDB can be configured by setting the system variable. This system variable can be changed dynamically with :

This system variable can also be set in a server in an prior to starting up the server:

The maximum amount of I/O capacity available to InnoDB in an emergency defaults to either 2000 or twice , whichever is higher, or can be directly configured by setting the system variable. This system variable can be changed dynamically with :

This system variable can also be set in a server in an prior to starting up the server:

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

AUTO_INCREMENT Lock Modes

The system variable determines the lock mode when generating values for tables. These modes allow to make significant performance optimizations in certain circumstances.

The system variable may be removed in a future release. See for more information.

The InnoDB storage engine in MariaDB Enterprise Server utilizes the Buffer Pool as a crucial in-memory cache. This Buffer Pool stores recently accessed data pages, enabling faster retrieval for subsequent requests. Recognizing patterns of access, InnoDB also employs predictive prefetching, caching nearby pages when sequential access is detected. To manage memory efficiently, a least recently used (LRU) algorithm is used to evict older, less frequently accessed pages.

To optimize server restarts, the Buffer Pool's contents can be preserved across shutdowns. At shutdown, the page numbers of all pages residing in the Buffer Pool are recorded. Upon the next startup, InnoDB reads this dump of page numbers and reloads the corresponding data pages from their respective data files, effectively avoiding a "cold" cache scenario.

The size of each individual page within the Buffer Pool is determined by the setting of the innodb_page_size system variable.

From , uses asynchronous I/O to read from and write to disk asynchronously. This forms part of the InnoDB Background Thread Pool.

Stages

Each asynchronous IO operation goes through multiple stages:

1. SUBMITTED – The IO operation is initiated.

The InnoDB storage engine has the following limitations.

Limitations on Schema

• InnoDB tables can have a maximum of 1,017 columns. This includes virtual generated columns.

• InnoDB tables can have a maximum of 64 secondary indexes.

• A multicolumn index on InnoDB can use a maximum of 32 columns. If you attempt to create a multicolumn index that uses more than 32 columns, MariaDB returns an Error 1070.

Limitations on Size

• With the exception of variable-length columns (that is, , , and ), rows in InnoDB have a maximum length of roughly half the page size for 4KB, 8KB, 16KB and 32KB page sizes.

• The maximum size for and columns is 4GB. This also applies to and .

• MariaDB imposes a row-size limit of 65,535 bytes for the combined sizes of all columns. If the table contains or columns, these only count for 9 - 12 bytes in this calculation, given that their content is stored separately.

Page Sizes

Using the system variable, you can configure the size in bytes for InnoDB pages. Pages default to 16KB. There are certain limitations on how you use this variable.

• MariaDB instances using one page size cannot use data files or log files from an instance using a different page size.

• When using a Page Size of 4KB or 8KB, the maximum index key length is lowered proportionately.

Limitations on Tables

InnoDB has the following table-specific limitations.

• When you issue a statement, InnoDB doesn't regenerate the table, rather it deletes each row from the table one by one.

• When running MariaDB on Windows, InnoDB stores databases and tables in lowercase. When moving databases and tables in a binary format from Windows to a Unix-like system or from a Unix system to Windows, you need to rename these to use lowercase.

• When using cascading , operations in the cascade don't activate triggers.

Table Analysis

When running twice on a table in which statements or transactions are running, MariaDB blocks the second until the statement or transaction is complete. This occurs because the statement or transaction blocks the second statement from reloading the table definition, which it must do since the old one was marked as obsolete after the first statement.

Table Status

statements do not provide accurate statistics for InnoDB, except for the physical table size.

The InnoDB storage engine does not maintain internal row counts. Transactions isolate writes, which means that concurrent transactions will not have the same row counts.

Auto-incrementing Columns

• When defining an index on an auto-incrementing column, it must be defined in a way that allows the equivalent of SELECT MAX(col) lookups on the table.

• Restarting MariaDB may cause InnoDB to reuse old auto-increment values, such as in the case of a transaction that was rolled back.

• When auto-incrementing columns run out of values, statements generate duplicate-key errors.

Transactions and Locks

• You can modify data on a maximum of 96 * 1023 concurrent transactions that generate undo records.

• Of the 128 rollback segments, InnoDB assigns 32 to non-redo logs for transactions that modify temporary tables and related objects, reducing the maximum number of concurrent data-modifying transactions to 96,000, from 128.000.

• The limit is 32,000 concurrent transactions when all data-modifying transactions also modify temporary tables.

• Issuing a statement sets two locks on each table when the system variable is enabled (the default).

_{This page is licensed: CC BY-SA / Gnu FDL}

XtraDB, previously the default InnoDB replacement in MariaDB, is no longer included in standard distributions. MariaDB now uses InnoDB by default.

The reasons you may want to use InnoDB instead of XtraDB in earlier versions of MariaDB are:

• You want to benchmark the difference between InnoDB/XtraDB

• You hit a bug in XtraDB

• You got a table space crash in XtraDB and recovery doesn't work. In some cases InnoDB may do a better job to recover data.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

When a transaction updates a row in an InnoDB table, InnoDB's MVCC implementation keeps old versions of the row in the InnoDB undo log. The old versions are kept at least until all transactions older than the transaction that updated the row are no longer open. At that point, the old versions can be deleted. InnoDB has purge process that is used to delete these old versions.

InnoDB Purge Threads

In MariaDB Enterprise Server, the InnoDB storage engine uses Purge Threads to perform garbage collection in the background. The Purge Threads are related to multi-version concurrency control (MVCC).

The Purge Threads perform garbage collection of various items:

• The Purge Threads perform garbage collection of the . When a row is updated in the clustered index, InnoDB updates the values in the clustered index, and the old row version is added to the Undo Log. The Purge Threads scan the Undo Log for row versions that are not needed by open transactions and permanently delete them. In ES 10.5 and later, if the remaining clustered index record is the oldest possible row version, the Purge Thread resets the record's hidden DB_TRX_ID field to 0.

• The Purge Threads perform garbage collection of index records. When an indexed column is updated, InnoDB creates a new index record for the updated value in each affected index, and the old index records are delete-marked. When the primary key column is updated, InnoDB creates a new index record for the updated value in every index, and each old index record is delete-marked. The Purge Threads scan for delete-marked index records and permanently delete them.

• The Purge Threads perform garbage collection of freed overflow pages. , , , , , and related types are sometimes stored on overflow pages. When the value on the overflow page is deleted or updated, the overflow page is no longer needed. The Purge Threads delete these freed overflow pages.

Feature Summary

Configuring the Purge Threads

The number of purge threads can be set by configuring the system variable. This system variable can be specified as a command-line argument to or it can be specified in a relevant server in an :

Optimizing Purge Performance

Configuring the Purge Batch Size

The purge batch size is defined as the number of records that must be written before triggering purge. The purge batch size can be set by configuring the system variable. This system variable can be specified as a command-line argument to or it can be specified in a relevant server in an :

Configuring the Max Purge Lag

If purge operations are lagging on a busy server, then this can be a tough situation to recover from. As a solution, InnoDB allows you to set the max purge lag. The max purge lag is defined as the maximum number of that can be waiting to be purged from the history until InnoDB begins delaying DML statements.

The max purge lag can be set by configuring the system variable. This system variable can be changed dynamically with :

This system variable can also be specified as a command-line argument to or it can be specified in a relevant server in an :

The maximum delay can be set by configuring the system variable. This system variable can be changed dynamically with :

This system variable can also be specified as a command-line argument to or it can be specified in a relevant server in an :

Configuring the Purge Rollback Segment Truncation Frequency

The purge rollback segment truncation frequency is defined as the number of purge loops that are run before unnecessary rollback segments are truncated. The purge rollback segment truncation frequency can be set by configuring the system variable. This system variable can be changed dynamically with :

This system variable can also be specified as a command-line argument to or it can be specified in a relevant server in an :

Configuring the Purge Undo Log Truncation

Purge undo log truncation occurs when InnoDB truncates an entire tablespace, rather than deleting individual records.

Purge undo log truncation can be enabled by configuring the system variable. This system variable can be changed dynamically with :

This system variable can also be specified as a command-line argument to or it can be specified in a relevant server in an :

An tablespace is truncated when it exceeds the maximum size that is configured for tablespaces. The maximum size can be set by configuring the system variable. This system variable can be changed dynamically with :

This system variable can also be specified as a command-line argument to or it can be specified in a relevant server in an :

Purge's Effect on Row Metadata

An InnoDB table's clustered index has three hidden system columns that are automatically generated. These hidden system columns are:

• DB_ROW_ID - If the table has no other PRIMARY KEY or no other UNIQUE KEY defined as NOT NULL that can be promoted to the table's PRIMARY KEY, then InnoDB will use a hidden system column called DB_ROW_ID. InnoDB will automatically generated the value for the column from a global InnoDB-wide 48-bit sequence (instead of being table-local).

• DB_TRX_ID - The transaction ID of either the transaction that last changed the row or the transaction that currently has the row locked.

If a row's last record is purged, this can obviously effect the value of the row's DB_ROLL_PTR column, because there would no longer be any record for the pointer to reference.

The purge process will set a row's DB_TRX_ID column to 0 after all of the row's associated records have been deleted. This change allows InnoDB to perform an optimization: if a query wants to read a row, and if the row's DB_TRX_ID column is set to 0, then it knows that no other transaction has the row locked. Usually, InnoDB needs to lock the transaction system's mutex in order to safely check whether a row is locked, but this optimization allows InnoDB to confirm that the row can be safely read without any heavy internal locking.

This optimization can speed up reads, but it come at a noticeable cost at other times. For example, it can cause the purge process to use more I/O after inserting a lot of rows, since the value of each row's DB_TRX_ID column will have to be reset.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

When a transaction writes data, it always inserts them in the table indexes or data (in the buffer pool or in physical files). No private copies are created. The old versions of data being modified by active InnoDB transactions are stored in the undo log. The original data can then be restored, or viewed by a consistent read.

Implementation Details

Before a row is modified, a diff is copied into the undo log. Each normal row contains a pointer to the most recent version of the same row in the undo log. Each row in the undo log contains a pointer to previous version, if any. So, each modified row has a history chain.

Rows are never physically deleted until a transaction ends. If they were deleted, the restore in ROLLBACK would be impossible. Thus, rows are simply marked for deletion.

Each transaction uses a view of the records. The determines how this view is created. For example, READ UNCOMMITTED usually uses the current version of rows, even if they are not committed (dirty reads). Other isolation levels require that the most recent committed version of rows is searched in the undo log. READ COMMITTED uses a different view for each table, while REPEATABLE READ and SERIALIZABLE use the same view for all tables.

There is also a global history list of the data. When a transaction is committed, its history is added to this history list. The order of the list is the chronological order of the commits.

The purge thread deletes the rows in the undo log which are not needed by any existing view. The rows for which a most recent version exists are deleted, as well as the delete-marked rows.

If InnoDB needs to restore an old version, it will simply replace the newer version with the older one. When a transaction inserts a new row, there is no older version. However, in that case, the restore can be done by deleting the inserted rows.

Effects of Long-Running Transactions

Understanding how the undo log works helps with understanding the negative effects long transactions.

• Long transactions generate several old versions of the rows in the undo log. Those rows will probably be needed for a longer time, because other long transactions will need them. Since those transactions will generate more modified rows, a sort of combinatorial explosion can be observed. Thus, the undo log requires more space.

• Transaction may need to read very old versions of the rows in the history list, thus their performance will degrade.

Of course read-only transactions do not write more entries in the undo log; however, they delay the purging of existing entries.

Also, long transactions can more likely result in deadlocks, but this problem is not related to the undo log.

Feature Summary

Configuration

System variables affecting undo logs include:

The undo log is not a log file that can be viewed on disk in the usual sense, such as the or , but rather an area of storage.

Before , the undo log is usually part of the physical system tablespace, but from , the and system variables can be used to split into different tablespaces and store in a different location (perhaps on a different storage device). From , multiple undo tablespaces are enabled by default, and the default is changed to 3 so that the space occupied by possible bursts of undo log records can be reclaimed after is set.

Each insert or update portion of the undo log is known as a rollback segment. The system variable allowed to reduce the number of rollback segments from the usual 128, to limit the number of concurrently active write transactions. was deprecated and ignored in and removed in , as it always makes sense to use the maximum number of rollback segments.

The related status variable stores the total number of available InnoDB undo logs.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

Starting with MariaDB Enterprise Server 10.5 and MariaDB Community Server 10.5, InnoDB uses the InnoDB Background Thread Pool to perform internal operations in the background. In previous versions, the internal operations were performed by dedicated threads. By using the InnoDB Background Thread Pool instead of many dedicated threads, InnoDB can reduce context switching and use system resources more effectively.

The InnoDB Background Thread Pool performs internal operations in multiple categories: tasks, timers, and asynchronous I/O.

Tasks are used to perform internal operations that are triggered by some event. In ES 10.5 and later and CS 10.5 and later, the following threads have been replaced by tasks with the InnoDB Background Thread Pool:

• The InnoDB Buffer Pool Resize Thread

• The InnoDB Buffer Pool Dump Thread

• The InnoDB Full-Text Search (FTS) Optimization Thread

Timers are used to perform internal operations that are triggered periodically. In ES 10.5 and later and CS 10.5 and later, the following threads have been replaced by timers with the InnoDB Background Thread Pool:

• The InnoDB Master Thread

• The InnoDB Defragmentation Thread

• The InnoDB Monitor Thread

• The InnoDB Error Monitor Thread

Asynchronous I/O is used to read from and write to disk asynchronously. In ES 10.5 and later and CS 10.5 and later, the following threads have been replaced by asynchronous I/O with the InnoDB Background Thread Pool:

• The

Feature Summary

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

The REDUNDANT row format is the original non-compacted row format.

The REDUNDANT row format was the only available row format before MySQL 5.0.3. In that release, this row format was retroactively named the REDUNDANT row format. In the same release, the COMPACT row format was introduced as the new default row format.

Using the REDUNDANT Row Format

• Redundant row format should not be used in modern versions of MariaDB Server.

• Redundant row format does not store large columns as efficiently as the Dynamic row format.

• Redundant row format limits indexing column values to 767 bytes, which is significant smaller than the Dynamic row format.

The easiest way to create an InnoDB table that uses the REDUNDANT row format is by setting the table option to REDUNDANT in a or statement.

It is recommended to set the system variable to ON when using this format.

The REDUNDANT row format is supported by both the Antelope and the Barracuda , so tables with this row format can be created regardless of the value of the system variable.

For example:

Index Prefixes with the REDUNDANT Row Format

The REDUNDANT row format supports index prefixes up to 767 bytes.

Overflow Pages with the REDUNDANT Row Format

All InnoDB row formats can store certain kinds of data in overflow pages. This allows for the maximum row size of an InnoDB table to be larger than the maximum amount of data that can be stored in the row's main data page. See for more information about the other factors that can contribute to the maximum row size for InnoDB tables.

In the REDUNDANT row format variable-length columns, such as columns using the , , and data types, can be partially stored in overflow pages.

InnoDB only considers using overflow pages if the table's row size is greater than half of . If the row size is greater than this, then InnoDB chooses variable-length columns to be stored on overflow pages until the row size is less than half of .

For , , and columns, only values longer than 767 bytes are considered for storage on overflow pages. Bytes that are stored to track a value's length do not count towards this limit. This limit is only based on the length of the actual column's data.

Fixed-length columns greater than 767 bytes are encoded as variable-length columns, so they can also be stored in overflow pages if the table's row size is greater than half of . Even though a column using the data type can hold at most 255 characters, a column can still exceed 767 bytes in some cases. For example, a char(255) column can exceed 767 bytes if the is utf8mb4.

If a column is chosen to be stored on overflow pages, then the first 767 bytes of the column's value and a 20-byte pointer to the column's first overflow page are stored on the main page. Each overflow page is the size of [innodb-system-variables#innodb_page_size|innodb_page_size]]. If a column is too large to be stored on a single overflow page, then it is stored on multiple overflow pages. Each overflow page contains part of the data and a 20-byte pointer to the next overflow page, if a next page exists.

_{This page is licensed: CC BY-SA / Gnu FDL}

MariaDB Enterprise Server InnoDB Schema Changes with the INPLACE Algorithm

Changes in enterprise databases and applications are inevitable. Schema changes often block other workloads, incur unique production situations that cannot be fully simulated for testing, and may have unpredictable execution times.

MariaDB Enterprise Server includes the In-place ALTER functionality for the InnoDB storage engine, such that:

When possible, schema change operations are performed INPLACE, minimizing impact on other workloads.

When the alter_algorithm system variable is set to INPLACE, schema change operations will not run unless they can be performed INPLACE, minimizing the risk of unpredictable behavior.

Additional information is available .

MariaDB Enterprise Server InnoDB Schema Changes with the INSTANT Algorithm

Changes in enterprise databases and applications are inevitable. Schema changes often block other workloads, incur unique production situations that cannot be fully simulated for testing, and may have unpredictable execution times.

MariaDB Enterprise Server includes the Instant ALTER functionality for the InnoDB storage engine, such that:

When possible, schema change operations are performed INSTANT, minimizing impact on other workloads.

When the alter_algorithm system variable is set to INSTANT, schema change operations will not run unless they can be performed INSTANT, minimizing the risk of unpredictable behavior.

Additional information is available .

MariaDB Enterprise Server InnoDB Schema Changes with the NOCOPY Algorithm

Changes in enterprise databases and applications are inevitable. Schema changes often block other workloads, incur unique production situations that cannot be fully simulated for testing, and may have unpredictable execution times.

MariaDB Enterprise Server includes the No-copy ALTER functionality for the InnoDB storage engine, such that:

When possible, schema change operations are performed NOCOPY, minimizing impact on other workloads.

When the alter_algorithm system variable is set to NOCOPY, schema change operations will not run unless they can be performed NOCOPY, minimizing the risk of unpredictable behavior.

Additional information is available .

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Can't Open File

If InnoDB returns something like the following error:

it may be that an orphan .frm file exists. Something like the following may also appear in the error log:

If this is the case, as the text describes, delete the orphan .frm file on the filesystem.

Could not find a valid tablespace file

In this case the table definition, the .frm file, is missing and the InnoDB dictionary expects it to be there. To remove the InnoDB dictionary entry, the existence of the file needs to faked and then dropped. The existence of the file is faked by creating the tablename.frm and potential the database directory if it is missing. Then a DROP TABLE or DROP DATABASE can be executed which will remove the InnoDB dictionary entry.

Use the query to identify potentially other tablespaces that are known but missing with:

Removing Orphan Intermediate Tables

An orphan intermediate table may prevent you from dropping the tablespace even if it is otherwise empty, and generally takes up unnecessary space.

It may come about if MariaDB exits in the middle of an operation. They are listed in the table, and always start with an #sql-ib prefix. The accompanying .frm file also begins with #sql-, but has a different name.

To identify orphan tables, run:

When is set, the #sql-*.ibd file will also be visible in the database directory.

To remove an orphan intermediate table:

• Rename the #sql-*.frm file (in the database directory) to match the base name of the orphan intermediate table, for example:

• Drop the table, for example:

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

Normally, adding a column to a table requires the full table to be rebuilt. The complexity of the operation is proportional to the size of the table, or O(n·m) where n is the number of rows in the table and m is the number of indexes.

In and later, the ALTER TABLE statement supports online DDL for storage engines that have implemented the relevant online DDL algorithms and locking strategies.

The InnoDB storage engine has implemented online DDL for many operations. These online DDL optimizations allow concurrent DML to the table in many cases, even if the table needs to be rebuilt.

See InnoDB Online DDL Overview for more information about online DDL with InnoDB.

Allowing concurrent DML during the operation does not solve all problems. When a column was added to a table with the older in-place optimization, the resulting table rebuild could still significantly increase the I/O and memory consumption and cause replication lag.

In contrast, with the new instant ALTER TABLE ... ADD COLUMN, all that is needed is an O(1) operation to insert a special hidden record into the table, and an update of the data dictionary. For a large table, instead of taking several hours, the operation would be completed in the blink of an eye. The ALTER TABLE ... ADD COLUMN operation is only slightly more expensive than a regular , due to locking constraints.

In the past, some developers may have implemented a kind of "instant add column" in the application by encoding multiple columns in a single or column. MariaDB was an early example of that. A more recent example is and related string manipulation functions.

Adding real columns has the following advantages over encoding columns into a single "expandable" column:

• Efficient storage in a native binary format

• Data type safety

• Indexes can be built natively

• Constraints are available: UNIQUE, CHECK, FOREIGN KEY

With instant , you can enjoy all the benefits of structured storage without the drawback of having to rebuild the table.

Instant is available for both old and new InnoDB tables. Basically you can just upgrade from MySQL 5.x or MariaDB and start adding columns instantly.

Columns instantly added to a table exist in a separate data structure from the main table definition, similar to how InnoDB separates BLOB columns. If the table ever becomes empty, (such as from or statements), InnoDB incorporates the instantly added columns into the main table definition. See for more information.

The operation is also crash safe. If the server is killed while executing an instant , when the table is restored InnoDB integrates the new column, flattening the table definition.

Limitations

• In , instant only applies when the added columns appear last in the table. The place specifier LAST is the default. If AFTER col is specified, then col must be the last column, or the operation will require the table to be rebuilt. In , this restriction was lifted.

• If the table contains a hidden FTS_DOC_ID column due to a , then instant will not be possible.

• InnoDB data files after instant

Example

The above example illustrates that when the added columns are declared NOT NULL, a DEFAULT value must be available, either implied by the data type or set explicitly by the user. The expression need not be constant, but it must not refer to the columns of the table, such as DEFAULT u+1 (a MariaDB extension). The DEFAULT current_timestamp() would be evaluated at the time of the ALTER TABLE and apply to each row, like it does for non-instant ALTER TABLE. If a subsequent ALTER TABLE changes the DEFAULT value for subsequent INSERT, the values of the columns in existing records will naturally be unaffected.

The design was brainstormed in April by engineers from MariaDB Corporation, Alibaba and Tencent. A prototype was developed by Vin Chen (陈福荣) from the Tencent Game DBA Team.

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

When the user creates a temporary table using the CREATE TEMPORARY TABLE statement and the engine is set as InnoDB, MariaDB creates a temporary tablespace file. When the table is not compressed, MariaDB writes to a shared temporary tablespace as defined by the innodb_temp_data_file_path system variable. MariaDB does not allow the creation of ROW_FORMAT=COMPRESSED temporary tables. All temporary tables are uncompressed. MariaDB deletes temporary tablespaces when the server shuts down gracefully and is recreated when it starts again. It cannot be placed on a raw device.

Internal temporary tablespaces, (that is, temporary tables that cannot be kept in memory) use either Aria or MyISAM, depending on the aria_used_for_temp_tables system variable. You can set the default storage engine for user-created temporary tables using the default_tmp_storage_engine system variable.

Prior to , temporary tablespaces existed as part of the InnoDB system tablespace or were file-per-table depending on the configuration of the innodb_file_per_table system variable.

Syntax for the value of the innodb_temp_data_file_path variable

The options for is one path or a set of paths, separated by ';'.

The syntax for each path is:

size can have extensions 'G' (Gigabytes), 'M' (Megabytes) or 'K' (Kilobytes). If no extension is given, then megabytes is assumed.

• The first size argument is the initial size of the temporary table space.

• autoextend means that the file size will automatically increase if needed.

• max can be used to limit the total size of the temporary file if autoextend is used.

Only the last path can have the autoextend , max and autoshrink options.

Sizing Temporary Tablespaces

In order to size temporary tablespaces, use the system variable. This system variable can be specified as a command-line argument to or it can be specified in a relevant server in an :

This system variable's syntax is the same as the system variable. That is, a file name, size and option. By default, it writes a 12MB autoextending file to ibtmp1 in the data directory.

To increase the size of the temporary tablespace, you can add a path to an additional tablespace file to the value of the system variable. Providing additional paths allows you to spread the temporary tablespace between multiple tablespace files. The last file can have the autoextend attribute, which ensures that you won't run out of space:

Unlike normal tablespaces, temporary tablespaces are deleted when you stop MariaDB. To shrink temporary tablespaces to their minimum sizes, restart the server.

Shrinking the Tablespace

From , the temporary tablespace can be shrunk by setting to ON:

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

In MariaDB Enterprise Server, the InnoDB storage engine uses a Redo Log. The Redo Log is a transaction log that InnoDB uses to write data to disk in a crash-safe manner.

Redo Log records are identified using the Log Sequence Number (LSN). The Redo Log is a circular log file that is a constant size. Old Redo Log records are frequently overwritten by new Redo Log records. InnoDB regularly performs checkpoints. During a checkpoint, InnoDB flushes Redo Log records to the InnoDB tablespace files.

When the server crashes, InnoDB performs crash recovery during server startup using the Redo Log. During crash recovery, InnoDB finds the last checkpoint in the Redo Log and flushes the Redo Log records since the last checkpoint to the InnoDB tablespace files.

For additional information, see "".

This page describes how to configure the InnoDB Redo Log.

Configure the InnoDB Redo Log Size

The size of the InnoDB Redo Log is configurable. If your server writes data at a very high frequency, then you may need to increase the redo log size, so that InnoDB does not have to perform checkpoints as frequently.

For the maximum capacity in the Redo Log, the Redo Log size should be the same as the , which is configured by the system variable.

The method to configure the Redo Log size depends on the server version and whether a server restart are performed:

Configure the InnoDB Redo Log Size with SET GLOBAL (ES 10.5 and Later)

Starting in MariaDB Enterprise Server 10.5, the size of the InnoDB Redo Log can be changed dynamically by setting the system variable using the statement. The statement requires the SUPER privilege.

The resize operation is performed asynchronously in the background. If the server is restarted before the operation completes, the request may be ignored. To ensure that the change survives server restarts, the system variable should also be set in a configuration file.

To configure the InnoDB Redo Log with the statement, use the following procedure:

1. Connect to the server using MariaDB Client as the root@localhost user account or another user account with the SUPER privilege:

1. Set the system variable to the new size using the statement.

For example, to set the size to 512 MB:

And to set the size to 2 GB:

1. The resize operation is performed asynchronously in the background. Confirm that the resize operation is complete by querying the system variable using the statement. The resize operation is complete when the output shows the new size as the value of the system variable.

Execute the following statement until it shows the new size:

1. Choose a configuration file for custom changes to system variables and options. It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

Ensure that your custom changes are read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name.

Some example configuration file paths for different distributions are shown in the following table:

1. Set the system variable in the configuration file.

It needs to be set in a group that are read by MariaDB Server, such as [mariadb] or [server].

When set in a configuration file, the value supports units, such as "M", "G", etc.

For example, to set the size to 512 MB:

And to set the size to 2 GB:

Configure the InnoDB Redo Log Size in a Configuration File (ES 10.5) and Later

Starting in MariaDB Enterprise Server 10.5, the size of the InnoDB Redo Log can be changed by setting the system variable in a configuration file.

To configure the InnoDB Redo Log in a configuration file, use the following procedure:

1. Choose a configuration file for custom changes to system variables and options.

It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

Ensure that your custom changes are read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name.

Some example configuration file paths for different distributions are shown in the following table:

1. Set the system variable in the configuration file. It needs to be set in a group that are read by MariaDB Server, such as [mariadb] or [server]. When set in a configuration file, the value supports units, such as "M", "G", etc.

For example, to set the size to 512 MB:

And to set the size to 2 GB:

1. Starting in MariaDB Community Server 10.5, the server must be restarted for the configuration change to take effect:

1. Starting in MariaDB Enterprise Server 10.5, the server can use the configuration change without a restart if you use .

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

Overview

In MariaDB Enterprise Server, the InnoDB storage engine uses Purge Threads to perform garbage collection in the background. The Purge Threads are related to multi-version concurrency control (MVCC).

The Purge Threads perform garbage collection of various items:

The Purge Threads perform garbage collection of the InnoDB Undo Log. When a row is updated in the clustered index, InnoDB updates the values in the clustered index, and the old row version is added to the Undo Log. The Purge Threads scan the Undo Log for row versions that are not needed by open transactions and permanently delete them.

The Purge Threads perform garbage collection of index records. When an indexed column is updated, InnoDB creates a new index record for the updated value in each affected index, and the old index records are delete-marked. When the primary key column is updated, InnoDB creates a new index record for the updated value in every index, and each old index record is delete-marked. The Purge Threads scan for delete-marked index records and permanently delete them.

The Purge Threads perform garbage collection of freed overflow pages. , , , , , and related types are sometimes stored on overflow pages. When the value on the overflow page is deleted or updated, the overflow page is no longer needed. The Purge Threads delete these freed overflow pages.

For additional information, see "".

This page describes how to configure the InnoDB Purge Threads.

Configure the Number of InnoDB Purge Threads

The number of the InnoDB Purge Threads is configurable. If your server deletes or updates rows at a very high frequency, then you may need to increase the number of purge threads.

The method to configure the number of Purge Threads depends on the server version and whether a server restart are performed:

Configure the Number of InnoDB Purge Threads with SET GLOBAL

The number of InnoDB purge threads can be changed dynamically by setting the system variable using the statement. The statement requires the SUPER privilege.

To ensure that the change survives server restarts, the system variable should also be set in a configuration file.

To configure the number of InnoDB Purge threads with the statement, use the following procedure:

1. Connect to the server using as the root@localhost user account or another user account with the SUPER privilege:

1. Set the system variable to the new size using the statement.

For example:

1. Choose a configuration file for custom changes to system variables and options. It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

Ensure that your custom changes are read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name.

Some example configuration file paths for different distributions are shown in the following table:

1. Set the system variable in the configuration file. It needs to be set in a group that are read by MariaDB Server, such as [mariadb] or [server].

For example:

Configure the Number of InnoDB Purge Threads in a Configuration File

The number of InnoDB Purge Threads can be configured by setting the system variable in a configuration file.

To configure the number of in a configuration file, use the following procedure:

1. Choose a configuration file for custom changes to system variables and options. It is not recommended to make custom changes to Enterprise Server's default configuration files, because your custom changes can be overwritten by other default configuration files that are loaded after.

2. Ensure that your custom changes are read last by creating a custom configuration file in one of the included directories. Configuration files in included directories are read in alphabetical order. Ensure that your custom configuration file is read last by using the z- prefix in the file name.

Some example configuration file paths for different distributions are shown in the following table:

1. Set the innodb_purge_threads system variable in the configuration file. It needs to be set in a group that are read by MariaDB Server, such as [mariadb] or [server].

For example:

1. Restart the server:

The server can use the configuration change without a restart if you use .

_{This page is: Copyright © 2025 MariaDB. All rights reserved.}

When InnoDB needs to store general information relating to the system as a whole, rather than a specific table, the specific file it writes to is the system tablespace. By default, this is the ibdata1 file located in the data directory, (as defined by either the datadir or innodb_data_home_dir system variables). InnoDB uses the system tablespace to store the data dictionary, change buffer, and undo logs.

You can define the system tablespace filename or filenames, size and other options by setting the innodb_data_file_path system variable. This system variable can be specified as a command-line argument to mariadbd or it can be specified in a relevant server option group in an option file:

This system variable defaults to the file ibdata1, and it defaults to a minimum size of 12M, and it defaults with the autoextend attribute enabled.

Changing Sizes

InnoDB defaults to allocating 12M to the ibdata1 file for the system tablespace. While this is sufficient for most use cases, it may not be for all. You may find after using MariaDB for a while that the allocation is too small for the system tablespace or it grows too large for your disk. Fortunately, you can adjust this size as need later.

Increasing the Size

When setting the system variable, you can define a size for each file given. In cases where you need a larger system tablespace, add the autoextend option to the last value.

Under this configuration, when the last system tablespace grows beyond the size allocation, InnoDB increases the size of the file by increments. To control the allocation increment, set the system variable.

Decreasing the Size

MariaDB starting with

From , when MariaDB starts up, unused InnoDB tablespace can be reclaimed, reducing the file size (). This is disabled by default and is enabled by adding the :autoshrink attribute to the system variable, e.g.:

Alternatively, starting with , the shrinking can be set to happen during a slow shutdown (e.g. after SET GLOBAL innodb_fast_shutdown=0) (). Technically, how this works is:

1. Find the last used extent in the system tablespace by iterating through the BITMAP in the extent descriptor page.

2. Check whether the tablespace is being used within fixed size, and if the last used extent is less than the fixed size, then set the desired target size to fixed size.

3. Flush all pages belonging to the system tablespace in flush list.

4. Truncate the truncated pages from FSP_FREE and FSP_FREE_FRAG list.

MariaDB until

In cases where the InnoDB system tablespace has grown too large, before , the process to reduce it in size is a little more complicated than increasing the size. MariaDB does not allow you to remove data from the tablespace file itself. Instead you need to delete the tablespace files themselves, then restore the database from backups. The backup utility produces backup files containing the SQL statements needed to recreate the database. As a result, it restores a database with the bare minimum data rather than any additional information that might have built up in the tablespace file. Use mariadb-dump to backup all of your InnoDB database tables, including the system tables in the mysql database that use InnoDB. You can find out what they are using the Information Schema.

If you only use InnoDB, you may find it easier to back up all databases and tables.

Then stop the MariaDB Server and remove the InnoDB tablespace files. In the data directory or the InnoDB data home directory, delete all the ibdata and ib_log files as well as any file with an .ibd or .frm extension. Once this is done, restart the server and import the dump file:

Using Raw Disk Partitions

Instead of having InnoDB write to the file system, you can set it to use raw disk partitions. On Windows and some Linux distributions, this allows you to perform non-buffered I/O without the file system overhead. Note that in many use cases this may not actually improve performance. Run tests to verify if there are any real gains for your application usage.

To enable a raw disk partition, first start MariaDB with the newraw option set on the tablespace:

When the MariaDB Server starts, it initializes the partition. Don't create or change any data, (any data written to InnoDB at this stage are lost on restart). Once the server has successful started, stop it then edit the configuration file again, changing the newraw keyword to raw.

When you start MariaDB again, it'll read and write InnoDB data to the given disk partition instead of the file system.

Raw Disk Partitions on Windows

When defining a raw disk partition for InnoDB on the Windows operating system, use the same procedure as defined above, but when defining the path for the system variable, use ./ at the start:

The given path is synonymous with the Windows syntax for accessing the physical drive.

System Tables within the InnoDB System Tablespace

InnoDB creates some system tables within the InnoDB System Tablespace:

• SYS_DATAFILES

• SYS_FOREIGN

• SYS_FOREIGN_COLS

• SYS_TABLESPACES

These tables cannot be queried. However, you might see references to them in some places, such as in the table in the database.

_{This page is licensed: CC BY-SA / Gnu FDL}

Overview

The InnoDB Read I/O Threads handle completion of read I/O requests, and the InnoDB Write I/O Threads handle completion of write I/O requests.

Starting with MariaDB Enterprise Server 10.5 and MariaDB Community Server 10.5, the InnoDB I/O Threads were replaced by the asynchronous I/O functionality in the .

For additional information, see "".

This page describes how to configure the InnoDB I/O Threads.

Overview

In MariaDB Enterprise Server, the InnoDB storage engine uses the Buffer Pool as an in-memory cache. The Buffer Pool caches pages that were recently accessed. If a lot of pages are being accessed sequentially, the Buffer Pool also preemptively caches nearby pages. Pages are evicted using a least recently used (LRU) algorithm.

The contents of the Buffer Pool can be reloaded at startup, so that InnoDB does not have to function with a "cold" cache after a restart. To support this, the page numbers of all pages in the Buffer Pool can be dumped at shutdown. During startup, the page numbers are read from the dump, and InnoDB uses the page numbers to reload each page from its corresponding data file.

The size of each page in the Buffer Pool depends on the value of the system variable.

Overview

The InnoDB undo log is a transaction log used by InnoDB to keep track of multiple row versions for multi-version concurrency control (MVCC). When a row's value changes, InnoDB stores old versions of the row in the Undo Log.

When transactions are committed and the old row versions are no longer necessary, the InnoDB Purge Threads asynchronously delete old row versions from the Undo Log in the background.

When a transaction is rolled back, InnoDB uses the Undo Log to rollback the transaction's changes.

For additional information, see "".

This page describes how to configure the InnoDB Undo Log.

Overview

In MariaDB Enterprise Server, InnoDB supports many different schema change operations. Many of the operations can be performed online with concurrent DML using little or no locking.

DDL Statements

NoteCOMPACT was the default row format in prior versions of MariaDB. MariaDB has since transitioned to DYNAMIC as the default row format.

The COMPACT row format is similar to the REDUNDANT row format, but it stores data in a more compact manner that requires about 20% less storage.

As with most errors, first take a look at the contents of the . If dealing with a deadlock, setting the option (off by default) will output details of all deadlocks to the error log.

It can also help to enable the various relating to the problem you are experiencing. There are four types: the standard InnoDB monitor, the InnoDB Lock Monitor, InnoDB Tablespace Monitor and the InnoDB Table Monitor.

Running will help determine whether there are errors in the table.

For problems with the InnoDB Data Dictionary, see .

See Also

The InnoDB implementation has diverged substantially from the InnoDB in MySQL. Therefore, in these versions, the InnoDB version is no longer associated with a MySQL release version.

The default InnoDB implementation is based on InnoDB from MySQL 5.7. See Why MariaDB uses InnoDB instead of XtraDB from MariaDB 10.2 for more information.

Note

XtraDB is a performance enhanced fork of InnoDB. For compatibility reasons, the still retain their original innodb prefixes. If the documentation says that something applies to InnoDB, then it usually also applies to the XtraDB fork, unless explicitly stated otherwise. In these versions, it is still possible to use InnoDB instead of XtraDB. See for more information.

Divergences

Some examples of divergences between MariaDB's InnoDB and MySQL's InnoDB are:

• (which is based on MySQL 5.6) included encryption and variable-size page compression before MySQL 5.7 introduced them.

• (based on MySQL 5.7) introduced persistent AUTO_INCREMENT () in a GA release before MySQL 8.0.

• (based on MySQL 5.7) introduced instant ADD COLUMN () before MySQL.

InnoDB Versions Included in MariaDB Releases

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}

Directly editing or moving the redo logs can cause corruption, and should never normally be attempted.

Overview

The InnoDB storage engine in MariaDB Enterprise Server employs a Redo Log to ensure data is written to disk reliably, even in the event of a crash. This is achieved by the Redo Log acting as a transaction log.

Redo Log records are uniquely identified by a Log Sequence Number (LSN). The Redo Log consists of circular log files of a fixed size, where older records are routinely overwritten by newer ones.

InnoDB periodically performs checkpoints. During a checkpoint operation, InnoDB writes (flushes) the Redo Log records to the InnoDB tablespace files.

In the event of a server crash, InnoDB utilizes the Redo Log for crash recovery during the subsequent server startup. It locates the last checkpoint within the Redo Log and then replays the Redo Log records generated since that checkpoint, effectively flushing these pending changes to the InnoDB tablespace files.

The redo log files are typically named ib_logfileN, where N is an integer. However, starting with , there is a single redo log file, consistently named ib_logfile0. The location of these redo log files is determined by the innodb_log_group_home_dir system variable, if configured. If this variable is not set, the redo log files are created in the directory specified by the datadir system variable. The redo log plays a crucial role during crash recovery and in the background process of flushing transactions to the tablespaces.

Feature Summary

Basic Configuration

Flushing Effects on Performance and Consistency

The system variable determines how often the transactions are flushed to the redo log, and it is important to achieve a good balance between speed and reliability.

Binary Log Group Commit and Redo Log Flushing

When both (the default) is set and the is enabled, there is one less sync to disk inside InnoDB during commit (2 syncs shared between a group of transactions instead of 3). See for more information.

Redo Log Group Capacity

The redo log group capacity is the total combined size of all InnoDB redo logs. The relevant factors are:

• From , there is 1 redo log.

• The size of each redo log file is configured by the system variable. This can safely be set to a much higher value from . Before , resizing required the server to be restarted. From the variable can be set dynamically.

The redo log group capacity is determined by the following calculation:

innodb_log_group_capacity = *

For example, if is set to 2G and is set to 2, then we would have the following:

• innodb_log_group_capacity = *

• = 2G * 2

• = 4G

Changing the Redo Log Group Capacity

MariaDB starting with

The number of redo log files is fixed.

The size of redo log files can be changed with the following process:

• Stop the server.

• To change the log file size, configure .

• Start the server.

Log Sequence Number (LSN)

Records within the InnoDB redo log are identified via a log sequence number (LSN).

Checkpoints

When InnoDB performs a checkpoint, it writes the LSN of the oldest dirty page in the to the InnoDB redo log. If a page is the oldest dirty page in the , then that means that all pages with lower LSNs have been flushed to the physical InnoDB tablespace files. If the server were to crash, then InnoDB would perform crash recovery by only applying log records with LSNs that are greater than or equal to the LSN of the oldest dirty page written in the last checkpoint.

Checkpoints are one of the tasks performed by the InnoDB master background thread. This thread schedules checkpoints 7 seconds apart when the server is very active, but checkpoints can happen more frequently when the server is less active.

Dirty pages are not actually flushed from the buffer pool to the physical InnoDB tablespace files during a checkpoint. That process happens asynchronously on a continuous basis by InnoDB's write I/O background threads configured by the system variable. If you want to make this process more aggressive, then you can decrease the value of the system variable. You may also need to better tune InnoDB's I/O capacity on your system by setting the system variable.

Determining the Checkpoint Age

The checkpoint age is the amount of data written to the InnoDB redo log since the last checkpoint.

Determining the Checkpoint Age in InnoDB

MariaDB starting with

reintroduced the status variable for determining the checkpoint age.

The checkpoint age can also be determined by the process shown below.

To determine the InnoDB checkpoint age, do the following:

• Query .

• Find the LOG section:

• Perform the following calculation:

innodb_checkpoint_age = Log sequence number - Last checkpoint at

In the example above, that would be:

• innodb_checkpoint_age = Log sequence number - Last checkpoint at

• = 252794398789379 - 252792767756840

• = 1631032539 bytes

Determining the Redo Log Occupancy

The redo log occupancy is the percentage of the InnoDB redo log capacity that is taken up by dirty pages that have not yet been flushed to the physical InnoDB tablespace files in a checkpoint. Therefore, it's determined by the following calculation:

innodb_log_occupancy = /

For example, if is 1.5G and is 4G, then we would have the following:

• innodb_log_occupancy = /

• = 1.5G / 4G

• = 0.375

If the calculated value for redo log occupancy is too close to 1.0, then the InnoDB redo log capacity may be too small for the current workload.

Updates

A number of redo log improvements were made in :

• Autosize innodb_buffer_pool_chunk_size ().

• Improve the redo log for concurrency ().

• Remove FIL_PAGE_FILE_FLUSH_LSN ().

Before , created a zero-length ib_logfile0 as a dummy placeholder. From (), the size of that dummy file was increased to 12304 (0x3010) bytes, and all updates of FIL_PAGE_FILE_FLUSH_LSN in the first page of the system tablespace are removed.

From , if the server is started up with a zero-sized ib_logfile0, it is assumed that an upgrade is being performed after a backup had been prepared. The start LSN will then be read from FIL_PAGE_FILE_FLUSH_LSN, and a new log file are created starting from exactly that LSN.

Manually creating a zero-sized ib_logfile0 without manually updating the FIL_PAGE_FILE_FLUSH_LSN in the system tablespace to a recent enough LSN may result in error messages such as "page LSN is in the future". If a log was discarded while some changes had already been written to data pages, all sort of corruption may occur.

If the database was initialized with a server that never updates the FIL_PAGE_FILE_FLUSH_LSN field, then any server startup attempts with a zero-size ib_logfile0 are refused because of an invalid LSN. If that field was ever updated with a valid LSN by an older server, this safety mechanism cannot work, and the server may "rewind" to an earlier LSN.

_{This page is licensed: CC BY-SA / Gnu FDL}