Considering setting up a larger database, the Spurtcommerce team used a replication strategy in the ecommerce solution to ensure data was transferred to another server. This server is usually a secondary physical computer that imports data from the primary publisher. The configuration is a master-slave setup where the master database is the original storage engine and the slave is the recipient of the replicated data. The replication configuration assumes that you have set up two different database servers. This article describes the benefits of replication and how to set it up in a Spurtcommerce database environment.

Why do we use replication at Spurtcommerce?

You may be wondering if there is any benefit to creating complexity between your database servers. Replication requires a different database server and requires additional configuration. It usually requires more maintenance and man-hours to configure and monitor the replication service. However, there are several benefits to businesses and DBAs.

First, your application no longer relies on a single database server. If the master server is down, you can temporarily switch the connection to the replicated server to ensure stability during critical outages. This also applies if the network goes down or the server hardware damages the physical machine.

Second, performance has actually improved, although the complexity will lead most administrators to believe that there will be a performance hit. When distributing data between multiple servers, you can connect different applications to each server to improve performance. That's how data centers work - they connect users to the closest available server to reduce response times.

Most companies use transactional database tables, which means the storage engine of choice is InnoDB. With replication, commits are initially stored on the network instead of on disk as is the case with physical servers. Recording is done synchronously for the physical server on disk, so recording over the network improves performance significantly.

Replication is also a type of database backup for disaster recovery which is more efficient than storing data on disk. With replication, you can restore your master server with replicated data instead of going through a backup file.

The basic configuration is master-slave, where the master handles the writes and the slave server only reads the data in the mirror database. You can also set up a master-master solution, but this applies to more advanced enterprise platforms. With a master-master setup, you can create a load-balanced environment where the servers share the load among multiple transactions. The MySQL server has a load balancer between the application and the database, and the load balancer sends queries to the database that can handle each transaction with the best performance.

Even with the fastest networks, there are replication delays to consider when setting up your environment. Latency is not a big issue if you are only using the replicated database for backups or for services like reporting. There is usually a 24-hour lag between production data and reporting tools, so time lag is not a major issue. However, if the replication server is the basis for critical transactions used for production, latency must be carefully monitored to avoid data integrity issues.

Therefore, the Spurtcommerce team planned the perfect database load balancing in their open-source ecommerce solution to support three databases – MySQL, PostgreSQL, and SQL Server.