I executed this crude benchmark:

--source include/have_innodb.inc

--source include/have_sequence.inc

SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;

SET GLOBAL innodb_purge_rseg_truncate_frequency=1;

CREATE TABLE t1 (

 a BIGINT PRIMARY KEY,

 b CHAR(255) NOT NULL DEFAULT '',

 c CHAR(255) NOT NULL DEFAULT '',

 d CHAR(255) NOT NULL DEFAULT '',

 e CHAR(255) NOT NULL DEFAULT '',

 f CHAR(255) NOT NULL DEFAULT '',

 g CHAR(255) NOT NULL DEFAULT '',

 h CHAR(255) NOT NULL DEFAULT ''

) ENGINE=InnoDB;

INSERT INTO t1 (a) SELECT seq FROM seq_1_to_100000;

SET profiling = 1;

--enable_info

ALTER TABLE t1 ADD INDEX(b),ADD INDEX(c),ADD INDEX(d),

ADD INDEX(e(10)),ADD INDEX(f(4)),ADD INDEX(g(5)),ADD INDEX(h(5));

ALTER TABLE t1 FORCE;

--disable_info

SHOW PROFILES;

SET profiling = 0;

show status like 'innodb_d%_writes';

FLUSH TABLES t1 FOR EXPORT;

show status like 'innodb_d%_writes';

UNLOCK TABLES;

DROP TABLE t1;

SET GLOBAL innodb_purge_rseg_truncate_frequency=@saved_frequency;

./mtr --mysqld=--innodb-{page-size=4k,buffer-pool-size=64m,log-optimize-ddl=1} innodb.benchmark

./mtr --mysqld=--innodb-{page-size=4k,buffer-pool-size=64m,log-optimize-ddl=0} innodb.benchmark

The results:

Note: the "affected rows: 0" for a non-empty table means that native ALTER TABLE was used.

The time for rebuilding tables is much longer. In 10.5, we expect this to go down with the redo log format improvements (MDEV-12353 and MDEV-14425). Until then, maybe we should allow the parameter to be set while the server is running, so that users can take the risk and temporarily disable redo logging for large table-rebuilding ALTER operations.

We should also keep in mind that thanks to MDEV-11369 and various subtasks of MDEV-11424, many ALTER TABLE operations are instantaneous, and table rebuilds should not occur very often.

I would be interested in seeing how much the innodb_dblwr_writes is reduced (and performance is improved) on generic DML workloads. Maybe it is enough justification to change the parameter to the safe default.

I plan to deprecate and ignore the parameter innodb_log_optimize_ddl=OFF as part of the MDEV-12353 implementation. The different redo log format should alleviate any performance regressions.

I repeated the microbenchmark after implementing some optimization for MDEV-12353. The physical format is generating slightly more redo log.

I amended the test case with a few reads of innodb_lsn_current (log_sys.lsn):

--source include/have_innodb.inc

--source include/have_sequence.inc

SET @saved_frequency = @@GLOBAL.innodb_purge_rseg_truncate_frequency;

SET GLOBAL innodb_purge_rseg_truncate_frequency=1;

CREATE TABLE t1 (

 a BIGINT PRIMARY KEY,

 b CHAR(255) NOT NULL DEFAULT '',

 c CHAR(255) NOT NULL DEFAULT '',

 d CHAR(255) NOT NULL DEFAULT '',

 e CHAR(255) NOT NULL DEFAULT '',

 f CHAR(255) NOT NULL DEFAULT '',

 g CHAR(255) NOT NULL DEFAULT '',

 h CHAR(255) NOT NULL DEFAULT ''

) ENGINE=InnoDB;

INSERT INTO t1 (a) SELECT seq FROM seq_1_to_100000;

SET profiling = 1;

show status like 'innodb_lsn_current';

--enable_info

ALTER TABLE t1 ADD INDEX(b),ADD INDEX(c),ADD INDEX(d),

ADD INDEX(e(10)),ADD INDEX(f(4)),ADD INDEX(g(5)),ADD INDEX(h(5));

show status like 'innodb_lsn_current';

ALTER TABLE t1 FORCE;

--disable_info

SHOW PROFILES;

SET profiling = 0;

show status like 'innodb_lsn_current';

show status like 'innodb_d%_writes';

FLUSH TABLES t1 FOR EXPORT;

show status like 'innodb_d%_writes';

UNLOCK TABLES;

DROP TABLE t1;

SET GLOBAL innodb_purge_rseg_truncate_frequency=@saved_frequency;

The execution time with the original settings

./mtr --mysqld=--innodb-{page-size=4k,buffer-pool-size=64m,log-optimize-ddl=1} innodb.benchmark

./mtr --mysqld=--innodb-{page-size=4k,buffer-pool-size=64m,log-optimize-ddl=0} innodb.benchmark

did not change much. However, the default redo log size must be so small that it is forcing page flushes. Let us fix that:

./mtr --mysqld=--innodb-{page-size=4k,buffer-pool-size=64m,log-file-size=512m,log-optimize-ddl=1} innodb.benchmark

./mtr --mysqld=--innodb-{page-size=4k,buffer-pool-size=64m,log-file-size=512m,log-optimize-ddl=1} innodb.benchmark

This will shrink the test time as follows:

I also collected statistics on the redo log bytes written:

So, it looks like with a reasonably large innodb_log_file_size, setting innodb_log_file_size=OFF may slightly improve performance. In MDEV-12353, the setting is deprecated and ignored. We can also note that the redo log records in the physical redo log format of MDEV-12353 are slightly larger than the old physiological format. The situation could slightly improve after btr_page_reorganize() has been rewritten to write less log. There should be some room to reduce the log record volume further without changing the format.

Given that there does not appear to be any performance regression when using a reasonably sized redo log file, I think that we can go one step further and deprecate and ignore the parameter. In MDEV-12353 there is no replacement for the MLOG_INDEX_LOAD record, which was written when some redo log was skipped.

I ran a bigger benchmark, with 10× the rows (seq_1_to_1000000) and the data directory on PCIe NVMe:

./mtr --mysqld=--innodb-{page-size=4k,buffer-pool-size=1g,log-file-size=1g,log-files-in-group=1,flush-log-at-trx-commit=1,log-optimize-ddl=1} innodb.benchmark1m

./mtr --mysqld=--innodb-{page-size=4k,buffer-pool-size=1g,log-file-size=1g,log-files-in-group=1,flush-log-at-trx-commit=1,log-optimize-ddl=0} innodb.benchmark1m

Before ADD INDEX, the t1.ibd file size is 2.5 GiB, and before the FORCE it is 3.3GiB. The tested revisions were as follows:

• 10.4 9398c3dfa5f8c2b2b5bc51dbf079e3edd343ae9e
• 10.5 2655984ed4d22cf6eeec59637e1bf31d78cac679

Even with a larger test, we can observe that innodb_log_optimize_ddl=0 (reverting the change in MySQL 5.7 to write less log on ALTER TABLE) is actually reducing performance, when the redo log file is reasonably large. The reason for this ought to be the extra flushing of the buffer pool right before the redo logging is re-enabled.

The variable innodb_lsn_current is not available before 10.5. On 10.5, I tried one more variant: performing FORCE, ALGORITHM=COPY as the last step. That should write roughly the same amount of pages, but the pre-sorting of index records will be disabled.

Only the last step should differ. The first two steps are identical, but there is minor fluctuation, likely due to purge (MDEV-12288).
Interestingly, for the non-native rebuild with ALGORITHM=COPY we are writing slightly less redo log than for the faster native rebuild. We might want to check if PageBulk::finishPage() could emit the higher-level redo log records that were introduced in MDEV-21724.

Conclusion: 10.5 with a properly configured redo log file size is faster than 10.4, and that innodb_log_optimize_ddl=0 (disabling the supposed optimization that was introduced in MySQL 5.7 and inherited to MariaDB 10.2.2) is likely improving performance in MariaDB Server 10.2, 10.3, and 10.4.

I accidentally ran the 10.5 benchmark on /dev/shm instead of NVMe (ext4fs). For a true apples-to-apples comparison, here are the tables with updated numbers for 10.5:

For ADD INDEX, 10.5 is clearly slower than 10.4 with innodb_log_optimize_ddl=0. This might be improved by reducing the log volume of PageBulk::finishPage().

Here is the comparison between FORCE and ALGORITHM=COPY again, on NVMe instead of RAM disk: