At NoRedInk, We've been using gh-ost for a few years now, and it's been a pleasure.<p>- The ability to control a running migration is crucial. We have pretty predictable load, and we generally run long-running migrations during off-peak hours. If a migration runs longer than we were expecting and might run into peak hours, we can pause the migration and have the migration not impact users.<p>- hooks make it trivial to integrate with other tools. Right now it reports to slack, but if we used it more, we'd likely hook it up to real monitoring infrastructure.<p>- there's a lot of default behavior that we want. I'd recommend regular users wrap their best practices in another script and not call gh-ost directly. It's nice to not worry about good defaults for e.g. throttling, or worrying about whether ghost is hooked up to some kind of external monitoring.
We evaluated gh-ost, but the killer for us is that it doesn't support any kind of foreign keys.<p>I understand that at GitHub's scale, foreign keys might be more of a hassle than what they are worth, but for a smallish company that values data integrity over scale and uptime, this is not an acceptable choice.
Back when I worked for Shopify, I got a chance to work on something similar -- GhostFerry(<a href="https://github.com/shopify/ghostferry" rel="nofollow">https://github.com/shopify/ghostferry</a>), which allows for doing all sorts of migrations, that too between various databases.<p>It was recently open-sourced. Do take a look.
I used it and it's really impressive. Works as described. The only issue with this is that you can't easily use it without understanding how it works. It's more of a system you have to own rather than a tool you can use, so you can't just point a new person at it and go "just run this".
That's really old and still good strategy. [off-topic] I've heard this first time from a novel (1964).<p>Flynn.io uses the same kind of strategy; transaction log && async replication (<a href="https://flynn.io/docs/databases" rel="nofollow">https://flynn.io/docs/databases</a>)<p>A little sad nanobox.io which one of my app running on has an inferior strategy; temporarily offline at the last sync moment (<a href="https://docs.nanobox.io/data-management/data-migrations-scaling/" rel="nofollow">https://docs.nanobox.io/data-management/data-migrations-scal...</a>)
Square also its online schema migration tool that is open source here: <a href="https://github.com/square/shift" rel="nofollow">https://github.com/square/shift</a><p>Its pretty cool. Check it out as well.
Very cool! Curious, does this leverage this go-mysql library at all? <a href="https://github.com/siddontang/go-mysql" rel="nofollow">https://github.com/siddontang/go-mysql</a>
We use gh-ost at Harvest[1] and it's a dream in comparison to manually migrating on a replica and switching master/slave roles [2].<p>Also the linked post[3] in the readme hit us very close to home. We originally tried some of our migrations with pt-online-schema-change, which was great in theory but caused a lot of locking contention during the actual process.<p>I see many people hammering on the lack of foreign key support which is interesting to me. At some point, a database system grows to where relying on MySQL's Online DDL[4] "works" but not really with production load. I feel like a team knows when they need to bring in a tool like this.<p>The dev in me understands how wonderful FKs are for consistency. But the db-guy in me that has had to deal with locking issues recognizes FKs as a tradeoff, not dogma.<p>If you shy away from migrating your large or busy tables, or are scheduling frequent maintenance down times in order to migrate these tables, that's when gh-ost (and others) are appropriate to evaluate.<p>So for us it's not an immediate red flag that gh-ost doesn't support FKs. We just have to work around that limitation[5] because the alternatives are much worse.<p>For the record, we don't gh-ost all of our migrations. Only the ones that are deemed sufficiently large enough are gh-osted and those heuristics will change from team-to-team.<p>But as a guy who has had to deal with our database issues AND as a developer who doesn't want to be chained by a database design decision from a decade ago, I love the flexibility gh-ost gives us as we continue to grow.<p>[1] <a href="https://www.getharvest.com/" rel="nofollow">https://www.getharvest.com/</a><p>[2] <a href="https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html" rel="nofollow">https://dev.mysql.com/doc/refman/5.6/en/replication-features...</a><p>[3] <a href="https://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html" rel="nofollow">https://dev.mysql.com/doc/refman/5.6/en/replication-features...</a><p>[4] <a href="https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html" rel="nofollow">https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-...</a><p>[5] <a href="https://github.com/github/gh-ost/issues/507#issuecomment-338725563" rel="nofollow">https://github.com/github/gh-ost/issues/507#issuecomment-338...</a>
I was investigating using the binary log for another project a few years ago, but came to the conclusion that it's too hard to work with ... I don't remember any details though, maybe someone can fill me in ?