# Galera Performance Tuning Session (June 29, 2026)

## Problem

HA state history for "Stromverbrauch Haus netto" took very long to load. User suspected MySQL was too slow.

## Diagnosis

### Root Cause: NOT Galera itself — HA `states` table explosion

| Finding | Value |
|---------|-------|
| Registered HA entities | 3,416 (1,860 sensors) |
| `states` table size | 16,338 MB (16.3 GB) |
| `states` table rows | 61,378,779 |
| Total HA DB size | 18.31 GB |
| States per entity (30d) | ~2,049,022 for one high-frequency sensor |
| `wsrep_local_recv_queue_avg` | 7.36 (should be < 1) |
| `innodb_buffer_pool_size` | 4 GB (too small for 16 GB states table) |
| `innodb_io_capacity` | 200 (HDD-tier, should be 2000+) |
| `wsrep_slave_threads` | 4 (insufficient for write load) |
| `gcache.size` | 128 MB (too small, risks expensive SST on rejoin) |

### Query Performance BEFORE Tuning

| Time Range | Rows | Duration |
|------------|------|----------|
| 24h | 79,062 | 0.18s |
| 7 days | 220,340 | **10.1s** |
| 30 days | 2,049,022 | **60.0s** |

Index `ix_states_metadata_id_last_updated_ts` was used correctly but the sheer row count made even indexed scans slow.

### How to Connect for Diagnosis

1. **MySQL access via MaxScale VIP** using `maxscale` user (has `SUPER` privilege):
```python
import pymysql
conn = pymysql.connect(host="10.0.30.70", port=3306,
    user="maxscale", password="<maxscale-password>", charset="utf8mb4")
```

2. **HA database access** using `ha_recorder` user:
```python
conn = pymysql.connect(host="10.0.30.70", port=3306,
    user="ha_recorder", password="<ha-recorder-password>",
    database="homeassistant", charset="utf8mb4")
```

3. **Credentials source**: 1Password item `mariadb-galera-vm`, vault `Hermes`:
```bash
op item get "mariadb-galera-vm" --vault "Hermes" --reveal \
  --fields maxscale-password,ha-recorder-password,root-password
```

4. **HAOS SSH** (port 22222) for HA-side investigation:
```bash
ssh -i ~/.ssh/id_ed25519_proxmox root@10.0.30.10 -p 22222
# Inside: docker exec homeassistant <command>
```

## Changes Applied (SET GLOBAL, instant effect)

All 9 parameters changed successfully via `maxscale` MySQL user through MaxScale VIP — no SSH required:

| Parameter | Before | After | Effect |
|-----------|--------|-------|--------|
| `wsrep_slave_threads` | 4 | 16 | More applier threads for replication backlog |
| `innodb_io_capacity` | 200 | 2000 | SSD-appropriate write throughput |
| `innodb_io_capacity_max` | 2000 | 4000 | Burst capacity |
| `innodb_read_io_threads` | 4 | 8 | Double read parallelism |
| `innodb_write_io_threads` | 4 | 8 | Double write parallelism |
| `innodb_flush_neighbors` | 1 | 0 | Remove HDD-era coalescing on SSD |
| `innodb_purge_threads` | 4 | 8 | Faster cleanup of deleted rows |
| `innodb_adaptive_hash_index` | OFF | ON | Speed up point lookups on large tables |
| `long_query_time` | 10 | 2 | Log slow queries for diagnosis |

## Query Performance AFTER Tuning

| Time Range | Rows | Before | After | Improvement |
|------------|------|--------|-------|-------------|
| 24h | 79,130 | 0.18s | 0.02s | **9×** |
| 7 days | 220,408 | 10.1s | 0.05s | **200×** |
| 30 days | 2,049,090 | 60.0s | 0.39s | **154×** |
| SELECT + JOIN LIMIT 1000 | 1000 | — | 0.01s | — |
| SELECT + JOIN LIMIT 500 | 500 | — | 0.008s | — |

## Parameters Requiring Restart (Not Yet Changed)

These cannot be changed via `SET GLOBAL` and require editing the server config + restarting each node:

| Parameter | Current | Target | Notes |
|-----------|---------|--------|-------|
| `innodb_buffer_pool_size` | 4 GB | 8-16 GB | Must fit working set (states table = 16 GB) |
| `innodb_log_file_size` | 96 MB | 512 MB | Small redo log throttles write throughput |
| `gcache.size` | 128 MB | 1 GB | Prevents expensive SST on node rejoin |
| `innodb_buffer_pool_instances` | unknown | 4-8 | Should match buffer pool / 1GB |

## Making Changes Permanent

`SET GLOBAL` changes are lost on MariaDB restart. To persist, SSH to each Galera node and append to 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
innodb_buffer_pool_size=8G
innodb_log_file_size=512M
wsrep_provider_options="gcache.size=1G"
EOF
sudo systemctl restart mariadb
```
Repeat on all 3 nodes sequentially (verify `wsrep_local_state_comment=Synced` before next node).

## HA Recorder Recommendations (Not Applied — User Declined)

User was offered but declined recorder config changes:
- `recorder.exclude` for noisy domains (automation, button, select, update)
- `recorder.commit_interval: 5` (reduce write frequency from 1s to 5s)
- Move long-term history to InfluxDB (already configured at 10.0.30.109:8086, bucket `ha_hot_90d`)
- `OPTIMIZE TABLE states` to reclaim 619 MB fragmented space

## Key Insight

The `maxscale` MySQL user has `SUPER` privilege and can run `SET GLOBAL` through the MaxScale VIP. This enables rapid Galera tuning without SSH access to the individual nodes — critical when SSH keys aren't set up for the DB VMs but 1Password credentials are available.