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

Thank you very much for these @molotovbliss

I run warden version 0.11.0 and here is how I incorporated your suggestions

Within my project, you can create the following

.warden
└── warden-env.yml

Within the warden-env.yml file you can extend warden per project

Within this file, I added

version: "3.5"
services:
  db:
    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

Looking at the db container for my project through portainer you can see the additional flags

Once again, thank you very much for your suggestions

Thank you very much for these @molotovbliss

I run warden version 0.11.0 and here is how I incorporated your suggestions

Within my project, you can create the following

.warden
└── warden-env.yml

Within the warden-env.yml file you can extend warden per project

Within this file, I added

version: "3.5"
services:
  db:
    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

Looking at the db container for my project through portainer you can see the additional flags

Once again, thank you very much for your suggestions

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 using mysql-tuner, mysql-primer, blogs, etc.

Anyhow, thanks a lot for the feedback. It's a humbling that it helped you and possibly others.

Cheers!

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

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

Cheers, glad it helped!