Backup is locking InnoDB tables

MySQL & MariaDB Percona XtraBackup
1

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:

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…

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’;

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