This topic describes online DDL tools in terms of their operating principles, limitations, as well as impacts on and adaptation to data migration.
Background information
Online DDL operations, also known as lock-free schema changes, are intended to avoid business blocking caused by table locking that occurs during schema changes due to the database mechanism. OceanBase Migration Service (OMS) supports general online DDL tools such as gh-ost, pt-osc, and Alibaba Cloud Data Management Service (DMS). They respond to lock-free schema changes in the following way:
Create a temporary table with the same schema as the source table.
Run a DDL operation to change the schema of the temporary table.
Replicate full data of the source table to the temporary table.
Synchronize incremental data from the source table to the temporary table until the data of the temporary table is the same as that of the source table.
Rename the source table as a to-be-deleted table and the temporary table as a source table.
This way, the online DDL tools can effectively avoid business blocking caused by long-time locking of the source table that involves a large amount of data.
Limitations
OMS allows you to use Alibaba Cloud DMS, gh-ost, or pt-osc to perform lock-free schema changes only when you migrate data from a MySQL database.
The online DDL tools restore online DDL statements to user DDL statements. However, they cannot avoid table locking during the execution of statements in the destination database. Therefore, you must assess whether the DDL statements are supported by the destination database.
The online DDL tools filter out tables that match the temporary table semantics based on the specified rules. If your business table matches the temporary table semantics, the table is also be filtered out. Consequently, the data synchronization result is not as expected. The following table describes the temporary table semantics defined by the online DDL tools.
Online DDL tool Mirrored temporarily table Other temporarily tables gh-ost _{original table name}_gho _{original table name}_del
_{original table name}_ghcpt-osc _{original table name}_new _{original table name}_old Alibaba Cloud DMS (lock-free schema change) tp_{change ID}_ogt_{original table name}
tp_{change ID}_g_{original table name}tp_{change ID}_ogl_{original table name}
tp_{change ID}_l_{original table name}
tp_{change ID}_del_{original table name}
tp_{change ID}_d_{original table name}
Impacts on data migration
Online DDL operations in the source database have different impacts on data migration depending on the range of migration objects.
When the migration objects do not include temporary tables generated by the online DDL tool:
Scenario: You specify migration objects for a data migration task by using the Specify Objects or Match Rules option. When you choose the Specify Objects option, you do not select temporary tables. When you choose the Match Rules option, you define a rule without covering temporary tables.
Impact: OMS does not fully replicate the creation, schema change, or rename operations on temporary tables to the destination database. Consequently, the task is interrupted due to inconsistency between schemas of tables in the source and destination databases.
When the migration objects include temporary tables generated by an online DDL tool:
Scenario: You specify migration objects for a data migration task by using the Match Rules option, and define a rule covering temporary tables.
Impact: OMS fully replicates lock-free schema changes in the source database, including a large number of DML operations on temporary tables. This does not interrupt the task, but causes massive data synchronization and affects performance.
Adaptation to data migration
You can specify whether to enable online DDL tools in the OMS console. If you specify to enable online DDL tools, OMS performs the following operations in the background:
Configure filter conditions to filter out all tables that match the temporary table semantics defined by the online DDL tool. These tables are not be synchronized to the destination database.
Save the DDL operations that change temporary tables in the source database and then apply the DDL operations to source tables in the destination database after these operations are completed.
This way, the following two problems can be solved:
DML and DDL operations on temporary tables are filtered out. This reduces the impact of redundant transmission of table objects on data migration performance.
Online DDL statements are restored to user DDL statements. This avoids interruption of data migration caused by inconsistency between schemas of tables in the source and destination databases.