Logo
弹性伸缩

弹性伸缩 #

语法描述类型
ALTER RESHARDING RULE定义扩缩容规则RAL
RESHARD TABLE tableName by rule启动扩缩容RDL
SHOW RESHARDING RULE查看扩缩容规则RAL
SHOW RESHARDING LIST查看扩缩容作业列表RAL
SHOW RESHARDING STATUS jobId查看扩缩容作业状态RAL
START RESHARDING jobId启动已经停止的扩缩容作业RAL
STOP RESHARDING jobId停止扩缩容作业RAL
SHOW RESHARDING CHECK ALGORITHMS查看一致性校验算法RAL
CHECK RESHARDING jobId执行扩缩容数据一致性校验RAL
START RESHARDING CHECK jobId启动停止的扩缩容数据一致性校验作业RAL
STOP RESHARDING CHECK jobId停止的扩缩容数据一致性校验作业RAL
DROP RESHARDING CHECK jobId删除一致性校验作业RAL
COMMIT RESHARDING jobId提交扩缩容作业RAL
FORCE COMMIT RESHARDING jobId强制提交扩缩容缩作业RAL
ROLLBACK RESHARDING jobId撤销扩缩容作业RAL
APPLY RESHARDING jobId应用扩缩容新配置（切换元数据）RAL
FORCE APPLY RESHARDING jobId强制应用扩缩容新配置（切换元数据）RAL
STOP RESHARDING SOURCE WRITING jobId停写RAL
RESTORE RESHARDING SOURCE WRITING jobId恢复停写RAL
AUTO RESHARD TABLE tableName BY rule扩缩容自动模式RDL
RESHARD SINGLE TABLE tableName TO BROADCAST单表扩容成广播表RDL
AUTO RESHARD SINGLE TABLE tableName TO BROADCAST单表扩容成广播表自动模式RDL
UPDATE RESHARDING jobId OFFSET POSITION更新扩缩容增量进度RAL

1. 定义扩缩容规则 #

ALTER RESHARDING RULE

因扩缩容 rule 具有默认值，无需创建，仅提供 ALTER 语句。

完整配置 DistSQL 示例

ALTER RESHARDING RULE (
READ(
  WORKER_THREAD=20,
  BATCH_SIZE=1000,
  SHARDING_SIZE=10000000,
  RATE_LIMITER (TYPE(NAME='QPS',PROPERTIES('qps'='500')))
),
WRITE(
  WORKER_THREAD=20,
  BATCH_SIZE=1000,
  RATE_LIMITER (TYPE(NAME='TPS',PROPERTIES('tps'='2000')))
),
STREAM_CHANNEL (TYPE(NAME='MEMORY',PROPERTIES('block-queue-size'='2000')))
);

配置说明

ALTER RESHARDING RULE (
READ( -- 数据读取配置。如果不配置则部分参数默认生效。
  WORKER_THREAD=20, -- 从源端摄取全量数据的线程池大小。如果不配置则使用默认值。
  BATCH_SIZE=1000, -- 一次查询操作返回的最大记录数。如果不配置则使用默认值。
  SHARDING_SIZE=10000000, -- 全量数据分片大小。如果不配置则使用默认值。
  RATE_LIMITER ( -- 限流算法。如果不配置则不限流。
  TYPE( -- 算法类型。可选项：QPS
  NAME='QPS',
  PROPERTIES( -- 算法属性
  'qps'='500'
  )))
),
WRITE( -- 数据写入配置。如果不配置则部分参数默认生效。
  WORKER_THREAD=20, -- 数据写入到目标端的线程池大小。如果不配置则使用默认值。
  BATCH_SIZE=1000, -- 一次批量写入操作的最大记录数。如果不配置则使用默认值。
  RATE_LIMITER ( -- 限流算法。如果不配置则不限流。
  TYPE( -- 算法类型。可选项：TPS
  NAME='TPS',
  PROPERTIES( -- 算法属性
  'tps'='2000'
  )))
),
STREAM_CHANNEL ( -- 数据通道，连接生产者和消费者，用于 read 和 write 环节。如果不配置则默认使用 MEMORY 类型。
TYPE( -- 算法类型。可选项：MEMORY
NAME='MEMORY',
PROPERTIES( -- 算法属性
'block-queue-size'='2000' -- 属性：阻塞队列大小
)))
);

2. 启动扩缩容 #

RESHARD TABLE tableName BY rule

参数说明

参数名说明
tableName执行扩缩容的表

示例

RESHARD TABLE t_order BY(
    STORAGE_UNITS(ds_0, ds_1, ds_2),
    SHARDING_COLUMN=order_id,
    TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
    KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);

3. 查看扩缩容规则 #

SHOW RESHARDING LIST;

参数说明

无

示例

SHOW RESHARDING LIST;
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id                                    | tables  | sharding_total_count | active | create_time         | stop_time |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| j51017f973ac82cb1edea4f5238a258c25e89 | t_order | 2                    | true   | 2022-10-25 10:10:58 | NULL      |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+

4. 查看扩缩容作业列表 #

SHOW RESHARDING LIST;

参数说明

无

示例

SHOW RESHARDING LIST;
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| id                                    | tables  | sharding_total_count | active | create_time         | stop_time |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+
| j51017f973ac82cb1edea4f5238a258c25e89 | t_order | 2                    | true   | 2022-10-25 10:10:58 | NULL      |
+---------------------------------------+---------+----------------------+--------+---------------------+-----------+

5. 查看扩缩容作业状态 #

SHOW RESHARDING STATUS jobId;

参数说明

参数名说明
jobid作业id

示例

SHOW RESHARDING STATUS 'j51017f973ac82cb1edea4f5238a258c25e89';
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| item | data_source | status                   | active | processed_records_count | inventory_finished_percentage | remaining_seconds | incremental_idle_seconds | error_message |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+
| 0    | ds_0        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
| 1    | ds_1        | EXECUTE_INCREMENTAL_TASK | true   | 3                       | 100                           | 0                 | 92                       |               |
+------+-------------+--------------------------+--------+-------------------------+-------------------------------+-------------------+--------------------------+---------------+

6. 启动已经停止的扩缩容作业 #

START RESHARDING jobId

参数说明

参数名说明
jobid作业id

示例

START RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

7. 停止扩缩容作业 #

STOP RESHARDING jobId

参数说明

参数名说明
jobid作业id

示例

STOP RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

8. 查看数据一致性校验算法 #

一致性校验算法选择请参考 数据一致性校验算法。

SHOW RESHARDING CHECK ALGORITHMS

示例

SHOW RESHARDING CHECK ALGORITHMS;
+----------------------------+---------------------------------------------+-----------------------------------------------------------------------------------------------+----------------------------------------------+
| type                       | type_aliases                                | supported_database_types                                                                      | description                                  |
+----------------------------+---------------------------------------------+-----------------------------------------------------------------------------------------------+----------------------------------------------+
| CRC32_MATCH                |                                             | MySQL,MariaDB,H2,Aurora                                                                       | Match CRC32 of records.                      |
| DATA_MATCH                 |                                             | SQL92,MySQL,PostgreSQL,openGauss,Oracle,SQLServer,MariaDB,H2,Hive,Presto,Aurora,DM,KingbaseES | Match raw data of records.                   |
| SphereEx:BATCH_CRC32_MATCH | SphereEx:DATA_CONSISTENCY_CHECK_CRC32_MATCH | MySQL,MariaDB,H2,Aurora                                                                       | Match CRC32 of records (Commercial edition). |
| SphereEx:DATA_DIFF         |                                             | SQL92,MySQL,PostgreSQL,openGauss,Oracle,SQLServer,MariaDB,H2,Hive,Presto,Aurora,DM,KingbaseES | Diff raw data of records.                    |
+----------------------------+---------------------------------------------+-----------------------------------------------------------------------------------------------+----------------------------------------------+

输出说明

  • type : 算法名
  • type_aliases : 算法别名
  • supported_database_types : 算法支持哪些数据库
  • description : 算法说明

9. 执行扩缩容数据一致性 #

CHECK RESHARDING jobId BY TYPE (NAME='algorithmType');
参数名说明
jobId作业id
algorithmType一致性校验算法类型

示例

指定 jobId 指定校验算法执行数据一致性校验

CHECK RESHARDING 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='CRC32_MATCH');

CHECK RESHARDING 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='DATA_MATCH', PROPERTIES('chunk-size'='1000'));

CHECK RESHARDING 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='SphereEx:BATCH_CRC32_MATCH');

CHECK RESHARDING 'j01016e501b498ed1bdb2c373a2e85e2529a6' BY TYPE (NAME='SphereEx:DATA_DIFF', PROPERTIES('diff-storage-type'='DATABASE','chunk-size'='1000','diff-storage-unit-name'='ds_2','diff-table-name'='spex_cnschk_1','incremental-idle-seconds-threshold'='30'));

10. 启动停止的扩缩容数据一致性检测作业 #

START RESHARDING CHECK jobId

参数说明

参数名说明
jobid作业id

示例

START RESHARDING CHECK  'j51017f973ac82cb1edea4f5238a258c25e89';

11. 停止扩缩容数据一致性检测作业 #

STOP RESHARDING CHECK jobId

参数说明

参数名说明
jobid作业id

示例

STOP RESHARDING CHECK  'j51017f973ac82cb1edea4f5238a258c25e89';

12. 删除扩缩容数据一致性检测作业 #

DROP RESHARDING CHECK jobId

参数说明

参数名说明
jobid作业id

示例

DROP RESHARDING CHECK 'j51017f973ac82cb1edea4f5238a258c25e89';

13. 提交扩缩容作业 #

COMMIT RESHARDING jobId

参数说明

参数名说明
jobId作业id

示例

COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

14. 强制提交扩缩容作业 #

FORCE COMMIT RESHARDING jobId

参数说明

参数名说明
jobid作业id

示例

FORCE COMMIT RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

15. 撤销扩缩容作业 #

ROLLBACK RESHARDING jobId

参数说明

参数名说明
jobid作业id

示例

ROLLBACK RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

16. 应用扩缩容新配置（切换元数据） #

APPLY RESHARDING jobId

参数说明

参数名说明
jobid作业id

示例

APPLY RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

17. 强制应用扩缩容新配置（切换元数据） #

FORCE APPLY RESHARDING jobId

参数说明

参数名说明
jobid作业id

示例

FORCE APPLY RESHARDING 'j51017f973ac82cb1edea4f5238a258c25e89';

18. 停写 #

STOP RESHARDING SOURCE WRITING jobId

参数说明

参数名说明
jobid作业id

示例

STOP RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';

19. 恢复停写 #

RESTORE RESHARDING SOURCE WRITING jobId

参数说明

参数名说明
jobid作业id

示例

RESTORE RESHARDING SOURCE WRITING 'j51017f973ac82cb1edea4f5238a258c25e89';

20. 扩缩容自动模式 #

AUTO RESHARD TABLE tableName BY rule

参数说明

参数名说明
tableName执行扩缩容的表

示例

AUTO RESHARD TABLE t_order BY(
    STORAGE_UNITS(ds_0, ds_1, ds_2),
    SHARDING_COLUMN=order_id,
    TYPE(NAME="hash_mod",PROPERTIES("sharding-count"="6")),
    KEY_GENERATE_STRATEGY(COLUMN=order_id,TYPE(NAME="snowflake"))
);

21. 单表扩容成广播表 #

RESHARD SINGLE TABLE tableName TO BROADCAST

参数说明

参数名说明
tableName执行扩缩容的单表

示例

RESHARD SINGLE TABLE t_address TO BROADCAST

22. 单表扩容成广播表自动模式 #

AUTO RESHARD SINGLE TABLE tableName TO BROADCAST

参数说明

参数名说明
tableName执行扩缩容的单表

示例

AUTO RESHARD SINGLE TABLE t_address TO BROADCAST

23. 更新扩缩容增量进度 #

UPDATE RESHARDING jobId OFFSET POSITION('storageUnitName'='position');
参数名说明
storageUnitName存储单元名称
position增量进度位点

示例

更新 MySQL 进度的格式是 file#pos，写法如下

UPDATE RESHARDING j51017f973ac82cb1edea4f5238a258c25e89 OFFSET POSITION('ds_0'='mysql-bin.000001#111','ds_1'='mysql-bin.000002#222');