- 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.
Np @WinstonN!
Also, don't forget to not use such for development as I believe it will do more harm than good as it's mostly meant for large db importing to not slow down pulling down latest DBs from other environments as well.
My particular use case was for a M1 to M2 migration. And found importing causing delays.
...and to reiterate: I wouldn't advise on keeping such in the
.yml
(perhaps a copy/pasta when needing to import, and back to default after). Understanding the data your working with helps greatly in the DB world! But blindly applying some solutions can do more harm than good. aka usingmysql-tuner
,mysql-primer
, blogs, etc.Anyhow, thanks a lot for the feedback. It's a humbling that it helped you and possibly others.
Cheers!