Online schema change (OSC) is the ability to alter a table without blocking reads or writes to the table. In some situations, the database server provides the ability to perform an online alteration internally, but in other cases an external OSC tool must be used. Skeema supports both approaches, and provides flexibility for conditionally executing external OSC tools based on environment (e.g. prod/stage/dev) and table size.
Using external online schema change tools
The alter-wrapper option allows you to configure an arbitrary external command to use, in place of running ALTER TABLE directly. This can be used to invoke an OSC tool, such as pt-online-schema-change, spirit, or gh-ost. Alternatively, you can configure alter-wrapper to execute any custom script/program, allowing you to use any programmatic logic you wish, such as conditionally deciding when to run an OSC tool, or perhaps communicating with a scheduler / job queue.
As always, only skeema push actually runs the configured alter-wrapper command. Meanwhile the output of skeema diff will display what command-line would be executed by a push, but it won’t actually be run.
When configuring the alter-wrapper command-line, you can use special placeholder variables to dynamically pass values into the command-line. See options with variable interpolation for more information. The following variables are supported by alter-wrapper:
| Variable | Value |
|---|---|
{HOST} | Hostname (or IP) of the database server that this ALTER TABLE targets |
{PORT} | Port number for the database server that this ALTER TABLE targets |
{SOCKET} | Unix domain socket path, if {HOST} is “localhost” |
{SCHEMA} | Schema name containing the table that this ALTER TABLE targets |
{USER} | Database connection username defined by the user option |
{PASSWORD} | Database connection password defined by the password option |
{PASSWORDX} | Behaves like {PASSWORD} when the command-line is executed, but only displays X’s whenever the command-line is displayed on STDOUT |
{ENVIRONMENT} | Environment name from the first positional arg on Skeema’s command-line, or “production” if none specified |
{DDL} | Full ALTER TABLE statement, including all clauses |
{NAME} | Table name that this ALTER TABLE targets |
{TABLE} | Table name that this ALTER TABLE targets (identical to {NAME}) |
{SIZE} | Size of table that this ALTER TABLE targets, in bytes. For tables with no rows, this will be 0, regardless of actual size of the empty table on disk. |
{CLAUSES} | Body of the ALTER TABLE statement, i.e. everything after ALTER TABLE <name> . This is what pt-online-schema-change’s --alter option expects. |
{TYPE} | Always the word “ALTER” in all caps |
{CLASS} | Always the word “TABLE” in all caps |
{CONNOPTS} | Session variables passed through from the connect-options option |
{DIRNAME} | The base name (last path element) of the directory being processed |
{DIRPATH} | The full (absolute) path of the directory being processed |
Always use an absolute path for the alter-wrapper command. Relative paths may not work as expected.
If an SSH tunnel is being used, the wrapper script is executed on the remote SSH host instead of locally. This can be useful for reasons of security/auditing, locking (preventing concurrent execution), uniformity (ensuring the same version of pt-osc or gh-ost), and portability (e.g. removing the need to get pt-osc working on Windows or MacOS).
The alter-wrapper option does not affect CREATE TABLE or DROP TABLE statements; nor does it affect non-table DDL such as CREATE DATABASE or ALTER DATABASE. To execute all DDL (regardless of operation type or object class) through an external script, see ddl-wrapper.
Example: using pt-online-schema-change
To configure Skeema to use Percona’s pt-online-schema-change to perform ALTER TABLE, you might use a .skeema config file line of:
alter-wrapper=/usr/local/bin/pt-online-schema-change --execute --alter {CLAUSES} D={SCHEMA},t={TABLE},h={HOST},P={PORT},u={USER},p={PASSWORDX}
The brace-wrapped variables will automatically be replaced with appropriate values, as described in the previous section. Variable values containing spaces or control characters will be escaped automatically. See the configuration guide for more information.
Bypassing external OSC for small or empty tables
External OSC tools may be unnecessary for small tables. Skeema can be configured to conditionally bypass the alter-wrapper depending on table size by setting alter-wrapper-min-size to a size, measured in bytes. Any table that is smaller than this size will be altered directly by skeema push, rather than executing the alter-wrapper command.
Sizes may be specified with a suffix of “K”, “M”, or “G” to multiply the preceding number by 1024, 1024^2, or 1024^3. See the “size” option type in the configuration guide.
The size comparison is a strict less-than. Since alter-wrapper-min-size defaults to 0, it has no effect by default, since no table can be less than 0 bytes.
To only skip alter-wrapper on empty tables (ones without any rows), set alter-wrapper-min-size to 1. Skeema always treats empty tables as size 0 bytes as a special-case.
Ignoring OSC tool shadow tables or triggers
External OSC tools operate by building a “shadow table” with the new desired structure, and then renaming it in place of the old table. Refer to the ignore regex option documentation to see examples of how to configure these options for use with pt-online-schema-change, spirit, or gh-ost.
Special handling for naming of foreign keys and check constraints
In MySQL, constraints have a schema-wide namespace. Since OSC tools operate by using a shadow table in the same schema as the original table, these tools must use different names for any foreign keys or check constraints when creating the shadow table (which eventually becomes the “new” table) in order to avoid a naming conflict with the “old” table. This means that users of OSC tools will see their constraint names changing automatically whenever a table with constraints is altered by an OSC tool.
By default, Skeema suppresses foreign key diffs that only affect naming, to avoid spurious diffs from prior usage of OSC tools. As of Skeema v1.12.1+, check constraint diffs that only affect naming are similarly suppressed.
If desired, the exact-match option can be supplied in order to override this behavior and force Skeema to emit an ALTER TABLE which restores constraint names to whatever was specified in your *.sql files. This inherently requires dropping and re-creating the constraints, which can be slow and disruptive. Since constraint naming is purely cosmetic, this is not recommended, especially for tables of non-trivial size.
Using the database server’s built-in OSC functionality
All modern versions of MySQL (5.6+) and MariaDB (10.0+) offer built-in support for non-disruptive online schema change for at least a subset of ALTER TABLE operations. This functionality is further improved in MySQL 8.0+ and MariaDB 10.3+ to support “instant” (metadata-only) alterations in some situations.
By default, the server will automatically use the least-disruptive form of alteration available for a given operation, but this behavior can be controlled explicitly using the ALGORITHM and LOCK clauses of ALTER TABLE. These clauses allow you to specify that an alteration must be performed in a specific online manner, or else return an error immediately if the desired alteration does not support the requested method.
You can force Skeema to include these clauses in generated ALTER TABLE statements by using the alter-algorithm and alter-lock options. When used in skeema push, executing the statement will fail if any generated ALTER TABLE cannot be executed using the requested ALGORITHM or LOCK clause, respectively.
Aside from risk of metadata lock waits, ALGORITHM=INSTANT alterations are generally safe and non-disruptive, regardless of table size. However, other forms of built-in online ALTER TABLE (such as ALGORITHM=INPLACE, LOCK=NONE) have two major drawbacks when operating on large tables:
With traditional binlog replication, replicas will not start the alter operation until it has completed on the primary. This typically results in substantial replication lag when the replicas execute the alteration.
Like any long transaction, an online alteration blocks InnoDB purge of old row versions (for all tables), causing unbounded growth in InnoDB’s history list length. If your workload involves a nontrivial volume of
UPDATEorDELETEqueries, database server performance may increasingly degrade over time, until the alter operation completes and purge may resume.
The first problem can be avoided by using AWS Aurora (which offers proprietary storage-level physical replication), or mitigated on MariaDB 10.8+ (which provides the binlog_alter_two_phase variable to have replicas start alterations right after the primary). However, the second problem has no solution or work-around.
For these reasons, consider using alter-wrapper and alter-wrapper-min-size to implement conditional logic: use built-in online DDL for smaller tables, and an external online schema change tool for larger tables.
If alter-wrapper-min-size is set to a value greater than 0, whenever the alter-wrapper is applied to a table (any table >= the supplied size value), the alter-algorithm and alter-lock options are both ignored automatically. In other words, the generated ALTER TABLE that is passed to the external OSC tool will automatically omit the ALGORITHM and LOCK clauses in this situation, to simplify this conditional configuration.
Avoiding metadata locking pileups
Before altering a table, you should confirm that there are no long-running transactions affecting the table that you wish to alter. This includes any type of long-running query, even reads, such as a long SELECT or an ill-timed mysqldump invocation. Otherwise, your ALTER TABLE will be blocked while waiting for a metadata lock, until the older long-running transaction completes. Worse still, your blocked session will in turn also block all other incoming queries for that table, since your blocked DDL has a higher priority in the wait queue than regular queries which also need the same metadata lock.
This risk applies to all forms of table alteration, regardless of whether you are using ALGORITHM=INSTANT or an external OSC tool.
In MySQL 8.0+, note that metadata locks are extended across foreign key relationships. This means that in addition to checking for long-running transactions affecting the table you wish to alter, you also need to check for long-running transactions affecting related tables on the other side of a foreign key constraint.