PolarDB:AUTO mode and DRDS mode databases

PolarDB-X database modes

Starting with PolarDB-X 5.4.13, you can create an AUTO mode database, also known as an automatic partitioning database. This mode supports automatic partitioning, where you can create a table without specifying a partition key, and the data is automatically and evenly distributed across the cluster. You can also use standard MySQL partitioning syntax to perform manual partitioning on tables. This allows you to easily benefit from the features of distributed databases, such as transparent distribution, auto scaling, and partition management.

Databases in PolarDB-X versions earlier than 5.4.13 are DRDS mode databases. This mode does not support automatic partitioning. When you create a table, you must use DRDS-specific sharding syntax to specify a sharding key. Otherwise, a non-partitioned table is created.

PolarDB-X 5.4.13 and later support both AUTO mode and DRDS mode databases. They can coexist within the same instance.

Usage notes

• To create an AUTO mode database, you must explicitly specify MODE='AUTO' in the CREATE DATABASE statement.

• If you omit the MODE parameter in the CREATE DATABASE statement, PolarDB-X creates a DRDS mode database by default.

• In an AUTO mode database, you cannot use DRDS sharding syntax to create sharded tables. You can only create partitioned tables.

• In a DRDS mode database, you cannot use partitioning syntax to create a partitioned table. You can only create a sharded table.

• Standard Edition clusters do not support creating AUTO mode databases.

Specify a database mode

When creating a database in PolarDB-X, use the MODE parameter to specify the database mode.

CREATE DATABASE auto_db MODE='AUTO';

Automatic partitioning and manual partitioning

Automatic partitioning

Automatic partitioning does not require you to specify any partitioning definitions, such as a partition key or partitioning policy, when you create a table. PolarDB-X automatically selects a partition key and horizontally partitions the table and its indexes. AUTO mode databases support automatic partitioning, but DRDS mode databases do not.

The following is an example.

Use standard MySQL syntax to create a table named tb without any partitioning definitions:

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

• In a DRDS mode database, this DDL statement creates a non-partitioned table.

  Run the SHOW statement to view the complete table creation 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)

• In an AUTO mode database, this DDL statement creates a partitioned table.Run the SHOW statement to view the complete table creation 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)

The automatic partitioning feature of PolarDB-X allows your application to easily benefit from the capabilities of a distributed database, such as auto scaling and partition management.

Manual partitioning

Manual partitioning requires you to explicitly specify partitioning definitions, such as a partition key and a partitioning policy, when creating a table. The table creation syntax for manual partitioning is different for AUTO mode and DRDS mode databases.

• AUTO mode database: Use standard MySQL syntax for creating a partitioned table. This mode supports multiple partitioning policies, including HASH, RANGE, and LIST.

  In the following example, the PARTITION BY HASH(a) syntax specifies column a as the partition key and HASH as the partitioning policy for the tb table:

  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 database: Use DRDS-specific sharding syntax for creating a sharded table. This mode only supports the HASH policy.

  In the following example, the DBPARTITION BY HASH(a) TBPARTITION BY HASH(a) syntax specifies column a as the sharding key for the tb table:

  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)

Routing algorithm comparison: partitioned vs. sharded tables

Feature comparison

Compared to a DRDS mode database, an AUTO mode database offers new features such as automatic partitioning, hot data splitting, partition scheduling, and Time to Live (TTL). It also provides significant optimizations to the distributed experience in areas like partition management and partitioning scheme modification.

The following table compares the main features of AUTO mode databases and DRDS mode databases.

Performance comparison

DRDS mode sharded tables and AUTO mode partitioned tables use different routing algorithms. To evaluate the performance differences, we benchmarked PolarDB-X with Sysbench. This test measures throughput (in queries per second, or QPS) for each mode across various Sysbench test scenarios.

Test environment

• PolarDB-X instance specification: polarx.x4.2xlarge.2e

  • Compute node (CN): 2 × (16 CPU cores, 64 GB RAM)

  • Data node (DN): 2 × (16 CPU cores, 64 GB RAM)

• Version: 5.4.13-16415631

• Table configurations:

  • Partitioned table:

    • 32 partitions

    • Partitioning statement: partition by hash(id) partitions 32

    • Total table data: 160 million rows

  • Sharded table:

    • 32 physical table shards

    • Sharding statement: dbpartition by hash(id) tbpartition by hash(id) tbpartitions 16

    • Total table data: 160 million rows

Test scenarios

Test results

FAQ

• Q1: When should I choose AUTO mode versus DRDS mode?

  A: We recommend using AUTO mode for both new applications and applications migrated from PolarDB-X 1.0.

• Q2: In AUTO mode, should I use automatic or manual partitioning when creating tables?

  A: During the testing phase, you can use automatic partitioning. If you later discover that your workload requires performance tuning, you can change the table's partitioning method by using a DDL statement (for example, the ALTER PARTITION statement). If you have a deep understanding of your business's specific SQL patterns and the relationships between tables, you can use manual partitioning from the start.

• Q3: How do I switch a database from DRDS mode to AUTO mode?

  A: For kernel versions 5.4.16 and later, you can use any of the following methods. For versions earlier than 5.4.16, only Method 2 and Method 3 are supported. To check your instance version, see View the engine version.

  • Method 1: Starting from version 5.4.16, the kernel provides the CREATE DATABASE LIKE/AS syntax, which allows you to convert a DRDS mode database to an AUTO mode database with a single command. For usage details, see Convert a DRDS mode database to an AUTO mode database.

  • Method 2: Create a new AUTO mode database in the target instance, create the required tables, and then use Data Transmission Service (DTS) to synchronize data from the original DRDS mode database to the new AUTO mode database.

  • Method 3: Use the mysqldump command to dump the data from the original DRDS mode database (excluding the table creation statements). Then, create a new AUTO mode database, create the tables, and import the dumped data file using the source command.

• Q4: What is the default number of partitions for automatic partitioning in AUTO mode?

  A: The default number of automatic partitions is calculated as: (Number of nodes at instance creation) × 8. For example, if an instance is created with 2 nodes, the default number of partitions is 2 × 8 = 16. This number remains constant after instance creation and is unaffected by auto scaling, unless you manually adjust the corresponding parameter.

• Q5: Can I manually adjust the default number of automatic partitions?

  A: Yes. The parameter for the default number of partitions is AUTO_PARTITION_PARTITIONS, which is an instance-level parameter. Therefore, changing this parameter affects the number of automatic partitions for all new tables created in any AUTO mode database within that instance. Note that if a new table's partition count differs from that of existing tables, join operation pushdown between them may fail, which can affect query efficiency. We recommend manually adjusting the partition count if needed. For more information, see Modify table-level partitions (AUTO mode).