michaeldejong
Joined 8 years ago
Karma
23
Submissions
8
Member since
Mar 24
Recent submissions
Comment · 3 years ago
Comment · 8 years ago
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.
Comment · 8 years ago
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!
Comment · 8 years ago
Agreed. But to be fair I've not yet encountered such a library/tool yet. All tools I've seen leave the active table alone, and simply create a new table, do all their work there, and finally somehow switch the two tables (either in naming or routing queries). Some even perform the work on a different database server.
Comment · 8 years ago
I agree with that. If your database doesn't care about a "schema", there usually are no operations available which change the schema of your data. Unfortunately that also means that if your database doesn't consider the schema its problem, when you do finally want to change the implicit schema of your data, it becomes the dev's problem.
Comment · 8 years ago
That's not great either. Moving the "master" role from one server to a replica isn't instantaneous (meaning you have a period of read-only state), and while your replica is performing the schema operation you still need to keep the data in sync with changes made on the master server (which to the best of my knowledge is non-trivial).
There are tools that "replay" data changes though: - FB's OSC (https://www.facebook.com/notes/mysql-at-facebook/online-sche...) - GH's gh-ost (https://github.com/github/gh-ost)
Comment · 8 years ago
Indeed. It seems there are many such tools available for MySQL: Openark kit [0] for instance, whose author also brought us gh-ost. Unfortunately most tools focus on just MySQL, and none really have an answer when you want to use (and enforce) foreign key constraints. Although it's hard to say, I suspect the users of these tools have "given up" on foreign key constraints.
[0] http://code.openark.org/forge/openark-kit [1] https://github.com/github/gh-ost
Comment · 8 years ago
Researcher/author of a tool [0,1] also attempting to tackle this problem here.
Unfortunately zero-downtime schema changes are even more complex than suggested here. Although the expand-contract method as described in the post is a good approach to tackling this problem, the mere act of altering a database table that is in active use is a dangerous one. I've already found that some trivial operations such as adding a new column to an existing table can block database clients from reading from that table through full table locks for the duration of the schema operation [2].
In many cases it's safer to create a new table, copy data over from the old table to the new table, and switch clients over. However this introduces a whole new set of problems: keeping data in sync between tables, "fixing" foreign key constraints, etc.
If there are others researching/building tooling for this problem, I'd love to hear from you.
[0] http://github.com/quantumdb/quantumdb
[1] https://speakerdeck.com/michaeldejong/icse-17-zero-downtime-...
[2] http://blog.minicom.nl/blog/2015/04/03/revisiting-profiling-...
[0] https://github.com/quantumdb/quantumdb