Master Aggregator Redundancy

Availability

SingleStore self-managed deployments running on-premises or in customer-managed data centers can configure the feature using SingleStore Toolbox either through a YAML-based cluster configuration file or via Toolbox commands.

Overview

By leveraging this feature, you can benefit from enhanced reliability and failover capabilities, allowing your mission-critical workloads to remain highly available.

The Master Aggregator (MA) node plays a pivotal role in the management and operational coordination of a SingleStore cluster. The MA is crucial not only for executing DDL operations but also for tasks such as ingestion, managing reference tables, and managing partitions during node failures.

To enable this feature, you must explicitly deploy an MA and two or more child aggregators (CA) nodes with the "voting member" role assigned at the time of cluster creation. Thus, there should be one primary MA and at least two additional voting members (CAs). Data across these three instances is synchronously replicated. In case of a failure of the primary (leader) MA, the remaining voting members (CAs) will elect a new primary MA from amongst themselves.

In the event of the MA becoming unavailable, one of the voting members will automatically be elected as the new MA through a consensus mechanism. SingleStore ensures that your transactions maintain the atomicity property by guaranteeing that all changes are either fully committed or rolled back during the process of electing the new MA. SingleStore recommends implementing a load balancer or a proxy to ensure that if the MA goes offline, your application can automatically retry and connect to the newly elected MA.

When an MA fails, the system automatically initiates recovery through a preconfigured workflow. Creating a new node is not always warranted as it depends on the type of failure encountered. Once the recovered or new node joins the voting membership, SingleStore's consensus algorithm ensures that it is fully synchronized and eligible to be elected as a potential MA.

Configure Master Aggregator Redundancy

To take advantage of this feature, you will need SingleStore v8.9 or later and Toolbox v 1.18.1 or later. With this combination, you can create a configuration of one MA and a minimum of two CAs set as voting members.

You can set up this feature in either of the following ways:

• Using a YAML-based configuration file

• Using Toolbox commands

# Cluster configuration for Master Aggregator (MA) redundancy.
#
# Deploys 1 MA + 3 CAs (all CAs are voting members) + 2 leaves.

license:
memsql_server_version: 9.0.0
package_type: rpm  # Use "deb" on Debian/Ubuntu.

# Enable consensus so voting members can elect a new MA on failure.
sync_variables:
  consensus_enabled: ON
  consensus_election_timeout: 30000  # In milliseconds; default is 30000.

# High availability across leaves (paired partitions).
high_availability: true

hosts:
  # ---- Master Aggregator ----
  - hostname: ma-host.example.com
    localhost: false
    nodes:
      - role: Master
        port: 3306

  # ---- Voting-member Child Aggregators (place in different failure domains) ----
  - hostname: ca-host-1.example.com
    localhost: false
    nodes:
      - role: Aggregator
        aggregator_role: voting_member
        port: 3306

  - hostname: ca-host-2.example.com
    localhost: false
    nodes:
      - role: Aggregator
        aggregator_role: voting_member
        port: 3306

  - hostname: ca-host-3.example.com
    localhost: false
    nodes:
      - role: Aggregator
        aggregator_role: voting_member
        port: 3306

  # ---- Leaf nodes ----
  - hostname: leaf-host-1.example.com
    localhost: false
    nodes:
      - role: Leaf
        port: 3306

  - hostname: leaf-host-2.example.com
    localhost: false
    nodes:
      - role: Leaf
        port: 3306

sdb-admin show-aggregators

✓ Successfully ran 'memsqlctl show-aggregators'
+-----------+------+--------+---------------+--------------------------------+------------------+---------------------+
|   Host    | Port | State  | Opened        | Average                        | Master           | Role                |
|           |      |        | Connections   | Roundtrip Latency (ms).        | Aggregator       |                     |
+-----------+------+--------+---------------+--------------------------------+------------------+---------------------+
| 127.0.0.1 | 3306 | online | 1             | null                           | 1                | Leader              |
| 127.0.0.1 | 3308 | online | 2             | 0.377                          | 0                | Voting Member       |
| 127.0.0.1 | 3309 | online | 2             | 0.313                          | 0                | Voting Member       |
| 127.0.0.1 | 3310 | online | 1             | 6.023                          | 0                | Voting Member       |
+-----------+------+--------+---------------+--------------------------------+------------------+---------------------+

Troubleshooting

If an MA is down, a new MA will be elected from the set of voting members and become automatically available to Toolbox in a few moments. While Toolbox may still display the stopped MA as the primary MA, this does not affect any commands. Toolbox will continue to work fine if at least one MA is up and running.

If Toolbox shows two or more running MAs (via sdb-admin list-nodes), some commands may become unavailable. In this case, you should manually stop one of the MAs using the sdb-admin stop-node --memsql-id command.

FAQs

1. Which global engine variables are used in configuring this feature?

   The consensus_enabled and consensus_election_timeout variables are used.

   • consensus_enabled must be set to ON to add aggregators as voting members.

   • consensus_election_timeout controls the time, in milliseconds, for which a voting member waits before conducting an election if it does not hear from the MA. You can adjust the value if required.

     SQL

     Copy

     SHOW VARIABLES LIKE '%election%';

     +----------------------------+-------+
     | Variable_name              | Value |
     +----------------------------+-------+
     | consensus_election_timeout | 30000 |
     +----------------------------+-------+

2. When an MA goes offline and a new voting member becomes the MA, how should the cluster be reconfigured to the three MA voting member configuration?

   You have to set up a process to restart the failed node. If restarting is not possible, you should remove the old aggregator, deploy a new CA, and add it as a voting member. Once this third voting member is provisioned, SingleStore’s consensus algorithm will ensure it is caught up and eligible to be elected as a potential MA.

3. Are there any specific steps needed to "catch up" the new MA?

   No, the new voting member automatically catches up once it is back online.

4. How to find out if an MA is down and a new MA is successfully promoted?

   You can check the output of either of these commands from any voting member. The current MA will be the one that the majority of the voting members report.

   • SQL

     Copy

     SHOW AGGREGATORS EXTENDED;

   • SQL

     Copy

     SELECT * FROM INFORMATION_SCHEMA.AGGREGATORS;

5. What does a user or application need to do when a new MA is promoted?

   SingleStore recommends implementing application-level retry logic together with a stable, load-balanced endpoint so that client connections do not depend on any specific aggregator host.

   Recommended setup for most cases is that you connect your application to a load balancer in front of the child aggregators (CAs) instead of pointing directly to the Master Aggregator (MA). The CAs will automatically route administrative tasks (such as DDL and metadata operations) to the current MA. If the MA fails, the system automatically elects a new one. Your application endpoints remain the same, and you only need to ensure your application is configured to handle retry requests if it encounters transient errors during the short failover window.

   If you need to expose a dedicated "MA endpoint" for administrative or DDL operations then configure your load balancer or proxy to route traffic to all three voting aggregators (the current MA and the two voting CAs). The cluster’s consensus algorithm handles the master election automatically so you do not need to manually update the load balancer when a failover occurs. The load balancer simply routes traffic to any healthy voting member, and the newly elected master will begin accepting MA-only operations immediately. Ensure that your application includes retry logic to handle the following errors:

   • Transient network or connection errors, such as a lost connection to MySQL server during query, or a client-side driver error.

   • Engine errors returned after a failover, such as:

     • This instance is not the master aggregator. Modifying a reference table is not permitted on child aggregators and leaves (ER_DISTRIBUTED_NOT_MASTER): The request reached a node that is no longer the current MA.

     • Server shutdown in progress (ER_SERVER_SHUTDOWN_CODE): The previous MA is shutting down.

     • Cannot connect to master aggregator (ER_CONNECT_TO_MASTER): A CA cannot reach the MA, typically during an election.

   For any of these errors, retry the request against the same load-balanced endpoint. The load balancer routes the retry to the newly elected MA.

6. If a DDL or DML operation is hitting the MA endpoint and, at the same time, the original MA goes offline and a new MA is being elected, what will happen to the operation? What are the various failure scenarios?

   SingleStore ensures your transactions maintain the atomicity property by guaranteeing that all changes are either fully committed or rolled back during the process of electing the new MA.

   In the following scenarios, SingleStore recommends implementing application-level retry logic (similar to Q#5) to ensure a DDL or DML request is re-established.

   Scenarios	Description
   A user sends a query to the CA, but the MA is offline.	The CA will internally retry the query until a new MA is elected, and the CA can connect to the new MA.
   A user sends a query to the MA, but the MA is offline	SingleStore recommends building retry logic in the application along with a load balancer or a proxy, so they can manage the connections and connect to the newly promoted MA.
   A user sends a query or a multi-statement transaction to the CA, which forwards it to the MA, but the MA goes offline while executing the query.	Similar to the above scenario, SingleStore recommends building retry logic in the application to allow connecting to the newly promoted MA. Without this retry logic, the user will receive an error stating that the connection to the server was lost.
   A user sends a query to the CA, which forwards it to the MA, but a new MA is elected while the previous MA is running the query.	Depending on how far the execution of the query has progressed, the CA will internally retry and forward the query to the new MA or the user may receive an error. To eliminate this error, SingleStore recommends building retry logic in your applications to connect to the new MA's endpoint.
   A user sends a query to the MA but another node is elected as the MA, without the previous MA going offline.	The query fails with ER_DISTRIBUTED_NOT_MASTER. Retrying the query forwards it to the new MA unless the query is non-forwardable, in which case the retry fails. For non-forwardable queries, the application must drop the connection and reconnect when this error occurs.

7. How should the CAs that are allocated as voting members be placed to best optimize resiliency?

   SingleStore recommends placing the CA nodes marked as voting members across different failure domains or datacenters. This will help improve the resiliency.

8. How does SingleStore resolve "split-brain" issues?

   Split-brain occurs when a network partition divides nodes into two groups. Nodes can communicate within their own group but not with the other group.

   To eliminate the possibility of a split-brain scenario, SingleStore ensures that only the primary MA can write data to the reference and cluster databases and execute DDL operations. Only this MA is responsible for managing cluster metadata, running cluster operations, and detecting failures on CAs and data nodes.

9. Is it possible that, after a split-brain resolution, the MA is elected in Zone A, and the leaf node resides in Zone B?

   If there is a majority of aggregators with the voting member role in Zone A then one of them will become the MA. The leaf node will not shut itself down  and is still reachable from the other nodes in Zone B.

   However, the leaf node is not reachable from the MA in Zone A, leading the MA to failover the leaf node. Assuming that all databases use sync replication:

   Case 1: If the cluster metadata states that the leaf node has the master instance M of a database partition and other leaf in Zone A has replica R of the database partition replicating synchronously, the MA will update the cluster metadata to state that R is the new master instance and M is now an offline async replica.

   Case 2: If the cluster metadata states that the leaf has the master instance M of a database partition and another leaf node in Zone A has replica R of the database partition replicating asynchronously, the MA will only update cluster metadata to state that M is now offline.

10. How are client queries and transactions handled while the system detects and resolves a split-brained aggregator? How does SingleStore ensure that DML through CA is not processed in the leaf node when the network is split?

    In Case 1 from Question 9, clients and aggregators in Zone B can continue reading from the database partition through M. However, writes to M will block because R, which must acknowledge them, is in Zone A. These writes will not commit, and R will not acknowledge them after the partition heals because R is now the master. While M continues to replicate synchronously to R, it does not switch to asynchronous replication.M does not switch to asynchronous replication on its own. It first asks the MA to update the cluster metadata and mark R as asynchronous. M switches only after that update succeeds. As the MA in Zone A is unreachable, the update fails, so M continues replicating synchronously to R.

    In Case 1 from Question 9, clients/aggregators in Zone A are able to read and write to the database partition via R.

    In Case 2 from Question 9, clients/aggregators in Zone B are able to read and write to the database partition via M.

    In Case 2 from Question 9, clients/aggregators in Zone A are not able to read or write to the database partition.

11. What happens in a network partition where the aggregators are evenly split (for example, two aggregators on one side and two on the other) and neither side has a quorum?

    Like other distributed systems that use consensus, SingleStore cannot resolve a 50/50 split when the cluster has an even number of nodes. Use an odd number of nodes (3, 5, or 7) to avoid this, for two main reasons:

    • Optimal fault-tolerance: For example, a three-node cluster needs two votes to make a decision and can tolerate 1 failure. A four-node cluster needs three votes but can still tolerate only 1 failure. Thus, adding a fourth node provides no additional fault tolerance; it only adds one more vote required for each decision. The same applies to six nodes versus five, and so on.

    • It avoids the split-brain scenario. If you use an odd number of nodes, network partitions, which split the nodes into two groups, will always result in one of the sides having a quorum.