MySQLstuck due to Xtrabackup FLUSH NO_WRITE_TO_BINLOG TABLES

MySQL & MariaDB Percona XtraBackup
, ,
1

Hello Percona Team,

We are taking backup using xtrabackup version 2.4.24 on Ubuntu Mysql 5.7. when we check the show process list; all select & update were stuck (hang) in the database. and we have found FLUSH NO_WRITE_TO_BINLOG TABLES hang all this queries because this flush command running since 8529 second and its executed from xtrabackup incremental backup.

Can you please help us how to avoid such issue ?

Below output from show processlist; and it is the most highest second running query.

6370132 | root | localhost | NULL | Query | 8529 | Waiting for table flush | FLUSH NO_WRITE_TO_BINLOG TABLES

2 Likes
2

Hello Pavan,

First, try to find out why the flush table is waiting for this long. Is it because of incoming replication events preventing the lock from succeeding. Please try using --safe-slave-backup to momentarily stop the replication slave thread, this may help the backup to succeed and you then don’t need to resort to using this option.
try to fix that.

you can also try options like
–ftwrl-wait-timeout=#
This option specifies time in seconds that innobackupex
should wait for queries that would block FTWRL before
running it. If there are still such queries when the
timeout expires, innobackupex terminates with an error.
Default is 0, in which case innobackupex does not wait
for queries to complete and starts FTWRL immediately.

There is another option that only works with Percona server 5.7 --lock-ddl
–lock-ddl Issue LOCK TABLES/LOCK INSTANCE FOR BACKUP if it is
supported by server at the beginning of the backup to
block all DDL operations.

2 Likes
3

Thanks @Rahul_Malik

I have captured show process list

during this event. and we have Master-Master Database Configuration (Not Native Mysql Replication , we are using Tungsten active-active Cluster and in that we do not have slave thread) and this issue happened on one of our master database.

1 Like
4

Apart from show processlist

SHOW ENGINE INNODB STATUS

It would help you to understand what FLUSH TABLES is waiting for.

There are another options in PXB which you can explore

–kill-long-query-type=name
This option specifies which types of queries should be
killed to unblock the global lock. Default is “SELECT”.
–kill-long-queries-timeout=#
This option specifies the number of seconds innobackupex
waits between starting FLUSH TABLES WITH READ LOCK and
killing those queries that block it. Default is 0
seconds, which means innobackupex will not attempt to
kill any queries.

2 Likes