This is the second blog of "Staying 2 Steps Ahead of the Performance of Your Magento Site," a series of five entries from Hostway partner Cynch, an expert on optimization and a leading migration and IT management agency.
The most widely used service configuration for supporting a Magento e-commerce website uses Apache as the web server and MySQL as the relational database. Nearly all of the content and configuration of Magento is stored in the relational database, creating a huge dependency on MySQL. So before you deploy your Magento site to your shiny new server, make sure that careful consideration has been paid to the MySQL configuration, either by your hosting company, your systems administrator or your developer – ideally, all three.
Magento’s database schema configuration is preset to use the InnoDB engine on its tables. This is important because nearly all default MySQL database installations – especially those that are “out of the box” from the operating system vendor – aren’t set up to allocate appropriate system resources to a large number of InnoDB tables. We recommend you force your technical team to spend major time reading the "InnoDB Performance Tuning Tips" portion of the MySQL online documentation, but for those with a short attention span, Cynch recommends you focus on these areas:
- innodb_buffer_pool_size = Simply put, this is how much memory MySQL will use to store InnoDB data in memory. More stuff in memory reduces the times MySQL needs to go to disk.
- innodb_thread_concurrency = This regulates the number of threads working concurrently inside of MySQL for InnoDB transactions. We recommend using the formula of 2 * (CPUCount + DiskCount).
- innodb_file_per_table = This tells MySQL to store tablespaces in individual files per table rather than in a single file. While this likely won’t increase normal day-to-day operations, we’ve found that it helps speed up recovery from a crashed server or MySQL service.
Tuning your MySQL service to closely match your server specifications and the size of your Magento database is worth the time – you’ll thank yourself later with faster page load times and better server performance.
If you enjoyed this post, then make sure to check out the next installment in our Magento optimization series, “Going Dedicated with Your Magento Installation.”