背景
在存量集群上进行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 划分,调整历史分期,未来分区和表结构,并且进行观察
非分区表
-
修改tag
ALTER TABLE test.no_partition_test SET ("replication_allocation" = "tag.location.group_a: 1,tag.location.group_b: 1"); -
确认
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)
动态分区表
- 历史分区表
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)
- 未来分区表:需要修改 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"
); |
自动分区表
- 历史分区
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)
- 未来分区
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"
);