Skip to content

Instantly share code, notes, and snippets.

@molotovbliss

molotovbliss/README.md

Last active August 22, 2025 11:51
Show Gist options
  • Save molotovbliss/406a4927f44f8730a653206029c8411c to your computer and use it in GitHub Desktop.
Save molotovbliss/406a4927f44f8730a653206029c8411c to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. molotovbliss revised this gist Feb 11, 2020. 1 changed file with 6 additions and 7 deletions.
    13 changes: 6 additions & 7 deletions magento2.base.yml
    View file
    Original file line number Diff line number Diff line change
    @@ -10,10 +10,9 @@ db:
    - mysqld
    - --max_allowed_packet=1024M

    # Set better innodb settings suited for importing large SQL dumps
    - --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
    - --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
  2. molotovbliss created this gist Jan 14, 2020.
    24 changes: 24 additions & 0 deletions README.md
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,24 @@
    ## 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.
    19 changes: 19 additions & 0 deletions magento2.base.yml
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,19 @@
    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

    # Set better innodb settings suited for importing large SQL dumps
    - --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