Not the answer you need?
Register and ask your own question!

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

viniciusmrviniciusmr EntrantInactive User Role Beginner
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?



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.

Comments

  • viniciusmrviniciusmr Entrant Inactive User Role Beginner
    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&gt; which has the title: And is supposed to contain:



    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.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.