MySQL :: MySQL Enterprise Backup 4.1 User's Guide :: 16.10 Performance / Scalability / Capacity Options

--number-of-buffers=num_buffers

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 11.1, “Optimizing Backup Performance” and Section 11.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

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. This option applies to these kinds of operations: copy-back, copy-back-and-apply-log (for directory backups only), extract, backup, and backup-and-apply-log. 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 the following operations, the number of read threads is always 1 regardless of this option's setting:

Restores of single-file backups
apply-log operations and the apply-log phase of a copy-back-and-apply-log, backup-and-apply-log, or apply-incremental-backup operation.

See Section 11.1, “Optimizing Backup Performance” and Section 11.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.

Default: 1.

--process-threads=num_threads

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, including compression and uncompression, encryption and decryption, apply-log operations, and packing and extracting of backup images. For backup-and-apply-log, copy-back-and-apply-log, and apply-incremental-backup, --process-threads sets the worker threads number for the apply-log phase of the operation. The option is ignored for those operations that do not involve data processing like copy-back (unless decryption or uncompression is involved ), backup-dir-to-image , or a backup operation that uses the --incremental-with-redo-log-only option.

Default: 6 for all operations to which the option is applicable.

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. See Section 11.1, “Optimizing Backup Performance” and Section 11.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

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. This option applies to these kinds of operations: copy-back, copy-back-and-apply-log, extract, backup, and backup-and-apply-log (when applied to directory backups). For the supported operations, multiple write threads are supported for any write target that is seekable; --write-threads is forced to be 1 only when the write target is non-seekable. The option is ignored for other operations like single-file backups (uses 1 write thread always), list-image (uses no write threads), and validate (uses no write threads).

If you specify a value of 0, it is silently adjusted to 1 (for operations that uses write threads). The maximum is 15. If you supply a negative value, it is silently adjusted to 15.For apply-log operations and the apply-log phase of copy-back-and-apply-log, backup-and-apply-log, or apply-incremental-backup, the number of write threads is always 0 regardless of this option's setting. See Section 11.1, “Optimizing Backup Performance” and Section 11.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.

Default: 1.

--limit-memory=MB

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

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, 400 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 400 MB limit, the maximum number of buffers is 25 (except for a cloud backup, for which extra memory is needed, and the maximum number of buffers is 18). If additional buffers are required because you increased the values for --read-threads, --process-threads, --write-threads, and/or --number-of-buffers, increase the --limit-memory value proportionally.

--sleep=MS

Command-Line Format	`--sleep=MS`
Type	Numeric
Default Value	`0`
Unit	millisecond

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.

--lock-wait-timeout

Command-Line Format	`--lock-wait-timeout=S`
Type	Numeric
Default Value	`60`
Minimum Value	`1`
Unit	second

Specify the timeout in seconds for the FLUSH TABLES WITH READ LOCK statement, which mysqlbackup issues during the final stage of a backup to temporarily put the database into a read-only state. If the timeout is exceeded, the statement is failed and the lock on the tables is released, so that queries held up by the lock can then be executed. mysqlbackup then retries the statement and continues with the backup. The timeout prevents the case in which a long query running on the server prevents the FLUSH TABLES WITH READ LOCK statement from finishing, holding up further queries and eventually bringing down the server. Default is 60. Minimum value is 1.

--lock-wait-retry-count

Command-Line Format	`--lock-wait-retry-count`
Introduced	4.1.4
Type	Integer
Default Value	`2147483647`
Minimum Value	`0`
Maximum Value	`2147483647`

Specify the maximum number of retries to be attempted by mysqlbackup after the FLUSH TABLES WITH READ LOCK statement, issued during the final stage of a backup to temporarily put the database into a read-only state, fails due to a timeout. The timeout is specified by the mysqlbackup option --lock-wait-timeout, or by the system variable --innodb_lock_wait_timeout on the server. This option prevents an excessive number of retries of the lock statement, during which all transactions are blocked on the server. Default and maximum value is 2147483647. Minimum value is 0 (no retry).

--page-reread-time=MS

Command-Line Format	`--page-reread-time=MS`
Type	Numeric
Default Value	`100`
Unit	millisecond

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 second).

--page-reread-count=retry_limit

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}

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.

--skip-relaylog

When working with a replica 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 replica 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 replica, 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.

--skip-final-rescan

Skip the final rescan for InnoDB tables that are modified by DDL operations after the database has been read-locked near the end of a backup operation. This potentially shortens the duration for the lock and reduces the backup's impact on the server's normal operation, especially when many tables are being backed up.

Warning

This option can cause an incomplete or inconsistent backup if, during the backup operation, DDL operations are executed on any InnoDB tables whose file-per-table tablespaces are outside the MySQL data directory (i.e., any InnoDB tables created using the DATA DIRECTORY table option).

The option is ignored for backups using the --incremental-with-redo-log-only option and for non-backup operations.

--log-bin-index[=PATH]

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

For 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]

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

For offline backups of replica 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]

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

For offline backups of replica servers only: specify the absolute path (including file name and extension) of the information file in which a replica records information about its source, 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]

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

For offline backups of replica servers only: specify the absolute path (including file name and extension) of the information file in which a replica 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]

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, YYYYMMDDHHMMSS, YY-MM-DD, YYYY-MM-DD, YY-MM-DD HH.MM.SS, or YYYYMMDDTHHMMSS (where T is the character T).

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

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

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.