github / github/gh-ost

[Proposal] Operational insights & ETA overshoot on large UUID-keyed tables (140M+ rows)

Open
#1,766 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.