Mimimize or Eliminate Slave Lag

Achieve data consistency and eliminate slave lag while maintaining 100% compatibility with Oracle MySQL. Easy migration path: Zero application changes required from Oracle MySQL to SchoonerSQL.

Multi-threaded Parallel Synchronous Replication = No Data Loss or Slave Lag

Redundancy is essential for high availability, so MySQL is usually run in a Master / Slave configuration (often with multiple Slaves). Writes (updates) and reads are done on the Master server, and any updates are then also performed on the Slave server(s). The Slave servers can handle reads, diverting workloads from the Master, though in Oracle MySQL deployments the data on the Slaves can lag behind the Master.

In Oracle MySQL the Master moves on to the next update without confirmation that the Slave(s) have also completed that update. As a result traditional MySQL replication can lose transactions, can present stale or inconsistent data, and can suffer from "slave lag" which requires the throughput on the Master to be throttled back to limit the damage in case of a server failure.

SchoonerSQL solves these problems and guarantees transactional consistency across a Master and its Slave(s) with no data loss, no slave lag, and no stale data. SchoonerSQL achieves this with its unique deep integration of synchronous replication with InnoDB. Before a commit is acknowledged to the client, the changes made on the Master are communicated to all synchronously connected Slaves with all-or-nothing and total order semantics. Synchronous replication is done using a push mechanism that leverages multiple parallel threads to apply the replication events on the Slaves at a high speed.

SchoonerSQL ensures that all Slaves in the synchronous cluster are perfectly consistent with the Master at all times. Thus Slave instances can service reads and provide the most up-to-date consistent data, while leveraging InnoDB's high concurrency multi-versioning support (writes do not block reads).

Schooner's synchronous replication provides several important advantages:

• Zero data loss
• Guaranteed consistency of data across all members of the cluster
• Zero Slave lag

The parallel application of updates in SchoonerSQL via synchronous replication leads 3x-4x increases in throughput compared to Oracle MySQL Enterprise 5.5 with semi-synchronous replication, using identical hardware.

Parallel Slave Log Applier Threads

Slave lag in stock MySQL replication environments is greatly exacerbated through the use of single-threaded slave log appliers. This serialization of updates at each slave can lead to large inconsistencies among all members of the asynchronous cluster. Semi-synchronous and per-database slave log appliers reduce the problem somewhat but do not eliminate it.
SchoonerSQL™ asynchronous slaves employ multiple, parallel log applier threads and therefore maintain a much more consistent database state. Slave lag is minimized.

Master-Master

Common methods for supporting master-master (single writer) MySQL deployments are:

• MySQL instances, each configured as both asynchronous master and slave to one another.
• MySQL master instances sharing storage via DRBD.

Each of these methods has issues in terms of maintenance, performance and reliability:

• Recovery of a MySQL master may require manual reconfiguration of its asynchronous slave.
• MySQL asynchronous replication is single-threaded. Large slave lags are not uncommon.
• DRBD is a low-level protocol that has no understanding of MySQL traffic. Performance can be very poor and recovery very slow.

SchoonerSQL™ synchronous replication groups provide a simple, high-performance and robust method for deploying a 2-node master-master topology:

• Configuration of a synchronous replication group is as simple as creating a SchoonerSQL™ instance and adding it to a synchronous replication group. All initialization and data transfer is handled automatically by the SchoonerSQL™ Administrator.
• SchoonerSQL™ automatically handles failover of instances within a synchronous replication group.
• SchoonerSQL™ automatically handles migration of instances within a synchronous replication group.

SchoonerSQL™ asynchronous slaves support parallel log applier threads which greatly increases the throughput of slaves and greatly reduces or eliminates slave lag.

Consistent Read Scale Out

Certain applications may depend on consistent read data that cannot be supported by asynchronous replication methods due to slave lag. SchoonerSQL™ replication groups provide an effective method for consistent read scale out.