Database Load Balancing

The most vital for smooth eCommerce operations

The team has ensured flexibility, redundancy, scalability, data integrity, 99.9% uptime and data consistency, 0.01% downtime, faster trouble shooting and excellent control of workload flow.

Keeping in mind the larger database setups, in the eCommerce Solution, Spurtcommerce team has used the strategy of replication to ensure that data is passed to another server. This server is usually a secondary physical machine that imports data from the main publisher. The configuration is a "master-slave" setup where the master database is the original storage machine and the slave is the recipient of the replicated data. Replication configurations assume that you have two different Database servers set up. This article explains the benefits of replication and how it has been set up in Spurtcommerce Database environment.

Why have we used Replication in Spurtcommerce?

You might first wonder if there are any benefits in creating complexity among your database servers. Replication requires another database server, and it requires additional configurations. It generally takes more maintenance and man hours to configure and monitor the replication service. However, there are several benefits for the business and database administrators.

First, your applications no longer rely on one database server. If the master server fails, you can temporarily switch the connection to the replicated server to provide stability during a critical outage. This includes if the network fails or server hardware crashes the physical machine.

Second, performance actually increases even though complexity would make most administrators think that it would cause performance degradation. When you spread data across multiple servers, you can connect different applications to each server to improve performance. This is generally the way data centers work – they connect a user to the closest available server to reduce response times.

Most businesses use transactional database tables, which means the preferred storage engine is InnoDB. With replication, commits are first written to the network instead of the hard drive like they do with one physical server. Writes are done synchronously for one physical server to the hard drive, so writing to the network greatly increases performance.

Replication is also a type of disaster recovery database backup that's more efficient than storing data to disks. With replication, you can restore your master server with replicated data instead of digging into backup files.

The basic configuration is master-slave where the master handles the write transactions and the slave server only reads the data into a mirrored database. You can also set up master-master solutions, but this is for more advanced enterprise platforms. With a master-master setup, you can create a load balanced environment where the servers share the load between multiple transactions. The MySQL servers have a load balancer between the application and the databases, and the load balancer sends requests to the database that can handle each transaction with the best performance.

Even with the fastest network, you have replication lag, and this should be considered when you set up your environment. Lag is not a large concern if you only use a replicated database for a backup or for services such as reporting. It's common to have a 24-hour lag between production data and reporting tools, so lag time isn't a main concern. However, if the replication server is a foundation for important transaction used for production, lag time should be closely monitored to avoid any data integrity issues.

This way, the Spurtcommerce team has planned the perfect database load balancing in their Open Source eCommerce Solution, to support three databases - MySQL, PostgresSQL and SQL Server.

Deep configuration

{
  replication: {
    master: {
      host: "server1",
      port: 3306,
      username: "test",
      password: "test",
      database: "test"
    },
    slaves: [{
      host: "server2",
      port: 3306,
      username: "test",
      password: "test",
      database: "test"
    }, {
      host: "server3",
      port: 3306,
      username: "test",
      password: "test",
      database: "test"
    }],

    /**
    * If true, PoolCluster will attempt to reconnect when connection fails. (Default: true)
    */
    canRetry: true,

    /**
     * If connection fails, node's errorCount increases.
     * When errorCount is greater than removeNodeErrorCount, remove a node in the PoolCluster. (Default: 5)
     */
    removeNodeErrorCount: 5,

    /**
     * If connection fails, specifies the number of milliseconds before another connection attempt will be made.
     * If set to 0, then node will be removed instead and never re-used. (Default: 0)
     */
     restoreNodeTimeout: 0,

    /**
     * Determines how slaves are selected:
     * RR: Select one alternately (Round-Robin).
     * RANDOM: Select the node by random function.
     * ORDER: Select the first node available unconditionally.
     */
    selector: "RR"
  }
}