How to optimize MySQL for best Magento 2 performance and Increase indexing speed?

Question

How to optimize MySQL for best Magento 2 performance and Increase indexing speed?

Answer 1

During the development of one of the B2B requirement I faced Magento 2 performance issue.

• The development includes creation of more than 6K shared catalogs , Companies and Customer group via API.
• It also includes creation of around 1K catalog via API.

As a result of this heavy data website performance got deteriorated.

During the analysis I found it's something can be improved from DB side.

I changed the below DB configuration and it results huge performance improvement.

I added these changes in my.cnf file . mysqld –help –verbose command helped me to get the file location.

• long_query_time = 1
• innodb_buffer_pool_size = 50% of RAM
• innodb_thread_concurrency = 2* no.of cpu + 2
• innodb_flush_log_at_trx_commit = 2
• thread_cache_size = 32
• query_cache_size = 64M (need to enable query_cache_type to set this parameter)
• query_cache_limit = 2M (need to enable query_cache_type to set this parameter)
• join_buffer_size = 8M
• tmp_table_size = 256M
• key_buffer = 32M
• innodb_autoextend_increment = 512

As a result of the above changes our website performance got improve, Most significant improvement I can see in Magento indexing.

Answer 2

There are two aspects one is the Mysql configuration.

Second aspect is how much data is being fetched from cache rather then by using Mysql queries.

1. Use varnish or litespeed server even avoid full page cache. Varnish is reverse proxy and will result in minimum PHP execution that in turn will mean in fewer Mysql queries.
2. Make sure that cache life is set to a higher values possible to avoid repeated cache creation.
3. Make sure that unnecessary cache Keys and identifiers do not result in extra cache copies.
4. Magento allows attributes to be in cache so use that feature.
5. You can modify the indexing batch size by editing the related XML this in turn will result in lower memory usage or faster execution. Higher batch size will result in faster execution but higher memory and vice versa.
6. Use of elastic search or flat product tables.
7. Check modules installed are optimized for Mysql queries.
8. Make sure that attribute creation and configuration is done properly to avoid unwanted Mysql load.
9. Keep customer group to minimum as they also result in extra indexing.