This section describes the performance considerations for backing up a database with MySQL Enterprise Backup. When optimizing and tuning the backup procedure, measure both the raw performance (how long it takes the backup to complete) and the amount of overhead on the database server. When measuring backup performance, consider:
The limits imposed by your backup procedures. For example, if you take a backup every 8 hours, the backup must take less than 8 hours to finish.
The limits imposed by your network and storage infrastructure. For example, if you need to fit many backups on a particular storage device, you might use compressed backups, even if that made the backup process slower.
The tradeoff between backup time and restore time. You might choose a set of options resulting in a slightly slower backup, if those options enable the restore to be much faster. SeeSection 13.2, “Optimizing Restore Performance” for performance information for the restore process.
Full or Incremental Backup
After taking a full backup, subsequent backups can be performed more quickly by doing incremental backups, where only the changed data is backed up. For an incremental backup, specify the--incremental or--incremental-with-redo-log-only option tomysqlbackup. SeeSection 20.7, “Incremental Backup Options” for information about these options. For usage instructions for the backup and apply stages of incremental backups, seeSection 4.3.3, “Making a Differential or Incremental Backup”.
Compressed Backup
Compressing the backup data before transmitting it to another server involves additional CPU overhead on the database server where the backup takes place, but less network traffic and less disk I/O on the server that is the final destination for the backup data. Consider the load on your database server, the bandwidth of your network, and the relative capacities of the database and destination servers when deciding whether or not to use compression. SeeSection 4.3.4, “Making a Compressed Backup” andSection 20.6, “Compression Options” for information about creating compressed backups.
Compression involves a tradeoff between backup performance and restore performance. 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.
InnoDB Configuration Options Settings
As discussed later, there are a number of reasons why you might prefer to run with the settinginnodb_file_per_table=1.
Parallel Backup
mysqlbackup can take advantage of modern multicore CPUs and operating system threads to perform backup operations in parallel. SeeSection 20.10, “Performance / Scalability / Capacity Options” for the options to control how many threads are used for different aspects of the backup process. If you see that there is unused system capacity during backups, consider increasing the values for these options and testing whether doing so increases backup 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-memoryoption, 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-threadsoption.If the storage device that you are backing up from (the source drive) can handle more I/O requests, increase the value of the
--read-threadsoption.If the storage device that you are backing up to (the destination drive) can handle more I/O requests, increase the value of the
--write-threadsoption (not applicable to single-file backups, which always use a single write thread).
Depending on your operating system, you can measure resource utilization using commands such astop,iostat,sar,dtrace, or a graphical performance monitor. Do not increase the number of read or write threads once the systemiowait value reaches approximately 20%.
MyISAM Considerations
Althoughmysqlbackup backs up InnoDB tables without interrupting database use, the final stage that copies non-InnoDB files (such as MyISAM tables and
.sdifiles) temporarily puts those tables into a read-only state, using the statementFLUSH TABLES. For best backup performance and minimal impact on database processing:tbl_name [, tbl_name]... WITH READ LOCKThen the time where non-InnoDB tables are read-locked will be short and the normal processing ofmysqld will not be disturbed much. If the preceding conditions are not met in your database application, use the
--only-innodboption to back up only InnoDB tables, or use the--no-lockingoption. Note that files copied under the--no-lockingsetting cannot be guaranteed to have consistent data.For a large database server, a backup run might take a long time. Always check thatmysqlbackup has completed successfully, either by verifying thatmysqlbackup returned exit code 0, or by observing thatmysqlbackup has printed the text“mysqlbackup completed OK!”.
Schedule backups during periods when no DDL operations involving tables are running. SeeAppendix B,Limitations of MySQL Enterprise Backup for restrictions on backups at the same time as DDL operations.
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 server. Although themysqlbackup 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 server communication controlled by the--protocol option is low-volume. For example,mysqlbackup retrieves information about database server parameters through the database server connection, but not table or index data.
Data Size
If certain tables or databases contain non-critical information, or are rarely updated, you can leave them out of your most frequent backups and back them up on a less frequent schedule. SeeSection 20.8, “Partial Backup and Restore Options” for information about the relevant options, andSection 4.3.5, “Making a Partial Backup” for instructions about leaving out data from specific tables, databases, or storage engines. Partial backups are faster because they copy, compress, and transmit a smaller volume of data.
To minimize the overall size ofInnoDB data files, consider enabling the MySQL configuration optioninnodb_file_per_table. This option can minimize data size forInnoDB tables in several ways:
It prevents the
InnoDBsystem tablespace from ballooning in size, allocating disk space that can afterwards only be used by MySQL. For example, sometimes huge amounts of data are only needed temporarily, or are loaded by mistake or during experimentation. Without theinnodb_file_per_tableoption, the system tablespace expands to hold all this data, and never shrinks afterward.It immediately frees the disk space taken up by an
InnoDBtable and its indexes when the table is dropped or truncated. Each table and its associated indexes are represented by a.ibd file that is deleted or emptied by these DDL operations.It allows unused space within a
.ibdfile to be reclaimed by theOPTIMIZE TABLEstatement, when substantial amounts of data are removed or indexes are dropped.It enables partial backups where you back up some
InnoDBtables and not others, as discussed inSection 4.3.5, “Making a Partial Backup”.It allows the use of table compression for InnoDB tables.
In general, using table compression by havingROW_FORMAT=COMPRESSED decreases table sizes and increase backup and restore performance. However, as a trade-off, table compression can potentially increase redo log sizes and thus slow down incremental backups and restores, as well asapply-log operations. SeeHow Compression Works for InnoDB Tables for details.
Avoid creating indexes that are not used by queries. Because indexes take up space in the backup data, unnecessary indexes slow down the backup process. (The copying and scanning mechanisms used bymysqlbackup do not rely on indexes to do their work.) For example, it is typically not helpful to create an index on each column of a table, because only one index is used by any query. Because the primary key columns are included in eachInnoDB secondary index, it wastes space to define primary keys composed of numerous or lengthy columns, or multiple secondary indexes with different permutations of the same columns.
Advanced: Apply-Log Phase (for Directory Backups only)
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 (theapply-log phase) to that separate machine.Apply-log Operation
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 thebackup-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 fromApply-log Operation.