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 Buffer Pool always has a single instance.

For additional information, see "InnoDB Buffer Pool".

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 innodb_buffer_pool_size 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 will be performed:

Configure the InnoDB Buffer Pool Size with SET GLOBAL

The size of the InnoDB buffer pool can be changed dynamically by setting the innodb_buffer_pool_size system variable using the SET GLOBAL statement. The SET GLOBAL statement requires the SUPER privilege.

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

To configure the InnoDB Buffer Pool with the SET GLOBAL 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:

$ mariadb --user=root

1. Set the innodb_buffer_pool_size system variable to the new size using the SET GLOBAL statement.

For example, to set the size to 2 GB:

SET GLOBAL innodb_buffer_pool_size=(2 * 1024 * 1024 * 1024);

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

SHOW GLOBAL STATUS
   LIKE 'Innodb_buffer_pool_resize_status';

+----------------------------------+----------------------------------------------------+
| Variable_name                    | Value                                              |
+----------------------------------+----------------------------------------------------+
| Innodb_buffer_pool_resize_status | Completed resizing buffer pool at 200904 17:49:48. |
+----------------------------------+----------------------------------------------------+

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 will be 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_buffer_pool_size system variable in the configuration file. It needs to be set in a group that will be 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:

[mariadb]
...
innodb_buffer_pool_size=2G

Configure the InnoDB Buffer Pool Size in a Configuration File

The size of the InnoDB Buffer Pool can be changed by setting the innodb_buffer_pool_size 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 will be 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_buffer_pool_size system variable in the configuration file.

It needs to be set in a group that will be 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:

[mariadb]
...
innodb_buffer_pool_size=2G

1. Restart the server:

$ sudo systemctl restart mariadb

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

_{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 innodb_read_io_threads and innodb_write_io_threads 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 will be 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 innodb_use_native_aio system variable, which is enabled by default.

Affected I/O Operations

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

• Linear read-ahead (configured by innodb_read_ahead_threshold)

• Random read-ahead (configured by innodb_random_read_ahead)

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

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

• Page flushing due to buffer pool capacity (configured by [innodb_max_dirty_pages_pct] and [innodb-system-variables/#innodb_max_dirty_pages_pct_lwm|innodb_max_dirty_pages_pct_lwm)

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

Configuration Procedure

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

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

Starting with MariaDB Enterprise Server 10.5, InnoDB's maximum number of asynchronous I/O requests can be changed dynamically by setting the innodb_read_io_threadsinnodb_write_io_threads system variables using the SET GLOBAL statement. The SET GLOBAL 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 innodb_write_io_threads system variable should be set to 4 (since 1024/256=4).

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

To configure InnoDB's maximum number of asynchronous I/O requests with the SET GLOBAL 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:

$ mariadb --user=root

1. Set the innodb_read_io_threads and innodb_write_io_threads system variables to the new values using the SET GLOBAL statement.

For example:

SET GLOBAL innodb_read_io_threads=8;
SET GLOBAL innodb_write_io_threads=8;

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 will be 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_read_io_threads and innodb_write_io_threads system variables in the configuration file.

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

For example:

[mariadb]
...
innodb_read_io_threads=8
innodb_write_io_threads=8

Configure the Number of InnoDB I/O Threads in a Configuration File

The number of I/O threads is configured by the innodb_read_io_threads and innodb_write_io_threads 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 will be 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_read_io_threads and innodb_write_io_threads system variables in the configuration file.

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

For example:

[mariadb]
...
innodb_read_io_threads=8
innodb_write_io_threads=8

1. Restart the server:

$ sudo systemctl restart mariadb

Starting with MariaDB Enterprise Server 10.5, the server can use the configuration change without a restart if you use SET GLOBAL.

_{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. BLOB, CHAR, TEXT, VARCHAR, VARBINARY, 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 "InnoDB Purge Threads".

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 will be performed:

Configure the Number of InnoDB Purge Threads with SET GLOBAL (ES 10.5) and Later

Starting in MariaDB Enterprise Server 10.5, the number of InnoDB purge threads can be changed dynamically by setting the innodb_purge_threads system variable using the SET GLOBAL statement. The SET GLOBAL statement requires the SUPER privilege.

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

To configure the number of InnoDB Purge threads with the SET GLOBAL 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:

$ mariadb --user=root

1. Set the innodb_purge_threads system variable to the new size using the SET GLOBAL statement.

For example:

SET GLOBAL innodb_purge_threads=8;

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 will be 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 will be read by MariaDB Server, such as [mariadb] or [server].

For example:

[mariadb]
...
innodb_purge_threads=8

Configure the Number of InnoDB Purge Threads in a Configuration File

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

To configure the number of innodb_purge_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 will be 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 will be read by MariaDB Server, such as [mariadb] or [server].

For example:

[mariadb]
...
innodb_purge_threads=8

1. Restart the server:

$ sudo systemctl restart mariadb

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

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

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

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 innodb_buffer_pool_size, which is configured by the innodb_buffer_pool_size system variable.

The method to configure the Redo Log size depends on the server version and whether a server restart will be 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 innodb_log_file_size system variable using the SET GLOBAL statement. The SET GLOBAL 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 innodb_log_file_size system variable should also be set in a configuration file.

To configure the InnoDB Redo Log with the SET GLOBAL 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:

$ mariadb --user=root

1. Set the innodb_log_file_size system variable to the new size using the SET GLOBAL statement.

For example, to set the size to 512 MB:

SET GLOBAL innodb_log_file_size=(512 * 1024 * 1024);

And to set the size to 2 GB:

SET GLOBAL innodb_log_file_size=(2 * 1024 * 1024 * 1024);

1. The resize operation is performed asynchronously in the background. Confirm that the resize operation is complete by querying the innodb_log_file_size system variable using the SHOW GLOBAL VARIABLES 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:

SHOW GLOBAL VARIABLES
   LIKE 'innodb_log_file_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 will be 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_log_file_size system variable in the configuration file.

It needs to be set in a group that will be 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:

[mariadb]
...
innodb_log_file_size=512M

And to set the size to 2 GB:

[mariadb]
...
innodb_log_file_size=2G

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 innodb_log_file_size 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 will be 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_log_file_size system variable in the configuration file. It needs to be set in a group that will be 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:

[mariadb]
...
innodb_log_file_size=512M

And to set the size to 2 GB:

[mariadb]
...
innodb_log_file_size=2G

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

$ sudo systemctl restart mariadb

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

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

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 innodb_data_file_path 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 innodb_undo_tablespaces 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 innodb_undo_directory 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:

[ERROR] InnoDB: Expected to open innodb_undo_tablespaces=8 but was able to find only 0
[ERROR] InnoDB: Plugin initialization aborted with error Generic error

To safely configure separate InnoDB undo log tablespaces:

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

2. Ensure that the server is stopped:

$ sudo systemctl stop mariadb

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 will be 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_undo_tablespaces system variable in the configuration file.

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

For example, to set the number of tablespaces to 8:

[mariadb]
...
innodb_undo_tablespaces=8

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

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

[mariadb]
...
innodb_undo_directory=/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:

$ sudo mkdir -p /innodb/undo
$ sudo chown mysql:mysql /innodb/undo

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:

$ sudo rm -fr /var/lib/mysql/*

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

2. Start the server:

$ sudo systemctl start mariadb

1. Connect to the server using MariaDB Client:

$ mariadb --user=root

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 SHOW GLOBAL VARIABLES statement:

SHOW GLOBAL VARIABLES
   WHERE Variable_name IN (
      'innodb_undo_tablespaces',
      'innodb_undo_directory'
   );

+-------------------------+--------------+
| Variable_name           | Value        |
+-------------------------+--------------+
| innodb_undo_directory   | /innodb/undo |
| innodb_undo_tablespaces | 8            |
+-------------------------+--------------+

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 innodb_undo_log_truncate system variable using the SET GLOBAL statement. The SET GLOBAL 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 innodb_max_undo_log_size 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 SET GLOBAL 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:

$ mariadb --user=root

1. Set the innodb_undo_log_truncate system variable to ON using the SET GLOBAL statement.

For example:

SET GLOBAL innodb_undo_log_truncate=ON;

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

For example, to set the size to 2 GB:

SET GLOBAL innodb_max_undo_log_size=(2 * 1024 * 1024 * 1024);

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

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

SET GLOBAL innodb_purge_rseg_truncate_frequency=64;

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 will be 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 will be read by MariaDB Server, such as [mariadb] or [server]. When set in a configuration file, the innodb_max_undo_log_size value supports units, such as "M", "G", etc.

For example:

[mariadb]
...
innodb_undo_log_truncate=ON
innodb_max_undo_log_size=2G
innodb_purge_rseg_truncate_frequency=64

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

• ALTER TABLE

• CREATE INDEX

• CREATE SPATIAL INDEX

• CREATE UNIQUE INDEX

• DROP INDEX

• RENAME TABLE

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 ALTER TABLE 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 ALTER TABLE statement.

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

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

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

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