Skip to content
Percona Software for MySQL Documentation

Reduced backup lock time

Percona XtraBackup Pro includes the capabilities that are typically requested by large enterprises. Percona XtraBackup Pro contains packages created and tested by Percona. These packages are supported only for Percona Customers with a subscription.

Become a Percona Customer

Important

The --lock-ddl=REDUCED option is a tech preview. Before using this option in production, we recommend that you test restoring production from physical backups in your environment, and also use the alternative backup method for redundancy.

Percona XtraBackup 8.4.0-2 adds the --lock-ddl=REDUCED option to reduce the time the server remains locked by xtrabackup during full and incremental backups. Now, you can execute Data Definition Language (DDL) operations while the backup is in progress.

Benefits

The --lock-ddl=REDUCED option features are as follows:

  • Acquire and release the backup lock quickly: The backup process minimizes the time it holds the backup lock, allowing for concurrent DDL operations.

    Comparing the backup duration with –lock-ddl=REDUCED

    While we strive to provide accurate performance benchmarks, real-world results may vary depending on your hardware and software configurations.

    The following tables illustrate the approximate time differences between --lock-ddl=ON and --lock-ddl=REDUCED for local and cloud backups (Amazon S3). The Improvement (X times) column shows how many times less the server is locked by xtrabackup using --lock-ddl=REDUCED compared to --lock-ddl=ON.

    Data size in gigabytes Backup duration with –lock-ddl=ON in seconds Backup duration with –lock-ddl=REDUCED in seconds Improvement (X times)
    50 GB 460.2 sec 2.169 sec 212.17
    100 GB 901.8 sec 1.305 sec 691.03
    200 GB 1820.4 sec 1.347 sec 1351.45
    400 GB 3580.2 sec 1.24 sec 2887.26
    500 GB 5436 sec 1.264 sec 4300.63
    Data size in gigabytes Backup duration with –lock-ddl=ON in seconds Backup duration with –lock-ddl=REDUCED in seconds Improvement (X times)
    50 GB 469.8 sec 3.859 sec 121.74
    100 GB 927.6 sec 4.072 sec 227.80
    200 GB 1851.6 sec 4.408 sec 420.05
    400 GB 3888 sec 3.948 sec 984.80
    500 GB 4896 sec 4.065 sec 1204.43

    Using --lock-ddl=REDUCED leads to a dramatic reduction in backup time compared to --lock-ddl=ON, especially with larger data sizes.

  • Track changes with redo logs: Redo logs record all file-level changes, ensuring data consistency during the backup process.

  • Handle DDL operations: The backup process generates metadata files to account for any DDL operations that occur while the backup is in progress.
  • Ensure consistency: The --prepare step processes generated metadata files and uses redo and undo logs to create a consistent database state.

Limitations

  • Certain DDL operations consume significant resources, and xtrabackup simultaneously requires I/O to copy and read files. This requirement can increase resource demand from DDL operations during the backup process.

  • The master key rotation with the ALTER INSTANCE ROTATE INNODB MASTER KEY is prohibited while backup is in progress.

  • Backups may be larger because new DDL operations are executed concurrently on the server, and the files generated by the server are included in the backup. Additionally, certain DDL operations, such as ADD INDEX or encryption changes to existing data files, will cause the data files to be recopied, increasing the backup size.

  • The number of open file handles in your operating system should be configured to match the number of files in the server data directory.

  • Taking a backup using page tracking is not supported with --lock-ddl=REDUCED option.

How the --lock-ddl=REDUCED option works

Percona Server for MySQL implemented LOCK TABLES FOR BACKUP to block DDL operations on a server. xtrabackup utilizes this lock during the backup process to ensure that any DDL operation does not corrupt the backup. With MySQL-compatible servers xtrabackup utilizes LOCK INSTANCE FOR BACKUP to lock the server during the backup. The lock does not affect the Data Manipulation Language (DML) operations. By default, xtrabackup enables the --lock-ddl=ON option to apply the backup lock at the start of the backup.

Taking backup with the --lock-ddl=REDUCED option includes the following phases:

Phase 1: Operations performed without the lock

Warning

Do not execute the ALTER INSTANCE ROTATE MASTER KEY command during Phase 1 if there are encrypted tables. This prevents backup failures.

The following operations are performed without the lock:

  1. Parse and copy the redo logs from the checkpoint up to the current LSN, and start tracking new file operations.
  2. Start the redo log thread to copy the redo logs.
  3. Track file operations by parsing the MLOG_FILE_* records from the redo log. These records help track changes in the files being backed up to ensure consistency.
  4. Copy the .ibd files.

Phase 2: Operations performed under the lock

The following operations are performed under the lock:

  1. Take the backup lock on the server to prevent new DDL operations, such as creating or altering tables.
  2. Copy non-InnoDB files.
  3. Check the file operations that were tracked and recopy the tablespaces.
  4. Create additional metadata files to perform the required actions (deletions or renames) on the already copied files. This approach ensures that the backup remains consistent and accurate without disrupting the streaming process. This is particularly important for streaming backups.
  5. Gather a sync point from all engines (InnoDB LSN, binlog, GTID, etc.) by querying the log_status.
  6. Stop the redo thread once it copies at least up to sync point at step 4.
  7. Release the backup lock on the server.
The list of metadata files
File Description
.new For new files that are created while the backup in progress and for files that need to be recopied due to encryption or ADD INDEX operations.
.del For deleted files. For a file that has been copied by backup but deleted while the backup is in progress, create a space_id.del file.
.ren For a file that has been copied but renamed while the backup is in progress, create a space_id.ren file. This file should contain the new file name.
.crpt For files that cannot be fully copied due to encryption changes. These files will be recopied, and a .crpt file will be created for the existing file.
.new.meta Similar to .new, but for incremental backups.
.new.delta Similar to .new, but for incremental backups. Create t1.ibd.meta and t1.ibd.delta instead of t1.ibd.

Phase 3. Handling new metadata files in --prepare

The following operations are performed to ensure data remains intact and consistent:

  1. Remove the .crpt files that match the name after stripping the extension. This step is crucial before the IBD scan because these files are incomplete (they can be zero size).
  2. Perform a scan to create a mapping of space_id to file names.
  3. Delete the file matching the space_id using space_id.del. In case of incremental backups, also delete the corresponding .new.meta and .new.delta files.
  4. Rename the file matching the space_id to the name specified in the file using space_id.ren.
  5. Replace the file that matches the name without the .new extension using the .new extension.
  6. Replace the meta and delta files that match the name without the .new in the name using .new.meta and .new.delta.

After Phase 3 is completed, the regular crash recovery starts.

Get expert help

If you need assistance, visit the community forum for comprehensive and free database knowledge, or contact our Percona Database Experts for professional support and services.

Community Forum Get a Percona Expert

Last update: 2024-12-18