MDEV-23720 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 (Deprecate and ignore innodb_log_optimize_ddl,
implemented in MariaDB 10.5.1), 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.

When the redo log is omitted (innodb_log_optimize_ddl=ON), also
Mariabackup may have to perform a lot of extra work, to re-copy the
entire data file if it is possible that any log was omitted during
the backup.

Starting with MariaDB 10.5.1, the parameter innodb_log_optimize_ddl
is deprecated and ignored. We hereby deprecate (but will not ignore)
the parameter in earlier versions as well.

4 files changed, 8 insertions, 6 deletions

diff --git a/mysql-test/suite/mariabackup/mlog_index_load.result b/mysql-test/suite/mariabackup/mlog_index_load.result
index bc83981106e..8925e897399 100644
--- a/mysql-test/suite/mariabackup/mlog_index_load.result
+++ b/mysql-test/suite/mariabackup/mlog_index_load.result
@@ -1,5 +1,6 @@
 CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB;
 INSERT INTO t1(a) SELECT * from seq_1_to_10000;
+SET GLOBAL innodb_log_optimize_ddl=ON;
 # xtrabackup backup
 t1.frm
 t1.ibd
diff --git a/mysql-test/suite/mariabackup/mlog_index_load.test b/mysql-test/suite/mariabackup/mlog_index_load.test
index fb29041fd3c..b9fd6f8ca32 100644
--- a/mysql-test/suite/mariabackup/mlog_index_load.test
+++ b/mysql-test/suite/mariabackup/mlog_index_load.test
@@ -2,6 +2,7 @@
 
 CREATE TABLE t1(i INT PRIMARY KEY auto_increment, a int) ENGINE INNODB;
 INSERT INTO t1(a) SELECT * from seq_1_to_10000;
+SET GLOBAL innodb_log_optimize_ddl=ON;
 
 let $targetdir=$MYSQLTEST_VARDIR/tmp/backup;
 
diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
index bed23a777a8..d3d30f8f53f 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result
@@ -1568,10 +1568,10 @@ READ_ONLY	YES
 COMMAND_LINE_ARGUMENT	REQUIRED
 VARIABLE_NAME	INNODB_LOG_OPTIMIZE_DDL
 SESSION_VALUE	NULL
-DEFAULT_VALUE	ON
+DEFAULT_VALUE	OFF
 VARIABLE_SCOPE	GLOBAL
 VARIABLE_TYPE	BOOLEAN
-VARIABLE_COMMENT	Reduce redo logging when natively creating indexes or rebuilding tables. Setting this OFF avoids delay due to page flushing and allows concurrent backup.
+VARIABLE_COMMENT	DEPRECATED. Ignored in MariaDB 10.5. Reduce redo logging when natively creating indexes or rebuilding tables. Enabling this may slow down backup and cause delay due to page flushing.
 NUMERIC_MIN_VALUE	NULL
 NUMERIC_MAX_VALUE	NULL
 NUMERIC_BLOCK_SIZE	NULL
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 637c66612ce..e36dcbad8ff 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -20245,10 +20245,10 @@ static MYSQL_SYSVAR_BOOL(log_compressed_pages, page_zip_log_pages,
 
 static MYSQL_SYSVAR_BOOL(log_optimize_ddl, innodb_log_optimize_ddl,
   PLUGIN_VAR_OPCMDARG,
-  "Reduce redo logging when natively creating indexes or rebuilding tables."
-  " Setting this OFF avoids delay due to page flushing and"
-  " allows concurrent backup.",
-  NULL, NULL, TRUE);
+  "DEPRECATED. Ignored in MariaDB 10.5."
+  " Reduce redo logging when natively creating indexes or rebuilding tables."
+  " Enabling this may slow down backup and cause delay due to page flushing.",
+  NULL, NULL, FALSE);
 
 static MYSQL_SYSVAR_ULONG(autoextend_increment,
   sys_tablespace_auto_extend_increment,