Large MySQL import stuck at “Waiting for table flush”

Posted on

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:

set unique_checks=1;
set foreign_key_checks=1;

What is going on now? Must I wait?

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *