Change innodb_log_optimize_ddl=OFF by default

MariaDB 10.2.2 inherited from MySQL 5.7 a perceived optimization of ALTER TABLE, which skips the writing of redo log records. In MDEV-16809 we introduced a parameter that allows the redo log to be written, so that Mariabackup would not be impacted, but we kept the MySQL 5.7 behaviour enabled by default (innodb_log_optimize_ddl=ON).

As noted in MDEV-19747, omitting the redo log writes can actually reduce performance, because we will have to wait for the data pages to be written out. When the redo log file is configured to be large enough, it actually can be much faster to write the redo log and avoid the extra page flushing. With 1 million rows, 4k page size and the data on RAM disk, I got the following numbers on 10.2 224c950462a22e09f4e2e37d19218c9129bccba6:

Since the time the original benchmarks for MDEV-19747 were run, the fix of the correctness bug MDEV-21347 probably increased the amount of log that is written for ALTER TABLE. Either this or the presence of the MLOG_MEMSET record in 10.4 could explain why MDEV-19747 shows that the rebuilding ALTER TABLE was not slower with the redo logging enabled. The benchmarks in MDEV-19747 were the ‘proof’ that it is safe to deprecate and ignore the parameter in 10.5 (and to always write redo log).

My primary motivation for changing the parameter is not performance but correctness, and the avoidance of bugs like MDEV-20755. In my opinion, a small decrease in ALTER TABLE performance should be a good trade-off. The suggested change of the default value can be overridden without a server restart by executing the following:

SET GLOBAL innodb_log_optimize_ddl=ON;