Overview
MySQL performs schema changes (such as adding fields, modifying field attributes, adding indexes, and modifying indexes) when a large amount of data is processed. The risk of locking tables must be considered because locking tables may cause failures to write normal business. This article mainly introduces four functions that can better avoid locking table problems caused by database changes.
Procedure
|
Description
|
Advantage
|
Disadvantage
|
Related topic
|
| (recommended) perform lock-free schema changes in DMS |
- Compared with the native capacities of databases, DMS allows you to change schemas at a managed speed. This avoids latency in synchronization between primary and secondary databases and has less impact on database performance. In addition, you can apply this feature to various scenarios where tables may be locked when schemas are changed by using native online DDL operations.
- Compared with other tools such as PT-Online and OSC, DMS lock-free structure change does not depend on triggers, and asynchronous execution has very little impact on databases, allowing security to be interrupted at any time.
- DMS allows you to use Data Transmission Service (DTS) synchronization tools to change schemas without locking tables. If a DTS copy link is configured for the table whose schema is to be changed, table copy will not be interrupted when the schema of the table is being changed.
note: DTS replication link must have been restarted after 14th Feb, 202020.
|
To change the schema without locking a table, make sure that the original table contains a primary key or unique index. This allows you to copy all or part of data from the original table and synchronize incremental data.
- If the table contains only a primary key, make sure that the primary key is not updated. Otherwise, the schema change fails.
- If the table contains no primary key but a unique index, make sure that the unique index is not updated. Otherwise, the schema change fails.
- If the table contains no primary key nor unique index, add a primary key or unique index to the table in the instance. Then, change the schema without locking the table.
|
For more information, see change lock-free structure. |
|
Online DDL
|
- Most operations are fast.
- Supports concurrent DML for most operations.
- Sufficient resources enable concurrent DML scenarios to have minor impacts on the current instance.
|
- The log space is insufficient.
- The "Duplicated Key" error occurs.
- This may easily lead to replication delays for read-only instances.
- Concurrent DML operations are not supported for some operations.
- Cannot pause.
- Restricted by the metadata locks of MDL tables.
|
|
|
PT-OSC
|
- Supports concurrent DML operations.
- Supports preventing the Slave replication latency from exceeding a specified threshold.
- Support to avoid the load on the instance during execution exceeding the specified threshold.
|
- Slow, big impact
- The source table does not support triggers.
- The source table must have a primary key or unique key defined.
- Triggers introduce lock competition on new tables for bound transactions.
- Restricted by the metadata locks of MDL tables.
|
|
|
GH-OST
|
- Performance impact is small.
- Supports concurrent DML operations.
- Can be paused, and parameters support dynamic adjustment.
- Can determine the switching time point and has controllable behavior.
|
- The source table does not support foreign key and Trigger triggers.
- The source table does not support the MySQL 5.7 JSON type.
- The source table must define a primary key or a unique key (excluding the null value).
- The name of a table with the same name as the source table cannot be ignored after the name is ignored.
- Restricted by the metadata locks of MDL tables.
|
|
Application scope