maria-discuss team mailing list archive

[mariadb@xxxxxxxxxxxxxx]

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