Online DDL strategies
Vitess supports both managed, online schema migrations (aka Online DDL) as well as unmanaged migrations. How Vitess runs a schema migration depends on the DDL strategy. Vitess allows these strategies:
vitess
(formerly known asonline
): utilizes Vitess's built-in VReplication mechanism. This is the preferred strategy in Vitess.gh-ost
: uses 3rd party GitHub's gh-ost tool.pt-osc
: uses 3rd party Percona's pt-online-schema-change as part of Percona Toolkit.pt-osc
strategy is experimental.mysql
: managed by the Online DDL scheduler, but executed via normal MySQL statement. Whether it is blocking or not is up to the specific query.direct
: unmanaged. The direct apply of DDL to your database. Whether it is blocking or not is up to the specific query.
CREATE
and DROP
are managed in the same way, by Vitess, whether strategy is vitess
, gh-ost
or pt-osc
.
See also ddl_strategy flags.
Specifying a DDL strategy #
You can apply DDL strategies to your schema changes in these different ways:
- The command
vtctldclient ApplySchema
takes a--ddl-strategy
flag. The strategy applies to the specific changes requested in the command. The following example applies thevitess
strategy to three migrations submitted together:
$ vtctldclient ApplySchema --ddl-strategy "vitess" --sql "ALTER TABLE demo MODIFY id bigint UNSIGNED; CREATE TABLE sample (id int PRIMARY KEY); DROP TABLE another;" commerce
ab185fdf_6e46_11ee_8f23_0a43f95f28a3
- Set
vtgate --ddl_strategy
flag. Migrations executed from withinvtgate
will use said strategy.
$ vtgate --ddl_strategy="vitess"
$ mysql
mysql> alter table corder force;
+--------------------------------------+
| uuid |
+--------------------------------------+
| 2015f08d_6e46_11ee_a918_0a43f95f28a3 |
+--------------------------------------+
- Set the
@@ddl_strategy
session variable to override the value ofvtgate
's--ddl_strategy
flag, for the current session.
mysql> set @@ddl_strategy="vitess --postpone-completion --allow-concurrent";
Query OK, 0 rows affected (0.01 sec)
mysql> alter table corder force;
+--------------------------------------+
| uuid |
+--------------------------------------+
| 861f7de9_6e46_11ee_a918_0a43f95f28a3 |
+--------------------------------------+
Choosing a DDL strategy #
Different strategies have different behavior for ALTER
statements. Sections below first break down specific handling and notes for each strategy, followed by an evaluation of the differences.
vitess #
The vitess
strategy invokes Vitess's built in VReplication mechanism. It is the mechanism behind resharding, materialized views, imports from external databases, and more. VReplication migrations use the same logic for copying data as do other VReplication operations, and as such the vitess
strategy is known to be compatible with overall Vitess behavior. VReplication is authored by the maintainers of Vitess.
VReplication migrations enjoy the general features of VReplication:
- Seamless integration with Vitess.
- Seamless use of the throttler mechanism.
- Visibility into internal working and status of VReplication.
- Agnostic to planned reparenting and to unplanned failovers. A migration will resume from point of interruption shortly after a new primary is available.
VReplication migrations further:
- Are revertible: you may switch back to the pre-migration schema without losing any data accumulated during and post migration.
- Support a wider range of schema changes. For example, while
gh-ost
has a strict requirement for a shared unique key pre/post migration,vitess
migrations may work with different keys, making it possible to modify a table'sPRIMARY KEY
without having to rely on an additionalUNIQUE KEY
.
gh-ost #
gh-ost was developed by GitHub as a lightweight and safe schema migration tool.
To be able to run online schema migrations via gh-ost
:
- If you're on Linux/amd64 architecture, and on
glibc
2.3
or similar, there are no further dependencies. Vitess comes with a built-ingh-ost
binary, that is compatible with your system. Note that the Vitess Docker images use this architecture, andgh-ost
comes pre-bundled and compatible. - On other architectures:
- Have
gh-ost
executable installed - Run
vttablet
with--gh-ost-path=/full/path/to/gh-ost
flag
- Have
Vitess automatically creates a MySQL account for the migration, with a randomly generated password. The account is destroyed at the end of the migration.
Vitess takes care of setting up the necessary command line flags. It automatically creates a hooks directory and populates it with hooks that report gh-ost
's progress back to Vitess. You may supply additional flags for your migration as part of @@ddl_strategy
session variable (using VTGate
) or --ddl-strategy
command line flag (using vtctldclient
). Examples:
set @@ddl_strategy='gh-ost --max-load Threads_running=200';
set @@ddl_strategy='gh-ost --max-load Threads_running=200 --critical-load Threads_running=500 --critical-load-hibernate-seconds=60 --default-retries=512';
vtctldclient --ddl-strategy "gh-ost --allow-nullable-unique-key --chunk-size 200" ...
Note: Do not override the following flags: alter, database, table, execute, max-lag, force-table-names, serve-socket-file, hooks-path, hooks-hint-token, panic-flag-file
. Overriding any of these may cause Vitess to lose control and track of the migration, or even to migrate the wrong table.
gh-ost
throttling is done via Vitess's own tablet throttler, based on replication lag.
Using pt-online-schema-change #
pt-online-schema-change is part of Percona Toolkit, a set of Perl scripts. To be able to use pt-online-schema-change
, you must have the following setup on all your tablet servers (normally tablets are co-located with MySQL on same host and so this implies setting up on all MySQL servers):
pt-online-schema-change
tool installed and is executable- Perl
libdbi
andlibdbd-mysql
modules installed. e.g. on Debian/Ubuntu,sudo apt-get install libdbi-perl libdbd-mysql-perl
- Run
vttablet
with-pt-osc-path=/full/path/to/pt-online-schema-change
flag.
Note that on Vitess Docker images, pt-online-schema-change
and dependencies are pre-installed.
Vitess automatically creates a MySQL account for the migration, with a randomly generated password. The account is destroyed at the end of the migration.
Vitess takes care of supplying the command line flags, the DSN, the username & password. It also sets up PLUGINS
used to communicate migration progress back to the tablet. You may supply additional flags for your migration as part of @@ddl_strategy
session variable (using VTGate
) or -ddl-strategy
command line flag (using vtctldclient
). Examples:
set @@ddl_strategy='pt-osc --null-to-not-null';
set @@ddl_strategy='pt-osc --max-load Threads_running=200';
vtctldclient ApplySchema --ddl-strategy "pt-osc --alter-foreign-keys-method auto --chunk-size 200" ...
Vitess tracks the state of the pt-osc
migration. If it fails, Vitess makes sure to drop the migration triggers. Vitess keeps track of the migration even if the tablet itself restarts for any reason. Normally that would terminate the migration; Vitess will cleanup the triggers if so, or will happily let the migration run to completion if not.
Do not override the following flags: alter, pid, plugin, dry-run, execute, new-table-name, [no-]drop-new-table, [no-]drop-old-table
.
pt-osc
throttling is done via Vitess's own tablet throttler, based on replication lag, and via a pt-online-schema-change
plugin.
pt-online-schema-change
is experimentalComparing the options #
There are pros and cons to using any of the strategies. Some notable differences:
General #
- All three options mimic an
ALTER TABLE
statement by creating and populating a shadow/ghost table behind the scenes, slowly bringing it up to date, and finally switching between the original and shadow tables. - All three options utilize the Vitess throttler.
Support #
- VReplication (
vitess
strategy) is internal to Vitess and supported by the Vitess maintainers. gh-ost
andpt-online-schema-change
are not supported by the Vitess maintainers.
Setup #
- VReplication is part of Vitess
- A
gh-ost
binary is embedded within the Vitess binary, compatible withglibc 2.3
andLinux/amd64
. The user may choose to use their owngh-ost
binary, configured with--gh-ost-path
.The embeddedgh-ost
binary will be removed in future versions. The user will need to install their owngh-ost
binary. pt-online-schema-change
is not included in Vitess, and the user needs to set it up on tablet hosts.- Note that on Vitess Docker images,
pt-online-schema-change
and dependencies are pre-installed.
- Note that on Vitess Docker images,
Load #
pt-online-schema-change
uses triggers to propagate changes. This method is traditionally known to generate high load on the server. Both VReplication andgh-ost
tail the binary logs to capture changes, and this approach is known to be more lightweight.- When throttled,
pt-online-schema-change
still runs trigger actions, whereas both VReplication andgh-ost
cease transfer of data (they may keep minimal bookkeeping operations).
Cut-over #
- All strategies use an atomic cut-over based on MySQL locking. At the end of the migration, the tables are switched, and incoming queries are momentarily blocked, but not lost.
- In addition,
vitess
offers a buffering layer, that reduces the contention on the database server at cut-over time.
MySQL compatibility #
pt-online-schema-change
partially supports foreign keys. Neithergh-ost
norVReplication
support foreign keys.
Vitess functionality comparison #
Strategy | Managed | Online | Trackable | Declarative | Revertible | Recoverable |
---|---|---|---|---|---|---|
direct | No | MySQL* | No | No | No | No |
mysql | Yes | MySQL* | Yes | Yes | No | No |
pt-osc | Yes | Yes* | Yes | Yes | CREATE,DROP | No* |
gh-ost | Yes | Yes* | Yes+ | Yes | CREATE,DROP | No* |
vitess | Yes | Yes* | Yes+ | Yes | CREATE,DROP,ALTER | Yes |
- Managed: whether Vitess schedules and operates the migration
- Online:
- MySQL supports limited online (
INPLACE
) DDL as well asINSTANT
DDL. See support chart.INSTANT
DDL is instant on both primary and replicas.INPLACE
is non-blocking on parent but serialized on replicas, causing replication lag. Otherwise migrations are blocking on both primary and replicas. gh-ost
,vitess
do not support foreign keyspt-osc
has support for foreign keys (may apply collateral blocking operations)
- MySQL supports limited online (
- Trackable: able to determine migration state (
ready
,running
,complete
etc)vitess
andgh-ost
strategies also makes available progress % and ETA seconds
- Declarative: support
--declarative
flag - Revertible:
vitess
strategy supports revertibleALTER
statements (orALTER
s implied by--declarative
migrations). All managed strategies supports revertibleCREATE
andDROP
. - Recoverable: a
vitess
migration interrupted by planned/unplanned failover, automatically resumes work from point of interruption.gh-ost
andpt-osc
will not resume after failover, but Vitess will automatically retry the migration (by marking the migration as failed and by initiating aRETRY
), exactly once for any migration.