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

71–80 of 96 posts

Re: GitHub's online schema migration for MySQL

#71

Post by asaph »

shlomi-noach wrote: post #69
anton_gogolev wrote: post #64

Earlier quoted context omitted.

How could foreign keys ever be a "hassle"?

They can be a hassle when you want to shard your data, having outgrown your single-instance capacity. You will either shard functionally (extracting complete tables to other database servers), in which case FKs will completely break, or horizontally (split rows across database servers), in which case you may or may not be able to still use FKs. They're also a performance impact on large tables since inserts/deletes m…

> You will either shard functionally (extracting complete tables to other database servers)

This isn't sharding. This is vertical partitioning. Sharding is a type of horizontal partitioning.

Reference: https://en.m.wikipedia.org/wiki/Shard_(database_architecture...

Re: GitHub's online schema migration for MySQL

#72

Post by paulryanrogers »

idunno246 wrote: post #17
emilsedgh wrote: post #16

Earlier quoted context omitted.

Probably no. As someone else pointed out, the reason so many similar tools exist for this task on mysql and there's no such tool for postgres is not that postgres isn't as popular. The reason is that this problem is almost non-existent on postgres as many table alterations do not lock the table.

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.

Re: GitHub's online schema migration for MySQL

#73

Post by fipar »

cup-of-tea wrote: post #70
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.

Yeah, but from a data integrity point of view that is horrifying. I guess they must have some very comprehensive testing framework to validate the code. Also, pardon my further ignorance, but if you're not going to use foreign key constraints, what is the point of using a relational db? Why not just a fast key-value store for each index?

You may me able to architect your application to do well-enough without Foreign Keys yet still require features that key-value stores do not provide, like SQL, triggers, etc.

In the specfic case of MySQL, while still horrifying (I agree with you! but it is one of the things you some times have to do at scale), you can create the Foreign Key constraints but then disable their verification and periodically look for violations, as described here: https://www.percona.com/blog/2011/11/18/eventual-consistency...

Re: GitHub's online schema migration for MySQL

#74

Post by fipar »

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.

That is true, but I wanted to share another angle that may or may not affect PostgreSQL while it continues to affect MySQL even as it has crash-safe (though not transactional) DDL now: these schema changes are online for the master, but are not replication-aware and can have impact in replication delay on servers down the hierarchy.

For this reason alone I think we'll continue to use schema-change tools on MySQL even if the server itself becomes better at those.

In the specific case of gh-ost, another good point is that migrations can be completely paused, which in MySQL is not true of online DDL.

Re: GitHub's online schema migration for MySQL

#75

Post by yxhuvud »

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.

Re: GitHub's online schema migration for MySQL

#77

Post by bpicolo »

kd22 wrote: post #76

Can someone shed some light on how this tool compares to something like Flyway?

It's an alternative to e.g. pt-online-schema-change [0]. The problem is that, for very large mysql tables / clusters, running DDL against the tables live will lock up reads/writes against the table for ages. These tools allow you to run those changes without taking downtime.

https://www.percona.com/doc/percona-toolkit/LATEST/pt-online...

Re: GitHub's online schema migration for MySQL

#79

Post by zzzeek »

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?

Are you referring to ON UPDATE cascades? Mutable/natural primary key values are not very common these days especially in a DB like MySQL. Incrementing integers are most common.

Re: GitHub's online schema migration for MySQL

#80

Post by zzzeek »

cup-of-tea wrote: post #70
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.

Yeah, but from a data integrity point of view that is horrifying. I guess they must have some very comprehensive testing framework to validate the code. Also, pardon my further ignorance, but if you're not going to use foreign key constraints, what is the point of using a relational db? Why not just a fast key-value store for each index?

The application still has foreign key references and JOIN, which you don't get in a k/v store, you just don't have the actual CONSTRAINT. MySQL worked this way for literally decades with the MyISAM storage engine that does not have referential integrity constraints, it just allows the FOREIGN KEY syntax to pass silently.
Post reply on HN