Distributed Transactions

Overview

MariaDB SkySQL includes the Distributed Transactions topology for transactional (OLTP) workloads.

Distributed Transactions is backed by the Xpand topology.

Component View

Distributed Transactions

In this service:

  • MariaDB Xpand provides distributed SQL with high availability (HA) and fault tolerance.

  • MariaDB MaxScale provides load balancing, HA, and automatic failover.

Service Details

Distributed Transactions delivers the following features:

Storage Engine

Xpand

Number of Xpand Instances (Foundation Tier)

1, 3, 6

Number of Xpand Instances (Power Tier)

1, 3, 6, 9, 12, 15, 18, 21

Number of MaxScale Instances

1 to 5

Data Distribution

Xpand

Load Balancing

Scale-out

Failover

Automated healing of Xpand

Production-Ready

Yes

Development/UAT-Ready

Yes

For additional information on Xpand instance options, see "Distributed Transactions Instance Sizes".

For additional information on MaxScale instance options, see "MaxScale Redundancy".

Design Features

The following design features and behavior apply to Distributed Transactions services with 3 or more Xpand nodes.

Distributed Transactions

Distributed Transactions supports modern massive-workload web applications which require strong consistency and data integrity. This service leverages the distributed SQL capabilities of MariaDB Xpand.

A distributed SQL database consists of multiple database nodes working together, with each node storing and querying a subset of the data. Strong consistency is achieved by synchronously updating each copy of a row.

MariaDB Xpand uses a shared-nothing architecture. In a shared-nothing distributed computing architecture nodes do not share the same memory or storage. Xpand's shared-nothing architecture provides important benefits, including read and write scaling, and storage scalability.

For additional information on MariaDB Xpand architecture, see "MariaDB Xpand" in MariaDB Documentation.

HA and Fault Tolerance

Distributed Transactions combines the high availability (HA) and fault tolerance capabilities of MariaDB Xpand and MariaDB MaxScale.

MariaDB Xpand is fault tolerant by design, and can tolerate a single node failure or a single zone failure without loss of data.

Xpand can tolerate a single node failure because Xpand stores redundant copies of data on multiple nodes. Xpand writes data to logical containers called slices. By default, Xpand automatically maintains two physical copies of each logical slice, which is it calls replicas. Therefore, if a single node fails no slices are lost because at least one replica of each slice is stored on other nodes.

Xpand can also tolerate a single zone failure because Xpand distributes replicas for each slice to nodes in different zones. In SkySQL, the Xpand nodes in the Distributed Transactions topology are automatically distributed evenly across 3 zones within the same region. Therefore, if a single zone fails no slices are lost because at least one replica of each slice is stored on nodes in another zone.

As long as Xpand has sufficient replicas and a quorum of nodes is available, it can lose a single node or zone without experiencing any data loss. Upon node or zone failure, Xpand automatically heals and protects the data without intervention. The rebalancer creates new replicas on the remaining nodes to replace the replicas from the failed node or zone. Upon node failure, the failed node is replaced by Kubernetes auto-healing, and the replacement node is picked-up by the rebalancer, restoring to the intended node count automatically. Upon zone failure, the nodes in the failed zone are replaced when the zone comes back online.

Elasticity and Scalability

Distributed Transactions leverages the elasticity and scalability capabilities of MariaDB Xpand.

Xpand provides elasticity by allowing scale up and scale down of node counts.

In advance of a period of high application traffic, or when database load outstrips capacity, you can scale up node counts. Increasing Xpand node counts increases available storage capacity, increases available storage IOPS, and increases available processing power. Xpand supports both read and write scaling, so scaling up can help improve throughput for both reads and writes.

When demand decreases, you can scale down node counts (minimum of 3 nodes). As the service scales down, Xpand automatically redistributes data to the remaining nodes, ensuring that it maintains high availability and fault tolerance.

Xpand also provides scalability by supporting parallel query evaluation, distributed query execution, and massively parallel processing (MPP). Xpand does an excellent job scaling many different kinds of complex queries.

Strong Consistency and ACID

Distributed Transactions leverages the strong consistency and concurrency controls of MariaDB Xpand.

MariaDB Xpand provides strong consistency using synchronous replication. When Xpand writes to a slice, multiple replicas of the slice are written to separate nodes to provide fault tolerance. Xpand writes all replicas for the slice in parallel. Before the write can complete, each participating node must provide an acknowledgment that its replica has been written. This approach to synchronous replication is highly scalable.

Xpand uses the Paxos protocol to resolve distributed transactions. The Paxos protocol allows each participating node to reach a consensus on whether a specific operation has been performed in a fault tolerant manner. This approach to transaction resolution allows operations to be performed durably on all nodes.

Xpand supports the Repeatable Read transaction isolation level. The Repeatable Read isolation level allows a transaction to see a consistent snapshot of data for a given table throughout the lifetime of a transaction, even if the underlying data has been modified. This approach to transaction isolation prevents concurrent transactions from interfering with each other, while still maintaining strong consistency.

Xpand implements a distributed Multi-Version Concurrency Control (MVCC) scheme. It supports lockless reads, 2-phase locking for writes, and online schema changes. This approach to concurrency allows both reads and writes to scale, while still maintaining strong consistency.

Xpand's strong consistency and concurrency controls allow Xpand to provide strong ACID semantics without sacrificing high availability and scalability.

Automatic Rebalancing

Distributed Transactions leverages the capabilities of MariaDB Xpand's rebalancer process.

The role of the rebalancer is to ensure that Xpand has a "healthy" data distribution, which means that:

  • Each slice should be protected with a sufficient number of replicas.

  • Each slice should have replicas distributed across zones.

  • No slice should have replicas on decommissioned nodes.

  • No slice should have excessive replicas.

  • No slice should be too large.

  • No node or zone should receive a disproportionate number of reads.

  • Each table and index should have sufficient ranking replicas to distribute read requests evenly between each node.

  • No node or zone should receive a disproportionate number of writes.

  • Each table and index should have sufficient replicas to distribute read requests evenly between each node.

The state of a database is constantly changing, due to data and load changes, node failure, and node maintenance. Xpand's rebalancer process maintains a "healthy" data distribution by regularly checking the data distribution and automatically rebalancing the data to counteract changes.

Single Node Configuration

MariaDB SkySQL for Distributed Transactions includes a single instance (1 Xpand node) topology to facilitate development and testing at the lowest price. A 1 Xpand node topology can be scaled up to the maximum number of nodes. After scale up, the minimum number of nodes available to scale down to is 3.