Using Percona XtraBackup on a MySQL Instance with a Large Number of Tables

Percona XtraBackupIn this blog post, we’ll find out how to use Percona XtraBackup on a MySQL instance with a large number of tables.

As of Percona Xtrabackup 2.4.5, you are required to have enough open files to open every single InnoDB tablespace in the instance you’re trying to back up. So if you’re running innodb_file_per_table=1, and have a large number of tables, you’re very likely to see Percona XtraBackup fail with the following error message:

If you run into this issue, here is what you need to do:

  1. Find out how many files you need:

I would add at least another 1000 to this number for system tablespace and other miscellaneous open file needs. You might want to go even higher to accommodate for a growing number of tables.

  1. Check the maximum number of files you can keep open in the system. If this number is too small Percona Xtrabackup might monopolize the open files in the system, causing other processes to fail when they try to open files. This can cause MySQL Server to crash, and other processes to fail.

If you need to, here is how to  increase the number:

  1. Increase the limit on the number of files the Percona XtraBackup process can open:

The best way to do this is using --open-files-limit option. For example, you can specify the following in your my.cnf:

Alternatively, you can pass it as a command-line option, or run ulimit -n 2000000 before running the backup command.

You need to be sure your user account has permissions to set open files limit this high. If you are doing backups under the “root” user, it shouldn’t be a problem. Otherwise, you might need to adjust the limits in  /etc/security/limits.conf:

Specifying a “soft” limit in this file eliminates the need to run ulimit before Percona XtraBackup, or specifying it in the configuration.

  1. There is one more possible limit to overcome. Even running as a root user, you might get the following error message:

If this happens, you might need to increase the kernel limit on the number of processes any can have:

The limit I have on this system is slightly above 1 million. You can increase it using the following:

With these configuration adjustments, you should be able to use Percona XtraBackup to backup MySQL instances containing millions of tables without problems.

What if you can’t allow Percona XtraBackup to open that many files? Then there is the option –close-files that won’t normally require increasing the limit to the number of open files. Using this option, however, might cause the backup corruption if you’re doing DDL operations during the backup.

From where does this strange limitation requiring you to keep all tablespaces open come? It comes from this issue. In some cases, DDL operations such as RENAME TABLE might cause the wrong file to be copied, and unable to be caught up by replying to InnoDB redo logs. Keeping the file open clearly shows which file corresponds to a given tablespace at the start of a backup process, and gets handled correctly.

This problem is not unique to Percona XtraBackup. If anything, Percona Xtrabackup goes the extra mile to ensure database backups are safe.  For comparison, MySQL Enterprise Backup 4.0  simply states:  

Do not run the DDL operations ALTER TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, REPAIR TABLE, RESTORE TABLE or CREATE INDEX while a backup operation is going on. The resulting backup might become corrupted.”

Share this post

Comments (10)

  • Guillaume Lefranc

    As far as I know, this is also an issue in older versions of XtraBackup such as 2.2 and 2.3 branches.

    December 28, 2016 at 1:37 pm
  • Peter Zaitsev


    Yes… Might be bad wording my point is what it is the issue in 2.4 (also earlier versions) I would like us to see what can be done to remove such limitation

    December 28, 2016 at 1:50 pm
  • Guillaume Lefranc


    I’ve always been very curious of the reason why this is a requirement?

    December 28, 2016 at 2:09 pm
    • Peter Zaitsev

      It is actually referred in the blog post. Second to last Paragraph. Here it is again

      December 28, 2016 at 2:27 pm
      • Guillaume Lefranc

        OK, makes sense. In this case, maybe having the option to disable open file handles would be great for specific use case where DBAs know the workload and are certain that there are no DDLs executing on the DB.

        December 28, 2016 at 4:35 pm
  • Valeriy

    It seems new line is missing here:

    “root@ts140i:/mnt/data/backup# cat /proc/sys/fs/file-max 3262006”

    December 29, 2016 at 2:02 pm
  • Marko Mäkelä

    I think that there is an architectural problem that was originally dictated by a business choice (the need to have a closed-source backup tool running in a separate process, only communicating with InnoDB via the file system).

    If the files were backed up by the database server process, there would be no need to keep a large number of file handles open, and optimizations like would cause no problem. (WL#7277 disables redo logging for part of ALTER TABLE…ALGORITHM=INPLACE operations. I introduced the redo log record type MLOG_INDEX_LOAD so that the Oracle backup tool would be able to notice that the backup failed.)

    That said, I believe that further changes to InnoDB would be needed to make hot backups work under any workload. In particular, certain DDL operations that rename files are problematic, because the renames are not synchronized with transaction commit, and there is no undoing of rename operations in the InnoDB transaction rollback. A proper solution to that would be a transactional data dictionary and a transactional table that manages the roll-forward/rollback of DDL operations, including the deletion or renaming of files. I hope that MySQL or will deliver that some day.

    On related news, I believe that further problems with hot backup might occur when InnoDB is extending data files while the files are being copied. InnoDB Hot Backup used to deal with this problem in –apply-log by silently extending data files when the redo log refers to out-of-bounds pages. A better solution (which would also make InnoDB more crash-tolerant) is to modify the InnoDB redo log scan so that it tracks the changes to the FSP_SIZE field in the tablespace header and then extends each file before any page-level redo log is applied. That is implemented in which should reach MariaDB 10.1 and 10.2 soon.

    December 30, 2016 at 1:40 am
  • Peter Zaitsev

    Hi Marko,

    Thank you for insiders prespective! The goal of Percona Xtrabackup is to work with standard MySQL to the maximum extent possible. It would be much easier to just to changes we need at Percona Server but it would not be as useful for community at large. We do make backups better with Percona server, such as using BACKUP LOCKS if they are supported.

    December 30, 2016 at 5:03 pm
  • Artsiom Som

    my variant for up limit on ubuntu/debian
    mkdir -p ${dmsl}
    touch ${dmsl}limits.conf
    echo “[Service]” » ${dmsl}limits.conf
    echo “LimitNOFILE = infinity” » ${dmsl}limits.conf
    echo “LimitMEMLOCK = infinity” » ${dmsl}limits.conf
    systemctl daemon-reload
    systemctl restart mysql

    May 18, 2017 at 4:57 am

Comments are closed.

Use Percona's Technical Forum to ask any follow-up questions on this blog topic.