DDL Redo compression

Background

DDL statements such as ALTER TABLE (index creation) or OPTIMIZE TABLE (table rebuild) rapidly generate large amounts of redo log during the B+ tree build phase. With parallel DDL enabled, the increased write speed can turn redo bandwidth into a cluster bottleneck, causing write jitter for other workloads.

DDL Redo compression compresses redo logs before writing, which:

Reduces redo write volume and bandwidth pressure.
Improves parallel DDL throughput and shortens DDL execution time.
Minimizes impact on online write workloads.
Reduces physical replication bandwidth usage and replay pressure.

Scenarios

DDL Redo compression applies to DDL operations that build a B+ tree using bulk load:

Creating a secondary index, such as ALTER TABLE ... ADD INDEX.
Rebuilding a table, such as ALTER TABLE ... ENGINE=InnoDB.
When using parallel DDL, the innodb_polar_parallel_ddl_threads parameter controls the number of parallel threads per DDL operation.

Note

Standard DML operations (INSERT, UPDATE, DELETE, LOAD DATA) are not affected by this feature.

Supported versions

Your PolarDB cluster must run one of the following versions. Query the version number to verify.

MySQL 8.0.2 with minor kernel version 8.0.2.2.27 or later.
MySQL 8.0.1 with minor kernel version 8.0.1.1.52 or later.
MySQL 5.7 with minor kernel version 5.7.1.0.39 or later.

Limitations

This feature applies only to Inplace DDL operations that build a B+ tree using the bulk load method. It does not support Copy DDL. Standard DML operations are not affected.
This feature is supported only for tables that use the InnoDB storage engine.
This feature is not supported for temporary tables and tables with ROW_FORMAT=COMPRESSED.
This feature is not supported for full-text (FULLTEXT) or spatial (SPATIAL) indexes.

Parameters

Configure the following parameters in the PolarDB console:

Compatibility: Some parameters use a loose_ prefix for MySQL configuration file compatibility.
Procedure: Search for and modify the parameters with the loose_ prefix.

Parameter

Level

Description

loose_innodb_bulk_load_redo_compress_enable

Global

Controls the scope of DDL Redo compression. Valid values:

none (Default): Disables DDL Redo compression.
secondary: Compresses the redo log for secondary indexes only.
all: Compresses the redo log for both the clustered index (primary key) and secondary indexes.

loose_innodb_bulk_load_redo_compress_algorithm

Global

Specifies the compression algorithm. Valid values:

none: Disables compression.
lz4 (Default): Uses the LZ4 compression algorithm.
zstd: Uses the Zstandard compression algorithm.

Usage

Enable compression

Modify cluster parameters in the PolarDB console to enable DDL Redo compression, as described in Set cluster and node parameters.

Log on to the PolarDB console.
In the left-side navigation pane, choose Settings and Management > Parameters.
Search for the loose_innodb_bulk_load_redo_compress_enable parameter and modify its value based on your requirements:
- To compress the redo log for only secondary indexes, set the value to secondary.
- To compress the redo log for both the primary key and secondary indexes, set the value to all.

Execute DDL

Once enabled, qualifying DDL operations automatically compress redo logs. Examples:

ALTER TABLE t1 ADD INDEX idx_b (b);
ALTER TABLE t1 ENGINE = InnoDB;
OPTIMIZE TABLE t1;

Disable compression

On the Settings and Management > Parameters page, set the loose_innodb_bulk_load_redo_compress_enable parameter to none.

Monitoring

SHOW ENGINE INNODB STATUS

Execute SHOW ENGINE INNODB STATUS and check the DDL OPERATIONS section to view the redo log write volume, the original and compressed byte counts, and the compression ratio:

----------
DDL OPERATIONS
----------
Amount of redo written by DDL bulk load: <total_bytes> Byte, <rate> Byte/s
Amount of compressed redo written by DDL bulk load: <compressed_bytes> Byte, original size: <original_bytes> Byte
Compression ratio: 0.35

SHOW GLOBAL STATUS

Query compression-related status variables. This method is suitable for monitoring system integration.

SHOW GLOBAL STATUS LIKE 'Innodb_ddl_bulk_load_write_redo%';

Status variable	Description
`Innodb_ddl_bulk_load_write_redo`	The total size, in bytes, of redo log data written by DDL bulk load operations. If compression is enabled, this value is the compressed size.
`Innodb_ddl_bulk_load_write_redo_compressed`	The total size of compressed redo log data, in bytes.
`Innodb_ddl_bulk_load_write_redo_original`	The total size of original redo log data before compression, in bytes.

The actual compression ratio is approximately Innodb_ddl_bulk_load_write_redo_compressed / Innodb_ddl_bulk_load_write_redo_original. A smaller value indicates better compression.

Best practices

Start with secondary mode: Enable secondary mode first to observe compression ratio and CPU overhead. Switch to all mode after confirming stable performance.
Algorithm selection: Use the default lz4 for a good balance of compression ratio and performance.
Combine with parallel DDL: Pair this feature with innodb_polar_parallel_ddl_threads to improve DDL execution efficiency and reduce the redo bandwidth bottleneck in parallel DDL scenarios.