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}

Percona XtraDB was an enhanced version of the InnoDB storage engine used in MariaDB before MariaDB 10.2, designed to better scale on modern hardware, and it includes a variety of other features useful in high-performance environments.

It is fully backwards compatible, and it identifies itself to MariaDB as "ENGINE=InnoDB" (just like InnoDB), and so can be used as a drop-in replacement for standard InnoDB.

Percona XtraDB includes all of InnoDB's robust, reliable -compliant design and advanced MVCC architecture, and builds on that solid foundation with more features, more tunability, more metrics, and more scalability. In particular, it is designed to scale better on many cores, to use memory more efficiently, and to be more convenient and useful. The new features are especially designed to alleviate some of InnoDB's limitations. We choose features and fixes based on customer requests and on our best judgment of real-world needs as a high-performance consulting company.

XtraDB was also available in MariaDB for Windows.

Percona XtraDB versions in MariaDB

• XtraDB from in

• XtraDB from in

• XtraDB from in

• XtraDB from in

• XtraDB from in

• XtraDB from in

• XtraDB from in

• XtraDB from []in

• XtraDB from in

• XtraDB from in

• XtraDB from in

• XtraDB from in

and

• and 5.3 include the latest XtraDB version from at the time they were released.

• version in

• version 5.1.54-12.5 in

• version 5.1.52-11.6 in and

• version 5.1.49-12 in

Notes

1. Misidentifies itself as 5.6.36-83.0 in

2. Misidentifies itself as 5.6.36-82.2 from to

3. Misidentifies itself as 5.6.32-79.0 in

4. Misidentifies itself as 5.6.36-83.0 in

See Also

More information can be found in the .

_{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, 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

InnoDB schema changes are performed using the following DDL statements:

About InnoDB Schema Changes and Online DDL

InnoDB schema changes and online DDL are performed with a wide range of statements:

Each operation supports a subset of the following algorithms: INSTANT, NOCOPY, INPLACE, or COPY.

By default, InnoDB will use the most efficient algorithm supported by an operation. This behavior can be changed by using the ALGORITHM clause with the statement or by changing the value of the alter_algorithm system variable.

Each operation supports a subset of the following locking strategies: NONE, SHARED, or EXCLUSIVE.

By default, InnoDB will use the most permissive locking strategy supported by an operation. This behavior can be changed by using the LOCK clause with the statement.

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 "InnoDB Undo Log".

This page describes how to configure the InnoDB Undo Log.

Configure InnoDB Undo Log Tablespaces

By default, the InnoDB undo log is located in the InnoDB system tablespace, which is defined by the system variable. However, it can be helpful to configure separate undo log tablespaces to spread out I/O load between different files or storage devices.

InnoDB can be configured to use separate undo log tablespaces by setting the system variable. The separate undo log tablespaces will have file names of the format undoN, where N is an integer.

When you configure separate undo log tablespaces, you can also configure the separate undo log tablespaces to go to a specific directory by setting the system variable. This is most helpful if you want to put the undo log tablespaces on a separate storage device.

Separate InnoDB undo log tablespaces must be configured prior to the initialization of the server's InnoDB data directory. If you try to configure separate InnoDB undo log tablespaces when the InnoDB data directory has already been initializes, you will see errors in the error log during startup similar to the following:

To safely configure separate InnoDB undo log tablespaces:

1. If you have preexisting data, backup your data with .

2. Ensure that the server is stopped:

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, to set the number of tablespaces to 8:

1. If you want your InnoDB undo log tablespaces to be in a specific directory, then also set the system variable in the configuration file:

For example, to set the directory to /innodb/undo:

1. If you want your InnoDB undo log tablespaces to be in a specific directory, then also create the directory, and give it the proper permissions:

1. Delete the current contents of the datadir and innodb_data_home_dir.

For example, if the default value of /var/lib/mysql is used for both:

1. Reinitialize the data directory using the MariaDB Install DB command.

2. Start the server:

1. Connect to the server using MariaDB Client:

1. If your server had preexisting data, then reload the backup taken at the beginning of the procedure.

2. Confirm that the configuration changes were properly applied by checking the values of the system variables using the statement:

1. Consider also enabling undo log truncation to increase performance of the InnoDB Purge Threads.

Enable InnoDB Undo Log Truncation

If a server is configured to have 2 or more separate InnoDB undo log files, then InnoDB undo log truncation can be enabled by setting the system variable using the statement. The statement requires the SUPER privilege.

When InnoDB undo log truncation is enabled, the InnoDB purge threads can truncate an entire undo log at once, rather than individually freeing each rollback segment within the undo log.

An undo log is truncated when its size exceeds the system variable.

The frequency at which the InnoDB purge threads check for undo logs to truncate is configured by setting the innodb_purge_rseg_truncate_frequency system variable using the statement.

To ensure that the changes survive server restarts, the system variables should also be set in a configuration file.

To enable InnoDB undo log truncation:

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 ON using the statement.

For example:

1. If you would like to change the size at which undo logs are truncated, then also set the system variable to the new size using the statement.

For example, to set the size to 2 GB:

1. If you would like the InnoDB purge threads to check the undo logs more frequently, then also set the system variable to a lower value using the statement.

For example, to configure the purge threads to check the undo logs for truncation every 64 iterations:

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 variables 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:

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

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.}

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 innodb_page_size system variable.

Starting with ES 10.5 and CS 10.5, the always has a single instance.

For additional information, see "".

This page describes how to configure the InnoDB Buffer Pool.

Configure the InnoDB Buffer Pool Size

The size of the InnoDB Buffer Pool can be configured by setting the system variable. On ES nodes that exclusively use the InnoDB storage engine, the InnoDB Buffer Pool should usually be between 50%-75% of the memory available.

The method to configure the Buffer Pool size depends on whether a server restart are performed:

Configure the InnoDB Buffer Pool Size with SET GLOBAL

The size of the InnoDB buffer pool 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 InnoDB Buffer Pool 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 2 GB:

1. Confirm that the resize operation has been completed by querying the status variable using the statement:

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 2 GB:

Configure the InnoDB Buffer Pool Size in a Configuration File

The size of the InnoDB Buffer Pool can be changed by setting the system variable in a configuration file.

To configure the InnoDB Buffer Pool 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 2 GB:

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.}

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 InnoDB Background Thread Pool.

For additional information, see "InnoDB I/O Threads".

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

Configure the Number of InnoDB I/O Threads

Starting with ES 10.5 and CS 10.5, the InnoDB I/O Threads have been replaced by the asynchronous I/O functionality in the InnoDB Background Thread Pool. In these versions, the and system variables have been repurposed. The value of each system variable is multiplied by 256 to determine the maximum number of concurrent asynchronous I/O requests that can be completed by the Background Thread Pool.

For example, if innodb_read_io_threads=2 and innodb_write_io_threads=4 are set, InnoDB are restricted to a maximum of 512 concurrent asynchronous read I/O requests and 1024 concurrent asynchronous write I/O requests.

Interaction with Asynchronous I/O

When asynchronous I/O is enabled, the InnoDB I/O Threads do not receive the initial I/O request from query threads. Instead, the query threads submit asynchronous I/O requests directly to the operating system, and after the operating system performs the operation, the InnoDB I/O Threads handle completion of the request.

Asynchronous I/O is enabled by the system variable, which is enabled by default.

Affected I/O Operations

The system variable affects completion of the following types of reads:

• Linear read-ahead (configured by )

• Random read-ahead (configured by )

The system variable affects completion of the following types of writes:

• Page flushing due to adaptive flushing (configured by and )

• Page flushing due to buffer pool capacity (configured by [innodb_max_dirty_pages_pct] and [)

• Page flushing due to LRU page evictions (configured by and )

Configuration Procedure

The method to configure the number of I/O threads depends on the server version and whether a server restart are performed:

Configure InnoDB's Maximum Number of Asynchronous I/O Requests with SET GLOBAL (ES 10.5) and Later

Starting with , InnoDB's maximum number of asynchronous I/O requests can be changed dynamically by setting the system variables using the statement. The statement requires the SUPER privilege.

The value of each system variable is multiplied by 256 to determine the maximum number of asynchronous I/O requests that can be performed by the Background Thread Pool. For example, if you want to allow a maximum of 1024 concurrent asynchronous write I/O requests, the system variable should be set to 4 (since 1024/256=4).

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

To configure InnoDB's maximum number of asynchronous I/O requests 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 and system variables to the new values 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 and system variables 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 I/O Threads in a Configuration File

The number of I/O threads is configured by the and system variables.

To configure the number of InnoDB I/O Threads 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 and system variables in the configuration file.

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

For example:

1. Restart the server:

Starting with , 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.}