1

I have a MySQL 5.5 database with thousands of tables and heavy write workload.

I need to use xtrabackup to take online backup (create a slave from production server with minimum downtime), but the problem is that final stage of backup (when it issues FLUSH TABLES WITH READ LOCK) takes a very long time to complete. I suspect that this is because it copies thousands of frm table definition files. Can I speed up this process by any means? For example if I know for sure that table structure isn't changing?

Also I saw that innobackupex has an option called --rsync which supposedly should help, but innobackupex is deprecated now, and xtrabackup is missing this option for some reason.

Any help to reduce period during which FLUSH TABLES WITH READ LOCK is effective will be appreciated.

Deinlandel
  • 121
  • 6

3 Answers3

1

There is a --no-lock option, however you can only use it if you don't have writable non-InnoDB tables and if you don't care about binlog position.

If you can't use --no-lock I would suggest to setup a replica and take backups from it.

BTW, --rsync wouldn't help here anyway.

akuzminsky
  • 738
  • 4
  • 9
1

Answering for posterity. Innobackupex with --rsync did the trick. It reduced lock time to seconds.

Deinlandel
  • 121
  • 6
1

Use the --rsync parameter.

Surprisingly, it IS available on xtrabackup, despite it not appearing anywhere on the Percona XtraBackup 2.4 Documentation as of this writting. The documentation that is wrong. =/

If you pass the parameter to the xtrabackup command, it works just like it would with innobackupex. And it make sense that it does, since innobackupex is just a 'caller' symlink now.

I saw in your self-answer that you 'fell-back' to using innobackupex. I would recommend against it, since, according to documentation:

From Percona XtraBackup version 2.3 innobackupex is has been rewritten in C and set up as a symlink to the xtrabackup. innobackupex supports all features and syntax as 2.2 version did, but it is now deprecated and will be removed in next major release. Syntax for new features will not be added to the innobackupex, only to the xtrabackup.

It is already outdated and lacking new functionalities that are available in xtrabackup.
E.g. "--databases-exclude"

In my scenario, backups used to waste 6 minutes in LOCK TABLES:

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

And with --rsync, it became less than a second.

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-
(...)
180824 13:07:28 Executing UNLOCK TABLES
180824 13:07:28 All tables unlocked

I've had the exact same issue you had, and found your question & answer, which together with the lack of a consistent documentation, made me believe it wasn't available and I ended up wasting a day looking for alternative solutions, since innobackupex is outdated and doesn't have some functions I need, like the aforementioned --databases-exclude.

Posting this answer so that if someone else find themselves with this same exact issue, they'll know they can use the parameter even though it's not on the docs.

Vinícius M
  • 153
  • 7
  • I've made a post on Percona Forums warning them about it: https://www.percona.com/forums/questions-discussions/percona-xtrabackup/52201-innobackupex-deprecated-but-xtrabackup-lacks-rsync-option-long-table-locks?p=52218#post52218 – Vinícius M Aug 27 '18 at 12:45