Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

GitHub's online schema migration for MySQL

github.com

Post reply on HN
Print view View original

81–90 of 96 posts

Re: GitHub's online schema migration for MySQL

#81

Post by xenomachina »

radicality wrote: post #56
killbrad wrote: post #50

Earlier quoted context omitted.

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?

Re: GitHub's online schema migration for MySQL

#82

Post by tomnipotent »

gaius wrote: post #65
Chilinot wrote: post #48

Earlier quoted context omitted.

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.

Re: GitHub's online schema migration for MySQL

#83

Post by idunno246 »

paulryanrogers wrote: post #72
idunno246 wrote: post #17

Earlier quoted context omitted.

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 mos…

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

Re: GitHub's online schema migration for MySQL

#84

Post by manigandham »

jamespo wrote: post #63
manigandham wrote: post #59

Earlier quoted context omitted.

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.

Re: GitHub's online schema migration for MySQL

#85

Post by sjansen »

yxhuvud wrote: post #75
perlgeek wrote: post #33

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.

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.

Re: GitHub's online schema migration for MySQL

#86

Post by gaius »

tomnipotent wrote: post #82
gaius wrote: post #65

Earlier quoted context omitted.

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!

Re: GitHub's online schema migration for MySQL

#87

Post by sciurus »

thathoo wrote: post #78

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

Re: GitHub's online schema migration for MySQL

#88

Post by sciurus »

yxhuvud wrote: post #75
perlgeek wrote: post #33

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.

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

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

Re: GitHub's online schema migration for MySQL

#89

Post by zmoazeni »

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

Re: GitHub's online schema migration for MySQL

#90

Post by zmoazeni »

perlgeek wrote: post #42
codedokode wrote: post #36

Earlier quoted context omitted.

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)

Post reply on HN