SST : Xtrabackup with no-lock

  • Filter
  • Time
  • Show
Clear All
new posts

  • SST : Xtrabackup with no-lock


    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.

    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?

    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
    Last edited by jaigupta; 02-19-2014, 06:13 AM.

  • #2
    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?