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 online 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 online 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 online 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 target database. Therefore, you must assess whether the DDL statements are supported by the target database.
After the online DDL tools are configured, they only take effect on subsequent online DDL operations. The online DDL operations performed before the online DDL tools are configured will maintain the original behavior, which may lead to exceptions in the data migration task.
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 (online 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 by Rule option. When you choose the Specify Objects option, you do not select temporary tables. When you choose the Match by Rule 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 target database. Consequently, the task is interrupted due to inconsistency between schemas of tables in the source and target 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 by Rule option, and define a rule covering temporary tables.
Impact: OMS fully replicates online 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 target database.
Save the DDL operations that change temporary tables in the source database and then apply the DDL operations to source tables in the target 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 target databases.