Speed up warden for faster importing of large sql dumps.

README.md

Speed up warden for faster importing of large sql dumps.

1. Add the innodb options to the warden/environments/magento2.base.yml docker-compose YML file.
2. Restart warden environment warden env down && warden sync stop && warden env up -d && warden sync start
3. Import the .sql file with warden db import.
4. Remove options & restart warden enviornment once import completed.

Why these settings

• innodb_buffer_pool_size : Will cache frequently read data
• innodb_log_buffer_size : Larger buffer reduces write I/O to Transaction Logs
• innodb_log_file_size : Larger log file reduces checkpointing and write I/O
• innodb_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.

Testing of performance:

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.

magento2.base.yml

db:
    hostname: mariadb
    image: mariadb:${MARIADB_VERSION:-10.3}
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD:-magento}
      - MYSQL_DATABASE=${MYSQL_DATABASE:-magento}
      - MYSQL_USER=${MYSQL_USER:-magento}
      - MYSQL_PASSWORD=${MYSQL_PASSWORD:-magento}
    command:
      - mysqld
      - --max_allowed_packet=1024M

          - --innodb_log_buffer_size=256M
          - --innodb_buffer_pool_size=4G
          - --innodb_log_file_size=1G
          - --innodb_write_io_threads=16
          - --innodb_flush_log_at_trx_commit=0
          - --innodb_strict_mode=OFF

@molotovbliss thanks dude, saved me a lot of time !

Cheers, glad it helped!