Continuing in our We Host Magento Better! series, we take a quick look at how MySQL can be tuned and optimized to improve Magento 1 and 2 performance. Lots of hosts may set up MySQL properly at the start, but it’s the ability to monitor and adjust settings that can make a world of difference…

Caching and Pools

Caching is the ability to store and quickly reference data and indexes. In MySQL, especially with Magento, most of this data is stored in the InnoDB storage engine. In order for this type of caching to be fast, having all of it in memory is important.

MySQL uses an InnoDB “Buffer Pool” to store this data in memory. Having this buffer not only properly sized but evaluated over time is crucial to maximizing performance.

MySQL uses a number of caches

Beyond the Buffer Pool, other caching settings play an important role. The Query Cache, a contentious setting that many think should be disabled, actually benefits Magento quite a bit. Properly tuned and sized, it can make a world of difference in page rendering times of un-cached pages. Table caches, max allowed packets, join/sort buffers, log buffers, etc… all can be tweaked to improve MySQL speeds and performance.

Proper sizing is often missing

As you can imagine, there is a lot of config that goes into setting up MySQL properly. While many web hosts do get this mostly right at the start, most fail to continue to evaluate performance and requirements as the stores evolve and grow. We see this time and time again.

As stores mature and evolve, their data storage requirements grow as well. This can result in the data in MySQL exceeding the buffer pool, meaning portions of it are not in memory, and are accessed slowly. Packet sizes might grow and exceed the initial max setting. This can result in failed processes that can be difficult to troubleshoot.

Here at LexiConn, we not only set up MySQL to perform optimally when a client first hosts with us, but we have processes in place to continue to monitor and adjust settings as needed to maintain that level of performance. I believe this is one of the areas that sets us apart from other Magento web hosts.

…

It’s important to make sure your web host has a firm grasp on optimal MySQL settings, and has a pro-active plan to adjust these settings as your Magento store grows. This missing piece can often cripple a store in terms of time to add an item to the cart, view an un-cached page, or run intensive reports or recreate indexes. Our Magento clients can rest easy knowing that LexiConn has their back when it comes to properly managing MySQL for their online store.