Join the MemSQL Community Today
Get expert advice, develop skills, and connect with others.

Replication and Durability

Replication

MemSQL replicates data in the following situations.

Synchronous and Asynchronous Replication

High availability, Redundancy-2 mode

With high availability redundancy-2 replication, you can specify, using the CREATE DATABASE, RESTORE DATABASE, and ALTER DATABASE commands, whether replication from master to replica partitions is synchronous or asynchronous. By default, synchronous replication is used. Synchronous replication from the master partitions will complete on all replicas before the commit of the transaction is acknowledged to the client application.

Synchronous replication is usually no more than ten to twenty percent slower than asynchronous replication. However, we recommend using synchronous replication; it provides much stronger data consistency guarantees as compared to asynchronous replication.

If a database is using synchronous replication and its replica partition fails to respond within five seconds, the replica partition switches to asynchronous replication; this allows the replica partition to not block the current and future replication transactions. The replica partition does not resume failover until both of the following are true:

  • The replica partition becomes responsive.
  • The master partition becomes responsive, if it was previously unresponsive.

When the replica partition resumes failover, it switches to synchronous replication.

REPLICATE DATABASE

When you use the REPLICATE DATABASE command, you can only replicate asynchronously.

Durability

In-memory database updates you make using DDL and DML commands are also durable; they are saved to the log on disk. Following a server restart, MemSQL recovers the server’s database partitions by loading them from the log to memory.

Synchronous and Asynchronous Durability

When you use the CREATE DATABASE, RESTORE DATABASE, and REPLICATE DATABASE commands, you can specify whether to use synchronous or asynchronous durability. Synchronous updates to the log on disk will complete before the commit of the transaction is acknowledged to the client application.

If the disk containing the log runs out of space, the database update transaction rolls back; no in-memory update is made.

Info

Database durability cannot be disabled. The reference for the CREATE DATABASE, RESTORE DATABASE, and REPLICATE DATABASE commands explains whether synchronous or asynchronous durability is used if this durability setting is not specified.

Recovery

Following a server restart, MemSQL loads the server’s database partitions from disk to memory. During this process, the databases are in the recovery state.

Run SHOW DATABASES with the EXTENDED option to see which databases are recovering and to get an estimate for how long recovery will take.

You cannot connect to a database while it is recovering.

Using Synchronous Replication and Synchronous Durability Together

You can use a database with both synchronous replication and synchronous durability enabled. In this case, database updates that you make using DDL and DML commands will commit after the following steps have completed:

  1. The updates are made, in-memory, on the master partitions.
  2. The updates on the master partitions are replicated to the replica partitions.
  3. The updates on the master partitions are written to disk.
  4. The updates on the replica partitions are written to disk.

Step two does not block step three; both steps can run in parallel.

Using Asynchronous Replication with Synchronous Durability is not Allowed

You cannot use asynchronous replication with synchronous durability. With asynchronous replication, after a transaction commits, data is replicated from the master to the replica partitions, with a possible delay. A crash of the master partitions would cause the replica partitions to be promoted to masters, resulting in possible data loss due to asynchronous replication. If the transaction were to have synchronous durability, it would commit on the replicas only after the logs on the replicas were saved; yet if the masters failed, data could still be lost when failing over to the replicas. So taking the time to save the logs on the replicas has no expected benefit. Hence, it is not allowed.

Replication and Durability Configuration

Replication and Durability use logs and snapshots. You can configure engine variables to optimize the use of these logs and snapshots.

Replication and Durability Error Handling

Replication and Durability use logs and snapshots. This section lists errors that can occur when MemSQL processes the logs and the snapshots. It also discusses how MemSQL addresses the errors.