← Back to team overview

maria-discuss team mailing list archive

FLUSH TABLES ... FOR EXPORT SLOW

 

I've recently updated from 10.3.14 => 10.6.7, and am running into an issue where FLUSH TABLES ... FOR EXPORT is sometimes very slow on large tables. One particular table that it seems to happen on often is over 20G. It doesn't hang, per se; it will complete eventually, but can take an hour or more.

2022-05-17 0:14:06 105636 [Note] InnoDB: Sync to disk of `MyDB`.`t1` started. 2022-05-17 0:14:06 105636 [Note] InnoDB: Writing table metadata to './MyDB/t1.cfg'
2022-05-17  0:14:06 105636 [Note] InnoDB: Table `MyDB`.`t1` flushed to disk
2022-05-17 0:14:06 105636 [Note] InnoDB: Sync to disk of `MyDB`.`t2` started. 2022-05-17 1:49:53 105636 [Note] InnoDB: Writing table metadata to './MyDB/t2.cfg'
2022-05-17  1:49:53 105636 [Note] InnoDB: Table `MyDB`.`t2` flushed to disk

in the above the obfuscated table name t2 took about 1.5 hours to complete.

In the previous version (10.3.14), these were always nearly instantaneous, even for the largest tables. At the time I'm executing the FLUSH TABLES the slave is stopped on the DB and there are no other users logged in to the DB (other than myself).

I know from the docs that FLUSH TABLES has seen changes since my previous version, but I'm not clear as to what the process is waiting for (or how to avoid it). Shutting down the server first (I have innodb_fast_shutdown = 0) seems to prevent it; and the shutdown generally only takes 5-10 minutes or so. But I'd rather not shutdown if I don't have to (and I'm not 100% convinced that I won't someday see the issue in that scenario, as well).

I've also tried this sequence thinking it might help:

 ---> STOP SLAVE Wed May 18 10:30:13 2022
 ---> SLAVE STOPPED Wed May 18 10:30:13 2022
 ---> FLUSH LOCAL TABLES WITH READ LOCK Wed May 18 10:30:14 2022
 ---> UNLOCK TABLES Wed May 18 10:30:15 2022
---> SET GLOBAL innodb_purge_rseg_truncate_frequency = 1 Wed May 18 10:30:15 2022
 ---> SET GLOBAL innodb_max_purge_lag_wait = 0 Wed May 18 10:30:15 2022
 ---> FLUSH TABLES ... FOR EXPORT Wed May 18 10:30:15 2022

but the FLUSH TABLES FOR EXPORT still waits on that large table.

Is there a way to make this happen quicker without requiring the DB to be shutdown first? Or am I missing something else in the process?

Thanks,

Dan