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

31–40 of 96 posts

Re: GitHub's online schema migration for MySQL

#31

Post by toomanybeersies »

stakecounter wrote: post #24
analogmemory wrote: post #21

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…

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.

Re: GitHub's online schema migration for MySQL

#32

Post by toomanybeersies »

timewarrior wrote: post #29
sethhochberg wrote: post #15

Earlier quoted context omitted.

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…

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.

Re: GitHub's online schema migration for MySQL

#33

Post by perlgeek »

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.

Re: GitHub's online schema migration for MySQL

#34

Post by pwnna »

throwawaypls wrote: post #19

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.

Re: GitHub's online schema migration for MySQL

#35

Post by pwnna »

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.

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.

Re: GitHub's online schema migration for MySQL

#36

Post by codedokode »

pwnna wrote: post #35
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.

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?

Re: GitHub's online schema migration for MySQL

#37

Post by codedokode »

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…

Also you cannot store foreign keys in JSON.

Re: GitHub's online schema migration for MySQL

#38

Post by y4mi »

toomanybeersies wrote: post #31
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…

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

Re: GitHub's online schema migration for MySQL

#39

Post by manigandham »

stakecounter wrote: post #24
analogmemory wrote: post #21

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…

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.

Re: GitHub's online schema migration for MySQL

#40

Post by Existenceblinks »

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

Post reply on HN