Dominik Schön
bbbe4f8985
- New patterns/ directory with 7 initial failure-mode patterns (PAT-001..007) - skill-impact.md audit trail for skill modifications - _template.md for future pattern creation - index.md updated with Patterns section - log.md entry for this change - Inspired by arXiv:2608.27454 (WikiSkill)
53 lines
1.8 KiB
Markdown
53 lines
1.8 KiB
Markdown
---
|
|
pattern_id: PAT-001
|
|
title: "OPTIMIZE TABLE on Galera triggers TOI DDL → Deadlock"
|
|
category: database
|
|
severity: high
|
|
status: active
|
|
first_observed: 2026-07
|
|
last_updated: 2026-08-30
|
|
related_systems: [galera-maxscale]
|
|
related_solution_docs:
|
|
- docs/solutions/bug-fixes/2026-07-23-cnpg-timeline-corruption-rebuild.md
|
|
related_skills: []
|
|
---
|
|
|
|
# OPTIMIZE TABLE on Galera triggers TOI DDL → Deadlock
|
|
|
|
## Symptom
|
|
|
|
Running `OPTIMIZE TABLE` on a Galera cluster node causes a cluster-wide stall or deadlock.
|
|
The table lock propagates via TOI (Total Order Isolation) to all nodes, blocking all writes
|
|
to ALL tables during the operation.
|
|
|
|
## Root Cause
|
|
|
|
Galera executes DDL statements (including `OPTIMIZE TABLE`, `ALTER TABLE`, etc.) in TOI mode.
|
|
This means the DDL is replicated as a global operation that blocks the entire cluster — not
|
|
just the target table. For large tables, the rebuild phase can take minutes, causing apparent
|
|
outages.
|
|
|
|
## Mitigation
|
|
|
|
1. **Use RSU before DDL**: Switch the node to Rolling Schema Update (RSU) mode before running
|
|
`OPTIMIZE TABLE`. This prevents cluster-wide blocking:
|
|
```
|
|
SET GLOBAL wsrep_OSU_method = 'RSU';
|
|
-- run OPTIMIZE TABLE on this node only
|
|
SET GLOBAL wsrep_OSU_method = 'TOI'; -- restore
|
|
```
|
|
2. **Schedule during maintenance window** — even with RSU, the node itself is degraded
|
|
3. **Consider pt-online-schema-change** for large tables — avoids blocking entirely
|
|
|
|
## Prevention
|
|
|
|
- Never run `OPTIMIZE TABLE` or `ALTER TABLE` in production without RSU on Galera
|
|
- Add this check to DBA runbooks and monitoring alerts
|
|
- The Galera skill (`mariadb-galera-cluster-administration`) documents this pitfall
|
|
|
|
## Evidence
|
|
|
|
- Observed during Galera cluster administration sessions (Jul 2026)
|
|
- MEMORY.md entry: "OPTIMIZE TABLE=TOI DDL→Deadlock! RSU davor."
|
|
- Galera cluster: nodes 300/301/302, VIP .70:3306
|