maria-discuss team mailing list archive
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`
2022-05-17 0:14:06 105636 [Note] InnoDB: Writing table metadata to
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`
2022-05-17 1:49:53 105636 [Note] InnoDB: Writing table metadata to
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
---> 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?