There are a lot of questions (1,2,3) about speed up MySql restoring. But all of them speed up data import. Is there a way to speed up schema tables creation? For example dump iundepended table in parallel and restoring them in parallel too.

Btw, SET FOREIGN_KEY_CHECKS=0 is not a solution, becase standart mysql dump creates dump file with that statement.

  • Which version you are using in MySQL 5.7 you have mysqlpump and you can have multiple threads of dump to speed that up. – Nawaz Sohail Mar 18 '16 at 8:17
  • I use MySql 5.6 – Cherry Mar 21 '16 at 11:45

File creation is the main part of the sluggishness, especially on Windows. So, decreasing the number of files is key to speeding up your process.

Each MyISAM table needs 3 files. Each InnoDB table needs 1 or 2 files. Switch to InnoDB if not already using it.

Each PARTITION is a "table", so it counts as above. More than about 50 partitions is inefficient, and this is one of the reasons.

Having more than a thousand tables is usually (not always) a sign of a poorly designed schema.

VIEWs, TRIGGERs, Stored routines, etc each take at least one file.

Parallel loading may or may not help; it depends on whether the OS can create multiple files in parallel.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.