As part of WL#7277 in MySQL 5.7, redo logging was disabled during ALTER TABLE…ALGORITHM=INPLACE operations that rebuild the table or create indexes (other than spatial indexes).
For deployments where ALTER TABLE operations may be executed concurrently with backup, it would be simpler to keep writing redo log, and not having backups fail, and not need any --lock-ddl-per-table option that makes backup interfere with the normal operation of the server. This would also remove the need to flush dirty pages from the buffer pool before committing the ALTER TABLE operation.
On the other hand, when bulk loading data (before MDEV-515 is implemented), it can be useful to load the data first, then create secondary indexes. For this use case, omitting the redo logging may be very useful. So, we should have an option that controls whether the redo logging should be disabled.
Attachments
Issue Links
causes
MDEV-20608innodb_log_optimize_ddl=OFF may omit some redo log
Closed
MDEV-21347innodb_log_optimize_ddl=OFF is not crash safe
Closed
relates to
MDEV-16791mariabackup : allow consistent backup, in presence of concurrent DDL, also without --lock-ddl-per-table
Closed
MDEV-19747Deprecate and ignore innodb_log_optimize_ddl
Closed
MDEV-23720Change innodb_log_optimize_ddl=OFF by default
Closed
MDEV-28415ALTER TABLE on a large table hangs InnoDB
The backup does not have to fail, it still can succeed by re-copying the affected tablespace at the end of backup (this technique necessary on other reasons, e.g when recreating tables see MDEV-16791)
Vladislav Vaintroub
added a comment - - edited The backup does not have to fail, it still can succeed by re-copying the affected tablespace at the end of backup (this technique necessary on other reasons, e.g when recreating tables see MDEV-16791 )
True, during backup, the MLOG_INDEX_LOAD record could be handled by copying the affected files (or pages) at the end of the backup. Theoretically, it should suffice to only copy the B-tree pages starting from the root page indicated by each MLOG_INDEX_LOAD record. While this extra copying is taking place, the log would still need to be copied. If the operation is a table rebuild (there is a MLOG_INDEX_LOAD record for the clustered index root page, that is, page number 3), then it is simplest to copy the entire file.
When it comes to MDEV-16791, I believe that for the other cases (files that are renamed or created during the backup) can be handled by processing the already copied redo log in a smarter way, either at the end of mariabackup --backup, or in mariabackup --prepare. All the necessary information should be present in the redo log file.
Marko Mäkelä
added a comment - True, during backup, the MLOG_INDEX_LOAD record could be handled by copying the affected files (or pages) at the end of the backup. Theoretically, it should suffice to only copy the B-tree pages starting from the root page indicated by each MLOG_INDEX_LOAD record. While this extra copying is taking place, the log would still need to be copied. If the operation is a table rebuild (there is a MLOG_INDEX_LOAD record for the clustered index root page, that is, page number 3), then it is simplest to copy the entire file.
When it comes to MDEV-16791 , I believe that for the other cases (files that are renamed or created during the backup) can be handled by processing the already copied redo log in a smarter way, either at the end of mariabackup --backup , or in mariabackup --prepare . All the necessary information should be present in the redo log file.
I think that we must also test a rebuild of tables that contain BLOBs (and using small innodb_log_file_size). But testing rebuild would be tricky before we have MDEV-16791.
Marko Mäkelä
added a comment - Thank you for the review. I wrote a test case, which shows that the redo logging is not correct for ROW_FORMAT=COMPRESSED :
# see unsupported_redo.test for the opposite ( default ) case
--source include/have_innodb.inc
--source include/have_sequence.inc
SET GLOBAL innodb_log_optimize_ddl= OFF ;
CREATE TABLE tz(id BIGINT PRIMARY KEY , i INT )
ENGINE=InnoDB /*ROW_FORMAT=COMPRESSED*/ ;
INSERT INTO tz(id) select * from seq_1_to_100000;
CREATE TABLE tr(id BIGINT PRIMARY KEY , i INT )
ENGINE=InnoDB ROW_FORMAT=REDUNDANT;
INSERT INTO tr(id) select * from seq_1_to_100000;
CREATE TABLE td(id BIGINT PRIMARY KEY , i INT )
ENGINE=InnoDB;
INSERT INTO td(id) select * from seq_1_to_100000;
DELIMITER //;
CREATE PROCEDURE a()
BEGIN
ALTER TABLE tz ADD INDEX (i);
ALTER TABLE tr ADD INDEX (i);
ALTER TABLE td ADD INDEX (i);
END //
DELIMITER ;//
let $targetdir=$MYSQLTEST_VARDIR/tmp/backup;
send call a();
--disable_result_log
exec $XTRABACKUP --defaults-file=$MYSQLTEST_VARDIR/my.cnf --backup --target-dir=$targetdir;
--enable_result_log
exec $XTRABACKUP --prepare --target-dir=$targetdir;
reap;
-- source include/restart_and_restore.inc
DROP PROCEDURE a;
SHOW CREATE TABLE tz;
SHOW CREATE TABLE tr;
SHOW CREATE TABLE td;
CHECK TABLE tz,tr,td;
SELECT COUNT (*) FROM tz;
SELECT COUNT (*) FROM tr;
SELECT COUNT (*) FROM td;
DROP TABLE tz,tr,td;
If the ALTER TABLE included ,FORCE (forcing a rebuild), then this may fail due to files being renamed, and not having MDEV-16791 yet.
If the ROW_FORMAT=COMPRESSED is not commented out, then the mariabackup --prepare would fail:
InnoDB: Failing assertion: !page || !page_zip || !fil_page_index_page_check(page)
I think that we must also test a rebuild of tables that contain BLOBs (and using small innodb_log_file_size ). But testing rebuild would be tricky before we have MDEV-16791 .
Bug
Major
MDEV-16791 mariabackup : allow consistent backup, in presence of concurrent DDL, also without --lock-ddl-per-table
The backup does not have to fail, it still can succeed by re-copying the affected tablespace at the end of backup (this technique necessary on other reasons, e.g when recreating tables see
MDEV-16791)