Backup is locking InnoDB tables

  • Filter
  • Time
  • Show
Clear All
new posts

  • Backup is locking InnoDB tables


    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.
    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.