Announcement

Announcement Module
Collapse
No announcement yet.

Backup is locking InnoDB tables

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Backup is locking InnoDB tables

    Hi,

    I've set up a bacula to get backup of a master-master percona cluster, which works at (mostly) unused time of the DB by running a script. I'm using unused master node for it because It'd be a read-only job.
    Backup is OK. Yet I'm getting lock errors with few cron sql updates meantime on the InnoDB tables:

    SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction (uncaught exception) at ...

    I've tried "--no-lock" (which later I saw is for MyISAM) and even "--safe-slave-backup" with no luck. Somehow it is locking.

    Backup script is originated from https://github.com/vitobotta/admin-s.../xtrabackup.sh and getting backups is done by (at my instance):

    innobackupex --no-lock --galera-info --user="$user" --password="$pass" (--incremental --incremental-basedir= if incr.) /dir/to/backup

    Here is a part from backup log:

    innobackupex: Starting ibbackup with command: xtrabackup_55 --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/root/mysql-backups/incr/2014-07-07_04-05-23 --tmpdir=/tmp --incremental-basedir='/root/mysql-backups/full/2014-07-06_09-08-57'
    innobackupex: Waiting for ibbackup (pid=25079) to suspend
    innobackupex: Suspend file '/root/mysql-backups/incr/2014-07-07_04-05-23/xtrabackup_suspended_2'
    xtrabackup_55 version 2.1.6 for Percona Server 5.5.31 Linux (x86_64) (revision id: 702)
    incremental backup from 87217402617 is enabled.
    xtrabackup: uses posix_fadvise().
    xtrabackup: cd to /var/lib/mysql
    xtrabackup: using the following InnoDB configuration:
    xtrabackup: innodb_data_home_dir = /var/lib/mysql
    xtrabackup: innodb_data_file_path = ibdata1:1000M:autoextend
    xtrabackup: innodb_log_group_home_dir = /var/lib/mysql
    xtrabackup: innodb_log_files_in_group = 2
    xtrabackup: innodb_log_file_size = 10737418240
    >> log scanned up to (87687025914)
    140707 4:05:24 InnoDB: Warning: allocated tablespace 1965, old maximum was 9
    >> log scanned up to (87687031390)
    xtrabackup: using the full scan for incremental backup
    [01] Copying /var/lib/mysql/ibdata1 to /root/mysql-backups/incr/2014-07-07_04-05-23/ibdata1.delta
    >> log scanned up to (87687032998)
    >> log scanned up to ....

    I'd appreciate if you can point me to the right direction..

    //edit: tried with --lock-wait-threshold=5 --lock-wait-query-type=all --lock-wait-timeout=60 option, no change. Like the locking process is the backup itself..
    Last edited by seqizz; 07-08-2014, 03:45 AM.

  • #2
    XtraBackup does a binary copy of the Innodb files and doesn't hold any innodb row locks during the backup process. You could try the following to get more information about the lock waits:

    1) Check innodb_lock_wait_timeout setting:

    mysql> show global variables like 'innodb_lock_wait_timeout';

    2) Run the command in parallel with the innobackupex and the cron jobs.
    Code:
    while true; do (date; echo "select l.requesting_trx_id, r.trx_mysql_thread_id, r.trx_query, l.blocking_trx_id, b.trx_mysql_thread_id, b.trx_query FROM information_schema.INNODB_LOCK_WAITS l INNER JOIN information_schema.INNODB_TRX r on l.requesting_trx_id=r.trx_id INNER JOIN information_schema.INNODB_TRX b on l.blocking_trx_id=b.trx_id \G" | mysql ) | tee -a trx_locks.out; sleep 10; done
    It would show each lock wait with the requesting trx's id, query, and the blocking trx's id, query. It would then become clear which query is blocking others.

    Comment

    Working...
    X