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 --lock-ddl-per-table, which blocks DDL statements by using metadata locks (MDL).

The following procedures describe a simplified backup operation when using --lock-ddl-per-table:

1. Parse and copy all redo logs after the checkpoint mark

2. Fork a dedicated thread to continue following new redo log entries

3. List the tablespaces required to copy

4. 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 .ibd files.

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 the MLOG_INDEX_LOAD event is for a tablespace that is not copied.

These assumptions may not be correct and may lead to inconsistent backups.

--lock-ddl-per-table redesign

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_LOAD event may be recorded if a CREATE INDEX statement 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_LOAD event is found.

• Gather the tablespace list to copy

• Copy the .ibd files.

Other Improvements

The following improvements have been added:

• If the .ibd file belongs to a temporary table, the SELECT query is skipped.

• For a FullText Index, an MDL is acquired on the base table.

• A SELECT query that acquires an MDL retrieves no data.

Contact us

For free technical help, visit the Percona Community Forum.

To report bugs or submit feature requests, open a JIRA ticket.

For paid support and managed or consulting services , contact Percona Sales.

---

Last update: 2022-07-19