XtraBackup is a great piece of software from Percona, which allows creating (nearly) lock-less MySQL/InnoDB backups. The tool has been around for quite some time and recently even received a major version bump. I have relied on it many times over the years. As it turns out, using it in some configurations may lead to heavy swapping or prevent MySQL from running queries.
So far I only kept complaining about the wrapper script XtraBackup has been distributed with and which was taken from Oracle's InnoDB Hot Backup. The infamous innobackupex-1.5.1 was neither well written, nor was it even fully compatible with the XtraBackup's feature set. This sometimes led to weird problems where there should not be any.
This time the problem can appear elsewhere. Mostly when one using the tool does not understand how it works in certain circumstances.
How does XtraBackup work?
In a few short words. XtraBackup performs two major tasks while it is running. The primary task is of course copying tablespaces, which constitutes the core of each backup. Because the process isn't anything like making a point-in-time snapshot, it takes a significant amount of time as InnoDB files are sequentially read and written into another location one after another. There will always be transactions changing data in tables between when a backup started and when it ended. So in order to provide a fully consistent, point-in-time backup, XtraBackup has to work on another task. It follows and archives InnoDB transaction logs contents as new transactions commit while the process is running. The end result is an inconsistent backup of tablespaces and a transaction log that enables XtraBackup to fix the inconsistencies during prepare phase.
Streaming mode.
In many cases XtraBackup is used in streaming mode. It enables sending a backup straight over a network link into another system without storing any of the files on a database server itself. This is very useful for larger databases which may simply not fit two complete copies of data on available storage. It may be also useful for busy databases as it removes many disk I/Os that would otherwise be used for writing an archive to local disks.
However it is not entirely true that a streaming backup does not use any disk space on the source server:
--tmpdir=DIRECTORY
This option specifies the location where a temporary file will be stored. The option accepts a string argument. It should be used when --remote-host or --stream is specified. For these options, the transaction log will first be stored to a temporary file, before streaming or copying to a remote host. This option specifies the location where that temporary file will be stored. If the option is not specifed, the default is to use the value of tmpdir read from the server configuration.
A transaction log backup will always be written to the local storage first, even if it has to be shipped out elsewhere later.
The problem.
What if you want to run XtraBackup, but also have configured database to use memory-based storage for temporary space, e.g. tmpdir in my.cnf was set to /dev/shm or other location that mounts a tmpfs volume? Such configuration is often used to help performance when a lot of temporary tables are being created on disk. Assuming that you fail to notice the tmpdir option comment for XtraBackup, which is not actually very unlikely as I have seen very few people use it, you will be running a backup that by default (temporarily) archives the database transaction log into RAM.
If the option is not specifed, the default is to use the value of tmpdir read from the (MySQL) server configuration.
Given your database is sufficiently large and busy, or perhaps because you throttle XtraBackup, the backup process may run many hours. In several hours time the transaction logs can grow a lot, even by several gigabytes or more. By default a tmpfs volume that is these days automatically mounted by any Linux distribution can use up to 50% of RAM. As in practice no one really changes the limit from its default value, the transaction log archive can possibly also eat up to 50% of system's memory. If RAM utilization on a server was high, and usually a mature database system uses even more than 90% of the available memory, it could eventually end up like this:
Swapping. When it happens a database server that is under any real load essentially stops responding. In this case it may not even matter whether it is MySQL instance memory being paged out or perhaps tmpfs volume contents.
At the same time the memory volume contents would look like this:
garfield ~ # ls -lh /dev/shm/ -rw-r--r-- 1 root root 75 05-22 09:08 mysql-stderr -rw-r--r-- 1 root root 226 05-22 09:08 mysql-stdout -rw-r--r-- 1 root root 2G 05-22 09:08 xtrabackup_logfile
More problems.
The above does not have to be the only problem. Regardless of what MySQL uses for its temporary directory, if the storage is too small (e.g. some use a few gigabytes large /tmp partition), more bad things can happen.
If it gets full during backup, MySQL will not be able to run any queries that rely on temporary files, e.g. some that use GROUP BY or ORDER BY:
mysql> SELECT * FROM sbtest ORDER BY pad DESC LIMIT 5; ERROR 3 (HY000): Error writing file '/dev/shm/MYJee0PR' (Errcode: 28) mysql> Bye garfield ~ # perror 28 OS error code 28: No space left on device
Another problem that I spotted was that XtraBackup continued performing backup even after it noticed and reported that one of its writes failed. This is clearly a bug, which permits creation of a broken backup.
innobackupex-1.5.1: Backing up file '/var/lib/mysql/sbtest/sbtest.ibd' >> log scanned up to (1 4052543506) >> log scanned up to (1 4052543506) >> log scanned up to (1 4052543506) write: 65536 > 2048 xtrabackup: Error: write to stdout xtrabackup: Error: xtrabackup_copy_logfile() failed. innobackupex-1.5.1: Backing up file '/var/lib/mysql/sbtest/sbtest2.ibd' [..] xtrabackup: The latest check point (for incremental): '1:4115657100' xtrabackup: Error: log_copying_thread failed. 120522 13:16:19 innobackupex-1.5.1: All tables unlocked 120522 13:16:19 innobackupex-1.5.1: Connection to database server closed innobackupex-1.5.1: Backup created in directory '/root/backup' innobackupex-1.5.1: MySQL binlog position: filename 'mysqld-bin.000067', position 107090 120522 13:16:19 innobackupex-1.5.1: completed OK! innobackupex-1.5.1: You must use -i (--ignore-zeros) option for extraction of the tar stream. garfield backup # echo $? 0
Conclusions.
Be careful when implementing streaming backup with XtraBackup. Make sure to check where it will store its temporary files as they may grow relatively large or even better always explicitly set tmpdir in the command line.
This problem also raises another important matter. If you configure a MySQL server to use a RAM based volume for a database temporary storage, make sure to tune tmpfs volume configuration rather than leave it with the default limit of 50%.