11

I was looking for the best practices and recommended configuration settings for configuring a MySQL server for hosting a Magento2 Application.

1
8

Below are some main Magento2 MySQL Recommended Configurations.

  1. The Magento application requires MySQL 5.6.x.
  2. Magento versions 2.1.2 and later are compatible with MySQL 5.7.x.
  3. Magento uses MySQL database triggers to improve database access during reindexing.
  4. If you expect to import large numbers of products into Magento, you can increase the value for max_allowed_packet that is larger than the default, 16MB.
  5. MariaDB and Percona are compatible with Magento because we support MySQL 5.6 APIs.

For more details refer this devdocs link - http://devdocs.magento.com/guides/v2.0/install-gde/prereq/mysql.html

Devdocs has listed all the recommended configuration.

1
  • may i know magento 2.3 default MySQL engine? @ Manthan Dave
    – zus
    Feb 6 '20 at 9:44
11

There is no ideal config for each project since it depends on from your configuration, count of your catalog, websites, etc..

Ideally, MySQL should be able to keep your magento database in RAM memory and don't down in swap.

One of the most important configuration for performance is innodb-buffer-pool-size. See details in MySQL documentation: https://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html

As an example, I have been using this config in my projects:

[mysqld]
## After edit config you have to remove log files and restart mysql server
## because after changing innodb-log-file-size you should recreate ib_logfiles
## Stop MySQL server
## rm /data/dir/ib_logfile*
## Run MySQL server

##Table open cache under MacOS and MySQL 5.6 should be 250. 
##Otherwise you will get error MySQL server has gone away
##table-open-cache                   = 250
table_open_cache                     = 4096

## Cache
table_definition_cache               = 4096
thread_cache_size                    = 256
query_cache_size                     = 64M
query_cache_type                     = 1

## Per-thread Buffers
sort_buffer_size                     = 16M

## Temp Tables
max_heap_table_size                  = 512M
tmp_table_size                       = 512M

## Networking
interactive_timeout                  = 3600
max_connections                      = 400
max_connect_errors                   = 1000000
max_allowed_packet                   = 512M
skip_name_resolve
skip_secure_auth
wait_timeout                         = 28800
## MyISAM
key_buffer_size                      = 256M
#myisam_recover                      = FORCE,BACKUP
myisam_sort_buffer_size              = 128M

## InnoDB
innodb_buffer_pool_size              = 2G
innodb_log_file_size                 = 256M
innodb_thread_concurrency            = 18
innodb_flush_log_at_trx_commit       = 2
##Fastest
##innodb_flush_log_at_trx_commit = 0

View and download latest version: https://gist.github.com/yvoronoy/a705387c1c995fb071f656bdb951c714

1
  • 2
    Just wanted to add, never blindly copy/paste such into your MySQL configs without evaluating & understanding what you are changing & why. Tools like tunning-primer.sh launchpad.net/mysql-tuning-primer/trunk/1.6-r1/+download/… & mysqltuner.pl mysqltuner.pl these scripts use MySQL runtime variables so having at least 24 hours at minimum before using either/or applying the suggestions.
    – B00MER
    Sep 8 '17 at 21:52
4

Web servers

  • Apache 2.2 or 2.4

  • In addition, the apache mod_rewrite module must be enabled.
    mod_rewrite enables the server to perform URL rewriting. For more
    information, see our Apache documentation.

  • nginx 1.8 (or latest mainline version)

Database

  • MySQL 5.6
  • MariaDB and Percona are compatible with Magento because we support MySQL 5.6 APIs.

PHP

  • 5.6.x

  • 5.5.x, where x is 22 or greater

  • 7.0.2, 7.0.6–7.0.x

There is a known PHP 7.0.5 issue that affects our code compiler; to avoid the issue, do not use PHP 7.0.5.

Not supported:

  • PHP 7.1
  • PHP 5.4

PHP documentation: CentOS, Ubuntu

Required PHP extensions:

  • bc-math

  • curl

  • gd, ImageMagick 6.3.7 (or later) or both
  • intl
  • mbstring
  • mcrypt
  • mhash
  • openssl
  • PDO/MySQL
  • SimpleXML
  • soap
  • xml
  • xsl
  • zip

PHP 7 only:

  • json

  • iconv

You can get more info from : Magento 2.0.x technology stack requirements

1
  • @S H Patel Thank you for your answer, but I was only looking for the MySQL ini configuration settings recommended for Magento2 Dec 21 '16 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.