GitHub's online schema migration for MySQL | Hacker News Reader

GitHub's online schema migration for MySQL

github.com

248 pointsqiuyesuifeng94 comments

At NoRedInk, We've been using gh-ost for a few years now, and it's been a pleasure.

- 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.

- 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.

- 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.

I'm probably really ignorant asking this, but how do you "pause" schema migrations period. And even if you did, how do you ensure a consistent experience for your users if your db is broken? Some sort of application logic to deal with inconsistencies? That seems really expensive (from a development work perspective).

Not OP but I’m familiar with the topic and run similar tooling on large clusters. By pause he probably means prevent it from starting on more databases and let whatever is inflight finish. For the second point, correct, your application needs to handle both schemas during transition. When that’s done, you can rip out the unneeded logic from your application.

> your application needs to handle both schemas during transition.

How is this typically done? Have a version number in the db? Have the app examine the schema with every transaction? Have the app assume old/new schema optimistically, and if that fails rollback and try with alt schema? Something else?

It's all described in the readme, but generally online schema change tools work by creating a new table, copying the data from the old table over, somehow keeping track of new writes to the old table, and then syncing those over. At the end the tables are swapped. With gh-ost you can pause the writes to the new table.

With gh-ost migrations are performed on a copy of the table. This, combined with the way data is copied to this table mean: - you can pause just by suspending the copy, - changes are invisible until the end, when tables are swapped.

The first point depends on the mechanism used to keep up with changes to the original table. You can’t fully pause migrations on pt-online-schema-change for example, as it leverages triggers for that part.

From my phone so sorry if too brief, gh-ost’s docs are great and would tell the whole story.

We evaluated gh-ost, but the killer for us is that it doesn't support any kind of foreign keys.

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.

This is an unfortunate by product due to the way that gh-ost is implemented. It is simply not possible to run it with a FK constraint. The reason is that since it replays the binlogs on the ghost table while the ghost table is not fully populated, the FK constraint will cause some of statements to fail. The data move from the original to the ghost table cannot be completed.

Cannot it add FK constraints after the ghost table is fully populated?

The problem is that adding FK constraints is another schema change, which causes MySQL to copy the whole table, and lock the table during this time -- which is precisely what gh-ost tries to avoid.

Worse, foreign keys from other tables to the one that is being changed would need to be updated as well, blocking those tables in turn.

> which causes MySQL to copy the whole table

This is wrong on a couple levels. First it doesn't copy the whole table: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-...

However, it can take a while if MySQL is evaluating the consistency. But you can disable that with `SET FOREIGN_KEY_CHECKS = 0` which turns it into a metadata change (nearly instantaneous).

You still will need to check for violations, but you can do that in a more friendly-to-load manner, and of course will need to deal with any violations manually.

But that strategy is a good middle ground to all-or-nothing FKs.

Edit: Whoops, looks like I was wrong on the table-copy part. Per "Otherwise, only the COPY algorithm is supported." So it does copy the data when `FOREIGN_KEY_CHECKS=1` (the default)

Yeah. See https://github.com/github/gh-ost/issues/507#issuecomment-338... on how to do it in a performant way.

Author of gh-ost here. Actually, it should be possible to support child-side foreign keys. They would have to be named differently (the foreign key constraint has a unique name in a schema) -- but it should work. See discussion in https://github.com/github/gh-ost/issues/507

It is true that it is not on our roadmap to implement FK support for gh-ost (see https://github.com/github/gh-ost/issues/331), but if anyone wishes to contribute support for FK we're grateful. We've had more complex contributions coming from the community and we're grateful for those.

Child-side foreign keys? Does that mean constraints in app logic instead of the database?

No, on the database itself. I used "child side" for lack of better terminology. Every foreign key has two edges: the "parent" table and the "child" table. Rows in the "child" table depend on existence of matching rows in the "parent" table.

It should be feasible to run `gh-ost` to ALTER a table that only has "child"-side constraints. It will be impossible to run `gh-ost` to ALTER a table that has "parent" side constraints.

Hope this clarifies.

I'm interested to know what companies are doing "at scale" to not need to use foreign keys. Do they just write user ids or whatnot into other tables?

I'm guessing they are validating the constraints in their applications instead of their databases. While this puts more requirements on your setup and developers, it offloads a lot of stress from your database.

How does the app know what is valid in a foreign key column without repeatedly retrieving the primary key column of the other table?

It repeatedly retrieves the parent row. Look at the URL when replying on HN and change the id by adding a few 0's - the app prevents a comment from being added if the ID isn't found. Of course, this doesn't prevent a developer from circumventing the apps constraint logic.

This would place a far higher load on the DB (which would validate the constraint via the index if allowed to) and create more network roundtrips. It’s 10x as expensive as just using a FK, and far less reliable!

>> It’s 10x as expensive as just using a FK

It's not about load, it's about locks and contention in the database caused by FK constraint enforcement. Extra read queries will barely be noticeable compared to that.

it's about locks and contention in the database caused by FK constraint enforcement

What locks do you mean?

Yeah, but from a data integrity point of view that is horrifying. I guess they must have some very comprehensive testing framework to validate the code.

Also, pardon my further ignorance, but if you're not going to use foreign key constraints, what is the point of using a relational db? Why not just a fast key-value store for each index?

You may me able to architect your application to do well-enough without Foreign Keys yet still require features that key-value stores do not provide, like SQL, triggers, etc.

In the specfic case of MySQL, while still horrifying (I agree with you! but it is one of the things you some times have to do at scale), you can create the Foreign Key constraints but then disable their verification and periodically look for violations, as described here: https://www.percona.com/blog/2011/11/18/eventual-consistency...

The application still has foreign key references and JOIN, which you don't get in a k/v store, you just don't have the actual CONSTRAINT. MySQL worked this way for literally decades with the MyISAM storage engine that does not have referential integrity constraints, it just allows the FOREIGN KEY syntax to pass silently.

Foreign keys add locks to referenced rows during insert and update transactions. Bad news if you're locking those rows for other reasons, like a kind of distributed lock. So the problem with FKs isn't that you don't need them; it's that the extra locking limits concurrency.

FK checks also affect performance, of course. Where I work, we disable FKs on our bulk inserts but keep them enabled otherwise and also in tests; but our workload is different from the usual consumer web app, we have multi-million row inserts per user, and no more than 100 users or so per customer, who each get their own tenant DB.

Are you referring to ON UPDATE cascades? Mutable/natural primary key values are not very common these days especially in a DB like MySQL. Incrementing integers are most common.

Google's Spanner doesn't support foreign keys but does support interleaved tables to cover some use cases of foreign keys. Spanner sees wide use inside Google.

The usual routes for data consistency between tables are batch clean up or in-app validation.

How could foreign keys ever be a "hassle"?

They can be a hassle when you want to shard your data, having outgrown your single-instance capacity. You will either shard functionally (extracting complete tables to other database servers), in which case FKs will completely break, or horizontally (split rows across database servers), in which case you may or may not be able to still use FKs.

They're also a performance impact on large tables since inserts/deletes must make multiple trips to the tables/indexes. That's a growing operational hassle as tables grow larger.

> You will either shard functionally (extracting complete tables to other database servers)

This isn't sharding. This is vertical partitioning. Sharding is a type of horizontal partitioning.

Reference: https://en.m.wikipedia.org/wiki/Shard_(database_architecture...

What? Why would enforced consistency be less worth at bigger scale? My guess would have been the complete opposite.

It's not a question of worth but feasibility. Just like an ideal schema is fully normalized, but performance concerns sometimes drive denormalization. When foreign keys can't be used to enforce data integrity, the application has to be built to compensate in other ways. Sometimes that means simply accepting dirty data, and designing the application to stay robust when encountering unexpected data. Other times it means building alternate solutions to discover and repair data issues.

shlomi-noach linked to https://github.com/github/gh-ost/issues/331 in another comment. That goes into some of the reasons to avoid foreign keys.

At a past job where we had a complex MySQL setup, I set up a slack autoresponse to post "Just say no!" anytime someone mentioned foreign keys. :-)

Back when I worked for Shopify, I got a chance to work on something similar -- GhostFerry(https://github.com/shopify/ghostferry), which allows for doing all sorts of migrations, that too between various databases.

It was recently open-sourced. Do take a look.

Hey! I'm the current maintainer of Ghostferry. Thank you for all your work!

For the reader here: one thing to clarify here is that gh-ost performs schema migration via a data migration between two different tables and it does it via a very efficient way. Ghostferry on the other hand is general purpose data migration library that moves data between different databases, most likely different hosts. Frequently, both schema migration and data migrations are abbreviated as migrations and thus may cause some confusion. The domain of operation of Ghostferry do not necessarily overlap with gh-ost, as it would be very inefficient to use Ghostferry to implement gh-ost.

That said it is a very interesting project on its own as it has a lot of potential use cases. I don't want to hijack the thread any further than I already have so if anyone has any further questions, you can contact information and docs in the repo.

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".

I agree. I've used it a lot too, but only after a few test runs against some snapshots to get familiar with the operational aspects of it.

So my understanding is that this is for migrating a db to a new one? Can someone explain like I was beginner why/how'd you would use this?

In certain scenarios if you need to modify the schema for a table in MySQL it will lead to the entire table being locked, and for large tables this could lead to a noticeable outage for users if you need to run queries on that table. One case I had where we faced this problem was changing the primary key for a table from 32 bit to 64 bit ints since we were running out of space. We used Percona's online schema change tool for handling this, which wrapped the creation of a new 'ghost' table (which has the target schema you want), rate limited writes from original table to ghost table, triggered writes from original table to ghost table as new writes came in, and finally a table rename from the ghost table back to the original table name in order to perform the full migration with no data loss or outage.

Sounds like this tool is doing something similar but avoiding the use of triggers for flexibility.

Ah ok! This sound like a great tool then. I have no need for it, but good one to star for a day when I might need it :)

We had to do something similar at my old job, but rather than migrating to a different schema, we were migrating our moderately sized DB (tens of gigabytes) from MySQL to Postgres.

We dual wrote to both DBs while we copied the existing data to the new DB, then switched them over. I think we had less than 5 minutes of downtime all up.

tens of GB is tiny though.

most production systems are at least a few hundred gb, and the previously mentioned scaling problems from foreign keys and constraints are pretty nonexistent unless you're starting to push the boundaries of normal ACID DBs.

i.e. a few TB of data with at least thousands of queries per second and lots of writes/updates

Modern advice: always use 64 bit integer ids. If it's a small table, it won't matter. If it's a big table, you'll need them anyway.

Yeah, this was a case of somebody (definitely not me, I would never!) generating a table in Rails, before Rails used 64 bit IDs on primary keys by default, and no one noticed before the table got really large. It looks like newer versions of Rails are doing it by default now: http://www.mccartie.com/2016/12/05/rails-5.1.html

That’s not always the best advice.

Consider a table that you know will only have a few rows, but is referenced in one or more FK columns. If I use a 64bit integer when 32bit (or smaller!) is enough, I’m now using twice as much space for the FK column. If there are millions of rows, plus indexes, that can add up pretty fast.

That's really old and still good strategy. [off-topic] I've heard this first time from a novel (1964).

Flynn.io uses the same kind of strategy; transaction log && async replication (https://flynn.io/docs/databases)

A little sad nanobox.io which one of my app running on has an inferior strategy; temporarily offline at the last sync moment (https://docs.nanobox.io/data-management/data-migrations-scal...)

This is a really amazing, very well designed and thought out, tool that solves a problem that should never exist.

Holy crap, an alternative to Percona? Why does MySQL get two awesome tools and Postgres nothing?

mysql is a bigger target market.

We recently started investing in Postgres because of support of JSON fields and nested indexes in those fields. Should we have chosen MySQL?

MySQL has I think all of these features now in 8.0 off the top of my head. Having said that it was only just released to stable very recently and like all good things it may pay to wait for a few more edge cases to be flexed out.

Ultimately I’d always suggest the tool you are most familiar with if it’s doing a good enough job.

They both have their issues (though I think in most cases Postgres has saner defaults. Alternative distributions of MySQL like Percona Server can help improve the situation somewhat for MySQL).

Doing anything meaningfully complex or mission-critical with either will always require care, attention, and understanding of how the database is doing its work. If you know MySQL internals particularly better, it may benefit you to focus your efforts there as modern MySQL is perfectly capable (decent online DDL support, decent native JSON support, etc).

If your team aren't experts with either, I'd invest my effort in learning Postgres.

I have extensive experience with MySQL. In fact I used to run a really big social network (70M+ users) based on MySQL db.

Main reason we chose Postgres was that JSON fields have been around for a few years. We really like the Mongo feature-set, but aren't very happy with reliability. In every discussion about Mongo, people used to recommend Postgres instead.

I'm currently working on a product that uses JSONb columns extensively.

To be honest, I don't like it. I'm not sure if it's bad design, or if it's just bad to mix relational databases with JSON, but I'm constantly battling to do things that I would find trivial in SQL.

I guess it really depends on your requirements though. I've found that JSONb is great for storing historical data and results, write-once sort of stuff. I've found it's not so good for storing objects that get modified, especially if a relation can change.

Also you cannot store foreign keys in JSON.

If I want to use it as a write only table where I would like to get virtual indexes for values inside the JSONb column.

Would you recommend using Postgres for this usecase?

This discussion might be useful re: indexing JSONb columns and a comparison of performance (a bit out of date, things have probably improved even further);

http://bitnine.net/blog-postgresql/postgresql-internals-json...

The GIN index is an inverted index, if you're expecting to query against several keys; alternatively if you have a large keyspace and no need to query outside a small number of properties, you could create individual hash or btree indexes for each one.

Postgres is good for this usecase, but as always, YMMV, consider alternatives/optimizations if your scale or write-volume dictate otherwise (e.g. sharding, Citusdb etc.)

Probably no.

As someone else pointed out, the reason so many similar tools exist for this task on mysql and there's no such tool for postgres is not that postgres isn't as popular.

The reason is that this problem is almost non-existent on postgres as many table alterations do not lock the table.

All alters require a full read/write lock, it’s just that most return instantly. This can be a problem if you have long running transactions, as the alter blocks behind all open txns and all new queries block behind that. python for instance has a very strong opinion that you should be using transactions for everything, and is much more likely to have to deal with it than say ruby.

But you’re right, my comment is mostly pedantic, that Postgres implements alters better so these tools aren’t needed.

This is great to know. I usually am able to manage without transactions. So alerts should be pretty fast.

There are some techniques for mitigating those, such as adding new columns as nullable without a default.

Right, adding a column with a default means the alter takes time while holding that lock and nothing can be read/written so is generally unsafe for big tables, but it doesn’t help if the alter can’t acquire the lock in the first place

fwiw, MySQL has supported JSON [1] and also allows nested indexes via functional/virtual indexes [2] since v5.7.8 (August 2015)

1. https://dev.mysql.com/doc/refman/5.7/en/json.html

2. https://dev.mysql.com/doc/refman/5.7/en/create-table-seconda...

There are several other notable options - SoundCloud's LHM, Facebook's online schema change tool, etc. They all have their different quirks.

(And as modern MySQL releases get better online DDL support, become less and less critical - though still useful for all of those edge cases where native lockless online DDLs can't work yet)

Postgres supports transactional DDL statements natively, and many alter table statements don't end up locking the table nearly as severely as some MySQL versions do.

Actually both lock for many (crucial) schema operators, and often severely enough to block your application from reading from the table(s) under change. I've been researching this stuff for a while. Check out http://blog.minicom.nl/blog/2015/04/03/revisiting-profiling-... . It's slightly outdated, but still holds.

That is true, but I wanted to share another angle that may or may not affect PostgreSQL while it continues to affect MySQL even as it has crash-safe (though not transactional) DDL now: these schema changes are online for the master, but are not replication-aware and can have impact in replication delay on servers down the hierarchy.

For this reason alone I think we'll continue to use schema-change tools on MySQL even if the server itself becomes better at those.

In the specific case of gh-ost, another good point is that migrations can be completely paused, which in MySQL is not true of online DDL.

I felt the same way, so I've been working on QuantumDB for the last couple of years. Take a look at https://quantumdb.io . QuantumDB doesn't use the binlog / WAL log like gh-ost does, but it does support foreign key constraints, and it allows you to perform several schema operations in one go without having to deal with the intermediates. It's still not ready for production, but feel free to try it out. Feedback is welcome!

Square also its online schema migration tool that is open source here: https://github.com/square/shift

Its pretty cool. Check it out as well.

That's not a schema migration tool per-se. It's a web interface for managing running a schema migration tool (in their case the venerable pt-osc, but there is an open issue for supporting gh-ost too).

Very cool! Curious, does this leverage this go-mysql library at all? https://github.com/siddontang/go-mysql

Yes, https://github.com/github/gh-ost/search?utf8=%E2%9C%93&q=go-...

Can someone shed some light on how this tool compares to something like Flyway?

It's an alternative to e.g. pt-online-schema-change [0]. The problem is that, for very large mysql tables / clusters, running DDL against the tables live will lock up reads/writes against the table for ages. These tools allow you to run those changes without taking downtime.

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online...

I believe RDS uses this same technique for instance resize/replace.

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].

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.

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.

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.

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.

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.

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.

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.

[1] https://www.getharvest.com/

[2] https://dev.mysql.com/doc/refman/5.6/en/replication-features...

[3] https://dev.mysql.com/doc/refman/5.6/en/replication-features...

[4] https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-...

[5] https://github.com/github/gh-ost/issues/507#issuecomment-338...

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 ?

You can jump through hoops or just use an RDBMS that supports transactional DDL.

That does not solve the problem. Transactional DDL still needs a full table lock for most operations, which on large tables can take minutes to hours. Then it's not really an online schema migration anymore.

Depends on a migration. Postgres can add / drop a column to a table with a billion rows in milliseconds as long as you don't provide a default value for the new column.

And in v11, even if there's a default column!

Also, even if the lock is not used, when you're changing an indexed column, you need to rebuild that index. In most production environments you just can't say "we're going to serve all the traffic without this index for a few hours" - that would kill the service (or a part of it if you're lucky and can disable it)

...so don't make use of the new indexed column until it's ready, why is that an issue? It's no different than waiting for Ghost to finish copying a table for DDL.

I think this is answered in the second sentence.

These same databases have background/concurrent index rebuild features. Change DDL, add index, then switch over.

so background means the index isn't ready right?

I'm not sure what the confusion is here. There's no downtime or slow serving as the first comment said because your app shouldn't be using any new columns until they're ready, whether that's through Ghost or transactional DDL + concurrent index rebuild.

Sure, you can avoid it, but mostly with some effort. Specifically if you're manually changing the column type which is covered by an index, you'd have to:

Add a new column with a new index. Copy the old data over and change the code/add a trigger to keep them synchronised. Change the code to use the new column instead. Remove the sync code/trigger. Drop the old column/index. And you're potentially left with a weird name, because the purpose of the column didn't change, just the type.

Or you could use gh-ost and do it in one go, semi-automated without any code changes, and with the same column name.

There is a big difference. You're only considering the "slowness" from the application perspective of querying a table without the index.

You need to also realize that database server itself takes a load hit when it kicks off these operations on large tables. I'm not sure on PostgreSQL, but I know sometimes you can not immediately cancel these operations in the middle on MySQL (it also takes time to revert).

With gh-ost you have full control over how fast this process goes and can even pause/resume it if you're experiencing issues.

But let's say it IS instantly cancelled. You're also ignoring the fact that you will take the same hit on any replicas if this statement is successful.

Some production environments don't take kindly to having all replicas lagging as the replication thread is blocking on the DDL change. My team has the luxury of being able to have our entire production environment served by a single master server (though we avoid it as much as possible), but it won't be long before we outgrow that and require at least one up to date replica. Many teams are already in that situation and for that, gh-ost is a godsend.

Transactional DDL solves the problem for a large % of use cases. I remember a study that had average prod db size among other things and it was something less than 10GB if memory serves.

Sometimes installing Ghost is a better tactical choice than migrating an entire running system to a new RDBMS.

Author of gh-ost here. Here are my thoughts on migrating to a different RDBMS: http://code.openark.org/blog/mysql/mysql-vs-postgresql-gh-os...

Everything is a choice that has Pros and Cons. For me personally outside of any technical considerations a simple rule applies: anything that has Oracle IP I want to avoid. If anyone thinks there is even a 1% chance Oracle is not planing to recoup it's investment into MySQL by royally f#$%ing over the people using it in some horrifically unethical manner I have a bridge to sell you.

Reply on news.ycombinator.com