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

SST : Xtrabackup with no-lock

jaiguptajaigupta ContributorCurrent User Role Beginner
Hello,

Our cluster has no MyISAM tables except mysql database.

Is it possible to use no-lock option with SST using Xtrabackup?

We have tried using following settings but it does not seems like old running queries are being killed.
[xtrabackup]
parallel=10
rebuild-threads=10
lock-wait-threshold=40
lock-wait-query-type=all
lock-wait-timeout=60
kill-long-queries-timeout=20
kill-long-query-type=all

Lock is a big problems as database was still locked even after one hour. We had to kill SST user process on mysql to release lock.

If no-lock is possible and safe (Given we don't make any changes to mysql database), how do we define it on config?
[xtrabackup]
no-lock=1

Related Logs (From second try)
===== NEW NODE =====
140219 2:58:28 [Note] WSREP: Shifting PRIMARY -> JOINER (TO: 1515455257)
140219 2:58:28 [Note] WSREP: Requesting state transfer: success, donor: 0
140219 4:23:01 [Note] WSREP: Created page /var/lib/mysql/gcache.page.000000 of size 134217728 bytes
140219 4:31:52 [Note] WSREP: Created page /var/lib/mysql/gcache.page.000001 of size 134217728 bytes
140219 4:46:34 [Note] WSREP: 0 (n3.x.com): State transfer to 1 (n5.x.com) complete.

===== DONOR =====
140219 2:58:28 [Note] WSREP: Shifting SYNCED -> DONOR/DESYNCED (TO: 1515455257)
140219 3:24:52 [Note] WSREP: Provider paused at 7d7e8fec-0097-11e3-ad50-fa7f7adf26af:1515465228
(Had to kill SST thread) 140219 4:01:15 [Note] WSREP: Provider resumed.
140219 4:46:34 [Note] WSREP: 0 (n3.x.com): State transfer to 1 (n5.x.com) complete.
140219 4:46:34 [Note] WSREP: Shifting DONOR/DESYNCED -> JOINED (TO: 1515881700)

I thought using Xtrabackup would lock tables only for short period but it seems using rsync will still prove to be lower downtime for donor even though it keeps donor down for complete process.

PS: Total data on node is ~150GB, all nodes connected with LAN

Comments

  • jaiguptajaigupta Contributor Current User Role Beginner
    Further looked into logs.

    ===== innobackupex =====
    140219 03:24:52 innobackupex: Starting to lock all tables.
    140219 04:46:31 innobackupex: Finished backing up non-InnoDB tables and files
    140219 04:46:31 innobackupex: Waiting for log copying to finish

    It seems that innobackupex took 1 hour and 22 minutes to copy only system tables (Non innodb).
    It seems that most time was spent scanning logs.
    Any way to reduce lock time to couple of minutes?
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.