关于分区表和分区表(动态分区表和自动分区表)进行 Resource group 实践

Viewed 13

背景

在存量集群上进行resource group的划分,会遇到一些问题,后续可以根据这个实践文档进行。

建表


-- 非分区表
CREATE TABLE `no_partition_test` (
  `tx_hash` varchar(1024) NOT NULL COMMENT "交易Hash",
  `tx_datetime` datetime NOT NULL COMMENT "交易时间",
  `account` varchar(1024) NULL COMMENT "地址",
  `tx_lt` bigint NULL COMMENT "交易逻辑时间",
  `orig_status` varchar(1024) NULL COMMENT "开始状态",
  `end_status` varchar(1024) NULL COMMENT "结束状态",
  `total_fee` bigint NULL COMMENT "总费用",
  `action_success` boolean NULL COMMENT "动作是否成功",
  INDEX idx_tx_datetime (`tx_datetime`) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(`tx_hash`, `tx_datetime`)
COMMENT '交易信息表'
DISTRIBUTED BY HASH(`tx_hash`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default:3"
);



-- 动态分区表

CREATE TABLE `dynamic_partition_test` (
  `tx_hash` varchar(1024) NOT NULL COMMENT "交易Hash",
  `tx_datetime` datetime NOT NULL COMMENT "交易时间",
  `account` varchar(1024) NULL COMMENT "地址",
  `tx_lt` bigint NULL COMMENT "交易逻辑时间",
  `orig_status` varchar(1024) NULL COMMENT "开始状态",
  `end_status` varchar(1024) NULL COMMENT "结束状态",
  `total_fee` bigint NULL COMMENT "总费用",
  `action_success` boolean NULL COMMENT "动作是否成功",
  INDEX idx_tx_datetime (`tx_datetime`) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(`tx_hash`, `tx_datetime`)
COMMENT '交易信息表'
PARTITION BY RANGE(`tx_datetime`) ()
DISTRIBUTED BY HASH(`tx_hash`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default:3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-10",
"dynamic_partition.end" = "10",
"dynamic_partition.prefix" = "p",
"dynamic_partition.create_history_partition" = "true"
);

-- 自动分区表
CREATE TABLE `auto_partition_test` (
  `tx_hash` varchar(1024) NOT NULL COMMENT "交易Hash",
  `tx_datetime` datetime NOT NULL COMMENT "交易时间",
  `account` varchar(1024) NULL COMMENT "地址",
  `tx_lt` bigint NULL COMMENT "交易逻辑时间",
  `orig_status` varchar(1024) NULL COMMENT "开始状态",
  `end_status` varchar(1024) NULL COMMENT "结束状态",
  `total_fee` bigint NULL COMMENT "总费用",
  `action_success` boolean NULL COMMENT "动作是否成功",
  INDEX idx_tx_datetime (`tx_datetime`) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(`tx_hash`, `tx_datetime`)
COMMENT '交易信息表'
AUTO PARTITION BY RANGE (date_trunc(`tx_datetime`, 'month'))()
DISTRIBUTED BY HASH(`tx_hash`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.default:3"
);

导入数据

INSERT INTO `no_partition_test` VALUES
('tx_hash_001', '2025-10-04 08:15:00', 'account_001', 1000000001, 'init', 'done', 1500, true),
('tx_hash_002', '2025-10-05 09:30:00', 'account_002', 1000000002, 'init', 'fail', 1600, false),
('tx_hash_003', '2025-10-06 10:45:00', 'account_003', 1000000003, 'sync', 'done', 1700, true),
('tx_hash_004', '2025-10-07 11:00:00', 'account_004', 1000000004, 'sync', 'fail', 1800, false),
('tx_hash_005', '2025-10-08 12:10:00', 'account_005', 1000000005, 'init', 'done', 1900, true),
('tx_hash_006', '2025-10-09 13:20:00', 'account_006', 1000000006, 'init', 'fail', 2000, false),
('tx_hash_007', '2025-10-10 14:25:00', 'account_007', 1000000007, 'sync', 'done', 2100, true),
('tx_hash_008', '2025-10-11 15:40:00', 'account_008', 1000000008, 'sync', 'done', 2200, true),
('tx_hash_009', '2025-10-12 16:50:00', 'account_009', 1000000009, 'init', 'done', 2300, true),
('tx_hash_010', '2025-10-13 17:55:00', 'account_010', 1000000010, 'init', 'fail', 2400, false),
('tx_hash_011', '2025-10-14 09:00:00', 'account_011', 1000000011, 'sync', 'done', 2500, true),
('tx_hash_012', '2025-10-15 10:10:00', 'account_012', 1000000012, 'sync', 'fail', 2600, false),
('tx_hash_013', '2025-10-16 11:20:00', 'account_013', 1000000013, 'init', 'done', 2700, true),
('tx_hash_014', '2025-10-17 12:30:00', 'account_014', 1000000014, 'init', 'fail', 2800, false),
('tx_hash_015', '2025-10-18 13:40:00', 'account_015', 1000000015, 'sync', 'done', 2900, true),
('tx_hash_016', '2025-10-19 14:50:00', 'account_016', 1000000016, 'sync', 'done', 3000, true),
('tx_hash_017', '2025-10-20 16:00:00', 'account_017', 1000000017, 'init', 'done', 3100, true),
('tx_hash_018', '2025-10-21 17:10:00', 'account_018', 1000000018, 'init', 'fail', 3200, false),
('tx_hash_019', '2025-10-22 18:20:00', 'account_019', 1000000019, 'sync', 'done', 3300, true),
('tx_hash_020', '2025-10-23 19:30:00', 'account_020', 1000000020, 'sync', 'done', 3400, true);



INSERT INTO `dynamic_partition_test` VALUES
('tx_hash_001', '2025-10-05 08:15:00', 'account_001', 1000000001, 'init', 'done', 1500, true),
('tx_hash_002', '2025-10-05 09:30:00', 'account_002', 1000000002, 'init', 'fail', 1600, false),
('tx_hash_003', '2025-10-06 10:45:00', 'account_003', 1000000003, 'sync', 'done', 1700, true),
('tx_hash_004', '2025-10-07 11:00:00', 'account_004', 1000000004, 'sync', 'fail', 1800, false),
('tx_hash_005', '2025-10-08 12:10:00', 'account_005', 1000000005, 'init', 'done', 1900, true),
('tx_hash_006', '2025-10-09 13:20:00', 'account_006', 1000000006, 'init', 'fail', 2000, false),
('tx_hash_007', '2025-10-10 14:25:00', 'account_007', 1000000007, 'sync', 'done', 2100, true),
('tx_hash_008', '2025-10-11 15:40:00', 'account_008', 1000000008, 'sync', 'done', 2200, true),
('tx_hash_009', '2025-10-12 16:50:00', 'account_009', 1000000009, 'init', 'done', 2300, true),
('tx_hash_010', '2025-10-13 17:55:00', 'account_010', 1000000010, 'init', 'fail', 2400, false),
('tx_hash_011', '2025-10-14 09:00:00', 'account_011', 1000000011, 'sync', 'done', 2500, true),
('tx_hash_012', '2025-10-15 10:10:00', 'account_012', 1000000012, 'sync', 'fail', 2600, false),
('tx_hash_013', '2025-10-16 11:20:00', 'account_013', 1000000013, 'init', 'done', 2700, true),
('tx_hash_014', '2025-10-17 12:30:00', 'account_014', 1000000014, 'init', 'fail', 2800, false),
('tx_hash_015', '2025-10-18 13:40:00', 'account_015', 1000000015, 'sync', 'done', 2900, true),
('tx_hash_016', '2025-10-19 14:50:00', 'account_016', 1000000016, 'sync', 'done', 3000, true),
('tx_hash_017', '2025-10-20 16:00:00', 'account_017', 1000000017, 'init', 'done', 3100, true),
('tx_hash_018', '2025-10-21 17:10:00', 'account_018', 1000000018, 'init', 'fail', 3200, false),
('tx_hash_019', '2025-10-22 18:20:00', 'account_019', 1000000019, 'sync', 'done', 3300, true),
('tx_hash_020', '2025-10-23 19:30:00', 'account_020', 1000000020, 'sync', 'done', 3400, true);


INSERT INTO `auto_partition_test` VALUES
('tx_hash_001', '2025-10-04 08:15:00', 'account_001', 1000000001, 'init', 'done', 1500, true),
('tx_hash_002', '2025-10-05 09:30:00', 'account_002', 1000000002, 'init', 'fail', 1600, false),
('tx_hash_003', '2025-10-06 10:45:00', 'account_003', 1000000003, 'sync', 'done', 1700, true),
('tx_hash_004', '2025-10-07 11:00:00', 'account_004', 1000000004, 'sync', 'fail', 1800, false),
('tx_hash_005', '2025-10-08 12:10:00', 'account_005', 1000000005, 'init', 'done', 1900, true),
('tx_hash_006', '2025-10-09 13:20:00', 'account_006', 1000000006, 'init', 'fail', 2000, false),
('tx_hash_007', '2025-10-10 14:25:00', 'account_007', 1000000007, 'sync', 'done', 2100, true),
('tx_hash_008', '2025-10-11 15:40:00', 'account_008', 1000000008, 'sync', 'done', 2200, true),
('tx_hash_009', '2025-10-12 16:50:00', 'account_009', 1000000009, 'init', 'done', 2300, true),
('tx_hash_010', '2025-10-13 17:55:00', 'account_010', 1000000010, 'init', 'fail', 2400, false),
('tx_hash_011', '2025-10-14 09:00:00', 'account_011', 1000000011, 'sync', 'done', 2500, true),
('tx_hash_012', '2025-10-15 10:10:00', 'account_012', 1000000012, 'sync', 'fail', 2600, false),
('tx_hash_013', '2025-10-16 11:20:00', 'account_013', 1000000013, 'init', 'done', 2700, true),
('tx_hash_014', '2025-10-17 12:30:00', 'account_014', 1000000014, 'init', 'fail', 2800, false),
('tx_hash_015', '2025-10-18 13:40:00', 'account_015', 1000000015, 'sync', 'done', 2900, true),
('tx_hash_016', '2025-10-19 14:50:00', 'account_016', 1000000016, 'sync', 'done', 3000, true),
('tx_hash_017', '2025-10-20 16:00:00', 'account_017', 1000000017, 'init', 'done', 3100, true),
('tx_hash_018', '2025-10-21 17:10:00', 'account_018', 1000000018, 'init', 'fail', 3200, false),
('tx_hash_019', '2025-10-22 18:20:00', 'account_019', 1000000019, 'sync', 'done', 3300, true),
('tx_hash_020', '2025-10-23 19:30:00', 'account_020', 1000000020, 'sync', 'done', 3400, true);

划分BE的tag

alter system modify backend "doriscluster-sample-be-0.doriscluster-sample-be-internal.doris.svc.cluster.local:9050" set ("tag.location" = "group_a");

alter system modify backend "doriscluster-sample-be-2.doriscluster-sample-be-internal.doris.svc.cluster.local:9050" set ("tag.location" = "group_b");


分别给非分区表、动态分区表和自动分区表进行tag 划分,调整历史分期,未来分区和表结构,并且进行观察

非分区表

  1. 修改tag
    ALTER TABLE test.no_partition_test SET ("replication_allocation" = "tag.location.group_a: 1,tag.location.group_b: 1");

  2. 确认
    show create table test.no_partition_test;

结果:

| no_partition_test | CREATE TABLE `no_partition_test` (
  `tx_hash` varchar(1024) NOT NULL COMMENT "Hash",
  `tx_datetime` datetime NOT NULL,
  `account` varchar(1024) NULL,
  `tx_lt` bigint NULL,
  `orig_status` varchar(1024) NULL,
  `end_status` varchar(1024) NULL,
  `total_fee` bigint NULL,
  `action_success` boolean NULL,
  INDEX idx_tx_datetime (`tx_datetime`) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(`tx_hash`, `tx_datetime`)
DISTRIBUTED BY HASH(`tx_hash`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.group_a: 1, tag.location.group_b: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
); |
+---------

show partitions from test.no_partition_test;

结果:
mysql> show partitions from no_partition_test\G;
*************************** 1. row ***************************
             PartitionId: 10597
           PartitionName: no_partition_test
          VisibleVersion: 2
      VisibleVersionTime: 2025-10-15 04:07:21
                   State: NORMAL
            PartitionKey:
                   Range:
         DistributionKey: tx_hash
                 Buckets: 10
          ReplicationNum: 2
           StorageMedium: HDD
            CooldownTime: 9999-12-31 15:59:59
     RemoteStoragePolicy:
LastConsistencyCheckTime: NULL
                DataSize: 35.721 KB
              IsInMemory: false
       ReplicaAllocation: tag.location.group_a: 1, tag.location.group_b: 1
               IsMutable: true
      SyncWithBaseTables: true
            UnsyncTables: NULL
        CommittedVersion: 2
                RowCount: 20
1 row in set (0.00 sec)

动态分区表

  1. 历史分区表

ALTER TABLE test.dynamic_partition_test MODIFY PARTITION (*) SET ("replication_allocation" = "tag.location.group_a: 1,tag.location.group_b: 1");

show partitions from table test.dynamic_partition_test;

结果:

mysql> show partitions from dynamic_partition_test limit 1\G;
*************************** 1. row ***************************
             PartitionId: 10643
           PartitionName: p20251005
          VisibleVersion: 2
      VisibleVersionTime: 2025-10-15 04:09:33
                   State: NORMAL
            PartitionKey: tx_datetime
                   Range: [types: [DATETIMEV2]; keys: [2025-10-05 00:00:00]; ..types: [DATETIMEV2]; keys: [2025-10-06 00:00:00]; )
         DistributionKey: tx_hash
                 Buckets: 10
          ReplicationNum: 2
           StorageMedium: HDD
            CooldownTime: 9999-12-31 15:59:59
     RemoteStoragePolicy:
LastConsistencyCheckTime: NULL
                DataSize: 9.766 KB
              IsInMemory: false
       ReplicaAllocation: tag.location.group_a: 1, tag.location.group_b: 1
               IsMutable: true
      SyncWithBaseTables: true
            UnsyncTables: NULL
        CommittedVersion: 2
                RowCount: 2
1 row in set (0.00 sec)

  1. 未来分区表:需要修改 dynamic_partition.replication_allocation 和 default.replication_allocation

ALTER TABLE test.dynamic_partition_test SET ("dynamic_partition.replication_allocation" = "tag.location.group_a: 1,tag.location.group_b: 1");

ALTER TABLE test.dynamic_partition_test SET ("default.replication_allocation" = "tag.location.group_a: 1,tag.location.group_b: 1");

show create table test.dynamic_partition_test;

 CREATE TABLE `dynamic_partition_test` (
  `tx_hash` varchar(1024) NOT NULL COMMENT "Hash",
  `tx_datetime` datetime NOT NULL,
  `account` varchar(1024) NULL,
  `tx_lt` bigint NULL,
  `orig_status` varchar(1024) NULL,
  `end_status` varchar(1024) NULL,
  `total_fee` bigint NULL,
  `action_success` boolean NULL,
  INDEX idx_tx_datetime (`tx_datetime`) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(`tx_hash`, `tx_datetime`)
PARTITION BY RANGE(`tx_datetime`)
(PARTITION p20251005 VALUES [('2025-10-05 00:00:00'), ('2025-10-06 00:00:00')),
PARTITION p20251006 VALUES [('2025-10-06 00:00:00'), ('2025-10-07 00:00:00')),
PARTITION p20251007 VALUES [('2025-10-07 00:00:00'), ('2025-10-08 00:00:00')),
PARTITION p20251008 VALUES [('2025-10-08 00:00:00'), ('2025-10-09 00:00:00')),
PARTITION p20251009 VALUES [('2025-10-09 00:00:00'), ('2025-10-10 00:00:00')),
PARTITION p20251010 VALUES [('2025-10-10 00:00:00'), ('2025-10-11 00:00:00')),
PARTITION p20251011 VALUES [('2025-10-11 00:00:00'), ('2025-10-12 00:00:00')),
PARTITION p20251012 VALUES [('2025-10-12 00:00:00'), ('2025-10-13 00:00:00')),
PARTITION p20251013 VALUES [('2025-10-13 00:00:00'), ('2025-10-14 00:00:00')),
PARTITION p20251014 VALUES [('2025-10-14 00:00:00'), ('2025-10-15 00:00:00')),
PARTITION p20251015 VALUES [('2025-10-15 00:00:00'), ('2025-10-16 00:00:00')),
PARTITION p20251016 VALUES [('2025-10-16 00:00:00'), ('2025-10-17 00:00:00')),
PARTITION p20251017 VALUES [('2025-10-17 00:00:00'), ('2025-10-18 00:00:00')),
PARTITION p20251018 VALUES [('2025-10-18 00:00:00'), ('2025-10-19 00:00:00')),
PARTITION p20251019 VALUES [('2025-10-19 00:00:00'), ('2025-10-20 00:00:00')),
PARTITION p20251020 VALUES [('2025-10-20 00:00:00'), ('2025-10-21 00:00:00')),
PARTITION p20251021 VALUES [('2025-10-21 00:00:00'), ('2025-10-22 00:00:00')),
PARTITION p20251022 VALUES [('2025-10-22 00:00:00'), ('2025-10-23 00:00:00')),
PARTITION p20251023 VALUES [('2025-10-23 00:00:00'), ('2025-10-24 00:00:00')),
PARTITION p20251024 VALUES [('2025-10-24 00:00:00'), ('2025-10-25 00:00:00')),
PARTITION p20251025 VALUES [('2025-10-25 00:00:00'), ('2025-10-26 00:00:00')))
DISTRIBUTED BY HASH(`tx_hash`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.group_a: 1, tag.location.group_b: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Etc/UTC",
"dynamic_partition.start" = "-10",
"dynamic_partition.end" = "10",
"dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.group_a: 1, tag.location.group_b: 1",
"dynamic_partition.buckets" = "10",
"dynamic_partition.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "-1",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
); |

自动分区表

  1. 历史分区

ALTER TABLE test.auto_partition_test MODIFY PARTITION (*) SET ("replication_allocation" = "tag.location.group_a: 1,tag.location.group_b: 1");

show partitions from table test.auto_partition_test;

mysql> show partitions from  test.auto_partition_test \G;
*************************** 1. row ***************************
             PartitionId: 11509
           PartitionName: p20251001000000
          VisibleVersion: 2
      VisibleVersionTime: 2025-10-15 04:07:50
                   State: NORMAL
            PartitionKey: tx_datetime
                   Range: [types: [DATETIMEV2]; keys: [2025-10-01 00:00:00]; ..types: [DATETIMEV2]; keys: [2025-11-01 00:00:00]; )
         DistributionKey: tx_hash
                 Buckets: 10
          ReplicationNum: 2
           StorageMedium: HDD
            CooldownTime: 9999-12-31 15:59:59
     RemoteStoragePolicy:
LastConsistencyCheckTime: NULL
                DataSize: 35.721 KB
              IsInMemory: false
       ReplicaAllocation: tag.location.group_a: 1, tag.location.group_b: 1
               IsMutable: true
      SyncWithBaseTables: true
            UnsyncTables: NULL
        CommittedVersion: 2
                RowCount: 20
1 row in set (0.00 sec)
  1. 未来分区

ALTER TABLE test.auto_partition_test SET ("default.replication_allocation" = "tag.location.group_a: 1,tag.location.group_b: 1");

show create table test.auto_partition_test;

| auto_partition_test | CREATE TABLE `auto_partition_test` (
  `tx_hash` varchar(1024) NOT NULL COMMENT "Hash",
  `tx_datetime` datetime NOT NULL,
  `account` varchar(1024) NULL,
  `tx_lt` bigint NULL,
  `orig_status` varchar(1024) NULL,
  `end_status` varchar(1024) NULL,
  `total_fee` bigint NULL,
  `action_success` boolean NULL,
  INDEX idx_tx_datetime (`tx_datetime`) USING INVERTED
) ENGINE=OLAP
UNIQUE KEY(`tx_hash`, `tx_datetime`)
AUTO PARTITION BY RANGE (date_trunc(`tx_datetime`, 'month'))
(PARTITION p20251001000000 VALUES [('2025-10-01 00:00:00'), ('2025-11-01 00:00:00')))
DISTRIBUTED BY HASH(`tx_hash`) BUCKETS 10
PROPERTIES (
"replication_allocation" = "tag.location.group_a: 1, tag.location.group_b: 1",
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V1",
"enable_unique_key_merge_on_write" = "true",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728",
"enable_mow_light_delete" = "false"
);
1 Answers

遇到类似场景,可以按照以上操作进行,后续有问题可以加主页微信进行讨论~