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?
GitHub's online schema migration for MySQL
21–30 of 96 posts
Re: GitHub's online schema migration for MySQL
#22You can jump through hoops or just use an RDBMS that supports transactional DDL.
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
#23You can jump through hoops or just use an RDBMS that supports transactional DDL.
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.
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
#24So 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 tool for handling this, which wrapped the creation of a new 'ghost' table (which has the target schema you want), rate limited writes from original table to ghost table, triggered writes from original table to ghost table as new writes came in, and finally a table rename from the ghost table back to the original table name in order to perform the full migration with no data loss or outage.
Sounds like this tool is doing something similar but avoiding the use of triggers for flexibility.
Re: GitHub's online schema migration for MySQL
#25Earlier quoted context omitted.
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.
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.
And in v11, even if there's a default column!
Re: GitHub's online schema migration for MySQL
#26You can jump through hoops or just use an RDBMS that supports transactional DDL.
Sometimes installing Ghost is a better tactical choice than migrating an entire running system to a new RDBMS.
Re: GitHub's online schema migration for MySQL
#27So 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…
Ah ok! This sound like a great tool then. I have no need for it, but good one to star for a day when I might need it :)
Re: GitHub's online schema migration for MySQL
#28You can jump through hoops or just use an RDBMS that supports transactional DDL.
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.
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
#29Earlier quoted context omitted.
We recently started investing in Postgres because of support of JSON fields and nested indexes in those fields. Should we have chosen MySQL?
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.
Re: GitHub's online schema migration for MySQL
#30Earlier 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…
This is great to know. I usually am able to manage without transactions. So alerts should be pretty fast.