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.
- 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 | +---------------------------+--------------------+------------+-----------+----------+-------------+
Notepolardbx-storage-1-master
andpolardbx-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.
- 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.
- 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
- 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 nodepolardbx-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.
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';
SHOW TOPOLOGY FROM tb1;
The following result is returned:
+----+------------------+------------+
| ID | GROUP_NAME | TABLE_NAME |
+----+------------------+------------+
| 0 | DB1_000000_GROUP | tb1 |
+----+------------------+------------+
1 row in set