Magento Perofrmance: Optimizing your MySQL server
This is the first of the series “Magento Performance”. In this post focus will be on tuning up MySQL server to make it optimal for Magento site. MySQL is most potential area for optimization. Optimizing its configuration can give good boost in performance.
There are several configurations which should be considered.
- Memory
Magento uses InnoDB storage engine which, uses memory pools to cache tables data, indexes to reduce disk I/O. Less disk I/O means faster access to data, indexes etc.The key is to assign as much memory as possible for in-memory cache. By default this value is 8MB which is very small. This value can be increased by setting “innodb_buffer_pool_size” variable in my.cnf. On normal servers running web and database server, with 1GB RAM the value should be set to 256MB or 512MB. If server is only running database server than this value should not be less than 512MB.
- Multi-threading
Innodb can use multiple cores of server processors to give concurrent connection. The key is to use this ability to boost concurrent connections. This can be achieved by configuring “innodb_thread_concurrency”, “thread_cache_size” variables properly.Magento recommends to calculate “innodb_thread_concurrency” value by equation “2 x [# of CPUs] + 2″. So on a server with 4 cores the value should be 10. The “thread_cache_size” allows to cache one client’s thread to be cached and used for another client after first is disconnected. This value can also be configured, but its value depends on “max_connections”.
This is complex setting so seek mysql expert opinion and benchmark performance for setting it. - Enable Query Cache
MySQL can cache results for queries and return it from cache, when identical query is done. For example queries running for home page will be almost identical for another home page view. Enabling the cache can save a lot of rework on MySQL end.The cache can be enabled by setting “query_cache_size” variable. At least this value should be set equivalent to 32MB to 64MB in bytes. Setting this value can be tricky as available resources very in each environment, so be sure to benchmark and test before finalizing.
- Enable Sort Buffer
Sort buffer is used to optimize sorting in ORDER BY and GROUP BY clause queries. Make sure that this buffer is enabled. Recommended value is 2MB to 6MB. This will speed up queries involving sort and GROUP BY.
More on Magento Performance
Disclaimer: Above recommendations are extracted based on “Magento Enterprise Edition Whitepaper” and my prior knowledge so no liabilities are accepted. Be sure the benchmark and adjust values according to your environment.