[Proposal] Operational insights & ETA overshoot on large UUID-keyed tables (140M+ rows)
- Dominant language
- Go
- Stars
- 13.6k
- Forks
- 1.4k
- Avg merge
- 2h 31m
- Merged PRs (30d)
- 4
Description
### Background & Operational Context
During a live zero-downtime migration on a ~390GB, 144M row production table with a UUID (`VARCHAR(36)`) primary key on MySQL (Cloud SQL), we navigated two notable operational edge cases that we would like to share and propose improvements for:
1. **Progress percentage overshoot & ETA calculation on UUID/String PKs:**
* The migration progress consistently overshot 100% (reaching `139%`, `146%`, and `164.8%`) displaying `ETA: due` for hours while actively copying keys.
* **Root Cause:** When the iteration key is non-numeric (hexadecimal UUID strings), statistical estimation from `EXPLAIN` differs substantially from the actual distribution in the lexicographical traversal space (`0000...` to `ffff...`), leaving operators without clear visibility into true completion times.
2. **Throttled State vs. Postponed Cut-Over Lifecycle:**
* Long-duration throttling via `--throttle-additional-flag-file` pauses active heartbeat writes and changelog application. Over extended maintenance windows, this can cause MySQL client connections to exceed `wait_timeout` (28,800s idle limits) and disconnect.
* In contrast, using `--postpone-cut-over-flag-file` (or socket `postpone`) allows row copying to finish 100% and maintains continuous lightweight binlog streaming (`Lag: 0.04s`, active heartbeats), completely preventing idle connection timeouts while safely waiting for a scheduled cut-over window.
### Proposed Contributions
We would like to contribute the following to `gh-ost`:
1. **Documentation PR:**
* Add an operational guide clarifying the operational differences between `--throttle-additional-flag-file` (load shedding/pause) and `--postpone-cut-over-flag-file` (scheduled cut-overs) during multi-hour maintenance windows.
* Document the behavior and interpretation of progress metrics when migrating tables with UUID/alphanumeric primary keys.
2. **Discussion on ETA / Metric Bounds:**
* Discuss potential heuristics to detect string/UUID primary keys and cap or weight the progress estimation in `go/logic/migrator.go` to avoid confusing `>100% / ETA: due` output.
We have a local branch ready and would love maintainer feedback before opening the documentation PR!
Contributor guide
Research direction
Start with the operational scenarios in the issue and read go/logic/migrator.go for the progress and ETA behavior. Review the existing documentation before adding guidance on throttling versus postponed cut-over and UUID/alphanumeric primary keys. Done should include maintainer agreement on the metric heuristic and a documentation PR covering both operational cases.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, mysql
- Domain
- databases, documentation
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100