Magento MySQL Configuration
If you run a Magento store you already know that in order to provide the best experience you will need a lot of resources and your database should be the top resource user especially if you have many products.
If you got here then you probably need something tangible and something that you can use to really boost your magento store’s database engine.
After you hve configured your mysql you can install mysql tuner (apt-get mysqltuner) and run it after a week usage to check how your magento store is doing and where you need to adjust your mysql configuration.
Because Magento uses InnoDB , the innodb_* settings are the most important in your mysql config (mysql 5.7.29).
The following example is for a server that has 32gb RAM :
innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_instances = 19
innodb_file_per_table = 1
innodb_log_files_in_group = 2
innodb_log_buffer_size = 32M
innodb_lock_wait_timeout = 7200
innodb_buffer_pool_size = 19G
innodb_thread_concurrency = 0
innodb_io_capacity=3000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_open_files = 600
innodb_autoinc_lock_mode=2
innodb_strict_mode = off
innodb_flush_method = O_DIRECT
innodb_thread_sleep_delay = 10000
You can adjust the configuration based on your memory size yourself and only edit a few config lines such as the buffer poole size.
innodb_buffer_pool_size = 60% of Total RAM ( ie 10G)
innodb_buffer_pool_instances = 10 (each pool instance needs to be 1G)
Other mysql settings:
query_cache_type = 0
query_cache_size = 0
skip-name-resolve
join_buffer_size = 24M
max_allowed_packet = 1G
wait_timeout=28800
Generate the settings based on your server’s resources and edit your mysql config usually located at /etc/mysql/my.cnf
Careful when using skip-name-resolve, if you have external scripts that use your magento mysql database use the real ip address when connecting.
To further boost your magento store, if you are on Magento 2 you can install ElasticSearch and enable it in your magento admin panel, make sure you enter a database prefix and test to see if you can connect.
Magento does not support ElasticSearch 7 so make sure you install either 5.x or 6.x from a ppa.
Save & Clear Cache !
