If there are thousands of tables and slow IO then Xtrabackup can spend a lot of time opening all the tablespaces

Bug #1130145 reported by Ovais Tariq on 2013-02-19
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona XtraBackup moved to https://jira.percona.com/projects/PXB
Fix Released
Sergei Glushchenko
Fix Released
Sergei Glushchenko
Fix Released
Sergei Glushchenko

Bug Description

Xtrabackup opens all the tablespaces as a part of its booting process. With innodb_file_per_table enabled and thousands of tables this can take a really lot of time, especially if there is not much IO capacity available for Xtrabackup to utilize. This is true even when partial backup needs to be done, as even in that case all the tablespaces are opened.

I was wondering about the possibility of skipping the tablespace opening step when doing the backup, this will speed up the Xtrabackup boot process and will specially be helpful for partial backups.

Related branches

In the presence of thousands of tables, it can be checked if a
higher value of innodb-open-files helps here (since by default
it is a LRU of size 768) and if xtrabackup checks that variable.

Ovais Tariq (ovais-tariq) wrote :


Higher values of innodb-open-files should only help if the same table is opened twice, however, yes LRU process can be avoided. I wonder how much of a difference LRU removal is going to make here. The proper solution would be IMHO to skip reading the tablespaces at the start of the backup.

Alexey Kopytov (akopytov) wrote :

XtraBackup has to create a list of files to copy on backup, so it has to iterate all tablespaces. It also has to open them to read their space IDs. This is required to detect cases when a tablespace is re-created during the backup (i.e. its ID changes), so we should omit it from the backup, so it will be recreated on redo log recovery.

But I agree, this can be optimized for partial backups. We still have to iterate tablespaces to find the ones we want to include into a partial backup, but we don't have to open them.

Changed in percona-xtrabackup:
status: New → Triaged
importance: Undecided → Medium

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXB-38

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers