Recently I needed to import very huge SQL dumps to my local machine. There were 10 different .sql files dumped from Moodle and each of them were sized more than 10GB with approximately 5 million rows. First of all, I tried to import them in a traditional way by using:
mysql -u username -p some_db < database1.sql
However each import request took a very long time, approximately 6-7 hours for each .SQL file. Please also you consider that I conducted this operation in a brand new laptop with Intel i7 CPU, 16GB RAM and 512GB SSD disk. Since I didn't have 60 hours for importing all these dump files, I took a look for potential solutions and found a very effective one.
InnoDB settings from
my.ini, MySQL configuration file. New settings were clearly effective on the import time of dump files. After tuning MySQL settings, each import request took approximately 20-30 minutes, which was slightly satisfying. First please take a look to the InnoDB representation shown below:
Open up your
my.ini file with your favorite text editor and edit the InnoDB settings as follows:
innodb_buffer_pool_size = 12G # 60% - 70% of your RAM size
innodb_log_buffer_size = 16M # 16M or 32M is fine
innodb_log_file_size = 3G # 25% of buffer pool size
innodb_write_io_threads = 32 # 32 is fine, 64 is maximum
innodb_flush_log_at_trx_commit = 0 # see image for details
That's all. Save your new settings, and restart MySQL:
$ sudo service mysql restart
Now you will be importing MySQL databases lightning fast.