3 ms·

"Cannot online add a new column" This is false. https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid https:

by mt42or 9y ago

"Cannot online add a new column" This is false. https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-... Very weird.

MarkusWinand 9y ago
From https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-...: Add column In-Place?: *Yes* Rebuilds Table?: *Yes* Permits Concurrent DML?: *Yes* (Concurrent DML is not permitted when adding an auto-increment column.) Only Modifies Metadata?: *No* Data is reorganized substantially, making it an expensive operation. In practice, the last no is a serious problem.
- mt42or 9y ago
  Could you elaborate why it prevents online DDL ?
  - MarkusWinand 9y ago
    I think the point of the article is that adding an index to a big table with lots of writes is practically not possible: Quoting from the article: real problem for big tables as adding a few columns to our biggest tables started to take 2+ hours or sometimes was completely unpredictable and exceeded our announced downtime windows. Apparently, adding a column was even a problem during a maintenance window because the runtime was even longer than they expected. Compared to PostgreSQL: As long as the new column is NULL and does not have a default value, it’s in practice a no-op to add it. No matter if your table size is 100MB or 100GB
    - tveita 9y ago
      The linked 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... generally works really well though, and in the simplest case it's as easy as following the usage example. I'm surprised that they knew about it yet didn't invest the time to learn it.

3 ms·

"Cannot online add a new column" This is false. https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid https:

by mt42or 9y ago

MarkusWinand 9y ago
From https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-...: Add column In-Place?: *Yes* Rebuilds Table?: *Yes* Permits Concurrent DML?: *Yes* (Concurrent DML is not permitted when adding an auto-increment column.) Only Modifies Metadata?: *No* Data is reorganized substantially, making it an expensive operation. In practice, the last no is a serious problem.
- mt42or 9y ago
  Could you elaborate why it prevents online DDL ?
  - MarkusWinand 9y ago
    I think the point of the article is that adding an index to a big table with lots of writes is practically not possible: Quoting from the article: real problem for big tables as adding a few columns to our biggest tables started to take 2+ hours or sometimes was completely unpredictable and exceeded our announced downtime windows. Apparently, adding a column was even a problem during a maintenance window because the runtime was even longer than they expected. Compared to PostgreSQL: As long as the new column is NULL and does not have a default value, it’s in practice a no-op to add it. No matter if your table size is 100MB or 100GB
    - tveita 9y ago
      The linked 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... generally works really well though, and in the simplest case it's as easy as following the usage example. I'm surprised that they knew about it yet didn't invest the time to learn it.