MySQL Enterprise Backup User's Guide (Version 3.11.1)
This section describes the performance considerations for restoring a database with MySQL Enterprise Backup. This subject is important because:
The restore operation is the phase of the backup-restore cycle that tends to vary substantially between different backup methods. For example, backup performance might be acceptable using mysqldump, but mysqldump typically takes much longer than MySQL Enterprise Backup for a restore operation.
The restore operation is often performed during an emergency, where it is critical to minimize the downtime of the application or web site.
The restore operation is always performed with the database server shut down.
The restore operation is mainly dependent on low-level considerations, such as I/O and network speed for transferring files, and CPU speed, processor cores, and so on for uncompressing data.
For the combination of options you can specify for a restore job, see Section 5.1.1.3, “Restore an Existing Backup”.
Restoring a partial backup takes less time than restoring a full backup, because there is less data to physically copy. See Section 5.1.9, “Partial Backup and Restore Options” for information about making 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. There might also be storage issues if there is not enough free space 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 5.1.7, “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 5.1.1.5, “Work with Single-File Backups” for information about making single-file backups.
If you store the backup data on a separate machine, and that machine is not as busy the machine hosting the database server, you can offload some postprocessing work (the apply-log phase) to that separate machine. Section 5.1.1.2, “Apply-Log Operations for Existing Backup Data”
There is always a performance tradeoff between doing the apply-log
phase immediately after the initial backup (makes restore faster),
or postponing it until right before the restore (makes backup
faster). In an emergency, restore performance is the most important
consideration. Thus, the more crucial the data is, the more
important it is to run the apply-log phase immediately after the
backup. Either combine the backup and apply-log phases on the same
server by specifying the
backup-and-apply-log
option, or perform
the fast initial backup, transfer the backup data to another server,
and then perform the apply-log phase using one of the options from
Section 5.1.1.2, “Apply-Log Operations for Existing Backup Data”.
Section 5.1.1.2, “Apply-Log Operations for Existing Backup Data”
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.
The mysqlbackup command can take advantage of modern multicore CPUs and operating system threads to perform backup operations in parallel. See Section 5.1.11, “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:
When tuning and testing backup performance using a RAID storage
configuration, consider the combination of option settings
--read-threads=3 --process-threads=6
--write-threads=3
. Compare against the combination
--read-threads=1 --process-threads=6
--write-threads=1
.
When tuning and testing backup performance using a non-RAID
storage configuration, consider the combination of option
settings --read-threads=1 --process-threads=6
--write-threads=1
.
When you increase the values for any of the 3
“threads” options, also increase the value of the
--limit-memory
option, to give the
extra threads enough memory to do their work.
If the CPU is not too busy (less than 80% CPU utilization),
increase the value of the
--process-threads
option.
If the storage device that you are restoring from (the source
drive) can handle more I/O requests, increase the value of the
--read-threads
option.
If the storage device that you are restoring to (the destination
drive) can handle more I/O requests, increase the value of the
--write-threads
option.
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%.