This section describes the performance considerations for restoring a database server with MySQL Enterprise Backup. This subject is important because:

For the combination of options you can specify for a restore job, see Section 19.3, “Restore Operations”.

Restoring Different Classes of Backup Data

Restoring a partial backup takes less time than restoring a full backup, because there is less data to physically copy. See Section 4.3.5, “Making a Partial Backup” for information about partial backups.

Restoring a compressed backup takes more time than restoring an uncompressed backup, because the time needed to uncompress the data is typically greater than any time saved by transferring less data across the network. If you need to rearrange your storage to free up enough space to uncompress the backup before restoring it, include that administration work in your estimate of the total time required. In an emergency, the time needed to uncompress the backup data before restoring it might be unacceptable. on the database server to hold both the compressed backup and the uncompressed data. Thus, the more critical the data is, the more likely that you might choose not to use compression: accepting a slower, larger backup to ensure that the restore process is as fast and reliable as possible. See Section 20.6, “Compression Options” for information about making compressed backups.

The unpacking process to restore a single-file backup is typically not expensive either in terms of raw speed or extra storage. Each file is unpacked directly to its final destination, the same as if it was copied individually. Thus, if you can speed up the backup substantially or decrease its storage requirements by using single-file backups, that typically does not involve a tradeoff with restore time. See Section 19.5, “Other Single-File Backup Operations” for information about making single-file backups.

The Apply-Log Phase (for Directory Backups only)

See Advanced: Apply-Log Phase (for Directory Backups only) for performance considerations regarding the apply-log phase.

Network Performance

For data processing operations, you might know the conventional advice that Unix sockets are faster than TCP/IP for communicating with the database. Although the mysqlbackup command supports the options --protocol=tcp, --protocol=socket, and --protocol=pipe, these options do not have a significant effect on backup or restore performance. These processes involve file-copy operations rather than client/server network traffic. The database communication controlled by the --protocol option is low-volume. For example, mysqlbackup retrieves information about database parameters through the database connection, but not table or index data.

Parallel Restore

mysqlbackup can take advantage of modern multicore CPUs and operating system threads to perform backup operations in parallel. See Section 20.10, “Performance / Scalability / Capacity Options” for the options to control how many threads are used for different aspects of the restore process. If you see that there is unused system capacity during a restore, consider increasing the values for these options and testing whether doing so increases restore performance:

For an apply-log operation, the --process-threads option controls the number of threads that read and write modified datafile pages in parallel; those threads are usually I/O bound, even though they also perform some in-memory processing.

Depending on your operating system, you can measure resource utilization using commands such as top, iostat, sar, dtrace, or a graphical performance monitor. Do not increase the number of read or write threads iowait once the system iowait value reaches approximately 20%.