KlustronDB Online DDL and Table Repartitioning Features and Usage

1. Background of the requirement:

After users create tables in a KlustronDB cluster and use them, as business requirements and table data volumes change, the originally created tables may no longer be suitable. For example, queries and insert operations may slow down due to unreasonable partitioning, or the table data may shrink and no longer need as many partitions as before. Users may also need to add or remove columns, modify column data types, change primary keys, add indexes, and so on. Users hope to complete these DDL operations or modify the table partitions online without affecting business operations. For this scenario, the KlustronDB team developed online DDL and online table partition modification features, which are referred to in this article as table repartitioning (repartition).

The Repartition feature not only supports single table to partitioned table, partitioned table to single table, modifying table partition rules, but also modifying table partition parameters.

It also supports making all DDL operations online (no table locks required, no impact on business system operation). For DDL statements that cannot be completed instantly, users can use this feature to perform online DDL --- first create the table according to the new table definition, then perform a repartition operation. This allows all data from the source table to be loaded and switched instantly without affecting the use of the original table, thereby avoiding any impact of DDL operations on the normal operation of the application system. Therefore, the repartition feature can also be considered KlustronDB's Online DDL functionality, applicable to all DDL operations. Specifically, for MySQL, these two types of alter table/alter column operations require table reconstruction (algorithm=copy), which is counterintuitive. First is changing the column data type — even if widening it (for example, changing int to bigint); second is changing the column nullability constraint to NULL or NOT NULL. Executing these two types of operations requires copying the entire table data and rebuilding the table. Users can use KlustronDB repartition to complete these operations online.

2 Implementation Principle:

Premise: The user has already created the target table using the new partitioning rules or according to the new table design, for example, create table t1(like t0, including all), and the structures of the source and target tables are the same.

Task execution process: Table redistribution is completed through the collaboration of the cluster_mgr and node_mgr modules. After receiving a business request, cluster_mgr parses the request content, checks the parameters, and plans the nodes that need to be operated by node_mgr. It then sends commands to the node_mgr module to perform specific operations.

Method: Export the data from the source table and import it into the target table, then import the updates made to the source table during this period into the target table.

1. Export full table data

node_mgr calls mydumper to dump the source table data and transmit the data files to the server where the Kluscomp instance is located

2. Load full data of the table

node_mgr calls the kunlun_loader tool to dump the full data from the source table into the target table. kunlun_loader is an extension of myloader by KlustronDB, adding the feature of mapping database and table names. When calling kunlun_loader to import data, it automatically converts database and table names according to the configured mapping rules. In addition, kunlun_loader uses KlustronDB's MySQL port to write data to the Kluscomp instances. To improve processing speed, node_mgr uses parallel dump and parallel load for multiple tables.

3. binlog catch-up --- binlog2sync

node_mgr calls the binlog2sync tool based on the starting positions of the binlogs on each shard recorded during the dump. The binlog2sync tool dumps binlog events starting from that position and filters out unnecessary binlog events according to the source database table as needed. Update events on the source tables are automatically converted into SQL statements and have the database and table names mapped. The converted SQL statements are sent to the Kluscomp instances through KlustronDB's MySQL port for execution.

4. The impending completion time renames the source table and target table through the Kluscomp instance

The binlog2sync tool determines whether to switch table names by checking the binlog write position in the source shard and the position of the binlogs it has already dumped. It aims to minimize the time required to dump binlogs after switching table names. Once the table name is switched, the business can no longer write to it, and the tool quickly synchronizes the remaining binlogs to ensure that all new data in the source is fully imported into the target table. Afterwards, the target table is renamed to the source table name, allowing normal business operations to resume. This phase will have a millisecond-level impact on the business.

3 The new table and the source table must have the following similarities and may have the following differences

Since the insert statements generated by mydumper and binlog2sync for KlustronDB online DDL (repartition) all include the column names of the source table, it is not difficult to draw the following conclusions regarding the similarities and differences between the table definitions of the source table and the new table.

For the sake of convenience, we refer to the two columns with the same name in the source table and the new table as colX and colY, respectively. The new table must be consistent with the source table in the following aspects and can differ in others.

3.1 Column Sets

All column names from the source table must appear in the new table, but their order can be different. The new table can add more columns, but these columns must have default values or allow NULL values. Therefore, an online DDL can perform multiple operations at the same time ---- adding columns (not only appending to the end but also inserting them at any position in the table), adjusting column order, and modifying column constraints and default values.

Columns with the same name in the source table and the new table can have different default values, NULL constraints, or check constraints. However, if colX has NULL values while colY is NOT NULL, or if the data in colX cannot satisfy colY's check constraint, then the data import will fail and cause the repartition operation to fail.

3.2 Column Data Types

The data types of columns with the same name in the source table and the new table must either be exactly the same or belong to the same major category, and the data type of colY can be wider but not narrower.

For example, both are integers. If colX is int, then colY can be bigint, but cannot be smallint or tinyint; if colX is float, then colY can be float or double; if colX is varchar(10), then colY can be varchar(11), char(11), text, but cannot be varchar(9), char(9), int, float, etc.

For string types, there is another special point, which is the character set --- if the colX data type is varchar/char(n)/[long/medium]text, its character set is best to be exactly the same as colY (the collation can be different if necessary, but it needs to conform to the expected logic of the application); if colX and colY character sets do need to be different (for example, this online DDL operation is to modify the character set), then colY's character set must include colX's character set. For example, if colX's character set is utf8mb3, colY's character set can be utf8mb4, but it cannot be latin1, gbk, etc., otherwise string searches may fail; if colX's character set is latin1, then colY's character set can be various Unicode character sets and gbk, etc.

If colX and/or colY are of domain type, it must be ensured that the valid value range of colY is wider than that of colX, and the various checks and constraints defined on the domain are also broader for colY than for colX; otherwise, data import into the new table will fail.

3.3 Index

The source table and the new table can have completely different index definitions and primary key definitions. For example, the new table may be designed to modify the primary key, or to add indexes or unique indexes. However, the data in the source table must meet all the unique index and primary key constraints of the new table, otherwise the data import will fail.

3.4 Partitioning Rules and Parameters

The source table and the new table can have completely different table partitioning rules and table partition parameters. Both the source table and the new table can be one of a single table, a mirrored table, or a partitioned table. For example, the source table could be a single, unpartitioned table, while the new table is partitioned according to a certain rule; or the new table could be a mirrored table; the source table could be a mirrored table, and the new table could be a single table or a partitioned table; the source table and the new table can use different partitioning rules (for example, hash -> range) or partition parameters (for example, both are hash-partitioned, but the source table has 4 partitions and the new table has 16 partitions).

3.5 Table Constraints

The source table and the new table can have different table-level check constraints and trigger definitions, but the data in the source table must be able to pass the check constraints of the new table, otherwise the data import will fail.

In summary, it can be seen that by using the repartition function of KlustronDB, a series of DDL and table repartition operations can be performed uniformly in a single operation, greatly reducing the operational management complexity and time cost for DBAs, and avoiding impacting the normal operation of application systems.

4 Configuration Usage:

4.1 Complete the operation through the table redistribution interface provided by cluster_mgr.

4.1.1 The source table structure and data are as follows:

4.1.2 Target table structure

4.1.3 Call the cluster_mgr table redistribution API

curl -d '

{

  "version":"1.0",

  "job_id":"",

  "job_type":"table_repartition",

  "timestamp":"1435749309",

  "user_name":"kunlun_test",

  "paras":{

    "src_cluster_id":"1",

    "dst_cluster_id":"3",

    "repartition_tables":"postgres_$$_public.transfer_accout=>postgres_$$_public.account"

  }

}

' -X POST http://127.0.0.1:58000/HttpService/Emit

This interface is an asynchronous interface that returns a job_id. Use the returned job_id to check the task execution status.

curl -d '

{

  "version":"1.0",

  "job_id":"10",

  "job_type":"get_status",

  "timestamp":"1435749309",

  "user_name":"kunlun_test",

  "paras":{

  }

}

' -X POST http://127.0.0.1:58000/HttpService/Emit

4.1.4 After the table task is successfully executed, the data situation of the target table

Source table data volume

Target data volume

4.2 Performing Table Redistribution Operations through the xpanel Interface

4.2.1 Log in to the cluster settings page where the source table is located

4.2.2 After selecting the configuration, click the submit button

END