Databases in AUTO mode and DRDS mode - PolarDB - Alibaba Cloud Documentation Center

This topic describes PolarDB-X databases in AUTO mode and DRDS mode and the differences between the two modes.

Overview of PolarDB-X database modes

Databases in AUTO mode are available in PolarDB-X V5.4.13 and later versions and are also known as databases in automatic partitioning mode. Databases in AUTO mode support the automatic partitioning feature. When you create a table, you do not need to specify a partition key. Data in the table is automatically and evenly distributed among partitions. You can also use standard MySQL statements to partition the table. Databases in AUTO mode support features that are provided for distributed databases, such as transparent distribution, auto scaling, and partition management.

PolarDB-X databases whose versions are earlier than V5.4.13 are databases in DRDS mode. Databases in DRDS mode do not support automatic partitioning. When you create a table, you can use the sharding syntax that is dedicated to PolarDB-X to specify a database shard key and a table shard key. If no database shard key or table shard key is specified, a non-partitioned table is created.

PolarDB-X V5.4.13 and later versions support databases in AUTO mode and DRDS mode. A PolarDB-X instance can contain databases in AUTO mode and DRDS mode.

Usage notes

To create a database in AUTO mode, specify MODE='AUTO' in the CREATE DATABASE statement.
By default, if you do not specify the value of the MODE parameter in the CREATE DATABASE statement, a database in DRDS mode is created.
If your database is in AUTO mode, you cannot use the sharding syntax of DRDS databases to shard tables when you create tables. You can create only partitioned tables in databases that are in AUTO mode.
If your database is in DRDS mode, you cannot use the table partitioning syntax to shard tables when you create tables. You can create only sharded tables in databases that are in DRDS mode.
You cannot create databases in AUTO mode in PolarDB-X clusters of the standard edition.

Use the MODE parameter to specify the database mode

PolarDB-X allows you to specify the MODE parameter when you create a database so that you can decide to create the database in AUTO or DRDS mode. The following table describes how to specify the MODE parameter.

Note

After a database is created, the mode of the database cannot be changed.

Parameter

Value

Description

Syntax for creating a database

Syntax for creating a table

MODE

'AUTO'

Specifies that the database is created in AUTO mode.

Example:

CREATE DATABASE auto_db MODE='AUTO';

For more information, see CREATE DATABASE.

Tables that are created in a database in AUTO mode are partitioned tables. You can use the standard CREATE TABLE MySQL statement to create tables. For information about how to create a partitioned table, see CREATE TABLE (AUTO mode).

'DRDS' (default)

Note

If you do not specify the MODE parameter, the system creates a database in DRDS mode.

Specifies that the database is created in DRDS mode.

Example:

```
CREATE DATABASE drds_db MODE='DRDS';
```
```
CREATE DATABASE drds_db;
```

For more information, see CREATE DATABASE.

Tables that are created in a database in DRDS mode are sharded tables. For information about how to create a sharded table, see CREATE TABLE ( DRDS mode ).

Automatic partitioning and manual partitioning

Automatic partitioning

To configure automatic partitioning, you do not need to specify partitioning-related configuration items, such as a partition key and a partitioning policy, when you create a table. PolarDB-X automatically selects the partition key and then performs horizontal partitioning on the table and the indexes of the table. Only tables of databases in AUTO mode can be automatically partitioned. The automatic partitioning feature is unavailable for databases in DRDS mode.

Example:

You can execute the following statement in standard MySQL syntax without specifying partitioning-related configuration items to create a table named tb:

CREATE TABLE tb(a INT, b INT, PRIMARY KEY(a));

If you execute the preceding DDL statement on a database in DRDS mode, a non-partitioned table is created.

You can execute the SHOW statement to view the details of the CREATE TABLE statement.

SHOW FULL CREATE TABLE tb \G
*************************** 1. row ***************************
       Table: tb
Create Table: CREATE TABLE `tb` (
    `a` int(11) NOT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
1 row in set (0.02 sec)

If you execute the preceding DDL statement on a database in AUTO mode, a partitioned table is created. The table is automatically partitioned based on the primary key. You can execute the SHOW statement to view the details of the CREATE TABLE statement.

SHOW FULL CREATE TABLE tb \G
*************************** 1. row ***************************
       TABLE: tb
CREATE TABLE: CREATE PARTITION TABLE `tb` (
    `a` int(11) NOT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 16
1 row in set (0.01 sec)

If a database is in AUTO mode, you can create tables by using the standard MySQL syntaxes, such as the syntax for creating indexes. The automatic partitioning feature of PolarDB-X provides the capabilities of distributed databases, such as transparent distribution, auto scaling, and partition management.

Manual partitioning

When you use the manual partitioning method to create a table, you need to specify partitioning-related configuration items such as the partition key and partitioning policy. If tables are manually partitioned, the syntax that you use to create tables in databases in AUTO mode is different from the syntax that you use to create tables in databases in DRDS mode.

AUTO mode: You can use the standard MySQL syntax to create partitioned tables. Partitioning policies such as hash partitioning, range partitioning, and list partitioning are supported.

In the following example, the PARTITION BY HASH(a) clause is used to specify the partition key a and the hash partitioning policy.

CREATE TABLE tb (a INT, b INT, PRIMARY KEY(a))
    -> PARTITION by HASH(a) PARTITIONS 4;
Query OK, 0 rows affected (0.83 sec)

SHOW FULL CREATE TABLE tb\G
*************************** 1. row ***************************
       TABLE: tb
CREATE TABLE: CREATE TABLE `tb` (
    `a` int(11) NOT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4
PARTITION BY KEY(`a`)
PARTITIONS 4
1 row in set (0.02 sec)

DRDS mode: You can use the sharding syntax for databases in DRDS mode when you create tables. Only the hash policy is supported.

In the following example, the DBPARTITION BY HASH(a) TBPARTITION BY HASH(a) clause is used to specify that column a is the sharding key.

CREATE TABLE tb (a INT, b INT, PRIMARY KEY(a))
    -> DBPARTITION by HASH(a)
    -> TBPARTITION by HASH(a)
    -> TBPARTITIONS 4;
Query OK, 0 rows affected (1.16 sec)

SHOW FULL CREATE TABLE tb\G
*************************** 1. row ***************************
       Table: tb
Create Table: CREATE TABLE `tb` (
    `a` int(11) NOT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4  dbpartition by hash(`a`) tbpartition by hash(`a`) tbpartitions 4
1 row in set (0.02 sec)

Comparison between routing algorithms that are used for partitioned tables and sharded tables

The major difference between partitioned tables and sharded tables is that the routing algorithm used for partitioned tables is different from the routing algorithm used for sharded tables.

In the routing algorithm that is used for sharded tables, the hash values and the number of physical table shards are used to perform modulo operations. If you want to change the number of shards, the hash values of all data need to be recalculated. For example, if you want to change the number of shards from four to five, you need to recalculate hash values for all data. The routing algorithm that is used for table sharding in DRDS mode is not suitable for scenarios in which you want to change the number of shards.
The default routing algorithm that is used for partitioned tables is a range-based consistent hashing algorithm. This algorithm allows you to perform operations on partitions, such as splitting partitions and merging partitions, to change the number of partitions. You do not need to recalculate hash values for all data when you use this algorithm. In AUTO mode, you can change the number of partitions of a table.

Comparison between the features that are provided for databases in AUTO mode and DRDS mode

Compared with databases in DRDS mode, databases in AUTO mode support new features such as automatic partitioning, hot data splitting, partition scheduling, and time to live (TTL) tables. The distribution feature that is provided for databases in AUTO mode is also optimized based on various dimensions such as partition management and partition modification.

The following table describes the differences between the features that are provided for databases in AUTO mode and DRDS mode.

Feature		Database in AUTO mode	Database in DRDS mode
Transparent distribution	Default primary key-based partitioning	Supported. If you do not configure partitioning policies when you create a table, the table is automatically partitioned based on the primary key.	Not supported.
	Default global secondary indexes (GSIs)	Supported. If you do not specify partitioning policies for an index table, the index table is automatically partitioned based on index key columns.	Not supported.
	Load balancing	Supported.	Not supported.
	Hot data distribution	Supported.	Not supported.
Partitioning policy	Hash partitioning and key partitioning	Supported. The consistent hashing routing algorithm is used. Hot data can be split into multiple partitions and distributed among multiple data nodes.	Modulo operations can be performed based on the number of shards to route data. Hot data cannot be split into multiple shards and distributed among multiple data nodes.
	Range partitioning and range columns partitioning	Supported. Hot data can be split into multiple partitions and distributed among multiple data nodes.	Not supported.
	List partitioning and list columns partitioning	Supported.	Not supported.
Vector partition key (Multiple columns are used as the partition key.)		Supported. A vector can be used as a partition key. For example, you can use the `PARTITION BY KEY(c1,c2,c3)` clause to partition a table.	Not supported.
Collation of the partition key		Supported.	Not supported.
Non-partitioned table and broadcast table		Supported.	Supported.
Partition management	Partition creation, deletion, and modification	Supported.	Not supported.
	Partition splitting and merging	Supported.	Not supported.
	Partition migration	Supported.	Not supported.
	Partition truncation	Supported.	Not supported.
	Analysis of hot data partitions	This feature will be released soon. Hot data partitions can be automatically analyzed.	Not supported.
Table modification	Table type change (The type of a table can be changed to non-partitioned table, broadcast table, and partitioned table.)	Supported.	Supported.
Table modification	Partition modification (The number of partitions, partition key type, and partition policy can be changed.)	Supported.	Supported.
Auto scaling	Whether data writing operations are interrupted during scaling operations	No	Yes. Data writing operations are interrupted for a short period of time.
Auto scaling	Whether other DDL operations can be performed during scaling operations	Yes.	No
Locality	Static storage isolation	Supported. You can specify physical storage resources when you create databases, tables, and partitions.	Supported. You can specify physical storage resources when you create databases and tables.
	Dynamic storage isolation	Supported. You can dynamically modify the physical storage resources that are used for databases and tables.	Not supported.
	Compatible with auto scaling	Yes.	No
Partition pruning	Partition pruning based on prefixes	Supported. When you use `PARTITION BY KEY(a,b,c)` to partition a table, Column a, Column b, and Column c are used as the vector partition key. In scenarios in which a=100 and b=100 is used in a query, or in scenarios in which a=100 is used in a query, partition pruning is triggered.	Not supported.
	Constant folding in calculation expressions	Supported. For example, partition pruning can be performed based on the `pk = POW(2, 4)` condition that contains a calculation expression.	Not supported. The partition key must be specified by a constant such as pk = 123. If the partition key is specified by a calculation expression such as `pk = POW(2, 4)`, the system scans the full table.
	Options of case sensitivity and ignoring spaces at the end of each line in partition routing configurations	Supported. You can specify a collation for the partition key to determine whether to ensure case sensitivity and ignore spaces at the end of lines in partition routing.	Not supported. The collation cannot be used for partition key columns. If you use the hash algorithm, case sensitivity of values can be ensured, and spaces that are specified at the end of lines cannot be ignored.
	Join operation pushdown	Supported. When operations are performed on partitions, such as splitting, merging, and migrating partitions, the pushdown process of join operations is not affected.	Supported.
	Partition selection	Supported. You can use the SELECT statement to query data from a specified partition. For example, you can execute `SELECT * FROM tb PARTITIONS (p1)` to query data from the p1 partition.	Not supported.
TTL		Supported.	Not supported.
AUTO_INCREMENT		Supported. Values in the auto-increment column are globally unique, monotonically increasing, and consecutive.	Supported. Values in the auto-increment column are globally unique but may not be monotonically increasing and consecutive.

Comparison between the performance of databases in DRDS mode and AUTO mode

The routing algorithm that is used for table sharding in DRDS mode is different from the routing algorithm that is used for table partitioning in AUTO mode. Sysbench is used to perform benchmark tests for PolarDB-X databases to obtain the queries per second (QPS) when the algorithms are used in different Sysbench test scenarios. You can use the test result to evaluate the differences between the performance of the routing algorithms.

Test environment

PolarDB-X instance type: polarx.x4.2xlarge.2e
- CN (16C64G) × 2
- DN (16C64G) × 2
Version: 5.4.13-16415631
Table partitioning settings and table sharding settings:
- Table partitioning:
  - 32 partitions
  - Partitioning clause: PARTITION BY HASH(id) PARTITIONS 32
  - Total volume of data: 160 million rows
- Table sharding:
  - 32 physical table shards
  - Sharding clause: DBPARTITION BY HASH(id) TBPARTITION BY HASH(id) TBPARTITION 2
  - Total volume of data: 160 million rows

Test scenario

The following list describes the scenarios in which Sysbench tests are performed:

oltp_point_select: Statements that include only equality conditions are executed to perform point read operations. The partition key of the table is used in the WHERE clause.
oltp_read_only: Statements that include the BETWEEN operator or an equality condition are executed to perform small-range and point read operations in a transaction. The partition key of the table is used in the WHERE clause.
oltp_read_write: Point read operations, point write operations, small-range read operations, and small-range write operations are performed based on the partition keys of the tables to process transactions.

Test results

The following analysis results are obtained based on the preceding test results:

The consistent hashing routing algorithm that is used in table partitioning is more complex than the modular hash routing algorithm that is used in sharding. In scenarios in which oltp_point_select queries are performed, the QPS for table partitioning is similar to the QPS for sharding.
The expressions in the statements that are used to perform oltp_read_only queries and oltp_read_write queries are more complex than the expressions in the statements that are used to perform oltp_point_select queries because small-range queries are required in oltp_read_only queries and oltp_read_write queries. In this case, the overall QPS on the partitioned tables is approximately 33% higher than the overall QPS on the sharded tables because the partitioned tables are pruned.

FAQ

How do I determine whether to create a database in AUTO mode or DRDS mode?
The AUTO mode is supported in PolarDB-X V5.4.13 and later versions. If your application is migrated from PolarDB-X 1.0, you can use only databases in AUTO mode. When you deploy a new application, you can use databases in AUTO mode.
How do I determine whether to use the automatic partitioning method or manual partitioning method when I create a table in a database in AUTO mode?
If the table that you create is used for application testing, you can use the automatic partitioning method to partition the table. If you want to modify the partitions to optimize database performance, you can execute the ALTER PARTITION DDL statement to change the partitioning method of the table. If you know the SQL statements that are used in your business scenarios and understand the relationships that are defined between individual tables, you can use the manual partitioning method when you create a table.
How can I change the mode of a database from DRDS to AUTO?
For PolarDB-X V5.4.16 and later versions, you can use one of the following methods to change the mode of a database from DRDS to AUTO. For PolarDB-X whose version is earlier than V5.4.16, only methods 2 and 3 are supported. For more information about how to view the version of a PolarDB-X instance, see View the version of an instance.
- Method 1: PolarDB-X V5.4.16 and later versions allow you to change the mode of a database from DRDS to AUTO by executing the CREATE DATABASE LIKE or CREATE DATABASE AS statement. For more information, see Convert a database in DRDS mode to a database in AUTO mode.
- Method 2: You can create a database in AUTO mode in the destination instance and create a table in the database. Then, use Data Transmission Service (DTS) to migrate data from the source database that is in DRDS mode to the table in the destination database that is in AUTO mode.
- Method 3: Run the mysqldump command to dump the data file of the source database that is in DRDS mode, excluding the CREATE TABLE statement. Then, create a database in AUTO mode and create a table in the database. Then, run the source command to import the dumped data file to the destination database that is in AUTO mode.
What is the number of partitions that are generated if I use the automatic partitioning method when I create a table in a database in AUTO mode?
The number of partitions of a table in AUTO mode is calculated by using the following formula: Number of nodes that is specified when the instance is created × 8. For example, if you specify 2 as the value of the Nodes parameter when you create an instance, the number of partitions of a table that is automatically partitioned is 16. The number of partitions of a table that is automatically partitioned remains unchanged even if you add nodes to the instance. If you want to change the number of partitions of a table, you can modify the value of the AUTO_PARTITION_PARTITIONS parameter.
Can I change the number of partitions of a table that is automatically partitioned?
Yes, you can change the number of partitions of a table. To change the number of partitions of a table that is automatically partitioned, modify the value of the AUTO_PARTITION_PARTITIONS parameter for the instance that contains the table. The configuration of the AUTO_PARTITION_PARTITIONS parameter takes effect on all tables in the instance. After the value of the AUTO_PARTITION_PARTITIONS parameter is modified, the number of partitions of new tables that are created by using the automatic partitioning method is equal to the new value of the AUTO_PARTITION_PARTITIONS parameter. Note that if the number of partitions in the new table is different from that in the original table, the JOIN operation between the two table cannot be pushed down, which affects the execution efficiency. We recommend that you manually set the number of partitions in the new table consistent with that in the original table. For more information, see Use ALTER TABLE to modify table partitions (AUTO mode).