Use mysql 8.0 INSTANT DDL if supported #1198
Description
Hi friends,
I would like to propose a new feature (likely on by default) where gh-ost attempts to apply the DDL change first (but with an assertion that it must be INSTANT). This is particularly targeted at MySQL 8.0+ users, but will likely work on some earlier versions as well (Aurora 5.7 supports instant changes, although I've not specifically tested with the instant assertion..).
I am happy to contribute a patch for it - but per your request I am opening an issue here first to discuss :-)
I hacked up something just now to show that it looks easy enough to add:
https://github.com/github/gh-ost/compare/master...morgo:gh-ost:attempt-instant-ddl?expand=1
For an example usage:
go run . \
--max-load=Threads_running=25 \
--critical-load=Threads_running=1000 \
--chunk-size=1000 \
--user="msandbox" \
--password="msandbox" \
--host="127.0.0.1" \
--port=8031 \
--allow-on-master \
--database="test" \
--table="customers" \
--verbose \
--initially-drop-old-table \
--ok-to-drop-table \
--execute --initially-drop-ghost-table --alter="add newcol9 int"
2022-11-09 20:17:59 INFO starting gh-ost
2022-11-09 20:17:59 INFO Migrating `test`.`customers`
2022-11-09 20:17:59 INFO inspector connection validated on 127.0.0.1:8031
2022-11-09 20:17:59 INFO User has SUPER, REPLICATION SLAVE privileges, and has ALL privileges on `test`.*
2022-11-09 20:17:59 INFO binary logs validated on 127.0.0.1:8031
2022-11-09 20:17:59 INFO Restarting replication on 127.0.0.1:8031 to make sure binlog settings apply to replication thread
2022-11-09 20:17:59 INFO Inspector initiated on mtocker-macbookpro.local:8031, version 8.0.31
2022-11-09 20:17:59 INFO Table found. Engine=InnoDB
2022-11-09 20:17:59 INFO Estimated number of rows via EXPLAIN: 9
2022-11-09 20:17:59 INFO Recursively searching for replication master
2022-11-09 20:17:59 INFO Master found to be mtocker-macbookpro.local:8031
2022-11-09 20:17:59 INFO log_slave_updates validated on 127.0.0.1:8031
2022-11-09 20:17:59 INFO Attempting to execute ALTER TABLE as INSTANT DDL
2022-11-09 20:17:59 INFO applier connection validated on 127.0.0.1:8031
2022-11-09 20:17:59 INFO applier connection validated on 127.0.0.1:8031
2022-11-09 20:17:59 INFO will use time_zone='SYSTEM' on applier
2022-11-09 20:17:59 INFO Examining table structure on applier
2022-11-09 20:17:59 INFO Applier initiated on mtocker-macbookpro.local:8031, version 8.0.31
2022-11-09 20:17:59 INFO INSTANT DDL Query is: ALTER /* gh-ost */ TABLE `test`.`customers` add newcol9 int, ALGORITHM=INSTANT
2022-11-09 20:17:59 INFO Success! Table `test`.`customers` migrated instantly
2022-11-09 20:17:59 INFO Tearing down inspector
2022-11-09 20:17:59 INFO Tearing down applier
# Done