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

Command-Line Format --number-of-buffers=NUMBER

Type numeric Default 14 Permitted Values Min 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 7.1, “Optimizing Backup Performance” and Section 7.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 1

Permitted Values

Performance / Scalability / Capacity Options

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

Command-Line Format --process-threads=NUMBER

Type numeric Default 6 Min Value 1 Permitted Values Max 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 7.1, “Optimizing Backup Performance” and Section 7.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 1 Min Value 1 Permitted Values Max 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 7.1, “Optimizing Backup Performance” and Section 7.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

Command-Line Format --limit-memory=MB

Type numeric

Default 100 for apply-log (without uncompression), 300 for other operations

Min Value 0 Permitted Values Max Value 999999

Specify maximum memory in megabytes that can be used by the mysqlbackup command. 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

Command-Line Format --sleep=MS

Type numeric

Permitted Values

Default 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

Performance / Scalability / Capacity Options

• --page-reread-time=MS

Command-Line Format --page-reread-time=MS

Type numeric

Permitted Values

Default 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

Command-Line Format --page-reread-count=number

Type numeric

Permitted Values

Default 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 abort abort warn Permitted Values Valid Values 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.

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.