github / github/gh-ost

Feature: Potentially reduce production impact while drastically reducing migration time using transportable tablespaces

Open
#261 4 comments 0 reactions 0 assignees View on GitHub
enhancement feature performance
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.