PolarDB:Use the LOCALITY attribute to specify data nodes ( DRDS mode )

Prerequisites

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

Usage notes

If you specify the storage location of a database or a single table by using LOCALITY, the storage location cannot be changed.

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.

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

   SHOW STORAGE;

   The following results are 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 location of sharded tables must be the same as the location of the database in which the table shards are stored. This way, the table shards can be isolated from each other.
   • The storage location of a single table in a database is independent of the storage location of the database. If the storage location is not specified when you create a single table in a PolarDB-X instance, the table is stored on a random data node. if you create more single tables in the PolarDB-X instance and do not specify the storage location for these tables, the tables are stored on the same data 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 results are 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 results are 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 LOCALITY attribute when you create a single table

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

CREATE TABLE tb1 (id int) LOCALITY='dn=polardbx-storage-0-master';

SHOW TOPOLOGY FROM tb1;

The following results are returned:

+----+------------------+------------+
| ID | GROUP_NAME       | TABLE_NAME | 
+----+------------------+------------+
| 0  | DB1_000000_GROUP | tb1        |
+----+------------------+------------+
1 row in set