Summary of Production Findings
Following a successful zero-downtime database migration on a ~390GB, 144M-row MySQL table with a UUID (VARCHAR(36)) primary key, our team encountered and analyzed two specific operational edge cases under live fintech traffic.
We would like to submit these findings to the core maintainers and propose specific contributions based on our production experience.
1. Progress Metric Overshoot on Lexicographical (UUID) Keyspaces
- Observed Behavior: The migration progress metrics consistently exceeded 100%, displaying
139%, 146%, and peaking at 164.8%, while remaining in a status of ETA: due for several hours while actively copying keys.
- Root Cause Analysis:
gh-ost calculates initial total row estimates using EXPLAIN optimizer estimates. When traversing non-numeric keyspaces (hexadecimal UUID strings), the density of the lexicographical range (0000... to ffff...) does not scale linearly with statistical sampling, causing progress calculation overshoots.
- Proposed Contribution: We would like to open a discussion on potential heuristics inside
go/logic/migrator.go to gracefully handle progress indicators when traversing alphanumeric primary keys.
2. Throttled State vs. Postponed Cut-Over Connection Lifecycles
- Observed Behavior: Extended throttling via
--throttle-additional-flag-file suspends active heartbeat writes. During multi-hour maintenance windows under continuous traffic throttling, the database client idle connections hit MySQL's configured wait_timeout limits (e.g., 28,800s), causing silent socket disconnections.
- Alternative Workaround: Utilizing the postpone mechanism (
--postpone-cut-over-flag-file) allows row copying to finish 100% and maintains an active heartbeat write and binlog streamer connection (Lag: ~0.04s), preventing idle timeouts.
- Proposed Contribution: We have drafted a documentation PR to explicitly outline the operational lifecycle differences between using Throttling (for emergency load shedding) and Postponing (for scheduled maintenance windows) to help SREs avoid timeout traps.
Summary of Production Findings
Following a successful zero-downtime database migration on a ~390GB, 144M-row MySQL table with a UUID (VARCHAR(36)) primary key, our team encountered and analyzed two specific operational edge cases under live fintech traffic.
We would like to submit these findings to the core maintainers and propose specific contributions based on our production experience.
1. Progress Metric Overshoot on Lexicographical (UUID) Keyspaces
139%,146%, and peaking at164.8%, while remaining in a status ofETA: duefor several hours while actively copying keys.gh-ostcalculates initial total row estimates usingEXPLAINoptimizer estimates. When traversing non-numeric keyspaces (hexadecimal UUID strings), the density of the lexicographical range (0000...toffff...) does not scale linearly with statistical sampling, causing progress calculation overshoots.go/logic/migrator.goto gracefully handle progress indicators when traversing alphanumeric primary keys.2. Throttled State vs. Postponed Cut-Over Connection Lifecycles
--throttle-additional-flag-filesuspends active heartbeat writes. During multi-hour maintenance windows under continuous traffic throttling, the database client idle connections hit MySQL's configuredwait_timeoutlimits (e.g., 28,800s), causing silent socket disconnections.--postpone-cut-over-flag-file) allows row copying to finish 100% and maintains an active heartbeat write and binlog streamer connection (Lag: ~0.04s), preventing idle timeouts.