1.3 Overview of Backup Performance and Capacity Considerations

In your backup strategy, performance and storage space are key aspects. You want the backup to complete quickly, with little CPU overhead on the database server. You also want the backup data to be compact, so you can keep multiple backups on hand to restore at a moment's notice. Transferring the backup data to a different system should be quick and convenient. All of these aspects are controlled by options of the mysqlbackup command.

Sometimes you must balance the different kinds of overhead -- CPU cycles, storage space, and network traffic. Always be aware how much time it takes to restore the data during planned maintenance or when disaster strikes. For example, here are factors to consider for some of the key MySQL Enterprise Backup features:

For disaster recovery, when speed to restore the data is critical, you might prefer to have critical backup data already prepared and uncompressed, so that the restore operation involves as few steps as possible.

It is during a disaster recovery that speed is most critical. For example, although a logical backup performed with the mysqldump command might take about the same time as a physical backup with the MySQL Enterprise Backup product (at least for a small database), the MySQL Enterprise Backup restore operation is typically faster. Copying the actual data files back to the data directory skips the overhead of inserting rows and updating indexes that comes from replaying the SQL statements from mysqldump output.

To minimize any impact on server performance on Linux and Unix systems, MySQL Enterprise Backup writes the backup data without storing it in the operating system's disk cache, by using the posix_fadvise() system call. This technique minimizes any slowdown following the backup operation, by preventing frequently accessed data from being flushed from the disk cache by the large one-time read operation for the backup data.

For more on techniques and tradeoffs involving backup and restore performance, see Chapter 7, Performance Considerations for MySQL Enterprise Backup.