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

51–60 of 96 posts

Re: GitHub's online schema migration for MySQL

#51

Post by killbrad »

shlomi-noach wrote: post #45
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 wis…

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

Re: GitHub's online schema migration for MySQL

#52

Post by michaeldejong »

pkulak wrote: post #5

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

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!

Re: GitHub's online schema migration for MySQL

#53

Post by michaeldejong »

josegonzalez wrote: post #13
pkulak wrote: post #5

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

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.

Re: GitHub's online schema migration for MySQL

#54

Post by rimliu »

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.

I think this is answered in the second sentence.

Re: GitHub's online schema migration for MySQL

#56

Post by radicality »

killbrad wrote: post #50
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).

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.

Re: GitHub's online schema migration for MySQL

#57

Post by timewarrior »

toomanybeersies wrote: post #32
timewarrior wrote: post #29

Earlier quoted context omitted.

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

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?

Re: GitHub's online schema migration for MySQL

#58

Post by shlomi-noach »

killbrad wrote: post #51
shlomi-noach wrote: post #45

Earlier quoted context omitted.

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

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.

Re: GitHub's online schema migration for MySQL

#59

Post by manigandham »

rimliu wrote: post #54
manigandham wrote: post #41

Earlier quoted context omitted.

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

Re: GitHub's online schema migration for MySQL

#60

Post by qaq »

shlomi-noach wrote: post #44
qaq wrote: post #4

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

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