hermes-skills/devops/mariadb-galera-cluster-administration/SKILL.md

---
name: mariadb-galera-cluster-administration
description: "Class-level skill for MariaDB Galera cluster + MaxScale proxy administration. Covers total-cluster-failure recovery, sequential SST, MaxScale routing during donor/desynced states, health verification, HA Recorder dependency troubleshooting, OOM-killer diagnosis (PVE host kills KVM), VM placement strategy, and balloon:0 rationale for DB VMs."
version: 1.0.0
tags: [mariadb, galera, maxscale, database, cluster, sst, mysql, ha, homeassistant]
---

## Overview

This umbrella skill covers MariaDB Galera cluster administration with MaxScale as the SQL proxy layer:
- **Total Cluster Failure Recovery** — bootstrap, sequential node rejoin, SST deadlock avoidance
- **MaxScale Routing During Recovery** — `available_when_donor`, readwritesplit tuning, maintenance mode
- **Health Verification** — wsrep status, cluster size, SST progress monitoring
- **Application Dependency Recovery** — HA Recorder reconnect after DB outage

Load this skill for any Galera cluster operation: startup, shutdown, node addition, SST troubleshooting, split-brain recovery, or MaxScale routing issues.

---

## Section 1: Total Cluster Failure Recovery

### 1.1 Symptoms of Total Failure
- All nodes show `systemctl status mariadb` → `Active: failed`
- Journal shows `Failed to reach primary view` and `Failed to open channel 'mariadb-galera' at 'gcomm://...'`: `-110 (Connection timed out)`
- No `grastate.ini` present (or `safe_to_bootstrap: 0` on all nodes — pick any node if absent)
- Application errors: `MySQLdb.OperationalError: (2013, 'Lost connection to server during query')`

### 1.2 Recovery Procedure (CRITICAL — sequential, not parallel!)

**Step 1: Bootstrap ONE node**
```bash
# On the chosen bootstrap node (e.g. db1 at 10.0.30.71):
sudo galera_new_cluster
# This runs mariadbd with --wsrep-new-cluster
# May appear to timeout but continues in background — verify separately
```

Verify bootstrap:
```bash
sudo systemctl is-active mariadb          # → active
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';"  # → Synced
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size';"          # → 1
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'wsrep_cluster_status';"       # → Primary
```

**Step 2: Start remaining nodes SEQUENTIALLY (one at a time)**
```bash
# On db2:
sudo systemctl start --no-block mariadb
# POLL until wsrep_local_state_comment = "Synced" BEFORE starting db3
```

**⚠️ PITFALL: Starting multiple nodes simultaneously causes SST DEADLOCK.**
All joining nodes request SST from `*any*`, but the only available donor becomes overloaded serving multiple concurrent SST streams. Result: `"No donor candidates temporarily available in suitable state"` — all nodes abort.

**Step 3: Repeat for each subsequent node** only after the previous one reaches `Synced`.

### 1.3 SST Monitoring
```bash
# On the joining node (joiner):
sudo systemctl is-active mariadb    # → "activating" during SST
sudo du -sh /var/lib/mysql/         # grows toward source size (~18-20GB)
sudo journalctl -u mariadb -n 5     # "Waiting for SST streaming to complete!"
ps aux | grep -E 'socat|mariabackup|mbstream'  # SST processes running
sudo ss -tnp | grep 4444            # active SST connection from donor

# On the source node (donor):
sudo mariadb -e "SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment';"  # → "Donor/Desynced"
ps aux | grep mariabackup           # backup process streaming to joiner
```

SST duration scales with DB size: ~18GB ≈ 30-40 minutes per node on typical homelab hardware.

### 1.4 Background Polling Pattern
SST takes too long for foreground terminal timeout. Use background polling:
```bash
ssh debian@JOINER_IP "
for i in \$(seq 1 120); do
  STATUS=\$(sudo systemctl is-active mariadb 2>/dev/null)
  if [ \"\$STATUS\" = 'active' ]; then
    WSREP=\$(sudo mariadb -e \"SHOW GLOBAL STATUS LIKE 'wsrep_local_state_comment'\" 2>/dev/null | awk '/wsrep_local_state_comment/{print \$2}')
    SIZE=\$(sudo mariadb -e \"SHOW GLOBAL STATUS LIKE 'wsrep_cluster_size'\" 2>/dev/null | awk '/wsrep_cluster_size/{print \$2}')
    echo \"Poll \$i: ACTIVE, state=\$WSREP, cluster_size=\$SIZE\"
    if [ \"\$WSREP\" = 'Synced' ]; then
      echo 'NODE FULLY SYNCED!'
      break
    fi
  else
    SIZE_DIR=\$(sudo du -sh /var/lib/mysql/ 2>/dev/null | awk '{print \$1}')
    echo \"Poll \$i: \$STATUS, datadir=\$SIZE_DIR\"
  fi
  sleep 10
done
"
```
Use `background=true, notify_on_complete=true` with `timeout=600` (or higher).

---

## Section 2: MaxScale Routing Configuration

### 2.1 The Donor Routing Problem (CRITICAL)

During SST, the donor node has state `Donor/Desynced`. By default, MaxScale's galeramon monitor marks donor nodes as unavailable, so `readwritesplit` won't route traffic to them. With only one synced node acting as donor, **MaxScale has zero routable backends** → applications get connection errors.

**Fix: Enable `available_when_donor`**
```bash
maxctrl alter monitor galera-monitor available_when_donor=true
```
This makes the donor node accept traffic while serving SST. Verify:
```bash
maxctrl list servers
# Donor node should show: "Master, Synced, Running, Donor/Desynced" with Connections > 0
```

**After all nodes are synced, revert:**
```bash
maxctrl alter monitor galera-monitor available_when_donor=false
```

### 2.2 Other Useful MaxScale Commands
```bash
# Server states
maxctrl list servers

# Set/clear maintenance (prevent traffic to a node)
maxctrl set server db2 maintenance
maxctrl clear server db2 maintenance

# Allow reads on master when no slaves available
maxctrl alter service rw-router master_accept_reads=true

# View monitor/service config
maxctrl show monitor galera-monitor
maxctrl show service rw-router

# MaxScale listener check
maxctrl list listeners
```

### 2.3 Testing Connectivity Through MaxScale
```bash
# From MaxScale host (localhost):
mariadb -h 127.0.0.1 -P 3306 -u ha_recorder -p'PASSWORD' --skip-ssl -e "SELECT 'OK' AS r, @@hostname;"

# From application host (via VIP):
mariadb -h 10.0.30.70 -P 3306 -u ha_recorder -p'PASSWORD' --skip-ssl -e "SELECT 1;"
```
### 2.4 Common MaxScale Issues

| Symptom | Cause | Fix |
|---------|-------|-----|
| No routable backends | Donor excluded by default | `available_when_donor=true` |
| "No valid servers from which to query MariaDB user accounts" | All backends down | Recover Galera first |
| readwritesplit finds no slave | Only master available | `master_accept_reads=true` |
| `set server db1 master` fails | Monitored server, can't set role manually | Use `maintenance`/`drain` only |
| All servers show "Down" but MariaDB is running | MaxScale VM IP blocked by `max_connection_errors` on Galera nodes | `FLUSH HOSTS;` on all 3 nodes (see §2.5) |
| `Could not find valid server for target type TARGET_ALL` in logs | All backends Down — same root cause as above or Galera cluster down | Fix Galera first or FLUSH HOSTS |

### 2.5 MaxScale IP Blocked by max_connection_errors (CRITICAL)

**Symptoms:** `maxctrl list servers` shows all 3 Galera nodes as `Down`, but
`mariadb` on each node is running and `wsrep_cluster_size=3`. MaxScale logs show
`Could not find valid server for target type TARGET_ALL` every ~30 seconds.

**Root Cause:** The MaxScale VM IP (e.g., 10.0.30.82) accumulated too many
connection errors on the Galera nodes (typically after a cluster outage or
restart). MariaDB's `max_connect_errors` counter blocks the host after reaching
the threshold (default 100), returning error 1129:
`Host '10.0.30.82' is blocked because of many connection errors`.

**Diagnosis:**
```bash
# Test MaxScale monitor connectivity from MaxScale VM to a Galera node:
ssh debian@10.0.30.70 "mariadb -h 10.0.30.71 -P 3306 -u maxscale -p'<maxscale-password>' -e 'SELECT 1;'"
# ERROR 2002 (HY000): ... 1129 - Host '10.0.30.82' is blocked because of many connection errors
```

**Fix:** Flush hosts on ALL 3 Galera nodes:
```bash
for node in 10.0.30.71 10.0.30.72 10.0.30.73; do
  ssh -i ~/.ssh/id_ed25519_proxmox debian@$node "sudo mariadb -e 'FLUSH HOSTS;'"
done
```

Wait 5 seconds for the monitor to re-check, then verify:
```bash
ssh debian@10.0.30.70 "maxctrl list servers"
# All 3 should show: Slave/Master, Synced, Running
```

**Prevention:** Increase `max_connect_errors` on Galera nodes or add
`max_connect_errors=1000000` to the server config to prevent blocking during
cluster instability.

**Note:** This scenario recurs frequently after any cluster instability
(restart, network blip, SST). Always check `maxctrl list servers` for "Down"
nodes first — if Galera itself is healthy (`wsrep_cluster_size=3`,
`wsrep_ready=ON`), `FLUSH HOSTS` is almost certainly the fix.

**Confirmed again 2026-07-06**: MaxScale VM 10.0.30.82 blocked on all 3
Galera nodes after extended downtime. `FLUSH HOSTS` on all 3 nodes
resolved it immediately — `maxctrl list servers` showed all 3 nodes
"Synced, Running" within seconds. The `maxscale@%` MySQL user existed
with correct grants and password, but the host was blocked at the
MariaDB connection-error-counter level (error 1129).

### 2.6 MaxScale Source-IP ACL Blocking (CRITICAL)

**Symptoms:** `Access denied for user 'root'@'10.0.30.XXX' (using password: YES)` from any host outside the MaxScale VM itself, even with correct credentials. Affects root, maxscale, app users — ALL users. The error comes from MaxScale, not Galera.

**Root Cause:** MaxScale's listener configuration restricts which source IPs may connect. Unlike `max_connection_errors` (§2.5, which blocks the MaxScale VM itself), this is an intentional ACL in the MaxScale listener/service definition. Even localhost (127.0.0.1) on the MaxScale VM may be blocked for certain users.

**Diagnosis:**
```bash
# Test from your host:
python3 -c "
import pymysql
try:
    conn = pymysql.connect(host='10.0.30.70', port=3306, user='root', password='<pw>', charset='utf8mb4')
    print('OK')
except Exception as e:
    print(f'Denied: {e}')
"
# Error: (1045, "Access denied for user 'root'@'10.0.30.230' (using password: YES)")
```

The source IP in the error message is YOUR host's IP, not the Galera node. MaxScale rejects before forwarding to any backend.

**Affected source IPs observed:**
- `.230` (Hermes host) — blocked
- `.62` (K8s worker) — blocked
- `127.0.0.1` (MaxScale VM localhost) — blocked for root, may work for other users
- `.82` (MaxScale VM external IP) — blocked for root

**⚠️ Galera VM SSH and Guest Agent:**
- Galera VMs (.71/.72/.73) have SSH **closed** (port 22 connection refused)
- QEMU Guest Agent is **not running** on Galera VMs — `qm guest exec` fails
- No `qm guest cmd <vmid> ping` works — agent not installed/running
- Direct network access to .71/.72/.73 is blocked even from PVE nodes and MaxScale VM
- The **only** access path is MaxScale VIP .70:3306, and only from allowlisted IPs

**⚠️ 1Password Item Names (corrected):**
- `mariadb-root` — root password (NOT `mariadb-galera-vm`)
- `mariadb-maxscale` — MaxScale admin password
- `mariadb-app-user` — username=dominik, password for app access
- All in vault "Kubernetes ESO" (ID: `334ykdtj5kar3jlpcrztjvx2fu`)
- ESO token retrievable: `kubectl get secret onepassword-token -n external-secrets -o jsonpath="{.data.token}" | base64 -d`

**⚠️ MaxScale VM Access:**
- Hostname: `maxscale-02`
- SSH: `ssh -i ~/.ssh/id_ed25519_proxmox debian@10.0.30.70` (works!)
- Has `mariadb` client installed at `/usr/bin/mysql`
- But even from MaxScale VM localhost, root SQL is denied — MaxScale ACL blocks before backend

**Workaround — SSH to MaxScale VM and use `maxctrl` or local `mariadb` client:**
```bash
# MaxScale VM accepts SSH as debian user
ssh -i ~/.ssh/id_ed25519_proxmox debian@10.0.30.70

# Once on MaxScale VM, use maxctrl to manage the cluster
maxctrl list servers
maxctrl list services

# For SQL operations, try connecting through MaxScale with a user that has
# the correct grant for the MaxScale VM's source IP:
mariadb --defaults-extra-file=/tmp/creds.cnf -h 127.0.0.1 -e "CREATE DATABASE ..."
```

**If ALL users are blocked from ALL sources:**
1. Check `maxctrl list listeners` for IP binding restrictions
2. Check MaxScale service `user`/`password` matching the MySQL user grants
3. The `maxscale` MySQL user (with SUPER) may work through `maxctrl` commands even when direct SQL is blocked
4. As last resort: SSH to a Galera node directly (if SSH is open) and run `sudo mariadb` locally

**Prevention:** When setting up MaxScale, document which source IPs are allowed and ensure the application's expected source IP is in the allowlist. For K8s migrations, the K8s worker IPs must be allowed if applications connect through the MaxScale VIP.

### 2.7 MaxScale VIP Architecture (Homelab — Updated 2026-07-13)

| Component | Address | Role |
|-----------|---------|------|
| MaxScale VIP | `10.0.30.70:3306` | rw (readwritesplit) |
| MaxScale VIP | `10.0.30.70:3307` | ro (readconnroute) |
| MaxScale VM 01 | `10.0.30.81` (VM 310, n5pro) | Active MaxScale instance |
| MaxScale VM 02 | `10.0.30.82` (VM 311, proxmox3) | Standby MaxScale instance |
| db1 | `10.0.30.71` (VM 300, n5pro) | Galera node |
| db2 | `10.0.30.72` (VM 301, proxmox3) | Galera node |
| db3 | `10.0.30.73` (VM 302, proxmox6) | Galera node |

Applications connect through `10.0.30.70:3306` for automatic failover and
load balancing. The `maxscale` MySQL user (with SUPER privilege) can be used
for `SET GLOBAL` tuning commands through the VIP without SSH access to nodes.

**HA Rules**: All 5 VMs (300, 301, 302, 310, 311) restricted to n5pro +
proxmox3 + proxmox6 (3 hardware-diverse hosts for quorum safety) via
strict `db-nodes` node-affinity rule. Anti-affinity keeps MaxScale
instances apart (310≠311) and Galera nodes apart (301≠302).
See `references/galera-cluster-topology.md` for full rules.cfg content.

**⚠️ Quorum rule**: Each Galera node MUST be on a different physical host.
2 nodes on the same host = quorum loss if that host fails.
**⚠️ Anti-affinity blocks migrations**: `qm migrate` fails if the target
host already has a VM with negative affinity. Check `rules.cfg` first.

PVE HA may migrate VMs — always verify current placement with `qm list`
before operating.

---

## Section 3: Home Assistant Recorder Dependency

### 3.1 Architecture
```
Home Assistant (HAOS VM) → MaxScale VIP (10.0.30.70:3306) → Galera Cluster (3 nodes)
```
HA `configuration.yaml` recorder config:
```yaml
recorder:
  db_url: mysql://ha_recorder:PASSWORD@10.0.30.70:3306/homeassistant?charset=utf8mb4
  purge_keep_days: 30
  db_max_retries: 20
```

### 3.2 When Recorder Fails
- HA logs: `Error during connection setup: (retrying in 5 seconds)` / `MySQLdb.OperationalError: (2013, 'Lost connection to server during query')`
- After retries exhausted: `Recorder setup failed, recorder shutting down`
- Downstream: `history`, `logbook` integrations fail (depend on recorder)
- `recorder` may show as loaded in API but `history`/`logbook` domains missing

### 3.3 Recovery
1. Ensure Galera cluster + MaxScale routing is functional FIRST
2. Restart HA: `POST /api/services/homeassistant/restart` with bearer token
3. Wait for `state: RUNNING` (may take 2-3 min with large DB schema migration check)
4. Verify: `GET /api/services` includes `recorder`, `history`, `logbook`
5. Test: `GET /api/history/period?filter_entity_id=sun.sun` → HTTP 200 with data

### 3.3b Silent Recorder Disconnect (No Errors Logged)

**Different from §3.2** — Recorder can lose its DB connection WITHOUT
logging any errors. HA runs normally (entities update, automations fire),
but no state data is written to the DB. History/Logbook show no recent data.

**Triggers:** MaxScale `FLUSH HOSTS`, Galera node restart, MaxScale VIP
failover — any brief connection interruption that the recorder thread
doesn't recover from.

**Diagnosis (does NOT require HA API — all checks on Galera directly):**

1. Check `recorder_runs` for an open run:
   ```sql
   SELECT run_id, start, `end` FROM recorder_runs ORDER BY run_id DESC LIMIT 3\G
   ```
   Open run (`end = NULL`) with old `start` = recorder disconnected mid-run.

2. Check latest state timestamp:
   ```sql
   SELECT FROM_UNIXTIME(MAX(last_updated_ts)) AS latest_state FROM states;
   ```
   If hours/days behind current time → recorder stopped writing.

3. **⚠️ Do NOT run `SELECT COUNT(*) FROM states`** — with 62M+ rows / 6.6 GB,
   this hangs for 30+ seconds. Use `information_schema.table_rows` instead:
   ```sql
   SELECT table_name, table_rows, ROUND(data_length/1024/1024) AS data_mb
   FROM information_schema.tables WHERE table_schema='homeassistant';
   ```

4. Check MaxScale sessions for `ha_recorder`:
   ```bash
   maxctrl list sessions
   ```
   If `ha_recorder` absent → connection is dead.

**Fix:** `ha core restart` (via SSH to HAOS on port 22222):
```bash
ssh -p 22222 root@10.0.30.10 "ha core restart"
```

Recorder reconnects and resumes writing immediately. Data gap between
disconnect and restart is permanently lost. InfluxDB (if configured) may
still have the data — it's a separate integration with its own connection.

### 3.4 HA Access Methods
- HA API: `curl -H "Authorization: Bearer $HA_TOKEN" $HA_URL/api/...`
- HA VM logs via Proxmox guest agent: `qm guest exec 106 -- bash -c 'docker logs homeassistant 2>&1 | tail -30'`
- HA config: `/mnt/data/supervisor/homeassistant/configuration.yaml` (accessed via Proxmox host SSH)

---

## Section 4: Verification Checklist

After full cluster recovery:
1. ✅ All 3 nodes: `wsrep_local_state_comment = Synced`, `wsrep_cluster_size = 3`
2. ✅ MaxScale: all servers `Running, Synced`, no `Donor/Desynced`
3. ✅ `available_when_donor` reverted to `false`
4. ✅ MaxScale connection test succeeds via VIP
5. ✅ HA services: `recorder`, `history`, `logbook` all loaded
6. ✅ HA History API returns HTTP 200 with data
7. ✅ No `ERROR (Recorder)` entries in recent HA logs

## Section 5: Application-Level Performance on Galera

### 5.1 Bulk-Load IDs for Dedup (Avoid Per-Row Roundtrips)

Every query through MaxScale → Galera incurs network latency + replication overhead. Patterns that do per-row existence checks (`SELECT 1 FROM tbl WHERE id=?` inside a loop) are catastrophically slow — a scraper checking 30k URLs against 28k existing rows took minutes appearing "hung" with zero output.

**Wrong:**
```python
for url in urls:
    if db_has_id(likely_id):  # one roundtrip per URL
        continue
    fetch_and_insert(url)
```

**Right:**
```python
existing_ids = set()  # load once
cur.execute("SELECT id FROM recipes")
existing_ids = {r[0] for r in cur.fetchall()}
for url in urls:
    if likely_id in existing_ids:  # in-memory, nanoseconds
        continue
    fetch_and_insert(url)
    existing_ids.add(new_id)  # keep set fresh
```

Loading 28k IDs into a Python `set()` takes ~0.3s. Every subsequent check is O(1) in-memory.

### 5.2 PyMySQL `executemany` Dict Field Pitfall

`pymysql.cursors.Cursor.executemany()` raises `TypeError: dict can not be used as parameter` if any value in the batch tuple is a Python `dict`. This occurs when source data has inconsistent typing — e.g., Schema.org JSON-LD `recipeCategory` is usually a string but sometimes a dict (`{"@type": "Thing", "name": "Dessert"}`) or a list.

**Fix:** Normalize every field to a scalar before building the row tuple:
```python
cat = r.get("category", "") or ""
if isinstance(cat, dict):
    cat = cat.get("name", "") or str(cat)
elif isinstance(cat, list):
    cat = ", ".join(str(c) for c in cat)
```

Apply the same pattern to `calories`, `rating_value`, and any field sourced from semi-structured JSON-LD. Validate with a dry-run scan (`isinstance(field, dict)` check across all records) before the first `executemany` batch.

### 5.3 Creating Application Databases on Galera

To add a new database + user for an application (e.g., recipe storage):

```bash
# SSH to any Galera node (e.g., 10.0.30.71)
ssh debian@10.0.30.71
sudo mariadb -e "
  CREATE DATABASE IF NOT EXISTS <dbname> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  CREATE USER IF NOT EXISTS '<user>'@'%' IDENTIFIED BY '<password>';
  GRANT ALL PRIVILEGES ON <dbname>.* TO '<user>'@'%';
  FLUSH PRIVILEGES;
"
```

The database propagates to all nodes automatically via Galera replication. Applications connect through the MaxScale VIP (`10.0.30.70:3306`), never directly to individual nodes.

**PyMySQL connection** (when `mysql` CLI is not installed on the application host):
```python
import pymysql
conn = pymysql.connect(
    host="10.0.30.70", port=3306,
    user="<user>", password="<password>",
    charset="utf8mb4", database="<dbname>",
)
```

FULLTEXT indexes on MariaDB require `ENGINE=InnoDB` and `CHARSET=utf8mb4`. Create them in the schema DDL, not as a post-migration `ALTER TABLE` (which locks the table on large datasets).

---

### 5.3b OOM-Killer: PVE Host Kills Galera KVM Process (2026-07-13)

**Different from §1.1** — this is NOT a MariaDB crash. The PVE host's Linux
kernel OOM-killer terminates the entire KVM process when host RAM is exhausted.

**Symptoms:**
- VM shows `running` in `qm status` but Guest Agent is down (`qm guest cmd <vmid> ping` fails)
- SSH to the VM is refused/closed (all guest services died with the KVM process)
- QEMU restarts the VM (onboot=1) but the guest OS boots incompletely (VmRSS far below allocated RAM)
- Galera cluster drops from 3→2 nodes; surviving nodes show `forgetting <UUID>` in journal

**Diagnosis (on the PVE HOST, not the VM):**
```bash
# SSH to the PVE node hosting the affected VM
ssh root@<pve-node-ip> "dmesg -T | grep -iE 'oom|killed|out of memory' | tail -20"
# Look for: "Out of memory: Killed process <PID> (kvm)" with task_memcg=/qemu.slice/<VMID>.scope
```

**Accessing a partially-booted VM via `qm guest exec`:**
When SSH is down but QEMU restarted the VM (onboot=1), the guest agent may
respond even though regular `qm agent <vmid> ping` fails. Use `qm guest exec`
to run commands directly:
```bash
# From the PVE host where the VM runs:
qm guest exec <vmid> -- /bin/bash -c "hostname; uptime; systemctl is-active mariadb; free -m | head -2"
# Returns JSON with exitcode, out-data, out-truncated fields
```
This is the fastest way to check if MariaDB is still in SST/activating state
when SSH is not yet available. Parse output with:
```bash
qm guest exec <vmid> -- /bin/bash -c "systemctl is-active mariadb" | python3 -c "import json,sys; print(json.load(sys.stdin).get('out-data','').strip())"
```

**Root Cause:** RAM overcommit on the PVE host:
```
overcommit = sum(running VM maxmem) / node physical RAM
```
Example: proxmox2 had 15 GB RAM but 22 GB VMs allocated (VM 129=12GB + VM 300=8GB + VM 311=2GB = 147%).

**Why `balloon: 0` is correct for Galera VMs:**
MySQL/Galera's InnoDB buffer pool holds hot pages under constant access.
If the PVE balloon driver reclaims memory, the guest kernel swaps those hot
pages to swap → massive I/O latency spikes → Galera flow control kicks in
and throttles writes across the ENTIRE cluster. One ballooned Galera node
can slow down the whole cluster. `balloon: 0` (disabled) ensures the VM
always holds its full allocated RAM.

**Fix sequence (user preference: RCA → restart → mitigation, in that order):**
1. **RCA first** — diagnose root cause BEFORE restarting. Check `dmesg -T | grep oom` on the PVE host, correlate with Galera journal timestamps on surviving nodes. Understand WHY the VM died before bringing it back.
2. **Restart the killed VM** — `qm stop <vmid>; qm start <vmid>` (or via HA manager). Wait for SST to complete (Galera rejoins via State Transfer from a surviving node). Expect 30-60 minutes SST time depending on DB size. During SST, the node shows `activating` in systemctl and `Joiner` in wsrep state.
3. **Mitigation** — address the root cause to prevent recurrence:
   - Migrate the VM to a node with RAM headroom
   - Set strict HA node-affinity rules to prevent failover to small nodes
   - Deploy RAM-based rebalancer (see PVE skill `scripts/pve-ram-rebalancer.sh`)
4. **Verify** — confirm Galera cluster size = 3, all nodes Synced, MaxScale routing restored

### 5.3c Galera/MaxScale VM Placement on PVE (Updated 2026-07-13)

**⚠️ CRITICAL: Quorum-Aware Placement Rule**

Each Galera node MUST be on a different physical PVE host. With 3 Galera
nodes, losing any single host must leave ≥2 nodes alive (= quorum). If 2
nodes share a host and that host fails, only 1 survives → **no quorum,
entire cluster goes down** (non-primary state, all writes blocked).

**Placement checklist:**
1. Count Galera nodes per PVE host — max 1 per host
2. Verify: for EACH host, if it fails, ≥2 Galera nodes remain on other hosts
3. Include MaxScale VMs in the analysis — they should also be distributed
4. Document the 3 allowed hosts in the HA `db-nodes` rule (strict=1)

**Previous placement (problematic — caused OOM + quorum risk):**
| VM | Name | Node | RAM | Issue |
|----|------|------|-----|-------|
| 300 | mariadb-01 | proxmox2 (15GB) | 8 GB | OOM killed — co-located with 12GB K8s CP |
| 301 | mariadb-02 | proxmox6 (15GB) | 8 GB | Overcommit risk |
| 302 | mariadb-03 | proxmox4 (15GB) | 8 GB | 200% overcommit with K8s worker |
| 310 | maxscale-01 | proxmox4 (15GB) | 2 GB | Same node as mariadb-03 |
| 311 | maxscale-02 | proxmox2 (15GB) | 2 GB | Same node as mariadb-01 |

**Intermediate placement (quorum risk — 2 nodes on proxmox3):**
| VM | Name | Node | Issue |
|----|------|------|-------|
| 300 | mariadb-01 | proxmox3 | 2 of 3 Galera on same host — quorum risk |
| 301 | mariadb-02 | proxmox3 | If proxmox3 fails, only 1 node survives |
| 302 | mariadb-03 | n5pro | OK |

**Corrected placement (3 different hosts, quorum-safe):**
| VM | Name | Node | RAM | Safe? |
|----|------|------|-----|-------|
| 300 | mariadb-01 | n5pro (91GB) | 8 GB | ✅ Massive headroom |
| 301 | mariadb-02 | proxmox3 (31GB) | 8 GB | ✅ Good headroom |
| 302 | mariadb-03 | proxmox6 (15GB) | 8 GB | ⚠️ 113% overcommit but effective use is only 5.5 GB |
| 310 | maxscale-01 | n5pro (91GB) | 2 GB | ✅ |
| 311 | maxscale-02 | proxmox3 (31GB) | 2 GB | ✅ |

HA rule `db-nodes` (strict=1) restricts all 5 VMs to n5pro + proxmox3 +
proxmox6 (3 hardware-diverse hosts). Anti-affinity rules keep MaxScale
instances apart (310≠311) and Galera nodes apart (301≠302).

**⚠️ PITFALL: Live Migration Can Kill Recently-Recovering Galera Nodes**

Migrating a Galera VM that is still in `activating` state (SST in progress)
or that was recently OOM-killed and restarted can cause it to drop from the
cluster and become unreachable (guest agent down, SSH refused). Sequence:
1. OOM-kill destroys KVM process
2. QEMU restarts VM (onboot=1) but guest OS boots incompletely
3. Live migration triggers → VM freezes during migration
4. After migration: guest agent down, SSH "No route to host", MariaDB stuck

**Safe migration sequence for recovering Galera VMs:**
1. Wait until `systemctl is-active mariadb` = `active` AND `wsrep_local_state_comment` = `Synced`
2. THEN migrate — never migrate during SST/activating state
3. After migration, verify guest agent responds: `qm agent <vmid> ping`
4. If guest agent is down post-migration: `qm stop <vmid>; qm start <vmid>` (full restart, not live migrate)

**⚠️ PITFALL: Anti-Affinity Rules Block Migrations**

Negative resource-affinity rules (e.g., `vm:310` ↔ `vm:311` must not
co-locate) will cause `qm migrate` to fail with:
```
cannot migrate resource 'vm:311' to node 'n5pro':
- resource 'vm:310' on target node 'n5pro' in negative affinity with resource 'vm:311'
```
Always check `/etc/pve/ha/rules.cfg` for conflicting rules before migrating.
The target node must not contain any VM that has negative affinity with
the VM being migrated.

---

### 6.1 The `maxscale` MySQL User Trick

The `maxscale` MySQL user (stored in 1Password item `mariadb-galera-vm`, field `maxscale-password`) has `SUPER` privilege. This means you can run `SET GLOBAL` commands **through the MaxScale VIP** without SSH access to the Galera nodes:

```python
import pymysql
conn = pymysql.connect(
    host="10.0.30.70", port=3306,
    user="maxscale", password="<maxscale-password>",
    charset="utf8mb4"
)
cur = conn.cursor()
cur.execute("SET GLOBAL wsrep_slave_threads=16")
```

This is the fastest way to tune Galera — no SSH, no config file edits, instant effect. Changes persist until the next MariaDB restart. To make them permanent, the same parameters must be added to the server config (`/etc/mysql/mariadb.conf.d/50-server.cnf` or equivalent) via SSH.

### 6.2 Key Tunable Parameters (Ordered by Impact)

| Parameter | Default | Recommended | Why |
|-----------|---------|-------------|-----|
| `wsrep_slave_threads` | 4 | 16 | Applier threads — if `wsrep_local_recv_queue_avg` > 1, nodes can't keep up |
| `innodb_io_capacity` | 200 | 2000 | 200 is HDD-tier; SSDs need 2000+ |
| `innodb_io_capacity_max` | 2000 | 4000 | Burst capacity for flush storms |
| `innodb_read_io_threads` | 4 | 8 | Parallel read threads |
| `innodb_write_io_threads` | 4 | 8 | Parallel write threads |
| `innodb_flush_neighbors` | 1 | 0 | 1 coalesces neighboring pages (HDD optimization); unnecessary on SSD |
| `innodb_purge_threads` | 4 | 8 | Cleanup threads for massive deletes (e.g., HA purges) |
| `innodb_adaptive_hash_index` | OFF | ON | Speeds up point lookups on large tables (states, recipes) |
| `long_query_time` | 10 | 2 | Catch slow queries in the slow query log |

Parameters that **cannot** be changed at runtime (require restart + config file):
- `innodb_buffer_pool_size` (4 GB → 8-16 GB recommended for HA workloads)
- `innodb_log_file_size` (96 MB → 512 MB)
- `gcache.size` (128 MB → 1 GB, wsrep_provider_options, prevents expensive SST on rejoin)

### 6.3 Diagnosing HA Recorder Slowness

When HA history/state charts load slowly, the bottleneck is almost always the `states` table in the `homeassistant` database, not Galera itself.

**Diagnosis procedure:**

1. Connect as `ha_recorder` (password in 1Password `mariadb-galera-vm`, field `ha-recorder-password`):
```python
conn = pymysql.connect(host="10.0.30.70", port=3306, user="ha_recorder",
    password="<ha-recorder-password>", database="homeassistant", charset="utf8mb4")
```

2. Check `states` table size:
```sql
SELECT table_name, ROUND(SUM(data_length+index_length)/1024/1024,1) AS size_mb, table_rows
FROM information_schema.tables WHERE table_schema='homeassistant'
GROUP BY table_name ORDER BY size_mb DESC LIMIT 10;
```

3. Find the entity's `metadata_id`:
```sql
SELECT metadata_id, entity_id FROM states_meta
WHERE entity_id LIKE '%stromverbrauch%';
```

4. Time queries at different ranges (24h, 7d, 30d):
```sql
SELECT COUNT(*) FROM states
WHERE metadata_id=<ID> AND last_updated_ts >= UNIX_TIMESTAMP('<datetime>');
```

5. Check the index is being used:
```sql
EXPLAIN SELECT * FROM states
WHERE metadata_id=<ID> AND last_updated_ts >= UNIX_TIMESTAMP('<datetime>')
ORDER BY last_updated_ts;
```

**Red flags:**
- `states` table > 10 GB or > 50M rows → needs purging or recorder excludes
- A single entity with > 1M state changes in 30 days → high-frequency sensor flooding the recorder
- Query time for 7-day range > 5s → Galera applier backlog or insufficient buffer pool
- `wsrep_local_recv_queue_avg` > 1 → applier threads can't keep up (increase `wsrep_slave_threads`)

### 6.4 Making Changes Permanent

`SET GLOBAL` changes are lost on restart. To persist, SSH to each Galera node and edit the server config:
```bash
ssh debian@10.0.30.71
sudo tee -a /etc/mysql/mariadb.conf.d/50-server.cnf << 'EOF'
[mariadb]
wsrep_slave_threads=16
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_read_io_threads=8
innodb_write_io_threads=8
innodb_flush_neighbors=0
innodb_purge_threads=8
innodb_adaptive_hash_index=ON
long_query_time=2
EOF
sudo systemctl restart mariadb
```
Repeat on all 3 nodes. Restart sequentially (one at a time, verify `Synced` before next).

---

- `references/galera-cluster-topology.md` — Homelab Galera/MaxScale topology, IPs, VM mapping, credentials locations
- `references/galera-recovery-runbook.md` — Step-by-step recovery transcript from June 2026 outage
- `references/galera-performance-tuning.md` — Performance tuning session: before/after metrics, HA states table diagnosis