lock-ddl-per-table Option Improvements¶
MySQL 5.7 has made improvements to bulk loading, one of these improvements is the ability to disable redo logging, which increased the speed of ADD INDEX operations.
To block DDL statements on an instance, Percona Server implemented LOCK TABLES FOR BACKUP. Percona XtraBackup uses this lock for the duration of the backup. This lock does not affect DML statements.
Percona XtraBackup has also implemented
blocks DDL statements by using metadata locks (MDL).
The following procedures describe a simplified backup operation when using
Parse and copy all redo logs after the checkpoint mark
Fork a dedicated thread to continue following new redo log entries
List the tablespaces required to copy
Iterate through the list. The following steps occur with each listed tablespace:
Query INFORMATION_SCHEMA.INNODB_SYS_TABLES to find which tables belong to the tablespace ID and take a MDL on the underlying table or tables in case there is a shared tablespace.
Copy the tablespace
The backup process may encounter a redo log event, generated by the bulk load
operations, which notifies backup tools that data file changes have been
omitted from the redo log. This event is an
MLOG_INDEX_LOAD. If this
event is found by the redo follow thread, the backup continues and assumes
the backup is safe because the MDL protects tablespaces already copied and
MLOG_INDEX_LOAD event is for a tablespace that is not copied.
These assumptions may not be correct and may lead to inconsistent backups.
Implemented in Percona XtraBackup version 2.4.21, the
--lock-ddl-per-table has been redesigned to minimize inconsistent backups.
The following procedure reorders the steps:
Acquire the MDL lock at the beginning of the backup
Scan the redo logs. An
MLOG_INDEX_LOADevent may be recorded if a
CREATE INDEXstatement has occurred before the backup starts. At this time, the backup process is safe and can parse and accept the event.
After the first scan has completed, the follow redo log thread is initiated This thread stops the backup process if an
MLOG_INDEX_LOADevent is found.
Gather the tablespace list to copy
The following improvements have been added:
.ibdfile belongs to a temporary table, the
SELECTquery is skipped.
For a FullText Index, an MDL is acquired on the base table.
SELECTquery that acquires an MDL retrieves no data.