PolarDB-X allows you to use LOCALITY to specify the storage location of a database or a table. This helps you isolate data and evenly distribute data to storage nodes. This topic describes how to use LOCALITY.

Prerequisites

The instance version is PolarDB-X 5.4.10 or later. For more information about how to view an instance version, see View the version of an instance.

Attention

After the storage location of a database or a single table is specified by using the LOCALITY syntax, the storage location cannot be modified.

Specify the storage location of a database

When you create a database, specify the storage location of the database so that the data in the database can be isolated from that in another database.

  1. If an instance is available, PolarDB-X you can execute the following statement to view the storage nodes in the instance:
    SHOW STORAGE;

    The following result is returned:

    +---------------------------+--------------------+------------+-----------+----------+-------------+
    | STORAGE_INST_ID           | LEADER_NODE        | IS_HEALTHY | INST_KIND | DB_COUNT | GROUP_COUNT |
    +---------------------------+--------------------+------------+-----------+----------+-------------+
    | polardbx-storage-1-master | 1.1.1.1:3308       | true       | MASTER    | 2        | 6           |
    | polardbx-storage-meta     | 1.1.1.1:3306       | true       | META_DB   | 2        | 2           |
    | polardbx-storage-0-master | 1.1.1.1:3306       | true       | MASTER    | 2        | 8           |
    +---------------------------+--------------------+------------+-----------+----------+-------------+
    Note
    • polardbx-storage-1-master and polardbx-storage-0-master are the storage nodes that store user data.
    • polardbx-storage-meta is the storage node of metadata. This storage node does not store user data.
  2. Execute the following statement to create a database in the instance and specify the storage location of the database as the polardbx-storage-0-master node:
    CREATE DATABASE db1 LOCALITY='dn=polardbx-storage-0-master';
    Note
    • If the storage location of the database is not specified when you create the database, the system evenly distributes the database among all the storage nodes by default.
    • The storage locations of table shards in the database are consistent with those of the database so that data in a table shard can be isolated from the data in another table shard.
    • The storage location of a single table in a database is not affected by the storage location of the database. If the storage location is not specified when you create a single table, the table is randomly stored on a storage node. PolarDB-X In addition, if you continue to create single tables on the instance and do not specify the storage location for these tables, the tables are stored on the storage node.
  3. After the tables are created, you can view the storage location of the database by executing the following statement:
    SHOW CREATE DATABASE db1;

    The following result is returned:

    +----------+------------------------------------------------------------------------+
    | DATABASE | CREATE DATABASE                                                        |
    +----------+------------------------------------------------------------------------+
    | db1      | CREATE DATABASE `db1` /* LOCALITY = "dn=polardbx-storage-0-master" */  |
    +----------+------------------------------------------------------------------------+
    1 row in set
  4. You can also view the logical and physical database shards created in the database by executing the following statement:
    SHOW DS;

    The following result is returned:

    +----+---------------------------+-----+------------------+------------+---------+
    | ID | STORAGE_INST_ID           | DB  | GROUP            | PHY_DB     | MOVABLE |
    +----+---------------------------+-----+------------------+------------+---------+
    | 0  | polardbx-storage-0-master | db1 | DB1_000000_GROUP | db1_000000 | 1       |
    | 1  | polardbx-storage-0-master | db1 | DB1_000001_GROUP | db1_000001 | 1       |
    | 2  | polardbx-storage-0-master | db1 | DB1_P00000_GROUP | db1_p00000 | 1       |
    | 3  | polardbx-storage-0-master | db1 | DB1_SINGLE_GROUP | db1_single | 0       |
    +----+---------------------------+-----+------------------+------------+---------+
    4 rows in set
    Note In the result, the row whose ID is 0 indicates that the logical database shard DB1_000000_GROUP and the physical database shard db1_000000 are created in the db1 database. The db1 database is stored on the storage node polardbx-storage-0-master.

Specify the storage location of a table

When you create a single table, specify the storage location of the table so that data can be evenly distributed to storage nodes.

Note PolarDB-X tables are categorized to distributed tables and single tables. If the storage location is not specified when you create a single table, the table is randomly stored on a storage node. In addition, if you continue to create single tables and do not specify the storage location for these tables, the tables are stored on the storage node. As a result, the storage node may be overloaded. If you use LOCALITY to specify that a single table is stored on another storage node, data can be evenly distributed to storage nodes. For more information about distributed tables, see Sharding Function.
An instance is available. The instance has two storage nodes: polardbx-storage-0-master and polardbx-storage-1-master. A db1 database has been created on the storage node polardbx-storage-1-master. In this case, if you need to create a table in the db1 database and specify the storage location of the table as polardbx-storage-0-master, use the following syntax:
CREATE TABLE tb1 (id int) LOCALITY='dn=polardbx-storage-0-master';
After the table is created, you can view the topology of the table by executing the following statement:
SHOW TOPOLOGY FROM tb1;

The following result is returned:

+----+------------------+------------+
| ID | GROUP_NAME       | TABLE_NAME | 
+----+------------------+------------+
| 0  | DB1_000000_GROUP | tb1        |
+----+------------------+------------+
1 row in set
Note The preceding result indicates that the tb1 table is stored in the database shard DB1_000000_GROUP.