Large MySQL import stuck at "Waiting for table flush"

Question

Two weeks ago (!) I started a large import (dump sql file is 540GB). Now the import is stuck at this state:

"show processlist" shows: Command = "Field List", State="Waiting for table flush".

Looking at the record count for a table I selected randomly, I see zero rows, whereas in the source DB there are millions of rows, so the import is clearly not done. The .ibd file for that table is tiny, whereas on the source it's several GB. For other tables this is not the case, i.e. the .ibd files are very large on the target DB.

The server shows heavy swap activity, with the load being around 5.

Following suggestions I got on this site I started the import with these commands:

set autocommit=0; set unique_checks=0; set foreign_key_checks=0;

...and ended the import with these commands:

commit; set unique_checks=1; set foreign_key_checks=1;

What is going on now? Must I wait?

Answer 1

Is this a managed service server or do you have full control over it and everything running against it? If it is a managed service server run by a 3rd party, it may be worth investigating if there is an errant cron job somewhere on their side misguidedly running ANALYZE TABLE on all the tables in the database, including the ones you are trying to load at the time. This is one of the most common causes of queries getting stuck in the "waiting for table flush" state, but it could also be a background backup task because that briefly has to flush table metadata when it begins.

Disclosure: I wrote the referenced article.

Answer 2

I have always found large mysqldumps to be problematic. The best tactic I know is to reduce the size of the import. aka split the database dump into many dumps that let you run a per table restore this will let you make tangible progress and reduce the size and time each import takes.

https://github.com/kedarvj/mysqldumpsplitter