PolarDB:Split a hotspot partition

Usage notes

• To split a hotspot partition, the version of your PolarDB-X instance must be 5.4.14-16539836 or later.

• To split a hotspot subpartition, the version of your PolarDB-X instance must be 5.4.17-16952556 or later.

• Before you split a hotspot partition, the corresponding table must be a KEY partitioned table that contains KEY partitions or KEY subpartitions.

Terms

• Table group: a collection of logical tables or global index tables that share identical partition key columns.

• Global index: an indexing technique for partitioned tables. You can create global indexes on partitioned tables by using non-partition keys. Global indexes can provide unique constraints.

Syntax

In PolarDB-X, the KEY partitioning method routes data rows to partitions based on the hash values that are calculated by using the consistent hashing algorithm. When you create a partitioned table, PolarDB-X evenly distributes a continuous hash space across partitions based on the definitions of partitions of the partitioned table. By default, for partitioned tables that contain two or more partition keys, only the first partition key is used in the hash space allocation. Other partition keys are reserved as maxvalue. This builds a multi-dimensional vector space to meet future data requirements.

When a large number of data rows are distributed to a partition based on a specific value in the first partition key, the partition becomes a hotspot partition. For example, in an orders table that is partitioned by customer ID, the partition that contains the ID of the customer placing the largest number of orders may become a hotspot partition. In this case, PolarDB-X allows you to hash the values based on the second partition key to disperse hotspot data and balance the data distribution. If the value combinations of the first and second partition keys still cause hotspot, PolarDB-X allows you to further hash the values based on the third partition key, and so on. This ensures an even data distribution and optimizes overall performance. The multi-level hashing strategy can dynamically adjust partitions and eliminate the impact of hotspot data.

The following code block shows how to split hotspot partitions:

ALTER { TABLE tbl_name | TABLEGROUP tg_name | TABLEGROUP BY TABLE tbl_name } 
		split_partition_by_hot_value_definition
  | split_template_subpartition_by_hot_value_definition
  | split_non_template_subpartition_by_hot_value_definition

  
split_partition_by_hot_value_definition:
SPLIT INTO [part_name_prefix] PARTITIONS partition_count BY HOT VALUE(hot_value)

split_template_subpartition_by_hot_value_definition:
SPLIT INTO [subpart_name_prefix] SUBPARTITIONS subpartition_count BY HOT VALUE(hot_value)

split_non_template_subpartition_by_hot_value_definition:
MODIFY PARTITION part_name SPLIT INTO [subpart_name_prefix] SUBPARTITIONS subpartition_count BY HOT VALUE(hot_value)

Scenario 1: Split a KEY partition that does not contain subpartitions

Split a partition of a table

The following code block shows the definition of the orders table:

create table orders ( id int(11) not null auto_increment, 
 seller_id int(11) default null, 
 primary key (id) )
 partition by key(seller_id, id) partitions 5

In this example, the orders data of a major seller with a partition key value of seller_id=88 is used. You can execute the following statement to distribute the orders data of the major seller into 10 new partitions and specify that the names of the new partitions are prefixed with hot88_. The prefix is used to identify which partitions are split from a hotspot partition.

alter table orders split into hot88_ partitions 10 by hot value(88)

If you split a partition of a partitioned table that contains only one partition key column, you must add a second partition key. For example, if you split the orders table defined in the following code block, you must add a second partition key before you can distribute the hotspot data across multiple partitions.

create table orders ( id int(11) not null auto_increment, 
 seller_id int(11) default null, 
 primary key (id) )
 partition by key(seller_id) partitions 5

This way, secondary hashing can be performed based on the hash space of the second partition key. For example, you can execute the following SQL statement to add a partition key named id:

alter table orders partition by key(seller_id,id) partitions 5;

Split partitions in a table group

The operations are similar to those for splitting table-level partitions. You need only to replace alter table #tb with alter tablegroup #tgname or alter tablegroup by #tb.

Scenario 2: Split a partition that contains subpartitions

Split a partition of a table

The following code block shows the definition of the t1 partitioned table:

create table t1 (
a bigint unsigned not null,
b bigint unsigned not null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition by key (a,b) partitions 4
subpartition by range columns (c,d)
(
partition p1
(
subpartition p1sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p1sp2 values less than ( maxvalue, maxvalue )
),
partition p2
(
subpartition p2sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p2sp2 values less than ( '2021-01-01', 'abc' ),
subpartition p2sp3 values less than ( '2022-01-01', 'abc' ),
subpartition p2sp4 values less than ( maxvalue, maxvalue )
),
partition p3
(
subpartition p3sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p3sp2 values less than ( maxvalue, maxvalue )
),
partition p4
(
subpartition p4sp1 values less than ( '2020-01-01', 'abc' ),
subpartition p4sp2 values less than ( maxvalue, maxvalue )
)
);

If you want to split hotspot data in a partition based on a partition key value, such as a=66, across 5 partitions, you can execute the following SQL statement similar to that used in Scenario 1:

alter table t1 split into hot66_ partitions 5 by hot value(66);

After you split hotspot data, you can execute the show create table t1 statement to observe the table structure. The table structure is similar to that before splitting. However, the names of the partitions may be different. The subpartitions in the new partitions that are split from the hotspot partition share the same definitions.

show create table t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
	`a` bigint(20) UNSIGNED NOT NULL,
	`b` bigint(20) UNSIGNED NOT NULL,
	`c` datetime NOT NULL,
	`d` varchar(16) NOT NULL,
	`e` varchar(16) NOT NULL,
	KEY `auto_shard_key_a_b` USING BTREE (`a`, `b`),
	KEY `auto_shard_key_c_d` USING BTREE (`c`, `d`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`,`b`)
SUBPARTITION BY RANGE COLUMNS(`c`,`d`)
(PARTITION `p1`
 (SUBPARTITION `p1sp1` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p1sp2` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `p2`
 (SUBPARTITION `p2sp1` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p2sp2` VALUES LESS THAN ('2021-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p2sp3` VALUES LESS THAN ('2022-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p2sp4` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `p3`
 (SUBPARTITION `p3sp1` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `p3sp2` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `p5`
 (SUBPARTITION `sp7` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp8` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_1`
 (SUBPARTITION `sp9` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp10` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_2`
 (SUBPARTITION `sp11` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp12` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_3`
 (SUBPARTITION `sp13` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp14` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_4`
 (SUBPARTITION `sp15` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp16` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `hot66_5`
 (SUBPARTITION `sp17` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp18` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB),
 PARTITION `p6`
 (SUBPARTITION `sp19` VALUES LESS THAN ('2020-01-01 00:00:00','abc') ENGINE = InnoDB,
 SUBPARTITION `sp20` VALUES LESS THAN (MAXVALUE,MAXVALUE) ENGINE = InnoDB))

Split partitions in a table group

The operations are similar to those for splitting table-level partitions. You need only to replace alter table #tb with alter tablegroup #tgname or alter tablegroup by #tb.

Scenario 3: Split a templated subpartition

Split a partition of a table

The following code block shows the definition of the tb1 table that contains templated subpartitions:

create table tb1 (
a bigint unsigned not null,
b bigint unsigned not null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition by key (c,d) partitions 3
subpartition by key (a,b) subpartitions 3;

If a subpartition contains hotspot data, such as data with a partition key value of "a=55" (data of a major seller), you can execute the following SQL statement to split the hotspot data into 5 new partitions:

alter table tb1 split into hot55_ subpartitions 5 by hot value(55);

After you execute the preceding SQL statement, data with a partition key value of "a=55" under all partitions are distributed across the 5 new partitions.

Split partitions in a table group

The operations are similar to those for splitting table-level partitions. You need only to replace alter table #tb with alter tablegroup #tgname or alter tablegroup by #tb.

Scenario 4: Split a non-templated subpartition

Split a partition of a table

The following code block shows the definition of the tb1 table that contains non-templated subpartitions:

create table tb1 (
a bigint unsigned not null,
b bigint unsigned not null,
c datetime NOT NULL,
d varchar(16) NOT NULL,
e varchar(16) NOT NULL
)
partition by key (c,d) partitions 2
subpartition by key (a,b)
(
partition p1 subpartitions 2,
partition p2 subpartitions 4
);

If a subpartition contains hotspot data, such as data with a partition key value of "a=55" (data of a major seller), you can execute the following SQL statement to split the hotspot data into 5 new partitions:

alter table tb1 modify partition p1 split into hot55_ subpartitions 5 by hot value(55);

After you execute the preceding SQL statement, data with a partition key value of "a=55" under all partitions are distributed across the 5 new partitions.

Split partitions in a table group

The operations are similar to those for splitting partitions in tables. You need only to replace alter table #tb with alter tablegroup #tgname or alter tablegroup by #tb.