Perform non-locking structure changes to the table using NineData OnlineDDL

NineData's SQL tasks support the NineData OnlineDDL feature, which allows for database schema changes to be made without affecting normal database usage.

Background

Traditional DDL statements (such as ALTER TABLE) require table locking to execute, which prevents any other modifications to the table during the lock period. This can cause long blocking times and negatively impact application running.

NineData's SQL tasks support the NineData OnlineDDL feature, which implements the following process:

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 will be 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 data catches up with the original table, the system will automatically select the best time to switch the business from the original table to the new table.

This process ensures that schema changes can be made without locking the table and without negatively impacting application performance.

Prerequisites

• You have created or joined an organization, and this organization has subscribed to either or . 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 . 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.

Operation Steps

OnlineDDL is enabled by default in the SQL policies provided by NineData. You only need to change the policy of OnlineDDL according to the actual business scenario, and associate the policies with the target data source to take effect. This article describes how to change the policy of OnlineDDL.

1. Log in to the NineData console.

2. In the left navigation pane, click > .

3. On the page, click the tab.

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

5. On the page, click the tab, find , and click the in the column on the right.

   tip

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

6. Configure the execution policy of Online DDL. For the explanation of the main parameters, please refer to the following table.

   Parameter	Description
   	The way to perform OnlineDDL. : Use MySQL's built-in OnlineDDL to modify table structure. : The system detects in advance whether the target DDL statement can be executed using MySQL's built-in OnlineDDL. If not, NineData OnlineDDL is used. If yes, MySQL's built-in OnlineDDL is preferred. : Execute directly using NineData's OnlineDDL.
   	The strategy when the selected OnlineDDL execution method fails to execute smoothly. : The table structure change task is not executed through OnlineDDL, and usual lock table modification is used instead. : The table structure change task is no longer executed, and the task ends.
   	The cleaning strategy of the original table after successfully performing non-lock table structure modification with NineData OnlineDDL. : The old table is automatically cleaned up by the background after the business switches to the new table. : The old table is retained after the business switches to the new table. If you choose this option, you need to manually clean up the old table.
   	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 is completed, the system 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, and you can configure this parameter according to the actual situation.
   	The number of retries for the source table to switch to the new table after the no-lock change task is completed. The default is 5 times.
   	Whether to dynamically adjust the number of rows copied to the temporary table based on the performance of the database. It is enabled by default.
   	Manually configure the number of rows copied to the temporary table. The default is 10,000 rows. This configuration is invalid when is enabled.

   tip

   After completing the switch of the old and new tables (i.e., the state of the corresponding SQL task is changed to ), NineData OnlineDDL will rename the old table as _<table_name>_<time>_del, where time represents the time when the new table was created, including the year, month, day, hour, minute, and second. If you have configured in the policy, you also need to manually delete the table with this name.

   For example, if you use NineData OnlineDDL to perform non-locking structure changes on a table named t1, after the switch between the old and new tables is completed, a table named _t1_20230209173740_del (example) will be added to the database. If you do not have other tables with similar naming rules, you can safely delete this table.

7. Click .

Effect Display

After completing the rule configuration of OnlineDDL, submit a SQL task containing DDL statements for the data source configured with this policy. During the standard pre-check stage, the system will automatically detect whether the DDL statement supports MySQL OnlineDDL. If it is not supported, the NineData OnlineDDL will be enabled for lock-free table structure changes.

Configuring OnlineDDL Runtime Parameters

When SQL tasks are executed through NineData OnlineDDL, you can configure the runtime parameters of OnlineDDL on the page of , including , , , and .

1. Log in to the NineData Console.

2. In the left navigation pane, click on >.

   tip

   If you cannot find , please make sure that your console is in organization mode. To switch from personal mode to organization mode, refer to Switch to Organization.

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

4. On the page, click on , then click on on the right side of .

5. On the popped-up page, click on next to the parameter that needs to be configured. Refer to the table below for parameter descriptions.

   Runtime Parameter	Description
   	Configure the lock timeout for the source table when switching business. If the table is locked for a duration exceeding this time, the switch will fail. Note: After the lock-free change task is completed, the source table will be locked to prevent new data from being written. Then, the business will be switched from the source table to the new table. The duration of table locking is affected by database load. You can configure this parameter based on the actual situation.
   	The number of retries for switching from the source table to the new table after the lock-free change task is completed. The default is 5 times.
   	Configure the number of rows copied to the temporary table each time. By default, it is dynamically adjusted based on the database's performance.
   	Specify the time period for business switching. By default, the switch occurs immediately after the change is completed. You can adjust the switch time to your business's off-peak hours as needed.

6. After configuring, click on .

Viewing OnlineDDL Execution Progress

When SQL tasks are executed through NineData OnlineDDL, you can check the overall progress of OnlineDDL on the page of .

1. Log in to the NineData Console.

2. In the left navigation pane, click on >.

   tip

   If you cannot find , please make sure that your console is in organization mode. To switch from personal mode to organization mode, refer to Switch to Organization.

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

4. On the page, find , then click on under the column.

5. On the page, you can view the running status of OnlineDDL and configure . For details about runtime parameters, please refer to Configuring OnlineDDL Runtime Parameters.