DocsICP FilingConsole
官方文档
首页

Specify data placement with LOCALITY (AUTO mode)

更新时间：
复制 MD 格式
产品详情
我的收藏

This topic describes how to use the locality keyword to specify the storage location for database objects in AUTO mode databases.

In AUTO mode databases, PolarDB-X lets you use the locality keyword to specify the storage location of a database, table, or partition to achieve data isolation or balanced data distribution.

The locality keyword provides the following capabilities:

  • You can define the locality property at the database, table, and partition levels. All other operations, except for node scale-in, alter data distribution while adhering to the locality constraints.
  • You can modify the locality property for a table group or partition group. Modifying the property automatically triggers an asynchronous data migration task.

Prerequisites

  • The instance kernel version must be 5.4.14 or later.
  • The logical database must be in AUTO mode.

To learn how to check the instance version, see View and upgrade the instance version.

Usage notes

  • After a node scale-in, any locality definition that includes the scaled-in node is automatically invalidated.
  • After you restore from backup, the original locality property is automatically invalidated.

View data node information

You can run the following command to view information about the data nodes in your PolarDB-X instance:

SHOW STORAGE;

The following result is returned:

+--------------------+----------------------------------+------------+-----------+----------+-------------+--------+-----------+-------+--------+
| 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
  • The STORAGE_INST_ID column shows the name of the data node. This is the name used in the locality definition.
  • The INST_KIND column shows the type of the data node. A node with the META_DB type is a metadata node and cannot be used to store business data.
  • The STATUS column shows the status of the data node. A value of 0 indicates that the node is available.
  • The DELETABLE column indicates whether the node can be scaled in. A value of false means the node cannot be scaled in. This includes the metadata node and a designated data node (referred to as data node 0).

Specify a storage location when creating a database

Specify a storage location when you create a database to achieve data isolation.

  1. Use the following statement to create a database in AUTO mode within your instance and specify its storage location. For detailed syntax, see CREATE DATABASE.
    CREATE DATABASE db1 LOCALITY='dn=polardbx-ng28-dn-0,polardbx-ng28-dn-1,polardbx-ng28-dn-2' MODE = 'auto';
  2. After creating the database, run the following statement to view its storage location.
    SHOW CREATE DATABASE `db1`;

    The following result 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)
  3. You can also run the following statement to view the logical database shards and physical database shards created in the database.
    SHOW DS;

    The following result 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)
    Note The storage location of a database must include data node 0. This restriction does not apply to the storage locations of tables and partitions.

Specify storage for logical tables

Specify a storage location when you create a logical table to achieve data isolation. The locality keyword is currently supported for non-partitioned tables and partitioned tables that use the Range, Hash, or List partitioning methods.

  1. Create a logical table in your instance and specify its storage location. For detailed 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';
  2. Run the following statement to view the table definition.
    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)
  3. Run the following statement to view the data distribution across the partitions 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)
    Note
    • When you create a table, the set of data nodes specified by the locality keyword must be a subset of the data nodes assigned to the parent database.
    • If you create a non-partitioned table, the locality keyword can specify only a single data node as its storage location.
    • For a logical table to match a table group, the locality properties of the table, table group, partition, and partition group must match.
    • By default, a logical table, its table group, its global secondary index (GSI), and the GSI's table group all share the same locality property.

Specify storage for partitions

When creating a logical table, you can specify storage locations at the partition level. This allows you to distribute different partitions of the same logical table across different data nodes.

  1. Create a logical table in your instance and specify the locality for its partitions.
    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';
  2. Run the following statement to view the table definition.
    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)
  3. Run the following statement to view the data distribution across the partitions 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)
    Note The data nodes specified for a partition must be a subset of the data nodes of the parent database, but they do not have to be a subset of the data nodes of the logical table.

Modify table group storage

Dynamically change the storage location of a table group.

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

The orders_region table from the previous example belongs to the tg3 table group. To modify its storage location, use the following command:

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

After the operation, the table topology is as follows:

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)

Within the same table group, partitions with a predefined locality adhere to their constraints. Other partitions are migrated according to the table group's locality constraint. Modifying the storage location of a table group or partition group returns a result quickly after the metadata is changed, and a partition migration task is generated to run asynchronously.

You can query the information_schema.ddl_plan view to check the progress of the corresponding partition migration task, REBALANCE TABLEGROUP tg3.

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 |
+----+---------------------+---------------------+--------------+------------------------------------------------------------------+---------+-----------+----------+-------------+--------+--------+---------------------+---------------------+----------------+

Modify partition group storage

Dynamically change the storage location of a partition group.

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

The orders_region table from the previous example belongs to the tg3 table group. To change the locality of its p3 partition, use the following command:

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

The new table topology is as follows:

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)

Locality property propagation during partition modification

Currently, partition modification operations in AUTO mode include modifying table group-level partitions (AUTO mode) and modifying table types and partitioning policies (AUTO mode).

During a partition modification, the new partition group and table group generally inherit the original locality property. The modified table group and partition group also automatically adhere to the data distribution constraints. However, the following three scenarios are exceptions:

  • When you change a table's type to a non-partitioned table or a broadcast table, the table's locality property is cleared by default.
  • For partition modification operations involving a data hotspot, the locality of the new partition is cleared by default.
  • When merging partitions, if the original partitions have different locality settings, the locality of the new partition is cleared by default.

The following table describes how locality changes during partition modification operations.

Operation type Object Locality persistence Notes
Partition strategy change Table Yes -
Table type change Table Persists only when a non-partitioned table is changed to a partitioned table. When a partitioned table is changed to a non-partitioned table or broadcast table, its locality property is automatically cleared.
Move partition Partition group Yes -
partition merge Partition group The new partition inherits the locality property only if all original partitions have the same locality setting. Otherwise, the locality of the new partition is cleared by default.
partition split Partition group Yes -
partition split by hot value Partition group No The locality of the new partition is cleared by default.
hot value extraction Partition group No The locality of the new partition is cleared by default.
partition drop Partition group - -
partition add Partition group Yes -
Modify LIST partition values Partition group Yes -
partition rename Partition group Yes -

Use cases

  • When creating a database, you can use locality to isolate data at the logical database level. This automatically distributes tables and partitions within the database across the specified data nodes.
  • When creating a non-partitioned table, you can use locality to specify its data node. This helps prevent storage pressure on data node 0 from an excessive number of non-partitioned tables.
  • When creating a partitioned table, you can specify locality at the partition level to place different partitions of the same logical table on different data nodes. For example, you can combine this with List partitioning to isolate data from different regions within the same logical table.
  • When a data hotspot occurs in a partition, you can use partition modification and locality to assign the hot partition to a dedicated data node, thereby isolating its physical storage resources.
Previous：Use the LOCALITY attribute to specify data nodesNext：Related operations for SQL statements