- Add the innodb options to the
warden/environments/magento2.base.yml
docker-compose YML file. - Restart warden environment
warden env down && warden sync stop && warden env up -d && warden sync start
- Import the .sql file with
warden db import
. - Remove options & restart warden enviornment once import completed.
innodb_buffer_pool_size
: Will cache frequently read datainnodb_log_buffer_size
: Larger buffer reduces write I/O to Transaction Logsinnodb_log_file_size
: Larger log file reduces checkpointing and write I/Oinnodb_write_io_threads
: Service Write Operations to .ibd files. According to MySQL Documentation on Configuring the Number of Background InnoDB I/O Threads, each thread can handle up to 256 pending I/O requests. Default for MySQL is 4, 8 for Percona Server. Max is 64.innodb_flush_log_at_trx_commit
: Not worried about data lost prevention steps.
With a 32GB .sql file from a Magento 1.x instance, importing with defaults would take days (avg 100KiB/s transfer rate)
according to pv
however with the above in place the import time eta of pv
is now only 10 hours
with an avg 750KiB/s transfer rate)
- Reference: https://dba.stackexchange.com/questions/83125/mysql-any-way-to-import-a-huge-32-gb-sql-dump-faster
NOTE, Strict mode only set to OFF for other non performance reason.