4 ms·

Beyond a certain size (basically, once the time the migration will take because of the size of the data it applies to is too large), migrations are a heavy inve

by nbm 10y ago

Beyond a certain size (basically, once the time the migration will take because of the size of the data it applies to is too large), migrations are a heavy investment - in elapsed time, I/O, and so forth. As such, they are planned to a degree that CD probably isn't the solution for it (for example, you probably can only have one migration in flight at a time). They aren't done live as a single big process that have the potential to lock all queries/updates over their execution, but rather as a set of smaller steps that don't lock. Facebook has spoken about its online schema change process before - https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932/ https://www.facebook.com/notes/mysql-at-facebook/online-sche... and its follow-up at https://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql-part-2/431123910932 https://www.facebook.com/notes/mysql-at-facebook/online-sche... for example, and I'm sure elsewhere. Most people using MySQL would potentially first use something like https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/2.1/pt-online-sc... instead of trying to create their own. The same principles apply to other data stores that have more rigid schemas.

merb 10y ago
That's not the case for Instagram, they use PostgreSql, they could update tables without any downtime and this is fast. The only problem are migrations which copies data or doesn't just add fields or remove them.
henrikschroder 10y ago
MySQL has online, non-blocking schema changes since version 5.6. But the underlying data file has to be upgraded to the latest format version for it to work first, and to do that in a non-blocking way on a master server you are probably best off running percona toolkit one first. ALTER TABLE --- FORCE does a data file rewrite.
- Rapzid 10y ago
  That's a big "yeah but". You can't rate limit the io. That's huge and the percona tool and LHM offer ways to keep io under control. Along with that you have to pay attention to the live schema update matrix in the docs; many things will upgrade with out locking but require copying the table and this will blast your IO. In addition even with the tools some changes require exclusive table locks. It needs it for a short period of time but if it can't get it because of a long running transaction , queue of transactions, and etc it can block everything up.

by nbm 10y ago

merb 10y ago

That's not the case for Instagram, they use PostgreSql, they could update tables without any downtime and this is fast. The only problem are migrations which copies data or doesn't just add fields or remove them.

henrikschroder 10y ago

MySQL has online, non-blocking schema changes since version 5.6. But the underlying data file has to be upgraded to the latest format version for it to work first, and to do that in a non-blocking way on a master server you are probably best off running percona toolkit one first. ALTER TABLE --- FORCE does a data file rewrite.

Rapzid 10y ago
That's a big "yeah but". You can't rate limit the io. That's huge and the percona tool and LHM offer ways to keep io under control. Along with that you have to pay attention to the live schema update matrix in the docs; many things will upgrade with out locking but require copying the table and this will blast your IO. In addition even with the tools some changes require exclusive table locks. It needs it for a short period of time but if it can't get it because of a long running transaction , queue of transactions, and etc it can block everything up.