Percona XtraBackup is an open source hot backup utility for MySQL-based servers. To take consistent and hot backup, it uses various locking methods, especially for non-transactional storage engine tables. This blog post discusses the cause and possible solution for queries with Waiting for table flush state in processlist when taking backups using Percona XtraBackup. Only MySQL 5.7 version is affected by this, as per my tests.
Type of locks taken by Percona XtraBackup
Before discussing the main issue, let’s learn about the type of locks used by Percona XtraBackup to take consistent backups. Percona XtraBackup uses backup locks as a lightweight alternative to FLUSH TABLES WITH READ LOCK. This feature is available in Percona Server for MySQL 5.6+. MySQL 8.0 allows acquiring an instance-level backup lock via the LOCK INSTANCE FOR BACKUP statement.
Locking is only done for MyISAM storage engine tables and other non-InnoDB tables after Percona XtraBackup finishes backing up all InnoDB/XtraDB data and logs. With backup locks, Percona XtraBackup uses LOCK TABLES FOR BACKUP automatically to copy non-InnoDB data and avoid blocking DML queries that modify InnoDB tables.
So for Percona Server for MySQL 5.7 and Percona XtraDB Cluster 5.7, it uses the following locking command while performing MyISAM and other non-InnoDB tables:
1 2 | Executing LOCK TABLES FOR BACKUP... Starting to backup non-InnoDB tables and files |
For upstream MySQL 5.7 versions, it uses the following locking commands:
1 2 3 | Executing FLUSH NO_WRITE_TO_BINLOG TABLES... Executing FLUSH TABLES WITH READ LOCK... Starting to backup non-InnoDB tables and files |
MySQL 8.0 uses the LOCK INSTANCE FOR BACKUP command.
Queries with Waiting for table flush state in processlist output while XtraBackup is running, this issue is visible in upstream MySQL 5.7 versions only; Percona Server for MySQL/Percona XtraDB Cluster/MySQL 8.0 will not have this issue since they use a different locking mechanism.
Root cause
When MySQL has a long-running query for a table/s, running XtraBackup will run FLUSH NO_WRITE_TO_BINLOG TABLES and all types of new queries on a table where a long-running query is running will have Waiting for table flush state in processlist.
This will be resolved after the long-running query is finished, and the next backup will complete successfully.
Test case example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE `joinit` ( `i` int(11) NOT NULL AUTO_INCREMENT, `s` varchar(64) DEFAULT NULL, `t` time NOT NULL, `g` int(11) NOT NULL, PRIMARY KEY (`i`) ) ENGINE=innodb; Create table joinit_new like joinit; INSERT INTO joinit VALUES (NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 ))); INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; INSERT INTO joinit SELECT NULL, uuid(), time(now()), (FLOOR( 1 + RAND( ) *60 )) FROM joinit; 33554618 rows |
#Start sysbench load on the database:
1 | $ sysbench /usr/share/sysbench/oltp_read_write.lua --mysql_storage_engine=innodb --table-size=1000 --tables=5 --mysql-db=test --mysql-host=127.0.0.1 --mysql-user=msandbox --mysql-password=msandbox --threads=4 --time=0 --report-interval=1 --events=0 --db-driver=mysql run |
Session 1: Ran the following query:
1 | mysql > select distinct i from joinit for update; |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql > show processlist; +-----+----------+-----------+-------+---------+------+--------------+------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+----------+-----------+-------+---------+------+--------------+------------------------------------------+ | 23 | msandbox | localhost | test1 | Query | 6 | Sending data | select distinct i from joinit for update | | 76 | msandbox | localhost | NULL | Query | 0 | starting | show processlist | | 171 | msandbox | localhost | test1 | Sleep | 57 | | NULL | | 207 | msandbox | localhost | test | Sleep | 135 | | NULL | | | 243 | msandbox | localhost | test | Execute | 0 | starting | COMMIT | | 244 | msandbox | localhost | test | Execute | 0 | starting | COMMIT | | 245 | msandbox | localhost | test | Execute | 0 | starting | COMMIT | | 247 | msandbox | localhost | NULL | Sleep | 3 | | NULL | +-----+----------+-----------+-------+---------+------+--------------+------------------------------------------+ 9 rows in set (0.00 sec) |
#Start XtraBackup:
1 2 3 4 5 6 7 8 9 | xtrabackup --user=msandbox --password=msandbox --backup --target-dir=~/backup Copying ./mysql/help_relation.ibd to /home/lalit/backup/mysql/help_relation.ibd ...done >> log scanned up to (13605240561) Executing FLUSH NO_WRITE_TO_BINLOG TABLES... >> log scanned up to (13605923511) >> log scanned up to (13606547653) >> log scanned up to (13607307899) |
Session 2: while session1 long-query is running:
1 | mysql > insert into joinit_new (SELECT * from joinit); |
Session 3: while session1 long-query is running:
1 | mysql > select count(*) from test1.joinit; |
As we can see from the example, all new queries on joinit table are in the Waiting for table flush state. Queries on other tables are not affected.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | mysql [localhost] {msandbox} ((none)) > show processlist; +-----+----------+-----------+-------+---------+------+-------------------------+-----------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+----------+-----------+-------+---------+------+-------------------------+-----------------------------------------------+ | 23 | msandbox | localhost | test1 | Query | 22 | Sending data | select distinct i from joinit for update | | 76 | msandbox | localhost | NULL | Query | 0 | starting | show processlist | | 171 | msandbox | localhost | test1 | Query | 7 | Waiting for table flush | insert into joinit_new (SELECT * from joinit) | | 207 | msandbox | localhost | test | Query | 3 | Waiting for table flush | select count(*) from test1.joinit | | 242 | msandbox | localhost | test | Execute | 0 | starting | COMMIT | | | 245 | msandbox | localhost | test | Execute | 0 | closing tables | SELECT c FROM sbtest5 WHERE id=? | | 247 | msandbox | localhost | NULL | Query | 11 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES | +-----+----------+-----------+-------+---------+------+-------------------------+-----------------------------------------------+ 9 rows in set (0.00 sec) |
Once the long query is completed, we don’t see the Waiting for table flush state in processlist, and backup will continue.
1 2 3 4 5 | >> log scanned up to (13619342387) >> log scanned up to (13619630994) Executing FLUSH TABLES WITH READ LOCK... >> log scanned up to (13619733489) >> log scanned up to (13619737067) |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | mysql > show processlist; +-----+----------+-----------+-------+---------+------+------------------------------+-----------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+----------+-----------+-------+---------+------+------------------------------+-----------------------------------------------+ | 23 | msandbox | localhost | test1 | Sleep | 184 | | NULL | | 76 | msandbox | localhost | NULL | Query | 0 | starting | show processlist | | 171 | msandbox | localhost | test1 | Query | 169 | Sending data | insert into joinit_new (SELECT * from joinit) | | | 242 | msandbox | localhost | test | Execute | 131 | Waiting for global read lock | UPDATE sbtest3 SET k=k+1 WHERE id=? | | 243 | msandbox | localhost | test | Execute | 131 | Waiting for global read lock | UPDATE sbtest2 SET k=k+1 WHERE id=? | 247 | msandbox | localhost | NULL | Query | 131 | Waiting for global read lock | FLUSH TABLES WITH READ LOCK | +-----+----------+-----------+-------+---------+------+------------------------------+-----------------------------------------------+ 9 rows in set (0.00 sec) mysql > show processlist; +-----+----------+-----------+-------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+----------+-----------+-------+---------+------+----------+------------------+ | 23 | msandbox | localhost | test1 | Sleep | 256 | | NULL | | 76 | msandbox | localhost | NULL | Query | 0 | starting | show processlist | | 171 | msandbox | localhost | test1 | Sleep | 241 | | NULL | | 207 | msandbox | localhost | test | Sleep | 237 | | NULL | +-----+----------+-----------+-------+---------+------+----------+------------------+ 4 rows in set (0.00 sec) |
1 2 3 4 5 6 | Writing /home/lalit/backup/backup-my.cnf ...done Writing /home/lalit/backup/xtrabackup_info ...done xtrabackup: Transaction log of lsn (13596553991) to (13619761343) was copied. completed OK! |
Possible solutions
List of options that you can use with Percona XtraBackup:
- xtrabackup –ftwrl-wait-timeout (seconds) – how long to wait for a good moment. Default is 0, not to wait.
- xtrabackup –ftwrl-wait-query-type – which long queries should be finished before FLUSH TABLES WITH READ LOCK is run. Default is ALL.
- xtrabackup –ftwrl-wait-threshold (seconds) – how long the query should be running before we consider it long running and potential blocker of global lock.
- xtrabackup –kill-long-queries-timeout (seconds) – how much time we give for queries to complete after FLUSH TABLES WITH READ LOCK is issued before start to kill. Default if 0, not to kill.
- xtrabackup –kill-long-query-type – which queries should be killed once kill-long-queries-timeout has expired. The default is SELECT.
Note: Killing select queries is OK but for DML/DDL it could lead to data inconsistency) so better to retry backup later/during non-peak hours.
References:
How Percona XtraBackup works (8.0)
How Percona XtraBackup works (2.4)
Improved FLUSH TABLES WITH READ LOCK handling
