News for hackers. Stuff that matters.
Re:GitHub's online schema migration for MySQL by qaq on Wednesday May 02, 2018 @11:27PM (#16983142)
You can jump through hoops or just use an RDBMS that supports transactional DDL.
Re:GitHub's online schema migration for MySQL by shlomi-noach on Thursday May 03, 2018 @02:23AM (#16983766)
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...
Re:GitHub's online schema migration for MySQL by qaq on Thursday May 03, 2018 @04:30AM (#16984248)
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.
Re:GitHub's online schema migration for MySQL by dtech on Thursday May 03, 2018 @01:05AM (#16983474)
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.
Re:GitHub's online schema migration for MySQL by c2h5oh on Thursday May 03, 2018 @01:07AM (#16983485)
Depends on a migration. Postgres can add / drop a column to a table with a billion rows in milliseconds as long as you don't provide a default value for the new column.
Re:GitHub's online schema migration for MySQL by anarazel on Thursday May 03, 2018 @01:10AM (#16983495)
And in v11, even if there's a default column!
Re:GitHub's online schema migration for MySQL by viraptor on Thursday May 03, 2018 @01:31AM (#16983555)
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)
Re:GitHub's online schema migration for MySQL by manigandham on Thursday May 03, 2018 @02:17AM (#16983738)
...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 by zmoazeni on Thursday May 03, 2018 @01:46PM (#16988071)
There is a big difference. You're only considering the "slowness" from the application perspective of querying a table without the index.

You need to also realize that database server itself takes a load hit when it kicks off these operations on large tables. I'm not sure on PostgreSQL, but I know sometimes you can not immediately cancel these operations in the middle on MySQL (it also takes time to revert).

With gh-ost you have full control over how fast this process goes and can even pause/resume it if you're experiencing issues.

But let's say it IS instantly cancelled. You're also ignoring the fact that you will take the same hit on any replicas if this statement is successful.

Some production environments don't take kindly to having all replicas lagging as the replication thread is blocking on the DDL change. My team has the luxury of being able to have our entire production environment served by a single master server (though we avoid it as much as possible), but it won't be long before we outgrow that and require at least one up to date replica. Many teams are already in that situation and for that, gh-ost is a godsend.

Re:GitHub's online schema migration for MySQL by rimliu on Thursday May 03, 2018 @03:50AM (#16984122)
I think this is answered in the second sentence.
Re:GitHub's online schema migration for MySQL by manigandham on Thursday May 03, 2018 @04:24AM (#16984232)
These same databases have background/concurrent index rebuild features. Change DDL, add index, then switch over.
Re:GitHub's online schema migration for MySQL by jamespo on Thursday May 03, 2018 @05:31AM (#16984493)
so background means the index isn't ready right?
Re:GitHub's online schema migration for MySQL by manigandham on Thursday May 03, 2018 @12:42PM (#16987527)
I'm not sure what the confusion is here. There's no downtime or slow serving as the first comment said because your app shouldn't be using any new columns until they're ready, whether that's through Ghost or transactional DDL + concurrent index rebuild.
Re:GitHub's online schema migration for MySQL by viraptor on Thursday May 03, 2018 @06:07PM (#16990442)
Sure, you can avoid it, but mostly with some effort. Specifically if you're manually changing the column type which is covered by an index, you'd have to:

Add a new column with a new index. Copy the old data over and change the code/add a trigger to keep them synchronised. Change the code to use the new column instead. Remove the sync code/trigger. Drop the old column/index. And you're potentially left with a weird name, because the purpose of the column didn't change, just the type.

Or you could use gh-ost and do it in one go, semi-automated without any code changes, and with the same column name.

Re:GitHub's online schema migration for MySQL by qaq on Thursday May 03, 2018 @04:35AM (#16984274)
Transactional DDL solves the problem for a large % of use cases. I remember a study that had average prod db size among other things and it was something less than 10GB if memory serves.
Re:GitHub's online schema migration for MySQL by mplewis on Thursday May 03, 2018 @01:11AM (#16983498)
Sometimes installing Ghost is a better tactical choice than migrating an entire running system to a new RDBMS.