How We Saved an 8 TB SingleStore Cluster From Degraded HA (And Why CLEAR ORPHAN DATABASES Should Be In Your Runbook)

Field notes from a Data-Sleek DBA on bringing a production SingleStore cluster back from the edge — without a single restore from backup.

The call you don’t want to get

If you run a self-hosted SingleStore cluster in production, there is a specific flavor of problem you hope you never see: the database is serving reads and writes, nothing looks visibly wrong from the application side, but underneath, one of your leaf nodes is carrying 64 master partitions with no sync slaves anywhere in the cluster.

You are one hardware failure away from an outage. And the command that’s supposed to fix it — REBALANCE PARTITIONS — fails with this error every time you run it:

ERROR 2440 (HY000): The rebalance partition operation on <leaf>:3306
failed due to low disk space (disk space needed 8973788 Mb,
disk space available 7319017 Mb)

That’s the call I got recently. What followed was about 48 hours of careful forensic work that ended with us recovering approximately 8.6 TB of disk space consumed by data the cluster didn’t know existed, from a single leaf node using a SingleStore command most operators don’t know exists. The cluster is back to full HA redundancy without a single restore from backup and without a minute of application downtime.

This post is the field-notes version of that recovery: what we saw, what we tried, what didn’t work, what did, and the lessons I’m taking into every SingleStore engagement from here on out. If you run a production SingleStore cluster — or you’re thinking about it — there are a few specific things in here that will save you hours when your own 2am call comes.

The cluster

The affected cluster was a self-hosted SingleStore 8.9.x deployment running on AWS — four leaf nodes on r5.4xlarge instances, each with a single 16 TB NVMe data volume. Two aggregators (one master, one child), two availability groups, HA configured with sync replication across the AG pairs. About 8 TB of active data across three sharded databases, 128 partitions each. It’s the kind of boringly competent SingleStore deployment you’d design if you were starting from scratch today.

The workload feeds real-time counter tables that downstream applications consume at very high read rates. Losing half the partitions of the largest database, even briefly, would have meant a production incident measured in lost business, not just lost queries.

What we found on first contact

The first symptom was subtle. Everything looked “online” at the surface:

SHOW LEAVES;
— All four leaves reporting Running, Connectable=True, Online

But dig one level deeper, and the picture changed. We ran SHOW CLUSTER STATUS and scrolled through the partition-level view of one leaf. What we expected to see on a healthy HA cluster is that every master partition on one leaf has a corresponding sync slave on its AG partner — 32 masters here, 32 slaves there, mirrored across the pair.

What we actually saw on the problem leaf was dozens of rows like this:

| database_name | master | pending | … | Orphan |

master pending with a Metadata Role = Orphan. In SingleStore’s vocabulary, that means the leaf thinks it owns these partitions, but the cluster metadata has moved on and no longer references them. Split-brain state. Meanwhile, the same partition ordinals on the partner leaf were showing as master online. Two leaves each thinking they were master of the same 64 partitions.

Over the following hours, SingleStore’s automatic recovery cleaned up most of the orphans on the smaller databases. But for the largest one — roughly 8 TB of active data — the orphans were removed but no fresh sync replicas were ever created on the recovered leaf. At the peak of the split-brain, two leaves each thought they were master of the same 64 partitions. After partial automatic recovery, we were left with this:

+——-+———+——–+
| Leaf  | Masters | Slaves |
+——-+———+——–+
| AG1-A |      32 |     32 |
| AG1-B |      64 |      0 |   <– 64 unpaired masters
| AG2-A |      32 |     32 |
| AG2-B |       0 |      0 |   <– completely empty for this DB
+——-+———+——–+

Half the database was being served by a single leaf with no backup. If that one node had gone down, we’d have been restoring from backup and writing an apology email.

Before SingleStore: the host-level mess nobody talks about

Here’s something I want to be honest about because I don’t see it written up enough: before you can debug a SingleStore cluster, you have to know that the hosts underneath it are actually healthy. The documentation tells you what kernel parameters to set. What nobody tells you is what happens when they’re set wrong, partially, inconsistently across hosts, or lost after a reboot.

On this cluster, the leaf nodes had drifted out of spec over an extended period. We found:

• /data not persistently mounted after reboot. The drive would come back on some reboots and not others. When it didn’t, SingleStore was running but couldn’t reach its data directory — a state that produces exactly the kind of split-brain, orphan-partition symptoms we were seeing.
• vm.max_map_count = 65530 on one host (SingleStore expects 1000000000). On another host it was set to 1000000 in the config file — still wrong by three orders of magnitude. Both hosts had config files that “looked” correct at a glance but had either never been reloaded or been overridden by a later file.
• vm.swappiness = 60 on multiple hosts (expected 1).
• Swap missing entirely on one host, undersized to 10 GB on a 128 GB machine on another, and a stale systemd swap unit complicating the rebuild on a third.
• memsql user limits inconsistent: duplicated NOFILE lines, nproc = 65535 instead of 128000, nice limits missing on some hosts and present on others.

And then there was the intermittent SSH problem. The primary aggregator would periodically lose the ability to SSH into one of the leaves. Sometimes it could reach it, sometimes it couldn’t. We never proved the root cause of this — my working hypothesis was that low vm.max_map_count was starving sshd of memory mappings during resource pressure, but we didn’t capture the specific kernel or sshd logs that would confirm it. I’m leaving it as an unproven contributing factor rather than pretending we nailed it.

What we did about the hosts

Before touching anything in SingleStore, I wrote a host validation script that checks every leaf against the full list of expected values, flags any drift, and reports duplicate or conflicting entries in the sysctl and limits config files. The script also verifies that /data is properly entered in /etc/fstab with the correct mount options so it comes back persistently after reboot — not just that it happens to be mounted right now.

Every leaf got run through the script. Every leaf got brought into spec. Then we rebooted each one in turn to verify the fixes held across restart.

This took roughly a full day of careful work — checking, fixing, re-checking, rebooting, re-checking — before we were confident the hosts were actually solid. In retrospect it was the most important day of the recovery, because every debugging step that followed assumed the hosts were stable. If they weren’t, we’d have been chasing ghosts for a week.

If you’re running self-hosted SingleStore and you’ve never audited your leaf hosts against the full requirements list, it’s worth pausing on that before going further. The product is excellent but it assumes the hosts it runs on are correctly configured, and in my experience that assumption is wrong more often than it’s right. Book a free consultation with us if you’d like a hand with that audit.

The first rebalance: why it failed

With the hosts validated, the next step was the obvious one: tell SingleStore to restore HA redundancy.

REBALANCE PARTITIONS ON <large_db>;

About 12 minutes later:

ERROR 2440 (HY000): The rebalance partition operation on <leaf>:3306
failed due to low disk space (disk space needed 8973788 Mb,
disk space available 7319017 Mb)

8.77 TB needed. 7.15 TB available. Short by about 1.6 TB.

This didn’t make sense at first. The leaves all had 16 TB drives. The cluster had been running fine on those drives for months. Why couldn’t one leaf accommodate the reseed of one database? I ran df -kh on the target leaf to verify the OS agreed with what SingleStore was reporting:

Filesystem      Size  Used Avail Use%  Mounted on
/dev/nvme1n1     16T  8.7T  7.0T  56%  /data

Yes — 8.7 TB used, 7.0 TB free, 56% full. Matches exactly. The problem is real.

And here’s the suspicious part. All four leaves showed near-identical disk usage — within about 20 GB of each other. That shouldn’t have been possible if one leaf was holding 64 extra unpaired master partitions that the other three weren’t. Something was on disk that shouldn’t have been, and it wasn’t showing up in SHOW DATABASES, the standard way to inventory what a cluster is storing.

The two rabbit holes (and the lesson about engaging support)

Before I tell you what actually fixed this, I want to describe the two wrong paths we went down, because they’re instructive.

Rabbit hole #1: the temp tables

SHOW TABLES on the large database revealed around 60 tables with _tmp_<hex> suffixes. They looked exactly like the kind of debris you’d expect from interrupted columnstore merger operations after a crash — and we had plenty of crashes in this cluster’s recent past. My first theory was that this was the waste, and cleaning it up would free enough space to unblock the rebalance.

I talked to the dev team, they confirmed which ones were safe to drop, we dropped about 62 of them.

— Verify the cleanup worked
SELECT IP_ADDR,
       ROUND(AVAILABLE_DATA_DISK_MB / 1024 / 1024, 2) AS avail_tb
FROM information_schema.MV_NODES
WHERE TYPE = ‘LEAF’;

The freed disk was about 49 MB. Noise. The temp tables were genuinely small — they were the application’s legitimate working state, not debris. The theory was wrong.

Rabbit hole #2: “the estimate must be inflated”

Having ruled out temp tables, I spent some time convinced that SingleStore’s 8.77 TB estimate was conservative — that it was accounting for working space, blob copy overhead, metadata expansion, and the real requirement was smaller. The observation that all four leaves had near-identical disk usage seemed to support this: if the problem leaf needed 8.77 TB more than it had, that would have to come from somewhere specific on the source leaf, and nothing in the numbers showed a source leaf that was 8.77 TB heavier than the others.

This was the wrong framing. The estimate wasn’t inflated. The problem leaf really did have ~8 TB of stuff on disk that wasn’t supposed to be there. Before I found the command that showed it, I spent several more hours in the wrong direction.

The lesson

I spent several hours chasing these two theories before I opened a SingleStore support ticket. In hindsight, I should have engaged support immediately after the first failed rebalance. When an error message references internal concepts (disk space, partition state, recovery) and your obvious remediation doesn’t work, vendor support knows things about their own product that aren’t in the public documentation. Vendor support is included in your license for exactly this reason — use it.

If you’re running self-hosted SingleStore and you’ve never opened a support ticket, open one now just to establish the relationship. You do not want your first-ever ticket to be a 2am production emergency.

The command nobody told me about: CLEAR ORPHAN DATABASES

SingleStore support’s response came back:

“There are orphan database partitions as a result of the cluster events that occurred earlier. You can clear them out with CLEAR ORPHAN DATABASES. Similarly to the rebalance, you can check it first with an explain: EXPLAIN CLEAR ORPHAN DATABASES.”

Here’s the thing I want to emphasize because it cost us hours: there are two completely different concepts that share the word “orphan” in SingleStore, and I had been looking at the wrong one.

• Partition-level orphans are what you see in SHOW CLUSTER STATUS when Metadata Role = Orphan. Individual replica rows where the metadata authority has moved on. This is what we had investigated and what the automatic recovery had already cleaned up.
• Database-level orphans are entire partition databases sitting on disk that the cluster metadata no longer references at all. They don’t appear in SHOW DATABASES. They don’t appear in SHOW CLUSTER STATUS. They don’t appear in any view a DBA would naturally check. They just take up space.

Every time a cluster topology changes — a leaf is removed and re-added, a recovery operation reorganizes partition assignments, a crash interrupts a failover mid-flight — there is a chance that the old on-disk partition database files are left behind. The new cluster topology works fine; it just has dead weight. Over months of topology churn on an unstable cluster, that dead weight can reach terabytes.

I ran the explain first, because you always run the explain first:

EXPLAIN CLEAR ORPHAN DATABASES;

The output showed orphan databases on the target leaf. I took df snapshots before and after the cleanup so I could measure the real effect.

Before:

/dev/nvme1n1     16T  8.7T  7.0T  56%  /data

Then I ran it:

CLEAR ORPHAN DATABASES;
— Query OK, 0 rows affected (58.02 sec)

0 rows affected — that’s what the client reported. I almost dismissed it. Then I ran df -kh again:

/dev/nvme1n1     16T   97G   16T   1%  /data

From 8.7 TB used down to 97 GB used. Approximately 8.6 TB reclaimed, on one leaf, in 58 seconds, from a command whose output said 0 rows affected.

Sit with that for a second. There was 8.6 TB of invisible data on a production leaf. Nothing in standard SingleStore monitoring would have told us about it. Nothing in SHOW DATABASES would have shown it. MV_NODES would have told us the disk was 55% full, but not that 99% of what was on it was garbage. If this cluster had been configured to auto-grow its volumes based on usage, we’d have been paying AWS for terabytes of orphan database files for months.

The “0 rows affected” message, by the way, is accurate in its own narrow way — no metadata rows changed because there was no metadata for the orphaned data in the first place. That’s the whole point of what makes them orphans. But it’s a misleading result message for a command that just moved 8.6 TB of data to the bit bucket.

Running the rebalance (and estimating how long it would take)

With disk space no longer an issue, we could run the rebalance:

REBALANCE ALL DATABASES;

But I wanted to know ahead of time how long this was going to take, because the answer matters for maintenance window planning, on-call communication, and dev team coordination. SingleStore gives you EXPLAIN REBALANCE which shows per-action time estimates, but reading 128 rows and doing mental math on serial vs. parallel execution is not my idea of a good time.

I wrote a small SQL-plus-shell estimator script that reads the EXPLAIN REBALANCE output, groups actions by phase, applies SingleStore’s documented parallelism limits, and produces a single “expected wall clock time” estimate. For an 8 TB rebalance, it told me: 16 to 18 hours.

That matched what actually happened. The rebalance ran cleanly from start to finish in that window, with the target leaf’s disk usage climbing smoothly as partitions were copied over and promoted. The production workload was unaffected throughout.

For this specific class of work — large rebalances on large databases — having a reliable wall-clock estimator is the difference between “open-ended maintenance window, please hold” and “we’ll be back at full HA by tomorrow morning, here’s the monitoring query to watch progress.” I’ll share the framework for that estimator in a future post; reach out if you want a preview.

The final state

After the rebalance completed:

+——-+———+——–+
| Leaf  | Masters | Slaves |
+——-+———+——–+
| AG1-A |      32 |     32 |
| AG1-B |      32 |     32 |
| AG2-A |      32 |     32 |
| AG2-B |      32 |     32 |   <– healthy
+——-+———+——–+

Full HA redundancy on every partition, every database, every leaf. Not a single restore from backup. Not a minute of application downtime. The cluster is back to being the boringly competent deployment it was supposed to be.

Why I still love SingleStore after all this

I want to say something that might sound strange given everything I just wrote: SingleStore came out of this looking good.

Think about what happened. A leaf crashed. It came back in a broken state. The cluster ran in a split-brain configuration for a while. The automatic recovery cleaned up some of the mess but left 8.6 TB of orphan database files on disk. The HA topology was degraded for an extended period. Multiple rebalance attempts failed due to disk constraints.

And through all of that, the production workload never saw a single error. Reads worked. Writes worked. Applications were happy. The data was consistent. There was no corruption, no loss, nothing that needed to be unwound from a backup.

Some databases I’ve worked with would have been completely unrecoverable at step two of that sequence. You’d be pulling a week-old backup, replaying WAL files, explaining to the business why the last 6 hours of transactions are gone. With SingleStore, the worst case was “we have reduced redundancy and we need to plan a rebalance.” That’s a very different class of problem.

The lesson here is not “SingleStore is fragile.” It’s “SingleStore is robust enough that you can get into weird states and still recover from them — if you know where to look.” The product rewards operators who understand its internals. It punishes operators who treat it as a black box. Which is exactly the lesson of every serious database I’ve ever worked with.

The takeaways

If you’re running self-hosted SingleStore, or you’re about to, here are the things I wish somebody had told me before this incident:

1. Audit your host configuration, cluster-wide, on a schedule. Not once when you set it up. On a schedule. Hosts drift. Reboots lose mounts. Config files get overridden. Limits get duplicated. Build a script that checks every parameter against expected values on every leaf, flags every discrepancy, and run it weekly.
2. Put EXPLAIN CLEAR ORPHAN DATABASES in your monitoring. Run it weekly at minimum. If the result is ever non-empty, you have orphan accumulation and you need to investigate. Do not wait until it blocks a recovery operation to discover you have 8 TB of invisible garbage.
3. Set a real disk utilization ceiling — and enforce it. “55% used” sounds fine in isolation. In our case, it was too tight to accommodate a single-database HA reseed operation. Capacity planning for SingleStore has to account for the largest single recovery the cluster might need to perform, plus working space, plus growth headroom. Ours didn’t, because nobody had ever done the math on “what would a recovery of our largest database actually require?”
4. Establish a SingleStore support relationship before you need it. Open a ticket for a non-critical question. Get a support contact. Learn how their portal works. The time to discover your ticket submission workflow is not 2am during a production incident.
5. SHOW DATABASES is not the same as “what’s on disk.” Internalize this. It should be obvious, but it isn’t, and it cost us hours of wrong-direction debugging. When something doesn’t add up, always verify at the OS level with df.
6. Don’t trust “0 rows affected.” For commands that operate at the storage layer, always measure the actual effect with df, not with the client’s result message.
7. Don’t go it alone on a degraded production cluster. The cluster in this story was recovered because I had time, experience with SingleStore internals, and a supportive dev team and vendor relationship. If any of those had been missing, the recovery would have been slower, riskier, or wouldn’t have worked at all. Operators running production distributed databases should not be solo acts.

When to call for help

If you’re reading this and any of the following sounds familiar:

• You’re running a self-hosted SingleStore cluster and you’re not sure whether your leaf hosts are fully spec-compliant.
• You’ve seen cluster events (crashes, failed recoveries, add-leaf / remove-leaf cycles) but you’ve never verified that the cluster is actually clean afterward.
• Your disk utilization has been creeping up and you don’t know how much of it is real data versus accumulated debris.
• You’re hitting errors on REBALANCE PARTITIONS and you’re not sure how to diagnose them.
• You’re thinking about moving to SingleStore but you’re not sure what you don’t know about running it in production.

…this is exactly the kind of work Data-Sleek does every day. We’ve spent years working inside distributed databases like SingleStore, Snowflake, and their close cousins — and we’ve built the host audit scripts, the rebalance estimators, the monitoring queries, and the runbook patterns that separate “we have a production problem” from “we had a production problem and recovered cleanly.”

If you want to talk through what your cluster looks like — or what it should look like before your own 2am call arrives — book a free consultation. The first conversation is on us. If we can save you hours of wrong-direction debugging the way SingleStore support saved us, it’s worth the call.

Frequently Asked Questions (FAQ)

Glossary

Availability Group (AG)
A logical grouping of leaf nodes in SingleStore that determines where partition replicas are placed. Masters and their sync slaves always live in different AGs, ensuring a single-node failure never takes down both copies of the same data.

Leaf Node
The worker tier in a SingleStore cluster. Leaf nodes store and process partition data. All reads and writes ultimately execute on leaves; aggregators coordinate but do not store data.

Master Partition
The authoritative copy of a partition. All writes go to the master. Each master has a corresponding sync slave on a leaf in the partner availability group for HA redundancy.

Sync Slave
A real-time replica of a master partition, kept in sync synchronously before a write is acknowledged. If a master goes down, the sync slave is promoted. A master with no sync slave is a single point of failure.

Orphan Database
An entire partition database sitting on disk that the cluster metadata no longer references. Invisible to SHOW DATABASES and standard monitoring views. Accumulates after interrupted recovery operations and can consume terabytes of disk space silently.

Split-Brain
A state where two nodes simultaneously believe they are the authoritative master of the same partition. Typically caused by a crash or network partition mid-recovery. Left unresolved, it risks data divergence.

Rebalance
The operation that redistributes partition masters and slaves across leaf nodes to restore an even, HA-compliant topology. In SingleStore, triggered with REBALANCE PARTITIONS or REBALANCE ALL DATABASES. Requires sufficient free disk space on target leaves to complete.

Need help auditing, tuning, or recovering your SingleStore cluster? Data-Sleek specializes in distributed database architecture and operations for production workloads. Book a free consultation and we’ll walk through your cluster together.