Feature: Potentially reduce production impact while drastically reducing migration time using transportable tablespaces
- Dominant language
- Go
- Stars
- 13.6k
- Forks
- 1.4k
- Avg merge
- 2h 31m
- Merged PRs (30d)
- 4
Description
As `gh-ost` is very flexible in where and how it can migrate a new table, we can add MySQL transportable tablespaces in the `gh-ost` process to not have to rebuild very big tables on the whole replication tree which would reduce impact, but also improve migration time considerably.
We could do this:
1. MySQL Replication tree: `Master`, `ProdSlave`, `NonProdSlave`
2. Start a migration, read binary logs from `NonProdSlave`, create new table and copy all rows in `NonProdSlave` and process the binary log as as necessary, but try to use as much resources as possible and do not throttle :-). (You might as variant just to an `ALTER TABLE` statement if it's faster)
3. When this is finished, `NonProdSlave` has the new table structure and all data in it, but changes will still happen on the master.
4. `NonProdSlave`: Do `FLUSH TABLE .. FOR EXPORT`. This can take a while and will ensure change buffer and dirty pages are merged and the tablespace is clean. Keep the lock by keeping the connection open.
5. `Master` & `ProdSlave`: Create the empty table with `SQL_LOG_BIN=0`
6. `Master` & `ProdSlave`: Copy the `table.{ibd,cfg}` files from `NonProdSlave`
7. `Master` & `ProdSlave`: `ALTER TABLE table IMPORT TABLESPACE`
8. `NonProdSlave`: `UNLOCK TABLE`
9. Continue `gh-ost` magic as if it was performing the migration directly from the master and process binary logs.
This however changes the architecture of `gh-ost` as now only MySQL client access is necessary but copying of files have to become possible somehow.
Documentation:
- https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html
- Examples: https://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html
Limitations:
- MySQL 5.6 >=
- Only supported when major versions are the same
- `FLUSH TABLES ... FOR EXPORT` makes a table readonly. The replica you run it on will start to lag.
Contributor guide
Assessment
This issue has not been assessed yet.