Magento is built to be flexible and feature rich, but its Achilles heel is performance. Magento uses multiple database queries, and extensive database logging for each website access. So, just by optimizing the database service, Magento page speed can be improved significantly.

Magento uses MySQL server by default, but MySQL is known to have several bottlenecks in parallel transactions, resource usage, etc. To avoid these performance issues, many websites now use high performance alternatives such as MariaDB and Percona.

Today we’ll see how a Percona XtaDB cluster can be used to improve Magento performance and uptime.

Database bottlenecks in Magento websites

Magento stores all its products, pages, orders, settings and addon information in its database. This information is stored in a segmeted way (known as EAV model), which requires Magento to query multiple database rows to get a single piece of information (like product details).

We maintain Magento websites of many online businesses that range from small cookie shops to large retail shops. In these websites, the following are the top causes we’ve noted for database performance issues.

1. Un-optimized database configuration – Depending on the number and complexity of database queries, several settings such as heap memory size, sort table size, etc. need to be adjusted. If not, queries will take longer to execute.
2. Un-optimized database tables – Data gets written and deleted from tables all the time. Over time, this can lead to empty spaces in database files, and makes the database size very large. It results in slower query execution.
3. Heavy Magento addons – Some addons such as “dynamic price listing” or “related products” execute several database queries for a single page. This increases the database load, and results in slow page load speed.
4. Un-optimized load distribution – On busy websites, there can be many simultaneous users on the website. If all visitors are served using a single database server, the queries will be put on a queue, leading to slow access speeds.

[ Periodic database and settings optimization will make your database super fast. Here’s how you can optimize MySQL. ]

How to make Magento database faster?

Every website visit leaves a record in the Magento database. Even a small business website issues thousands of database operations per day. So, it is natural that the database size changes, its memory requirements changes, and its processing overhead increases. If left un-attended, these factors can quickly add up to form a performance bottleneck.

To prevent the formation of a bottleneck, we use several methods:

1. Optimize the tables regularly to minimize database size. A large database quickly brings down the site performance. We audit database table size at least once a week, clean out logs and session tables that consume space. The tables are then optimized to compress data, and minimize file size.
2. Periodically audit the database for performance issues. We manually check the optimization levels of each table, check if the database settings (such as query_cache size) are adequate for the changed table sizes, and see if there are any anomalies in operations (such as slow queries, aborted queries, etc.). Any noted issues are fixed on the spot.
3. Identify and fix heavy site code that’s present in themes, plugins, etc. When we note a slow database query, we back-trace it to the Magento function that sent in the query. We then work with the site developers or our Magento developers to streamline the code.
4. Monitor the database for serious performance issues. We keep an eye on several critical database performance metrics (such as query_count, query_time, queue_wait, etc.) that gives us an idea about database health. If we detect an anomaly, we quickly find the root cause and fix the issue before it can affect website performance.
5. Change Magento server architecture as the site grows. The performance and uptime needs of a small site is different from a large one. As the site traffic increase, it might be necessary to split the database load into multiple servers. A great way to spread the load is to use database clusters. We’ve found Percona XtraDB cluster to be a great option for this purpose.

[ Database optimization is just a part of Magento optimization. Here’s a full list of things you can do to make your site blazing fast. ]

Using Percona XtraDB cluster for fast database access

As we discussed above, un-optimized database architecture is a major reason for slow speeds in high traffic Magento sites. If all site visitors in a busy website is served by a single database server, it’s just a matter of time before it responds slow to queries.

Load balancing is an effective solution to avoid a single server bottleneck. MySQL supports several replication methods, but are known to produce inconsistent data. Out of the many replication systems we’ve tried (like MySQL replication, Galera, NDB, etc.), we’ve found Percona’s XtraDB Cluster to be reliable and fast.

Magento website infrastructure using Percona XtraDB

To demonstrate, let me give you an example of a recent Magento infrastructure we implemented using Percona XtraDB cluster. Shown here is the website architecture.

Magento Percona XtraDB cluster load balancing

The Magento site was served using two web servers that sits behind an Nginx load balancer. The web servers connect to a Database Load Balancer implemented using HAProxy. This load balancer distributes the database queries to 3 XtraDB servers evenly. To make sure the site always remains online, a database fail-over was configured for the load balancer using a high availability router called “KeepAliveD”.

Let’s take a quick look at how the database services were setup:

Percona XtraDB Cluster (PXC)

We used a 3-node database cluster for this Magento website. This prevented the possibility that even if one node went out of sync, there’ll be 2 other nodes that can feed it the “latest” data. We configured many other features such as binary logging for easy fault recovery, auto-commit retires for fixing dead locks, etc.

With a PXC database system we saw a 40% improvement in page load times during peak hour site traffic.

Database load balancing using HAProxy

We setup HAProxy load balancer in “least connected” mode, so that, database queries would be sent to the least busy PXC node. This ensured that no single server was overloaded. Health checks were setup using “clustercheck” to exclude a server from the cluster if it was found to be out-of-sync. This prevented database corruption.

[ Click here to learn more about how Percona XtraDB Cluster load balancing can be implemented. ]

Load balancer high availability using KeepAliveD

The database load balancer posed the risk of a single point failure. If it went down, the whole cluster will be unreachable. To negate this, we configured a backup load balancer to take its place in case the main server went down. This was done using “Floating IPs”, that is, in case the main server went down, its IPs will be transferred to the backup load balancer, thereby avoiding a downtime.

[ Click here to know more about how to setup Percona high availability using KeepAliveD. ]

Wrapping up..

Business websites need an infrastructure that is cost effective, while capable of fast performance. In this solution we explained how we used Percona XtraDB to resolve database level performance bottlenecks in high traffic Magento sites. By choosing a clustering solution, we were able to deliver high availability and easily scalability along with improving website performance.

Bobcares helps business websites of all sizes achieve world-class performance and uptime, using tried and tested website architectures. If you’d like to know how to make your website more reliable, we’d be happy to talk to you.