Documentation Home
MySQL Enterprise Backup 3.10 User's Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.0Mb
PDF (A4) - 1.0Mb


5.1.11 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
    Permitted ValuesTypenumeric
    Default14
    Min Value1

    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
    Permitted ValuesTypenumeric
    Default1
    Min Value1
    Max Value15

    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.

  • --write-threads=num_threads

    Command-Line Format--write-threads=NUMBER
    Permitted ValuesTypenumeric
    Default1
    Min Value1
    Max Value15

    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
    Permitted ValuesTypenumeric
    Default100 for apply-log (without uncompression), 300 for other operations
    Min Value0
    Max Value999999

    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
    Permitted ValuesTypenumeric
    Default0

    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

    Command-Line Format--page-reread-time=MS
    Permitted ValuesTypenumeric
    Default100

    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
    Permitted ValuesTypenumeric
    Default500

    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
    Permitted ValuesTypeenumeration
    Defaultabort
    Valid Valuesabort
    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.


User Comments
Sign Up Login You must be logged in to post a comment.