Online/inplace DDL operations

[hermanlee]

Issue by spetrunia Tuesday Jun 30, 2015 at 17:57 GMT Originally opened as https://github.com/MySQLOnRocksDB/mysql-5.6/issues/87

---

This task is about adding support for online (and/or in-place) DDL operations for MyRocks.

What can be supported

• Some DDL changes have no effect on how the data is stored and so can be trivially supported: * Default value changes * N in char(N) \ possibly something else
• Some changes may require data format change \ Should we try support for adding/removing non-key fields?
• Some changes allow for in-place operation: \ The most important are ADD INDEX/DROP INDEX

  How to support it

SQL layer will make the following calls:

h->check_if_supported_inplace_alter() // = HA_ALTER_INPLACE... h->prepare_inplace_alter_table() ... h->commit_inplace_alter_table

The first is to inquire whether the storage engine supports in-place operation for the given ALTER TABLE command. The latter are to actually made the change.

[alxyang]

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

we need to revisit exactly which of these we can/want to support. From what I understand, we want to make CREATE INDEX fully online (still allows DML statements to occur).

Also on the roadmap would be renaming columns, default value changes, changing default auto increment value, adding/dropping columns. For adding/dropping columns, it's unclear to me exactly how online DDL will be faster since it still needs to copy the table under the hood, even according to the documentation above for InnoDB. Need to investigate more here.

[baotiao]

hello @alxyang . what's going on with online DDL feature?

[yoshinorim]

Currently we have higher priorities for 1. supporting MyRocks in MySQL 8.0 and 2. improving MyRocks/RocksDB performance. About online schema change, there are several existing solutions like https://github.com/github/gh-ost so it is not the highest priority. We will be likely to start supporting major features like online schema change based on MySQL 8.0, not 5.6.

[baotiao]

@yoshinorim Thank you. we are going to add this feature in MySQL 5.7, if it work perfectly, we can summit a path for you

[yoshinorim]

@baotiao That is great. Please submit a pull request once it is ready!