The Best Alternative MySQL Database Engines For Magento – Percona, Mariadb

You can always move out of MySQL. At least, there are 2 alternatives: MariaDB and Percona. MariaDB is a fork of the MySQL developed by the community under the GNU GPL from the original creators of MySQL. The intents of MariaDB include maintaining high compatibility with MySQL, ensuring a “drop-in” replacement capability with library binary equivalency and matching with MySQL commands and APIs. Percona Server is another MySQL alternative which if free and open source. It offers high scalability, improved performance, useful features, and all the necessary instrumentations. In addition, Percona relies on self-tuning algorithms and supports high-performance hardware. In this post, we gathered all the information necessary to understand whether you should stay with  MySQL or move to alternative solutions, such as MariaDB and Percona.

The below information is based on Magento Stackexchange materials!

Talking about MySQL alternative, we should mention two different types of performance:  the page load time for a single user and the overall capacity. They are very different and not strictly related. There are two possibilities: to build a fast store with limited capacity; to make a slow site with lots of capacity. Each has its own bottlenecks.

Page load time for a single user

The key aspect here is latency, and only the cache is a hit. To minimize latency and to make page load time shorter, you should perform the following tasks:

• Tune the cache sizes of MySQL appropriately – the right answer depends on every single store.
• Don’t forget to reduce network latency. In case of 64 byte frames, you should set 4.096 µs for 1 Gbps, 5.12 µs for 100 Mbps, 51.2 µs for 10 Mbps. By transitioning from 100Mbps to 1Gbps you can get the improvement of 20%.
• Work with network capacity. Increase this parameter to a minimum of 100Mb/s, or move the DB server locally.
• Use SOLR. External engines often works better:  un-tuned SOLR can produce layered navigation faster than MySQL.
• Tune the app. Magento has some problems with building collections and caching them – some core code issues cripple performance. For instance, you can remove the product count from the layered navigation to get faster page load time.
• Work with MySQL slow logs. Don’t forget to check slow queries and add indexes if necessary.

In this case the application is a bottleneck. To improve the performance, first if all you should  change the core-code and make your template less heavy. Such actions will have a more dramatic effect on a page load time than any changes to MySQL configurations.

Don’t waste your time on

• Changing the storage engine, because both MariaDB and Percona have InnoDB engine, which performs exactly the same as a vanilla MySQL build.
• Running a MySQL slave, unless the slave is located closer or consists of better hardware.
• Running an external DB server, unless you have run out of hardware/resources or you’ve got multiple servers. For other cases, MySQL on the local machine is a better solution, because it reduces all the network overhead and latency. Even a 100Gb/s network isn’t better.

Overall capacity

MySQL can be a bottleneck. At the certain point it can slow things down. With Varnish and properly configured FPC, MySQL does become a bottleneck. To increase the performance of your Magento store, you can perform the following:

• Use alternative MySQL engine. XtraDB provides better performance under load and shows significant benefits over a stock MySQL solution.
• Update your MySQL. The newer version is always better.
• You can also change PHP MySQL driver. In many cases it provides better performance than native MySQL solution.
• Don’t forget to use better search engine. By moving the search to SOLR/Sphinx or some 3rd party service, you can alleviate the burden of non-transactional load.
• Replace native layered navigation engine. Remember, that SOLR is much faster than MySQL.
• Set up a MySQL slave. As a result, you will get a better browsing load, but keep in mind, that this solution will not help you with the processing of more orders per hour.

Don’t waste your time on

• Master/Master. Don’t use Master/Master in production – you will just get a lot of new problems – it simply isn’t worth it.

Read vs Write Scalability

Read scalability can be performed infinitely without extra complication with the addition of new slaves.The ratio of Reads to Writes in Magento is about 0.1%, so writes shouldn’t be much of a concern.

Hardware

Software changes and improvements are almost useless for the insufficient hardware. First of all you should verify, that you are not using

• Overly saturated network links
• Low-quality switches with limited buffers
• Geographically distant servers and poor network QoS/CoS
• Limited amount of RAM and low memory bandwidth RAM
• Software RAID controller
• Low IOPs HDD subsystem
• Low clock speed CPU and bandwidth chipset
• Almost all types of hardware virtualisation

Nowadays, there are some servers with 160 cores and 2TB of RAM. Similar cloud hardware is much less powerful. Remember, that there is always a massive scope for vertical scaling before any horizontal scaling.

The target is in a constant pursuit of performance

The bottleneck will always keep moving. All your improvements will lead to new problems. We have some examples for the stock Magento store:

• When you turn the caches on, add a backend cache or an application-level full page cache, use an alternative search/layered navigation engine, add additional front-end cache servers – PHP becomes the bottleneck.
• For server-level front-end cache and additional application servers, the bottleneck is MySQL.
• If you add a MySQL slave, front-end cache becomes the bottleneck.
• For the additional application servers, the bottleneck is SOLR/Sphinx.

Make only reasonable changes

Don’t add a MySQL slave just because someone says that it will provide better performance.  All the improvements should be reasonable. Otherwise, replication problems or synchronisation issues are inevitable.

Examples

300 orders per hour

To serve 300 orders per hour, it is recommended to use the following hardware:

1 Server

• 2x Intel E5-4620
• 64GB HDD: 4x 80k IOP/s SSDs
• Hardware RAID 10
• Magento EE
• MageStack

The load averages during the entire time remain under 3.00. For such conditions, you will feel the need to add a local MySQL slave only at that tipping point .

180 orders per hour

• 2x Intel E5-4620
• 64GB HDD: 4x 80k IOP/s SSDs
• Hardware RAID 10
• Magento CE
• MageStack

Firewall Packets

Varnish Traffic

Nginx Traffic

MySQL Load

CPU Load

The distribution of load

This image tells that MySQL is not a burden, unless your store gets more than a few thousand orders per hour.

Summary

Performance changes should be implemented according to all every single case. Each Magento store has its unique features, so it is extremely necessary to analyse your current bottlenecks to make subtle changes, adjustments and improvements.

The other benefits of MySQL alternatives

Remember, that performance is not the only advantage. With MySQL alternatives you can also get:

• Better Toolkit (for instance, Percona includes pt-query-digest and xtrabackup)
• Higher release frequency
• Better support
• Unique features: Percona provides warm cache restarts with InnoDB pool preservation

Source: Magento Stackexchange (Ben Lessani – Sonassi)