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

41–50 of 96 posts

Re: GitHub's online schema migration for MySQL

#41

Post by manigandham »

viraptor wrote: post #28
dtech wrote: post #22

Earlier quoted context omitted.

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.

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.

Re: GitHub's online schema migration for MySQL

#42

Post by perlgeek »

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?

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.

Re: GitHub's online schema migration for MySQL

#43

Post by stakecounter »

manigandham wrote: post #39
stakecounter wrote: post #24

Earlier quoted context omitted.

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…

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

Re: GitHub's online schema migration for MySQL

#45

Post by shlomi-noach »

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.

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.

Re: GitHub's online schema migration for MySQL

#46

Post by cup-of-tea »

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?

Re: GitHub's online schema migration for MySQL

#47

Post by MichaelGlass »

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.

Re: GitHub's online schema migration for MySQL

#48

Post by Chilinot »

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?

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.

Re: GitHub's online schema migration for MySQL

#49

Post by barrkel »

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?

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.

Re: GitHub's online schema migration for MySQL

#50

Post by killbrad »

MichaelGlass wrote: post #47

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

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).
Post reply on HN