This topic describes how to use the LOCALITY attribute to specify the data nodes on which you want to store a database object that is partitioned by using the AUTO partitioning method. This syntax applies to only AUTO mode databases.
PolarDB-X supports the LOCALITY attribute for databases in AUTO mode. You can specify data nodes for databases, tables, or partitions to isolate data and evenly distribute data.
The LOCALITY attribute can be used in the following scenarios:
You can specify the LOCALITY attribute for databases, tables, and partitions. After you specify the LOCALITY attribute for a database, table, or partition, 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 LOCALITY attribute for table groups and partition groups. After the value of the LOCALITY attribute is changed, an asynchronous data migration task is automatically triggered.
Prerequisites
The kernel version of your PolarDB-X instance must be V5.4.14 or later.
The logical database must be partitioned in AUTO mode.
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 a data node is deleted, the LOCALITY attribute that includes the ID of the data node automatically becomes invalid.
The LOCALITY attribute is specified based on 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.
View information about data nodes of a PolarDB-X instance
You can execute the following statement to view information about 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 |
+--------------------+----------------------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+STORAGE_INST_ID: the ID of the data node that is included in the value of the LOCALITY attribute.INST_KIND: the type of the data node. If the value isMETA_DB, the data node is a metadata node that cannot be used to store business data.INST_KIND: the status of the data node. If the value is0, the data node is available.DELTETABLE: indicates whether the data node can be deleted. If the value isfalse, the data node cannot be deleted. In the preceding example, the metadata node and the data node whose ID is polardbx-ng28-dn-0 cannot be deleted.
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 instance.
Syntax
CREATE DATABASE database_name database [database_option_list];Example
Create a database that use the AUTO partitioning mode and specify the LOCALITY attribute for the database. For more information about the syntax, see CREATE DATABASE.
CREATE DATABASE db1 LOCALITY='dn=polardbx-ng28-dn-0,polardbx-ng28-dn-1,polardbx-ng28-dn-2' MODE = 'auto';After the database is created, execute the following statement to query information about the database, including the data nodes that are used to store data in the database:
SHOW CREATE DATABASE `db1`;The following information is returned:
+----------+--------------------------------------------------------------------------------------------------------------------+ | 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)Execute the following statement to query the logical database shards and physical database shards of the database:
SHOW DS;The following information is returned:
+----+--------------------+--------------------+---------------------------------+----------------------+---------+ | 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)NoteThe storage locations of a database must include a data node whose serial number is 0. The storage locations of tables and partitions are not subject to this limit.
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 partitioned 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]Example
Create a logical table and specify the LOCALITY attribute for the logical table. For more information about the syntax, see CREATE TABLE( AUTO mode ).
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';Execute the following statement to query information about the table:
SHOW CREATE TABLE `t_order`;The following information is returned:
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)Execute the following statement to query information about each partition of the logical table.
SHOW TOPOLOGY `t_order`;The following information is returned:
+----+------------------+--------------------+----------------+-------------+--------------------+ | 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)NoteThe data nodes that are specified in the LOCALITY attribute of the logical table must be included in the data node set for 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 is included in a logical table group, the value of the LOCALITY attribute of the logical table must be the same as the values of the LOCALITY attribute of the table group, partition, and partition group.
By default, the values of the LOCALITY attribute of the logical table, logical table group, global secondary indexes (GSIs) of the logical table, and GSI table group are the same.
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 logical table. This way, different partitions of the logical table are stored on different data nodes.
Example
Create a logical table and specify the LOCALITY attribute for each partition of the logical table.
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';Execute the following statement to query information about the logical table:
SHOW CREATE TABLE `orders_region`;The following information is returned:
+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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)Execute the following statement to query information about each partition of the logical table.
SHOW TOPOLOGY `orders_region`;The following information is returned:
+----+------------------+--------------------------+----------------+-------------+--------------------+ | 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)NoteThe data nodes that are specified for the partitions of the logical table must be included in the data node set for 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 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.
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 for table partitions for which you did not specify the LOCALITY attribute when you created the corresponding tables are changed. The data nodes for table partitions for which you specified the LOCALITY attribute when you created the corresponding tables remain unchanged. After the meta information about a table group or partition group whose LOCALITY attribute is changed is modified, the new information 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:
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.
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)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 change the types of tables, and operations that are performed to change 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. However, in the following scenarios, the value of the LOCALITY attribute changes:
After a table is changed to a non-partitioned table or 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 values of the LOCALITY attribute of the new partitions become empty.
After multiple partitions whose values of the LOCALITY attribute are different are merged into one partition, the value of the LOCALITY attribute of the new partition is empty.
LOCALITY changes after partition modification operations:
Operation type | Object | Whether LOCALITY changes | Description |
Changing the partitioning strategy | Table | Yes | - |
Changing the table type | Table | The LOCALITY attribute remains unchanged only when a non-partitioned table is changed to a partitioned table. | If a partitioned table is changed to a non-partitioned table or broadcast table, the LOCALITY attribute of the table becomes invalid. |
Migrating partitions | Partition group | Yes | - |
Merging partitions | Partition group | The new partition inherits the value of the LOCALITY attribute of the partitions that are merged only when the values of the LOCALITY attribute of all partitions that are merged are the same. | If the values of the LOCALITY attribute of the partitions are different, the value of the LOCALITY attribute of the new partition is empty by default. |
Splitting a partition | Partition group | Yes | - |
Splitting a partition by hot value | Partition group | No | By default, the value of the LOCALITY attribute of the new partition is empty. |
Extracting hot data from a partition | Partition group | No | By default, the value of the LOCALITY attribute of the new partition is empty. |
Dropping a partition | Partition group | - | - |
Adding a partition | Partition group | Yes | - |
Modifying partitions based on the LIST partitioning method | Partition group | Yes | - |
Renaming a partition | Partition group | Yes | - |
Common 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 on 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 0 when the excessive 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, partitions of the table can be distributed on 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.
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.