Deprecate and ignore innodb_log_optimize_ddl

MariaDB Server 10.2.2 disabled redo logging for certain ALTER TABLE operations. In MDEV-16809 (10.2.17, 10.3.9) a possibility to enable the redo logging was introduced, by SET GLOBAL innodb_log_optimize_ddl=OFF. By default, the redo logging remains disabled. Backup can be much slower if such non-logged ALTER TABLE are performed while mariabackup --backup is running.

MDEV-19738 in 10.5 will skip the InnoDB doublewrite buffer in some cases. But, this cannot be done safely if redo logging can be disabled during ALTER TABLE. Hence, to get full advantage of MDEV-19738, we should change the default value of innodb_log_optimize_ddl to OFF, and possibly deprecate and ignore this parameter altogether.

The reason why I would consider making innodb_log_optimize_ddl read-only is that with MDEV-19738, changing the setting at runtime soon before or after affected ALTER TABLE is not crash-safe if the server is killed during a page write. But on the other hand, for ‘persons who know what they are doing’, maybe we should allow the parameter to modified while the server is running. SET GLOBAL requires special privileges anyway.

Before changing the default value, we should find out how much the speed of ALTER TABLE operations is affected by the change. With innodb_log_optimize_ddl=OFF, the extra page flushing will be avoided, and the affected forms of ALTER TABLE should become durable solely via redo log. Page flushing for the ALTER TABLE should be a little faster, because the doublewrite buffer can be avoided.

The affected forms of ALTER TABLE are:

--echo # rebuilding a table and all its indexes

ALTER TABLE t1 FORCE, ALGORITHM=INPLACE, LOCK=SHARED;

--echo # creating secondary indexes (one or several)

ALTER TABLE t1 ADD INDEX (b), ADD INDEX (c), ALGORITHM=INPLACE, LOCK=SHARED;

I would like to know the performance of this kind of ALTER TABLE on larger tables, with both settings of innodb_log_optimize_ddl.