Locality - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to use the LOCALITY attribute to specify data nodes.

You can use the LOCALITY attribute for databases in AUTO mode to specify data nodes for databases, tables, or partitions. This helps isolate data and evenly distribute data to data nodes.

You can use the LOCALITY attribute in the following scenarios:

You can use the LOCALITY attribute to specify data nodes for databases, tables, and partitions. After you specify the LOCALITY attribute for a database, a table, or a partition, the operations that are performed to modify data distribution are restricted by the value of the LOCALITY attribute. Delete operations that are performed to delete data nodes are not restricted by the value of the LOCALITY attribute.
You can change the value of the LOCALITY attribute for table groups and partition groups. After you change the value of the LOCALITY attribute, an asynchronous data migration task is automatically triggered.

Prerequisites

The kernel version of your PolarDB-X instance is 5.4.14 or later.
The logical database is partitioned in AUTO mode.

Note

For information about how to view the kernel version of a PolarDB-X instance, see View and update the version of an instance.

Precautions

After you delete a data node, the LOCALITY attribute that includes the ID of the data node automatically becomes invalid.
The LOCALITY attribute is specified by using the IDs of data nodes. After you restore data on the data nodes from backups, the original LOCALITY attribute of the database object automatically becomes invalid.

Define the LOCALITY attribute of a database object

View information about the data nodes of a PolarDB-X instance

You can execute the following statement to view information about the data nodes of a PolarDB-X instance:

SHOW STORAGE;

Sample result:

+--------------------+----------------------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+
| STORAGE_INST_ID    | LEADER_NODE                      | IS_HEALTHY | INST_KIND | DB_COUNT | GROUP_COUNT | STATUS | DELETABLE | DELAY | ACTIVE |
+--------------------+----------------------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+
| polardbx-ng28-dn-0 | polardbx-ng28-dn-0-cands-0:14289 | true       | MASTER    | 1        | 2           | 0      | false     | null  | null   |
| polardbx-ng28-dn-1 | polardbx-ng28-dn-1-cands-0:14176 | true       | MASTER    | 1        | 1           | 0      | true      | null  | null   |
| polardbx-ng28-dn-2 | polardbx-ng28-dn-2-cands-0:14568 | true       | MASTER    | 1        | 1           | 0      | true      | null  | null   |
| polardbx-ng28-dn-3 | polardbx-ng28-dn-3-cands-0:16796 | true       | MASTER    | 1        | 1           | 0      | true      | null  | null   |
| polardbx-ng28-gms  | polardbx-ng28-dn-0-cands-0:14289 | true       | META_DB   | 2        | 2           | 0      | false     | null  | null   |
+--------------------+----------------------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+

Note

STORAGE_INST_ID: the ID of the data node. The LOCALITY attribute is specified by using the IDs of data nodes.
INST_KIND: the type of the data node. If the value is META_DB, the data node is a metadata node, which cannot be used to store business data.
STATUS: the status of the data node. If the value is 0, the data node is available.
DELTETABLE: indicates whether the data node can be deleted. If the value is false, the data node cannot be deleted. In the preceding example, you cannot delete the metadata node and the data node whose ID is polardbx-ng28-dn-0.

Specify the LOCALITY attribute when you create a database

When you create a database, you can specify the data nodes on which you want to store data in the database. This way, data in the database is isolated from data in other databases in the same PolarDB-X instance.

Syntax:

CREATE DATABASE database_name databse [database_option_list];

Create a database that uses the AUTO partitioning mode and specify the LOCALITY attribute for the database. For more information about the syntax, see CREATE DATABASE.

Example:

CREATE DATABASE db1 LOCALITY='dn=polardbx-ng28-dn-0,polardbx-ng28-dn-1,polardbx-ng28-dn-2' MODE = 'auto';

After the database is created, you can execute the SHOW CREATE DATABASE statement to view information about the database. To view the data nodes that are used to store data in the database, you can execute the SHOW DS statement.

mysql> SHOW CREATE DATABASE `db1`;
+----------+--------------------------------------------------------------------------------------------------------------------+
| DATABASE | CREATE DATABASE                                                                                                    |
+----------+--------------------------------------------------------------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /* MODE = 'auto' LOCALITY = "dn=polardbx-ng28-dn-0,polardbx-ng28-dn-1,polardbx-ng28-dn-2" */ |
+----------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

mysql> SHOW DS;
+----+--------------------+--------------------+---------------------------------+----------------------+---------+
| ID | STORAGE_INST_ID    | DB                 | GROUP                           | PHY_DB               | MOVABLE |
+----+--------------------+--------------------+---------------------------------+----------------------+---------+
|  0 | polardbx-ng28-dn-0 | db1                | DB1_P00000_GROUP                | db1_p00000           |       1 |
|  1 | polardbx-ng28-dn-1 | db1                | DB1_P00001_GROUP                | db1_p00001           |       1 |
|  2 | polardbx-ng28-dn-2 | db1                | DB1_P00002_GROUP                | db1_p00002           |       1 |
|  3 | polardbx-ng28-gms  | information_schema | INFORMATION_SCHEMA_SINGLE_GROUP | polardbx_info_schema |       0 |
+----+--------------------+--------------------+---------------------------------+----------------------+---------+
4 rows in set (0.04 sec)

Note

The storage locations of a database must include a data node whose serial number is 0. This limit is not imposed on the storage locations of tables and partitions.

Specify the LOCALITY attribute when you create a non-partitioned table

When you create a non-partitioned table, you can specify the LOCALITY attribute to enable automatic sharding of the table across multiple data nodes. The shards of the non-partitioned table are automatically balanced on the data nodes based on the number of non-partitioned tables and the amount of data on each data node.

Create multiple non-partitioned tables in an instance and specify the LOCALITY attribute as "balance_single_table=on" for the tables. For more information about the syntax, see CREATE TABLE (AUTO mode).

Example:

CREATE TABLE t_sgl1 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) SINGLE locality = 'balance_single_table=on';

CREATE TABLE t_sgl2 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) SINGLE locality = 'balance_single_table=on';

CREATE TABLE t_sgl3 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) SINGLE locality = 'balance_single_table=on';

CREATE TABLE t_sgl4 (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) SINGLE locality = 'balance_single_table=on';

After the non-partitioned tables are created, you can execute the SHOW CREATE TABLE statement to view information about the tables. To view the data distribution of the non-partitioned tables, you can execute the SHOW TOPOLOGY statement.

mysql> SHOW CREATE TABLE `t_sgl1`;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                        |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_sgl1` (
	`id` bigint(11) NOT NULL AUTO_INCREMENT,
	`order_id` varchar(20) DEFAULT NULL,
	`buyer_id` varchar(20) DEFAULT NULL,
	`seller_id` varchar(20) DEFAULT NULL,
	`order_snapshot` longtext,
	`order_detail` longtext,
	PRIMARY KEY (`id`),
	INDEX `l_i_order` (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
/* LOCALITY='balance_single_table=on' */ |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> SHOW TOPOLOGY `t_sgl1`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  1 | DB1_P00001_GROUP | t_sgl1_92mv_00000  | p1             | db1_p00001  | polardbx-ng28-dn-1 |
+----+------------------+--------------------+----------------+-------------+--------------------+
3 rows in set (0.15 sec)

mysql> SHOW TOPOLOGY `t_sgl2`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  1 | DB1_P00002_GROUP | t_sgl2_87fg_00000  | p1             | db1_p00002  | polardbx-ng28-dn-2 |
+----+------------------+--------------------+----------------+-------------+--------------------+
3 rows in set (0.15 sec)

mysql> SHOW TOPOLOGY `t_sgl3`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  1 | DB1_P00002_GROUP | t_sgl3_64uj_00000  | p1             | db1_p00001  | polardbx-ng28-dn-1 |
+----+------------------+--------------------+----------------+-------------+--------------------+
3 rows in set (0.15 sec)

mysql> SHOW TOPOLOGY `t_sgl4`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  1 | DB1_P00001_GROUP | t_sgl4_71mb_00000  | p1             | db1_p00002  | polardbx-ng28-dn-2 |
+----+------------------+--------------------+----------------+-------------+--------------------+
3 rows in set (0.15 sec)

The preceding result indicates that the non-partitioned tables are automatically sharded across multiple data nodes.

Specify the LOCALITY attribute when you create a logical table

When you create a logical table, you can specify the data nodes on which you want to store data in the table. This way, data in the table is isolated from data in other tables in the same database. You can specify the LOCALITY attribute for non-partitioned tables and tables that are partitioned by using the RANGE partitioning method, HASH partitioning method, or LIST partitioning method.

Syntax:

CREATE [PARTITION] TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
    [table_options]
    [table_partition_definition]
    [local_partition_definition]

Create a logical table in an instance and specify the LOCALITY attribute for the table. For more information about the syntax, see CREATE TABLE (AUTO mode).

Example:

CREATE TABLE t_order (
  `id` bigint(11) NOT NULL AUTO_INCREMENT BY GROUP,
  `order_id` varchar(20) DEFAULT NULL,
  `buyer_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  `order_snapshot` longtext DEFAULT NULL,
  `order_detail` longtext DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 locality = 'dn=polardbx-ng28-dn-1,polardbx-ng28-dn-2';

After the logical table is created, you can execute the SHOW CREATE TABLE statement to view information about the table. To view the data distribution of the logical table, you can execute the SHOW TOPOLOGY statement.

mysql> SHOW CREATE TABLE `t_order`;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE   | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                        |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_order` (
	`id` bigint(11) NOT NULL AUTO_INCREMENT,
	`order_id` varchar(20) DEFAULT NULL,
	`buyer_id` varchar(20) DEFAULT NULL,
	`seller_id` varchar(20) DEFAULT NULL,
	`order_snapshot` longtext,
	`order_detail` longtext,
	PRIMARY KEY (`id`),
	INDEX `l_i_order` (`order_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8
/* LOCALITY='dn=polardbx-ng28-dn-1,polardbx-ng28-dn-2' */ |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> SHOW TOPOLOGY `t_order`;
+----+------------------+--------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME         | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------+----------------+-------------+--------------------+
|  0 | DB1_P00002_GROUP | t_order_18dV_00001 | p2             | db1_p00002  | polardbx-ng28-dn-2 |
|  1 | DB1_P00001_GROUP | t_order_18dV_00000 | p1             | db1_p00001  | polardbx-ng28-dn-1 |
|  2 | DB1_P00001_GROUP | t_order_18dV_00002 | p3             | db1_p00001  | polardbx-ng28-dn-1 |
+----+------------------+--------------------+----------------+-------------+--------------------+
3 rows in set (0.15 sec)

Note

The data nodes that are specified in the LOCALITY attribute of the logical table must be included in the data node set of the database that contains the logical table.
If you want to create a non-partitioned table, you can specify only one data node in the LOCALITY attribute for the logical table.
If the logical table belongs to a logical table group, the value of the LOCALITY attribute of the logical table must be the same as the LOCALITY attribute values of the table group, partition, and partition group.
By default, the logical table, logical table group, global secondary indexes (GSIs) of the logical table, and GSI table group share the same LOCALITY attribute value.

Specify the LOCALITY attribute for partitions of a logical table

When you create a logical table, you can specify a data node for each partition of the table. This way, different partitions of the logical table are stored on different data nodes.

Example:

CREATE TABLE orders_region(
 order_id int AUTO_INCREMENT primary key,
 customer_id int,
 country varchar(64),
 city varchar(64),
 order_time datetime not null)
PARTITION BY LIST COLUMNS(country,city)
(
  PARTITION p1 VALUES IN (('China','Shanghai')) LOCALITY = 'dn=polardbx-ng28-dn-2',
  PARTITION p2 VALUES IN (('China','Beijing')) LOCALITY = 'dn=polardbx-ng28-dn-2',
  PARTITION p3 VALUES IN (('China','Hangzhou')) ,
  PARTITION p4 VALUES IN (('China','Nanjing')) ,
  PARTITION p5 VALUES IN (('China','Guangzhou')) ,
  PARTITION p6 VALUES IN (('China','Shenzhen')) ,
  PARTITION p7 VALUES IN (('China','Wuhan')) ,
  PARTITION p8 VALUES IN (('America','New York'))
) LOCALITY = 'dn=polardbx-ng28-dn-0,polardbx-ng28-dn-1';

mysql> SHOW CREATE TABLE `orders_region`;
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TABLE         | CREATE TABLE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| orders_region | CREATE TABLE `orders_region` (
	`order_id` int(11) NOT NULL AUTO_INCREMENT,
	`customer_id` int(11) DEFAULT NULL,
	`country` varchar(64) DEFAULT NULL,
	`city` varchar(64) DEFAULT NULL,
	`order_time` datetime NOT NULL,
	PRIMARY KEY (`order_id`),
	KEY `auto_shard_key_country_city` USING BTREE (`country`, `city`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
/* LOCALITY='dn=polardbx-ng28-dn-0,polardbx-ng28-dn-1' */
PARTITION BY LIST COLUMNS(`country`,`city`)
(PARTITION `p1` VALUES IN (('China','Shanghai')) ENGINE = InnoDB LOCALITY='dn=polardbx-ng28-dn-2',
 PARTITION `p2` VALUES IN (('China','Beijing')) ENGINE = InnoDB LOCALITY='dn=polardbx-ng28-dn-2',
 PARTITION `p3` VALUES IN (('China','Hangzhou')) ENGINE = InnoDB,
 PARTITION `p4` VALUES IN (('China','Nanjing')) ENGINE = InnoDB,
 PARTITION `p5` VALUES IN (('China','Guangzhou')) ENGINE = InnoDB,
 PARTITION `p6` VALUES IN (('China','Shenzhen')) ENGINE = InnoDB,
 PARTITION `p7` VALUES IN (('China','Wuhan')) ENGINE = InnoDB,
 PARTITION `p8` VALUES IN (('America','New York')) ENGINE = InnoDB) |
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> SHOW TOPOLOGY `orders_region`;
+----+------------------+--------------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME               | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------------+----------------+-------------+--------------------+
|  0 | DB1_P00002_GROUP | orders_region_RlsY_00000 | p1             | db1_p00002  | polardbx-ng28-dn-2 |
|  1 | DB1_P00002_GROUP | orders_region_RlsY_00001 | p2             | db1_p00002  | polardbx-ng28-dn-2 |
|  2 | DB1_P00001_GROUP | orders_region_RlsY_00003 | p4             | db1_p00001  | polardbx-ng28-dn-1 |
|  3 | DB1_P00001_GROUP | orders_region_RlsY_00004 | p5             | db1_p00001  | polardbx-ng28-dn-1 |
|  4 | DB1_P00001_GROUP | orders_region_RlsY_00006 | p7             | db1_p00001  | polardbx-ng28-dn-1 |
|  5 | DB1_P00000_GROUP | orders_region_RlsY_00002 | p3             | db1_p00000  | polardbx-ng28-dn-0 |
|  6 | DB1_P00000_GROUP | orders_region_RlsY_00005 | p6             | db1_p00000  | polardbx-ng28-dn-0 |
|  7 | DB1_P00000_GROUP | orders_region_RlsY_00007 | p8             | db1_p00000  | polardbx-ng28-dn-0 |
+----+------------------+--------------------------+----------------+-------------+--------------------+
8 rows in set (0.25 sec)

Note

The data nodes that are specified for the partitions of the logical table must be included in the data node set of the database that contains the table partitions. These data nodes can be excluded from the data node set for the logical table.

Change the LOCALITY attribute of a database object

Change the LOCALITY attribute of a table group

You can dynamically change the LOCALITY attribute of a table group based on your business requirements.

Syntax:

ALTER TABLEGROUP identifier SET LOCALITY = locality_option
locality_option:
    'dn=storage_inst_id_list'
 |  ''

Example:

For example, if the orders_region table is included in the tg3 table group, you can execute the following statement to change the LOCALITY attribute of the tg3 table group:

ALTER TABLEGROUP `tg3` SET LOCALITY = `dn=polardbx-ng28-dn-0`;

After the LOCALITY attribute of the tg3 table group is changed, you can execute the SHOW TOPOLOGY statement to view the topology relationships in the orders_region table.

mysql> SHOW TOPOLOGY `orders_region`;
+----+------------------+--------------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME               | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------------+----------------+-------------+--------------------+
|  0 | DB1_P00002_GROUP | orders_region_RlsY_00000 | p1             | db1_p00002  | polardbx-ng28-dn-2 |
|  1 | DB1_P00002_GROUP | orders_region_RlsY_00001 | p2             | db1_p00002  | polardbx-ng28-dn-2 |
|  2 | DB1_P00000_GROUP | orders_region_RlsY_00002 | p3             | db1_p00000  | polardbx-ng28-dn-0 |
|  3 | DB1_P00000_GROUP | orders_region_RlsY_00003 | p4             | db1_p00000  | polardbx-ng28-dn-0 |
|  4 | DB1_P00000_GROUP | orders_region_RlsY_00004 | p5             | db1_p00000  | polardbx-ng28-dn-0 |
|  5 | DB1_P00000_GROUP | orders_region_RlsY_00005 | p6             | db1_p00000  | polardbx-ng28-dn-0 |
|  6 | DB1_P00000_GROUP | orders_region_RlsY_00006 | p7             | db1_p00000  | polardbx-ng28-dn-0 |
|  7 | DB1_P00000_GROUP | orders_region_RlsY_00007 | p8             | db1_p00000  | polardbx-ng28-dn-0 |
+----+------------------+--------------------------+----------------+-------------+--------------------+
8 rows in set (0.17 sec)

After the LOCALITY attribute of the tg3 table group is changed, the data nodes of table partitions for which you did not specify the LOCALITY attribute when you created the corresponding tables are changed. The data nodes of table partitions for which you specified the LOCALITY attribute when you created the corresponding tables remain unchanged.

Note

After you modify the metadata of a table group or partition group whose LOCALITY attribute is changed, the new metadata is immediately sent to the backend system. Then, the backend system generates a migration task to asynchronously migrate partitions in the table group. In this example, the backend system runs the REBALANCE TABLEGROUP tg3 task to migrate partitions. You can execute the following statement to query the progress of the migration task from information_schema.ddl_plan:

mysql> select * from information_schema.ddl_plan where table_schema = "db1";
+----+---------------------+---------------------+--------------+------------------------------------------------------------------+---------+-----------+----------+-------------+--------+--------+---------------------+---------------------+----------------+
| ID | plan_id             | job_id              | table_schema | ddl_stmt                                                         | state   | ddl_type  | progress | retry_count | result | extras | gmt_created         | gmt_modified        | resource       |
+----+---------------------+---------------------+--------------+------------------------------------------------------------------+---------+-----------+----------+-------------+--------+--------+---------------------+---------------------+----------------+
|  1 | 1465819565798723584 | 1465819579241467904 | db1          | REBALANCE TABLEGROUP `tg3`  EXPLAIN=false ASYNC=true DEBUG=false | SUCCESS | REBALANCE |      100 |           0 |        |        | 2022-05-24 14:37:58 | 2022-05-24 14:38:11 | tablegroup:tg3 |
+----+---------------------+---------------------+--------------+------------------------------------------------------------------+---------+-----------+----------+-------------+--------+--------+---------------------+---------------------+----------------+

Change the LOCALITY attribute of a partition group

You can dynamically change the LOCALITY attribute of a partition group based on your business requirements.

Syntax:

ALTER TABLEGROUP identifier SET PARTITIONS part_name LOCALITY = locality_option
locality_option:
    'dn=storage_inst_id_list'
 |  ''

Example:

For example, if the orders_region table is included in the tg3 table group and the p3 partition is included in the orders_region table, you can execute the following statement to change the LOCALITY attribute of the p3 partition:

ALTER TABLEGROUP `tg3` SET PARTITIONS p3 LOCALITY = `dn=polardbx-ng28-dn-1`;

After the LOCALITY attribute of the p3 partition is changed, you can execute the SHOW TOPOLOGY statement to view the topology relationships in the orders_region table.

mysql> show topology orders_region;
+----+------------------+--------------------------+----------------+-------------+--------------------+
| ID | GROUP_NAME       | TABLE_NAME               | PARTITION_NAME | PHY_DB_NAME | DN_ID              |
+----+------------------+--------------------------+----------------+-------------+--------------------+
|  0 | DB1_P00002_GROUP | orders_region_RlsY_00000 | p1             | db1_p00002  | polardbx-ng28-dn-2 |
|  1 | DB1_P00002_GROUP | orders_region_RlsY_00001 | p2             | db1_p00002  | polardbx-ng28-dn-2 |
|  2 | DB1_P00001_GROUP | orders_region_RlsY_00002 | p3             | db1_p00001  | polardbx-ng28-dn-1 |
|  3 | DB1_P00000_GROUP | orders_region_RlsY_00003 | p4             | db1_p00000  | polardbx-ng28-dn-0 |
|  4 | DB1_P00000_GROUP | orders_region_RlsY_00004 | p5             | db1_p00000  | polardbx-ng28-dn-0 |
|  5 | DB1_P00000_GROUP | orders_region_RlsY_00005 | p6             | db1_p00000  | polardbx-ng28-dn-0 |
|  6 | DB1_P00000_GROUP | orders_region_RlsY_00006 | p7             | db1_p00000  | polardbx-ng28-dn-0 |
|  7 | DB1_P00000_GROUP | orders_region_RlsY_00007 | p8             | db1_p00000  | polardbx-ng28-dn-0 |
+----+------------------+--------------------------+----------------+-------------+--------------------+
8 rows in set (0.11 sec)

Change the LOCALITY attribute of a table

You can dynamically change the LOCALITY attribute of a table based on your business requirements. Take note that this operation triggers data migration.

To change a non-partitioned table whose LOCALITY attribute is "balance_single_table=on" into a partitioned table, you must explicitly remove the LOCALITY attribute by performing a repartition. Example:

ALTER TABLE t_sgl1 PARTITION BY HASH(ID) PARTITIONS 32 LOCALITY = "";

To change the LOCALITY attribute of a logical table, you can directly modify the LOCALITY attribute by performing a repartition. Example:

ALTER TABLE t_sgl1 PARTITION BY HASH(ID) PARTITIONS 8 LOCALITY = "dn=dn1";

Value changes of the LOCALITY attribute after partition modification operations

Databases in AUTO mode support partition modification operations including ALTER TABLEGROUP operations, operations that are performed to change the types of tables, and operations that are performed to change the partitioning strategies of tables. For more information, see ALTER TABLEGROUP and Change the type and partitioning rule of a table.

After a partition modification operation is performed, the new partition group or table group inherits the original value of the LOCALITY attribute, and the system performs data distribution for the new table group or partition group based on the original value of the LOCALITY attribute. The value of the LOCALITY attribute changes in the following scenarios:

After a table is converted into a non-partitioned table or a broadcast table, the value of the LOCALITY attribute of the table becomes empty.
After a partition that stores hot data is split into multiple partitions, the LOCALITY attribute values of the new partitions become empty.
After multiple partitions whose LOCALITY attribute values are different are merged into one partition, the LOCALITY attribute value of the new partition is empty.

LOCALITY attribute changes after partition modification operations:

Operation type	Object	Whether the LOCALITY attribute changes
Change the partitioning strategy	Table	No
Change the table type	Table	The LOCALITY attribute remains unchanged only if a non-partitioned table is converted into a partitioned table.
Migrate partitions	Partition group	No
Merge partitions	Partition group	The new partition inherits the LOCALITY attribute value of the merged partitions only if the merged partitions share the same LOCALITY attribute value.
Split a partition	Partition group	No
Split a partition by hot value	Partition group	Yes
Extract hot data from a partition	Partition group	Yes
Drop a partition	Partition group	-
Add a partition	Partition group	No
Modify partitions by using the LIST partitioning method	Partition group	No
Rename a partition	Partition group	No

Scenarios

When you create a database, you can use the LOCALITY attribute to specify data nodes for logical databases to isolate data. Tables and partitions in the database are automatically distributed across the specified data nodes.
When you create a non-partitioned table, you can use the LOCALITY attribute to specify a data node for the table. This helps avoid high storage pressure on the data node whose serial number is 0 when a large number of non-partitioned tables exist.
When you create a partitioned table, you can use the LOCALITY attribute to specify data nodes for partitions. This way, the partitions of the table can be distributed across different data nodes. For example, you can use the LIST partitioning method to partition data and store data that is generated in different regions on different data nodes. For more information, see the "Specify the LOCALITY attribute for partitions of a logical table" section of this topic.
When data partitions contain hot data, you can split the partitions and use the LOCALITY attribute to specify data nodes on which you want to store hot data partitions. This way, hot data is physically isolated from non-hot data. For more information, see the "Change the LOCALITY attribute of a table" section of this topic.