Skip to main content

Run MySQL schema changes with OnlineDDL

For online schema changes, the biggest risk is not only incorrect SQL but also table locks that interrupt service. NineData OnlineDDL is built for MySQL change scenarios and can complete table structure changes with minimal impact on normal reads and writes.

Use cases​

  • Online column changes: Add or change columns while the business continues to read and write.
  • Index changes: Reduce blocking impact during index adjustments.
  • Release risk control: Use runtime parameters, cutover windows, and retry settings to manage OnlineDDL execution.

Background​

Traditional DDL statements, such as ALTER TABLE, may lock the table while the change runs. During that lock, other writes to the table can be blocked and application performance can be affected.

NineData SQL tasks can use OnlineDDL, which works like this:

flow

  1. Create a new table with the same structure as the old table and execute table structure changes on the new table.
  2. During the change period, incremental data from the source table is synchronized to the new table in real time.
  3. Copy all data from the original table to the new table.
  4. After the new table catches up with the original table, NineData selects a suitable time to switch traffic from the original table to the new table.

This process lets you change table structure without long table locks and with minimal impact on application performance.

Before you begin​

  • You have created or joined an organization, and this organization has subscribed to either DevOps Pro or DevOps Enterprise. Please ensure that your annual or monthly subscription is still active. For more information, please refer to Manage Organizations.
  • Your current account has switched to the target organization. For more information, please refer to Switching to an Organization.
  • Your role is Administrator. For more information about roles, see Roles.
  • The database type is MySQL.

Notes​

NineData OnlineDDL switches the business from the old table to the new table.

Procedure​

OnlineDDL is enabled by default in the SQL standards provided by NineData. Adjust the OnlineDDL policy for your business scenario, then bind it to the target data source. This article explains how to update the policy.

  1. Sign in to the NineData Console.

  2. In the left navigation pane, click Datasource > Policy & Process.

  3. On the Policy & Process page, click the SQL Dev Policy tab.

  4. Click the target Name, or click the Details in the Actions column on the right.

  5. On the Details page, click the SQL Task & Console tab, find OnlineDDL Config, and click the Edit in the Actions column on the right.

    tip

    Make sure that OnlineDDL Config is enabled, that is, the switch in the Status column is turned on.

  6. Configure the execution policy of Online DDL. The table below describes the main parameters.

    Parameter
    		Description
    Online DDL Exec ModeThe OnlineDDL execution mode.
    • MySQL OnlineDDL: Use MySQL built-in OnlineDDL to modify the table structure.
    • Adaptive(Prefer MySQL Online. Use Platform Online if not support.): Check whether the target DDL statement can use MySQL built-in OnlineDDL. If it can, NineData prefers MySQL built-in OnlineDDL. If it cannot, NineData OnlineDDL is used.
    • Mandatory use of platform OnlineDDL (mostly used in slave-delay-sensitive scenarios): Use NineData OnlineDDL directly. If NineData detects that MySQL Instant DDL can complete the change, the database built-in capability is used.
    Strategy when OnlineDDL is not available with MySQL or platformThe fallback action when the selected OnlineDDL execution mode cannot run successfully.
    • Forced to execute with MySQL(not online): Run the table structure change without OnlineDDL and use the standard locking DDL behavior.
    • Rufuse to excute and exit: Stop the table structure change task.
    Platform OnlineDDL Temp table clean strategyThe cleanup strategy for the old table after NineData OnlineDDL completes the non-locking schema change.
    • Clean immediately: NineData cleans up the old table in the background after traffic switches to the new table.
    • DO NOT clean(need to clean manually): Keep the old table after traffic switches to the new table. If you choose this option, manually clean up the old table later.
    Cutover Lock Timeout(s)Configures the lock timeout period for the source table during business switchovers. If the table is locked for longer than this time, the switchover fails. The default is 2 seconds.
    Note: After the no-lock change task finishes, NineData locks the source table to prevent new data from being written. Then, it switches the business from the source table to the new table. The duration of the table lock is affected by the database load. Configure this parameter for your environment.
    Retry count when cutover failThe number of retries for switching from the source table to the new table after the no-lock change task finishes. The default is 5 times.
    ChunkSize AdaptiveWhether to dynamically adjust the number of rows copied to the temporary table based on the performance of the database. It is enabled by default.
    Full copy batch sizeManually configure the number of rows copied to the temporary table. The default is 10,000 rows. This configuration is invalid when ChunkSize Adaptive is enabled.
    tip

    After the old and new tables are switched and the SQL task status changes to Success, NineData OnlineDDL renames the old table to _<table_name>_<time>_del. The time value is the creation time of the new table, including year, month, day, hour, minute, and second. If DO NOT clean(need to clean manually) is configured in the OnlineDDL Config policy, manually delete the renamed table later.

    For example, if NineData OnlineDDL performs a non-locking structure change on a table named t1, after traffic switches from the old table to the new table, NineData adds a table named _t1_20230209173740_del (example) to the database. If no other tables use similar naming rules, this table can be safely deleted.

  7. Click OK.

How it works in SQL tasks​

After the OnlineDDL rule is configured, submit a SQL task that contains DDL statements for a data source bound to this policy. During precheck, NineData checks whether the DDL statement supports MySQL OnlineDDL. If it does not, NineData OnlineDDL is enabled for the non-locking schema change.

OnlineDDL

Set OnlineDDL runtime parameters​

When a SQL task uses NineData OnlineDDL, configure OnlineDDL runtime parameters on the Details page of SQL Task. Runtime parameters include Lock Table Timeout, Retry Times, Full Policy, and Table Switching Time Config.

  1. Sign in to the NineData Console.

  2. In the left navigation pane, click DevOps>SQL Task.

    tip

    If SQL Task is unavailable, make sure that your console is in organization mode. To switch from personal mode to organization mode, see Switch to Organization.

  3. On the SQL Task page, find the target SQL task and click its Task ID.

  4. On the Details page, click More, then click View next to OnlineDDL Run Parameters.

  5. In the parameter window, click edit next to the parameter that you want to configure. The table describes the runtime parameters.

    Runtime ParameterDescription
    Lock Table TimeoutConfigure the source-table lock timeout during traffic switchover. If the table stays locked longer than this value, the switchover fails.
    Note: After the lock-free change task finishes, the source table is locked to prevent new writes. Then, traffic switches from the source table to the new table. Table lock duration is affected by database load. Configure this parameter for your environment.
    Retry TimesThe number of retries for switching from the source table to the new table after the lock-free change task finishes. The default is 5 times.
    Full PolicyConfigure the number of rows copied to the temporary table each time. By default, it is dynamically adjusted based on the database's performance.
    Table Switching Time ConfigSpecify the time period for business switching. By default, the switch occurs immediately after the change finishes. Adjust the switch time to your business's off-peak hours.

  6. After configuring, click Save.

View OnlineDDL execution progress​

When a SQL task uses NineData OnlineDDL, check the overall OnlineDDL progress on the Details page of SQL Task.

  1. Sign in to the NineData Console.

  2. In the left navigation pane, click DevOps>SQL Task.

    tip

    If SQL Task is unavailable, make sure that your console is in organization mode. To switch from personal mode to organization mode, see Switch to Organization.

  3. On the SQL Task page, find the target SQL task and click its Task ID.

  4. On the Details page, find Execution Results, then click OnlineDDL Process under the Details column.

  5. On the OnlineDDL Process page, view the running status of OnlineDDL and configure Run Parameters. For details about runtime parameters, see Configuring OnlineDDL Runtime Parameters.

Result​

After the policy is configured and bound to a MySQL data source, SQL tasks that contain supported DDL statements can use OnlineDDL during precheck and execution. Use the SQL task details page to review execution status, runtime parameters, and OnlineDDL progress.

Next Steps​

  • Submit a SQL task that contains the target DDL statement.
  • Review OnlineDDL progress on the SQL task details page.