I recently had to import some quite large SQL dumps to my local machine for data
analysis purposes. There were 10 different
.sql dumps in total, and each of
them were sized more than 100GB.
First of all, I tried to import each by using the
mysql some_database < some_dump.sql
Unsurprisingly, each task took very long to finish, approximately 6-7 hours per dump file, in a brand new laptop with i7 CPU, 16GB RAM and 1TB SSD. Since I didn’t have 60 hours for importing all, I had to take a look for solutions that can potentially speed up the process.
After grasping the reasons behind, I changed the
InnoDB settings (
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
That was it after restarting MySQL:
$ sudo service mysql restart
New settings were clearly effective on the import time of dump files. Each import task started to take around 20-30 minutes after fine tuning the settings.