3 ms·

It has been so long since I used MySQL (and that’s for the better). Does MySQL still break when running ALTER TABLE on a huge table? One thing I don’t like ab

by didip 5y ago

It has been so long since I used MySQL (and that’s for the better). Does MySQL still break when running ALTER TABLE on a huge table? One thing I don’t like about MySQL is that it has too many foot gun.

zinodaur 5y ago
> Does MySQL still break when running ALTER TABLE on a huge table? Yes it does. We've been stealing bits from old fields to avoid running alter table IDK if postgres does it any better though, a thing I've noticed from maxing out databases is that many features just don't work any more
throwawayboise 5y ago
Create the new table. Dump and load from the old table. Drop the old table. Rename the new table.
- jean_rwgps 5y ago
  While you tell your users to come back in 2 hours?
shlomi-noach 5y ago
Online schema change solutions have been around for over the past decade and are commonly used to ALTER TABLE with no downtime (or with minimal interruption) on the largest deployments of MySQL today. The two most common solutions are pt-online-schema-change and gh-ost, and if you are running MySQL today and still running direct ALTER TABLE suffering outage, then you're in for a pleasant change. On top of that, most MySQL ALTER TABLE operations with InnoDB tables support non-blocking, lockless operation as well. My main concern with these is that they're still replicated sequentially leading to replication lags. MySQL is also slowly adding "Instant DDL", currently still limited to just a few types of changes. Disclosure: I authored gh-ost (at GitHub), oak-online-alter-table (the original schema change tool) and am a maintainer for Vitess and working on online schema changes in Vitess. Links: - https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc... - https://github.com/github/gh-ost https://github.com/github/gh-ost - Past HN discussion: https://news.ycombinator.com/item?id=16982986 https://news.ycombinator.com/item?id=16982986 - https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op... - https://vitess.io/docs/user-guides/schema-changes/ https://vitess.io/docs/user-guides/schema-changes/ Edited for formatting.

by didip 5y ago

zinodaur 5y ago

> Does MySQL still break when running ALTER TABLE on a huge table? Yes it does. We've been stealing bits from old fields to avoid running alter table IDK if postgres does it any better though, a thing I've noticed from maxing out databases is that many features just don't work any more

throwawayboise 5y ago

Create the new table. Dump and load from the old table. Drop the old table. Rename the new table.

jean_rwgps 5y ago
While you tell your users to come back in 2 hours?

shlomi-noach 5y ago

Online schema change solutions have been around for over the past decade and are commonly used to ALTER TABLE with no downtime (or with minimal interruption) on the largest deployments of MySQL today. The two most common solutions are pt-online-schema-change and gh-ost, and if you are running MySQL today and still running direct ALTER TABLE suffering outage, then you're in for a pleasant change. On top of that, most MySQL ALTER TABLE operations with InnoDB tables support non-blocking, lockless operation as well. My main concern with these is that they're still replicated sequentially leading to replication lags. MySQL is also slowly adding "Instant DDL", currently still limited to just a few types of changes. Disclosure: I authored gh-ost (at GitHub), oak-online-alter-table (the original schema change tool) and am a maintainer for Vitess and working on online schema changes in Vitess. Links: - https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html https://www.percona.com/doc/percona-toolkit/3.0/pt-online-sc... - https://github.com/github/gh-ost https://github.com/github/gh-ost - Past HN discussion: https://news.ycombinator.com/item?id=16982986 https://news.ycombinator.com/item?id=16982986 - https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-op... - https://vitess.io/docs/user-guides/schema-changes/ https://vitess.io/docs/user-guides/schema-changes/ Edited for formatting.