GitHub's online schema migration for MySQL • Hacker News Board

Re: GitHub's online schema migration for MySQL

#91

Post by zmoazeni » Thu, May 03, 2018, 5:33 PM UTC

codedokode wrote: post #36

pwnna wrote: post #35

Earlier quoted context omitted.

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?

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

Re: GitHub's online schema migration for MySQL

#92

Post by zmoazeni » Thu, May 03, 2018, 5:46 PM UTC

manigandham wrote: post #41

viraptor wrote: post #28

Earlier quoted context omitted.

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.

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.

Re: GitHub's online schema migration for MySQL

#93

Post by viraptor » Thu, May 03, 2018, 10:07 PM UTC

manigandham wrote: post #84

jamespo wrote: post #63

Earlier quoted context omitted.

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.

Re: GitHub's online schema migration for MySQL

#94

Post by tomnipotent » Fri, May 04, 2018, 12:26 AM UTC

gaius wrote: post #86

tomnipotent wrote: post #82

Earlier quoted context omitted.

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.

Re: GitHub's online schema migration for MySQL

#95

Post by gaius » Fri, May 04, 2018, 5:11 AM UTC

tomnipotent wrote: post #94

gaius wrote: post #86

Earlier quoted context omitted.

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?

Re: GitHub's online schema migration for MySQL

#96

Post by sa46 » Fri, May 04, 2018, 7:39 AM UTC

cup-of-tea wrote: post #46

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.

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?

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.