15.10 Performance / Scalability / Capacity Options

These options limit the resources used by the backup process, in order to minimize backup overhead for busy or huge databases, or specify behaviors of the process when encountering resource issues.

• --number-of-buffers=num_buffers

  Property	Value
  Command-Line Format	--number-of-buffers=NUMBER
  Type	Numeric
  Default Value	14
  Minimum Value	1

  Specifies the number of buffers, each 16MB in size, to use during multithreaded options.

  Use a high number for CPU-intensive processing such as backup, particularly when using compression. Use a low number for disk-intensive processing such as restoring a backup. This value should be at least as high as the number of read threads or write threads, depending on the type of operation.

  Default: currently 14.

  For compression or incremental backup operations, the buffer size is slightly more than 16MB to accommodate the headers.

  One additional buffer is used for single-file incremental backup and single-file compressed backup.

  Compressed backup, compressed single-file backup, and uncompress apply-log operations require one additional buffer for each process thread.

  If you change the number of read, write, and processing threads, you can experiment with changing this value so that it is slightly larger than the total number of threads specified by those other options. See Section 10.1, “Optimizing Backup Performance” and Section 10.2, “Optimizing Restore Performance” for additional advice about recommended combinations of values for this and other performance-related options for various hardware configurations, such as RAID or non-RAID storage devices.

• --read-threads=num_threads

  Property	Value
  Command-Line Format	--read-threads=NUMBER
  Type	Numeric
  Default Value	1
  Minimum Value	1
  Maximum Value	15

  Specifies the number of threads to use for reading data from disk.

  Default: currently 1. This default applies to these kinds of operations: copy-back, extract, and backup. If you specify a value of 0, it is silently adjusted to 1. The maximum is 15; if you supply a negative value, it is silently adjusted to 15. For apply-log operations, the number of read threads is always 1 regardless of this option setting. See Section 10.1, “Optimizing Backup Performance” and Section 10.2, “Optimizing Restore Performance” for advice about recommended combinations of values for --read-threads, --process-threads, and --write-threads for various hardware configurations, such as RAID or non-RAID storage devices.

• --process-threads=num_threads

  Property	Value
  Command-Line Format	--process-threads=NUMBER
  Type	Numeric
  Default Value	6
  Minimum Value	1
  Maximum Value	15

  Specifies the number of threads to use for processing data, such as compressing or uncompressing backup files.

  Default: currently 6. This default applies to these kinds of operations: extract, and backup. It is ignored when you use any of the options --incremental-with-redo-log-only, apply-incremental-backup, copy-back, or backup-dir-to-image.

  If you specify a value of 0, it is silently adjusted to 1. The maximum is 15; if you supply a negative value, it is silently adjusted to 15. For apply-log operations, the number of process threads is always 1 regardless of this option setting. See Section 10.1, “Optimizing Backup Performance” and Section 10.2, “Optimizing Restore Performance” for advice about recommended combinations of values for --read-threads, --process-threads, and --write-threads for various hardware configurations, such as RAID or non-RAID storage devices.

• --write-threads=num_threads

  Property	Value
  Command-Line Format	--write-threads=NUMBER
  Type	Numeric
  Default Value	1
  Minimum Value	1
  Maximum Value	15

  Specifies the number of threads to use for writing data to disk.

  Default: currently 1. This default applies to these kinds of operations: copy-back, extract, and backup. It is ignored when you use any of the single-file backup options list-image or validate.

  If you specify a value of 0, it is silently adjusted to 1. The maximum is 15; if you supply a negative value, it is silently adjusted to 15. For apply-log operations, the number of write threads is always 0 regardless of this option setting. See Section 10.1, “Optimizing Backup Performance” and Section 10.2, “Optimizing Restore Performance” for advice about recommended combinations of values for --read-threads, --process-threads, and --write-threads for various hardware configurations, such as RAID or non-RAID storage devices.

• --limit-memory=MB

  Property	Value
  Command-Line Format	--limit-memory=MB
  Type	Numeric
  Default Value	100 for apply-log (without uncompression), 300 for other operations
  Minimum Value	0
  Maximum Value	999999

  Specify maximum memory in megabytes that can be used by mysqlbackup. Formerly applied only to apply-log operation, but in MySQL Enterprise Backup 3.8 and higher it applies to all operations. Do not include any suffixes such as mb or kb in the option value.

  Default: 100 for apply-log not used with --uncompress, 300 for all operations (in megabytes).

  The memory limit specified by this option also caps the number of 16MB buffers available for multithreaded processing. For example, with a 300 MB limit, the maximum number of buffers is 18. If additional buffers are required because you increase the values for --read-threads, --process-threads, --write-threads, and/or --number-of-buffers, increase the --limit-memory value proportionally.

• --sleep=MS

  Property	Value
  Command-Line Format	--sleep=MS
  Type	Numeric
  Default Value	0

  Specify the number in milliseconds to sleep after copying a certain amount of data from InnoDB tables. Each block of data is 1024 InnoDB data pages, typically totalling 16MB. This is to limit the CPU and I/O overhead on the database server.

  Default: 0 (no voluntary sleeps).

• --no-locking

  Disables locking during backup of non-InnoDB files, even if a connection is available. Can be used to copy non-InnoDB data with less disruption to normal database processing. There could be inconsistencies in non-InnoDB data if any changes are made while those files are being backed up.

• --page-reread-time=MS

  Property	Value
  Command-Line Format	--page-reread-time=MS
  Type	Numeric
  Default Value	100

  Interval in milliseconds that mysqlbackup waits before re-reading a page that fails a checksum test. A busy server could be writing a page at the same moment that mysqlbackup is reading it. Can be a floating-point number, such as 0.05 meaning 50 microseconds. Best possible resolution is 1 microsecond, but it could be worse on some platforms. Default is 100 milliseconds (0.1 seconds).

• --page-reread-count=retry_limit

  Property	Value
  Command-Line Format	--page-reread-count=number
  Type	Numeric
  Default Value	500

  Maximum number of re-read attempts, when a page fails a checksum test. A busy server could be writing a page at the same moment that mysqlbackup is reading it. If the same page fails this many checksum tests consecutively, with a pause based on the --page-reread-time option between each attempt, the backup fails. Default is 500.

• --on-disk-full={abort|abort_and_remove|warn}

  Property	Value
  Command-Line Format	--on-disk-full=option
  Type	Enumeration
  Default Value	abort
  Valid Values	abort warn abort_and_remove

  Specifies the behavior when a backup process encounters a disk-full condition. This option is only for backup operations (backup, backup-and-apply-log, and backup-to-image).

  • abort: Abort backup, without removing the backup directory. The disk remains full.

  • abort_and_remove: Abort backup and remove the backup directory.

  • warn: Write a warning message every 30 seconds and retry backup until disk space becomes available.

  Default: abort.

• --skip-unused-pages

  Skip unused pages in tablespaces when backing up InnoDB tables. This option is applicable to the backup and backup-to-image operations, but not to incremental backups. The option is ignored by the backup-and-apply-log operation.

  Note that backups created with the --skip-unused-pages option cannot be restored using copy-back-and-apply-log.

  Unused pages are free pages often caused by bulk delete of data. By skipping the unused pages during backups, this option can reduce the backup sizes and thus the required disk space and I/O resources for the operations. However, subsequent apply-log operations on the backups will take more time to complete, as the unused pages are inserted back into the tables during the operations.

• --skip-binlog

  Skip including the binary log files in the backup during a backup operation, or skip copying the binary log files onto a server during a restore operation.

  Binary log files, together with the binary log index file, are included by default for all kinds of online backups (full, incremental, compressed, partial, single-file, etc.). See Table 1.1, “Types of Files in a Backup”, for details. Use this option to skip backing up binary logs for the following situations:

  • If resource or performance issues arise.

  • If any binary log files are missing on the server you are backing up, in order to avoid mysqlbackup throwing an error for the missing files.

  • If you are making an incremental backup that is based on a backup (full or incremental) created using the --no-locking option, as binary log information will then be unavailable to mysqlbackup in that situation.

  The binary log files and the binary log index file, when included in a backup, are always copied into the restored server's data directory during a restore operation; if that is not the behavior you desire, use this option to skip the restoring of the binary log.

• --skip-relaylog

  When working with a slave server, skip including the relay log files in the backup during a backup operation, or skip copying the relay log files onto a server during a restore operation.

  Relay log files, together with the relay log index file and the master.info and the slave.info files, are included by default for all kinds of online backups (full, incremental, compressed, partial, single-file, etc.) of a slave server. See Table 1.1, “Types of Files in a Backup”, for details. Use this option to skip backing up relay logs if resource, performance, or other issues arise.

  Note

  If a user runs a FLUSH LOGS statement while backup is in progress for a slave, the backup process will fail. Use the--skip-relaylog option if you expect a FLUSH LOGS statement will be run during the backup and it is not necessary to include the relay logs in the backup.

  The relay log files and the files backed up together with them, when included in a backup, are always copied into the restored server's data directory during a restore operation; if that is not the behavior you desire, use this option to skip the restoring of the relay log.

• --log-bin-index[=PATH]

  Property	Value
  Command-Line Format	--log-bin-index=FILENAME
  Type	File name
  Default Value	data_dir/host_name-bin.index

  For MySQL 5.5 and earlier, as well as all offline backups: specify the absolute path (including file name and extension) of the index file on the MySQL server that lists all the used binary log files, if it is different from the default path given below, in order to include the binary log files in the backup.

  Default: data_dir/host_name-bin.index.

• --relay-log-index[=PATH]

  Property	Value
  Command-Line Format	--relay-log-index=FILENAME
  Type	File name
  Default Value	data_dir/host_name-relay-bin.index

  For offline backups of slave servers only: specify the absolute path (including file name and extension) of the index file on the MySQL server that lists all the used relay log files, if it is different from the default path given below, in order to include the relay log files in the backup.

  Default: data_dir/host_name-relay-bin.index.

• --master-info-file[=PATH]

  Property	Value
  Command-Line Format	--master-info-file=FILENAME
  Type	File name
  Default Value	data_dir/master.info

  For offline backups of slave servers only: specify the absolute path (including file name and extension) of the information file in which a slave records information about its master, if it is different from the default path given below, in order to include the information file in the backup.

  Default: data_dir/master.info.

• --relaylog-info-file[=PATH]

  Property	Value
  Command-Line Format	--relaylog-info-file=FILENAME
  Type	File name
  Default Value	data_dir/relay-log.info

  For offline backups of slave servers only: specify the absolute path (including file name and extension) of the information file in which a slave records information about the relay logs, if it is different from the default path given below, in order to include the information file in the backup.

  Default: data_dir/relay-log.info.

• --optimistic-time[=DATE-TIME]

  Property	Value
  Command-Line Format	--optimistic-time=DATE-TIME
  Type	String
  Default Value	now

  Perform an optimistic backup with the value specified with the option as the “optimistic time”—a time after which the tables that have not been modified are taken as “inactive tables.” The “inactive tables”are believed to be unlikely to change during the backup process. The inactive tables are backed up in the optimistic phase of the backup, and all other tables are backed up in the normal phase. See Section 4.3.6, “Making an Optimistic Backup” for details on the concept, use cases, and command samples for an optimistic backup.

  Accepted formats for specifying the option include:

  • now: This includes all tables into the optimistic phase of the backup process. It is the default value for the option when no value is specified.

  • {Number}{Unit}: Indicates the optimistic time as a time at a certain duration into the past. {Unit} can be any one of years, months, hours, and minutes. Some examples for option strings in this format include: 5years, 2days,13months, 23hours, and 35minutes.

  • A date-time format in any of the following forms: YYMMDD, YYYYMMDD, YYMMDDHHMMSS, YYYYMMDDHHMMSSYY-MM-DD, YYYY-MM-DD, YY-MM-DD, or HH.MM.SSYYYYMMDDTHHMMSS, as designated by the ISO 8601 standard.

  When both the optimistic-time and the optimistic-busy-tables options are used and they come into conflict on determining which tables are to be backed up in the optimistic phase, optimistic-busy-tables takes precedence over optimistic-time.

• --optimistic-busy-tables=REGEXP

  Property	Value
  Command-Line Format	--optimistic-busy-tables=REGEXP
  Type	String

  Perform an optimistic backup, using the regular expression specified with the option to select tables that will be skipped in the first phase of an optimistic backup, because they are likely to be modified during the backup process. Tables whose fully qualified names (in the form of database_name.table_name) are matched by the regular expression are taken as “busy tables”, which will be backed up in the second or the “normal” phase of the backup. Tables whose fully qualified names are NOT matched by the regular expression are taken as “inactive tables”, which will be backed up in the first or the “optimistic” phase of the backup. See Section 4.3.6, “Making an Optimistic Backup” for details on the concept, use cases, and command samples for an optimistic backup.

  MySQL Enterprise Backup will throw an error if the option is used but no regular expression is supplied with it.

  When both the optimistic-time and the optimistic-busy-tables options are used and they come into conflict on determining which tables are to be “optimistic”, optimistic-busy-tables takes precedence over optimistic-time.

• --free-os-buffers

  (For release 4.0.1 and later) Free the system buffer cache at the end of a backup operation by syncing all data from the buffer cache to the hard disk. Using the option might increase the backup time significantly for systems with slow storage devices and databases with many tables.

  Default: Automatic syncing disabled.