This topic provides answers to frequently asked questions about DDL statements.
How do I assess the impact on my business before I execute a DDL statement?
Generally, before you execute a DDL statement in a distributed database, you must check whether the DDL statement locks tables, whether data needs to be backfilled, and whether the business traffic is significantly affected. Data backfilling affects the execution duration of a DDL statement.
PolarDB-X has made significant optimizations in the preceding three aspects:
PolarDB-X supports the online DDL feature. This way, table locking is not required for most DDL statements.
PolarDB-X optimizes the data backfilling speed. This effectively shortens the execution duration of DDL statements.
PolarDB-X optimizes asynchronous execution and multi-write for logically executed DDL statements that involve MOVE PARTITION, Online Modify Column (OMC), or rebalancing. This minimizes the duration of multi-write conflicts and write performance deterioration during data backfilling, and effectively mitigates the impact of such DDL statements on business traffic.
For more information about how to assess the possible impact of a specific DDL statement on your business, see Online DDL.
Some DDL statements are time-consuming. What is the impact on such DDL statements if the connection from a client to PolarDB-X times out? What is the solution?
In PolarDB-X, DDL statements are synchronously executed by default. If a disconnection occurs, the execution of a DDL statement may be suspended. If the expected execution duration of a DDL statement is long, asynchronously execute the DDL statement. For more information, see Asynchronously execute DDL statements.
How do I monitor and manage DDL statements that are being executed, especially those that are being asynchronously executed?
PolarDB-X provides a series of DDL management statements. You can execute these statements to view the execution status of DDL statements, view the historical execution results of DDL statements, and suspend, resume, or roll back DDL statements. This allows you to effectively control DDL statements and reduce the risks of changes. For more information, see DDL management statements.
How do I gain insight into the execution modes of DDL statements in PolarDB-X for better O&M?
In short, DDL statements in PolarDB-X are executed in either of the following modes:
DDL statements are executed by using the Online Schema Change (OSC) capability of compute nodes (CNs).
DDL statements are pushed down to and executed on data nodes (DNs).
For more information, see DDL execution modes.
Can I optimize the execution process of DDL statements since I have thoroughly understood how DDL statements are executed in PolarDB-X? For example, can I speed up the execution of a DDL statement?
Yes. Take note that the optimization must be based on your in-depth understanding of the execution modes of DDL statements, the characteristics of your business traffic, and available resources in PolarDB-X.
You can adjust the degree of parallelism (DOP) for DDL statements that are pushed down to DNs. For more information, see DOP control for physically executed DDL statements.
For DDL statements that are executed by using the OSC capability of CNs, data backfilling consumes the most time. You can adjust the speed of data backfilling. For more information, see DOP control for logically executed DDL statements.
What are the other features or optimizations for DDL that can improve my user experience?
OMC
If you change column types in a regular way, DML statements are blocked on DNs during the change, which affects your business. The Online Modify Column (OMC) feature of PolarDB-X allows you to change column types online. This way, tables are not locked during the change. For more information, see Change the column types without locking tables.
Instant ADD COLUMN
If you use the traditional method to add columns, all physical shard data needs to be recreated on DNs, which occupies a large amount of system resources. PolarDB-X supports the Instant ADD COLUMN feature. When you add a column to a table by using this feature, you need to only modify the definition of the table. The existing data in the table does not need to be modified. This way, a column can be instantly added to a table regardless of the table size. For more information, see Instant ADD Column.
DDL atomicity
PolarDB-X supports the two-phase DDL feature. You can use this feature to prevent the situation in which the physical execution of user DDL statements is successful on some shards but fails on other shards. The two-phase DDL feature is enabled by default. For more information, see DDL atomicity.