You can set partition weights for tables based on business requirements. Partition weights can be set at the table level or partition level, from top to bottom.
Limitations and considerations
Partition weights can be set only in user tenants, not in the sys tenant.
Currently, you can set partition weights only for non-partitioned tables and partitions of partitioned tables, but not for subpartitions.
Weights can only be set for existing entity partitions. Weights cannot be specified when creating a table. You must create the table first, and then set the partition weights for the table.
Partition weights must be integers in the range of [1, +∞). By default, a partition has no weight.
When setting partition weights, it is recommended to use as few weight tiers as possible. You are advised to divide them into at most three tiers:
High weight = 100% * number of partitions
Medium weight = 50% * number of partitions
Low weight = 1
For partitioned tables that do not need to participate in weight balancing, it is recommended not to set partition weights.
When setting partition weights, subpartitions inherit the weight of their parent partition by default. If partition weights are set at multiple levels, the weight of a subpartition overrides that of its parent.
After setting partition weights, if you perform an offline DDL operation (such as Truncate Table or Truncate Partition) that causes changes to the partition entities, the previously set partition weights become invalid and must be reset. For offline DDL operations that may cause partition weights to become invalid, see Online DDL and Offline DDL Operations.
Set partition weights
Log in to the user tenant of the cluster.
Note that you must specify the corresponding options in the following sample code based on your actual database configurations.
obclient -h10.xx.xx.xx -P2883 -uroot@mysql001#obdemo -p***** -A(Optional) Create the table for which you want to set partition weights.
For example, create a hash-partitioned table named
tbl1_h.obclient(root@mysql001)[test]> CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50)) PARTITION BY HASH(col1) PARTITIONS 5;(Optional) Retrieve the partition information of the table.
obclient(root@mysql001)[test]> SELECT TABLE_NAME, PARTITION_NAME FROM information_schema.PARTITIONS WHERE TABLE_NAME ='tbl1_h';The query result is as follows:
+------------+----------------+ | TABLE_NAME | PARTITION_NAME | +------------+----------------+ | tbl1_h | p0 | | tbl1_h | p3 | | tbl1_h | p2 | | tbl1_h | p4 | | tbl1_h | p1 | +------------+----------------+ 5 rows in setSet the partition weight to an appropriate level based on your business requirements.
Partition weights can only be integers, with a range of [1, +∞). By default, partitions have no weight.
Set table-level weight
You can set the table-level partition weight for both partitioned and non-partitioned tables. For example, to set the partition weight of the partitioned table
tbl1_hto 1, use the following statement.obclient(root@mysql001)[test]> CALL DBMS_BALANCE.SET_BALANCE_WEIGHT(1,'test','tbl1_h');Set partition-level weight
To set the partition weight of partition
p0in tabletbl1_hto 2, use the following statement.obclient(root@mysql001)[test]> CALL DBMS_BALANCE.SET_BALANCE_WEIGHT(2,'test','tbl1_h','p0');
After the preceding steps are completed, view the partition weight information of the table.
obclient(root@mysql001)[test]> SELECT * FROM oceanbase.DBA_OB_OBJECT_BALANCE_WEIGHT;The query result is as follows:
+----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+ | TABLE_ID | PARTITION_ID | SUBPARTITION_ID | WEIGHT | DATABASE_NAME | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | TABLEGROUP_NAME | DATABASE_ID | TABLEGROUP_ID | OBJECT_ID | +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+ | 500002 | NULL | NULL | 1 | test | tbl1_h | NULL | NULL | NULL | 500001 | NULL | 500002 | | 500002 | 500003 | NULL | 2 | test | tbl1_h | p0 | NULL | NULL | 500001 | NULL | 500003 | +----------+--------------+-----------------+--------+---------------+------------+----------------+-------------------+-----------------+-------------+---------------+-----------+ 2 rows in set
What to do next
After setting the partition weights, if you want to achieve balanced partition distribution, you can call the DBMS_BALANCE.TRIGGER_PARTITION_BALANCE subprogram to manually trigger partition balancing. For more information about how to manually trigger partition balancing, see Manually trigger partition balancing.
Alternatively, you can wait for the scheduled partition balancing task to trigger. For information about scheduled partition balancing tasks, see Configure a scheduled partition balancing task.