Buy Percona ServicesBuy Now!

innobackupex deprecated but xtrabackup lacks --rsync option = long TABLE LOCKS

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • innobackupex deprecated but xtrabackup lacks --rsync option = long TABLE LOCKS

    Hello,

    I'm setting up a live MySQL backup procedure using xtrabackup.
    The server is ~99% InnoDB, but the final backup phase still executes "FLUSH TABLES WITH READ LOCK", and keeps that lock for approximately 6 minutes, while slowly copying all the .frm files one by one. During these whole 6 minutes, the backup barely grows a single MiB, maximum.
    However it's still a 6 minute server lock, every time.
    Regardless of it being a multiple gigabyte full backup, or a negligible 1MiB incremental backup taken 10 seconds later, where it takes 8 seconds to go through the non-locking, InnoDB phase.
    180824 14:52:53 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
    180824 14:52:53 Executing FLUSH TABLES WITH READ LOCK...
    (...)
    180824 14:58:55 Executing UNLOCK TABLES
    180824 14:58:55 All tables unlocked


    However, using <innobackupex (...) --rsync> the tables stay locked for less than a second. Literally.
    180824 13:07:28 Starting prep copy of non-InnoDB tables and files
    (...)
    180824 13:07:28 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
    180824 13:07:28 Executing FLUSH TABLES WITH READ LOCK...
    180824 13:07:28 Starting to backup non-InnoDB tables and files
    180824 13:07:28 Starting rsync as: rsync -t . --files-from=/tmp/xtrabackup_rsyncfiles_pass2 /home/2018-08-24_13-04-18/
    180824 13:07:28 rsync finished successfully.

    (...)
    180824 13:07:28 Executing UNLOCK TABLES
    180824 13:07:28 All tables unlocked

    There are no DDL, DCL or DTL queries being run at any times. Just DML and DQL queries.
    Pretty much just INSERTs, UPDATEs and SELECTs.
    And that's only on the already backed-up InnoDB databases.

    So, given that a 6-minute global lock is completely unreasonable for the server applications, what should be my most correct course of action?
    Should I set up this brand new procedure using innobackupex then? Even with the documentation somewhat explicitly warning against it?
    innobackupex innobackupex is the symlink for xtrabackup. innobackupex still supports all features and syntax as 2.2 version did, but is now deprecated and will be removed in next major release.

    [Taken from Percona XtraBackup 2.4 Documentation, Release 2.4.8, page 86. avaliable at: http://www.dba86.com/docs/mysql/xtrabackup.pdf ]

    Or should I do something else?
    Is there any plans to implement --rsync in xtrabackup?
    Is there any other way I could work around the long time to copy the frm files? Or some other suggestion?

    Thank you for your attention.


  • #2
    Hello again,

    Update: <--rsync> IS implemented on xtrabackup, despite it not appearing anywhere on the Percona XtraBackup 2.4 Documentation.

    If you pass the parameter to the xtrabackup command, it works just like innobackupex.

    That solves my problem and you can consider my main issue on this thread closed.

    However, I believe the documentation should be corrected to reflect that.
    For instance, the page <https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/xbk_option_reference.html> which has the title: "The xtrabackup Option Reference"

    And is supposed to contain:

    "This page documents all of the command-line options for the xtrabackup binary."
    [Emphasis added by me]

    And yet it does not have a single mention of rsync or of the <--rsync> parameter.

    Same goes for the PDF documentation.

    Thank you for your attention.

    Comment


    • #3
      Hi viniciusmr I apologize that I didn't get to this post more quickly...

      We appreciate your update as that may well help others who encounter the same challenge. Regarding the documentation, I will look into this and raise a request for the amendment. That will also save others the time in the future.

      Comment

      Working...
      X