Sam
Sam

Reputation: 552

What's the fastest way to import a 1TB .sql file? Too slow currently

My DB got corrupt with errors like,

InnoDB log sequence number is in the future

and so went to a backup copy that I had and exported an SQL file that needs to be re-imported.

The .sql file is close to 800GB and the actual Db was around 1.3TB in size.

Now I've started the import but it's moving at a ridiculously slow speed of just 10-12GB/hour. My site will be down for 6 days before I can fully import the data and hoping for a faster way. Any other suggestions on what I could do to speed things up?

I have a 64GB RAM Centos Machine and have set innodb_buffer_pool_size to 45GB, so that's been taken care of. To note, the source .sql file is on a SATA drive and the MySQL is on SSD. I don't have enough space on the SSD to have the .sql also be on the same drive.

Possible solutions that I haven't tried because wondering whether worth stopping the import and trying it again:

  1. I haven't yet tried the option of putting SET FOREIGN_KEY_CHECKS=0, SET autocommit=0, and SET unique_checks=0
  2. Wondering if it's worth considering splitting the .sql files into multiple smaller files and running them in parallel, will such a thing work? Not sure how to split such a large file safely and is there anything that could go wrong in doing this?

Upvotes: 3

Views: 2659

Answers (0)

Related Questions