Skip to content
logo
Percona XtraBackup
Smart memory estimation
Initializing search
    percona/pxb-docs
    percona/pxb-docs
    • Home
      • About Percona XtraBackup
      • How Percona XtraBackup works
      • Understand version numbers
      • Install Percona XtraBackup 8.0
      • Use an APT repo to install Percona XtraBackup
      • Use a YUM repo to install Percona XtraBackup
      • Use DEB or RPM downloaded packages to install Percona XtraBackup
      • Install Percona XtraBackup from a Binary Tarball
      • Compile and install Percona XtraBackup from source code
      • What’s in the packages
      • Uninstall Percona XtraBackup
      • Run Percona XtraBackup in a Docker container
      • Implementation details
      • Connection and privileges needed
      • Configure xtrabackup
      • Server version and backup version comparison
      • xtrabackup exit codes
      • The backup cycle - full backups
      • Incremental backup
      • Compressed backup
      • Partial backups
      • Percona XtraBackup user manual
      • Throttling backups
      • Encrypted InnoDB tablespace backups
      • Encrypt backups
      • LRU dump backup
      • Point-in-time recovery
      • Smart memory estimation
        • How does Smart memory estimation work
        • Exapmple of Smart memory estimation usage
      • Work with binary logs
      • Improved log statements
      • Work with SELinux
      • Work with AppArmor
      • Enable the server to communicate via TCP/IP
      • Install and configure an SSH server
      • Analyze table statistics
      • FLUSH TABLES WITH READ LOCK option
      • lock-ddl-per-table option improvements
      • Take an incremental backup using page tracking
      • The xbcloud binary
      • Use the xbcloud binary with Swift
      • Use xbcloud Binary with Amazon S3
      • Xbcloud with an IAM instance profile
      • Use the xbcloud binary with MinIO
      • Use the xbcloud with Google Cloud Storage
      • Exponential backoff
      • Use the xbcloud binary with Microsoft Azure Cloud Storage
      • How-tos and recipes
      • Release notes index
      • Percona XtraBackup 8.0.31-24 (2023-02-07)
      • Percona XtraBackup 8.0.30-23 (2022-11-14)
      • Percona XtraBackup 8.0.29-22 (2022-07-19)
      • Percona XtraBackup 8.0.28-21 (2022-05-25)
      • Percona XtraBackup 8.0.28-20
      • Percona XtraBackup 8.0.27-19
      • Percona XtraBackup 8.0.26-18.0
      • Percona XtraBackup 8.0.25-17.0
      • Percona XtraBackup 8.0.23-16.0
      • Percona XtraBackup 8.0.22-15.0
      • Percona XtraBackup 8.0.14
      • Percona XtraBackup 8.0.13
      • Percona XtraBackup 8.0.12
      • Percona XtraBackup 8.0.11
      • Percona XtraBackup 8.0.10
      • Percona XtraBackup 8.0.9
      • Percona XtraBackup 8.0.8
      • Percona XtraBackup 8.0.7
      • Percona XtraBackup 8.0.6
      • Percona XtraBackup 8.0.5
      • Percona XtraBackup 8.0.4
      • Percona XtraBackup 8.0-3-rc1
      • Error Message: Found tables with row versions due to INSTANT ADD/DROP columns
      • The xtrabackup option reference
      • The xbcrypt binary
      • The xbstream binary
      • Frequently asked questions
      • Glossary
      • Index of files created by Percona XtraBackup
      • Trademark policy
      • Copyright and licensing information
      • Version checking

    • How does Smart memory estimation work
    • Exapmple of Smart memory estimation usage

    Smart memory estimation¶

    The Smart memory estimation is tech preview feature. Before using Smart memory estimation in production, we recommend that you test restoring production from physical backups in your environment, and also use the alternative backup method for redundancy.

    Percona XtraBackup 8.0.30-23 adds support for Smart memory estimation feature to compute the memory required to --prepare a backup.

    Percona XtraBackup performs the backup procedure in two steps:

    • Creates a backup

      To create a backup, Percona XtraBackup copies your InnoDB data files. While copying the files, Percona XtraBackup runs a background process that watches the InnoDB redo log, also called the transaction log, and copies changes from it.

    • Prepares a backup

      During the prepare phase, Percona XtraBackup performs crash recovery against the copied data files using the copied transaction log file. Percona XtraBackup reads all the redo log entries into memory, categorizes them by space id and page id, reads the relevant pages into memory, and checks the log sequence number (LSN) on the page and on the redo log record. If the LSN in the redo log is more recent than on the page, Percona Xtrabackup applies the redo log changes to the page.

      To prepare a backup, Percona Xtrabackup uses InnoDB Buffer Pool memory. Memory for 256 pages is reserved for loading the pages into the buffer pool, while the remaining memory is used for hashing/categorizing the redo log entries.

      The available memory is controlled by the --use-memory option. If the available memory on the buffer pool is insufficient, the work is performed in multiple batches. After the batch is processed, the memory is freed to release space for the next batch. This process greatly impacts performance as an InnoDB page holds data from multiple rows. If a change on a page happens in different batches, that page is fetched and evicted numerous times.

    How does Smart memory estimation work¶

    Starting with Percona XtraBackup 8.0.30-23, while copying redo log entries during the backup phase, Percona XtraBackup computes the memory required for prepare phase. Percona XtraBackup also takes into consideration the number of InnoDB pages to be fetched from the disk. Then Percona XtraBackup checks the server’s available free memory and uses that memory up to the limit specified in the --use-free-memory-pct option to run --prepare. The default value of --use-free-memory-pct option is 0. This means that the option is disabled by default. If, for example, you set the option to 50, 50% of the free memory is used to prepare a backup. The maximum allowed value is 100.

    Exapmple of Smart memory estimation usage¶

    Here is an example of how Smart memory estimation can improve the time spent on prepare.

    Let’s say we run 3 backups, using sysbench with 16, 32, and 64 tables containing 1M rows each. The --use-free-memory-pct option is set to 50. This means that 50% of the free memory is used to prepare a backup. The backup is run on an ec2 c4.8xlarge instance (36 vCPU’s / 60G memory / General Purpose SSD (gp2)).

    During each --backup the following sysbench is run:

    sysbench --db-driver=mysql --db-ps-mode=disable --mysql-user=sysbench --mysql-password=sysbench --table_size=1000000 --tables=${NUM_OF_TABLES} --threads=24 --time=0 --report-interval=1 /usr/share/sysbench/oltp_write_only.lua run

    The following table shows the backup details:

    Used memory (G) Size of Xtrabacup log (G) Size of backup (G)
    16 tables 3.375 0.7 4.7
    32 tables 8.625 2.6 11
    64 tables 18.5 5.6 22
    • Used memory (G) - the amount of memory required by Percona XtraBackup with --use-free-memory-pct=50

    • Size of Xtrabacup log (G) - the size of Percona XtraBackup log file (redo log entries copied during the backup)

    • Size of backup (G) - the size of the resulting backup folder

    Prepare executed without Smart Memory Estimation use the default of 128M for the buffer pool.

    The results are the following:

    Note

    The following results are based on tests in a specific environment. Your results may vary.

    Time to run --prepare

    • 16 tables result - prepare time dropped to ~5.7% of the original time. An improvement in recovery time of about 17X.

    • 32 tables result - prepare time dropped to ~8,2% of the original time. An improvement in recovery time of about 12x.

    • 64 tables result - prepare time dropped to ~9.9% of the original time. An improvement in recovery time of about 10x.

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: 2022-11-14
    Percona LLC and/or its affiliates, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better.