PolarDB:Multi-tenant management

Use cases

Multi-tenant mode is suited for scenarios where you want workload consolidation without sacrificing isolation:

• SaaS application isolation: Host multiple customers on a single PolarDB cluster, with each customer's data and CPU resources fully isolated from others.

• Mixed workloads: Run OLTP and batch jobs on the same cluster by assigning dedicated CPU to time-sensitive tenants while letting batch workloads use shared resources.

• Test and production isolation: Share a cluster across environments by giving production tenants a guaranteed CPU floor and letting test tenants use remaining capacity.

How it works

The system tenant, regular tenant A, and regular tenant B each have a DB_1 database and a user_1 user. Although these databases and users share the same name, they are distinct and independent of each other.

Key concepts

Tenant — A hierarchical layer between the cluster and the database or user level, introduced to support multi-tenant architecture. Two types exist:

• System tenant: Hosts all pre-existing databases and users from before multi-tenant mode was enabled. A privileged account of the system tenant can access databases across all tenants given the required permissions. System tenants do not require management operations.

• Regular tenant: Created under the system tenant. Databases and users across regular tenants are completely isolated and cannot access each other. Regular tenants cannot access system tenant databases.

Regular tenants are further classified by CPU scheduling behavior, based on the min_cpu value:

Change min_cpu to switch a tenant between exclusive and shared.

Resource configuration — Defines the CPU resources a tenant can use, using min_cpu (guaranteed minimum) and max_cpu (hard upper limit). Only CPU resources are supported for isolation and scheduling.

Prerequisites

Before you begin, make sure your cluster meets all of the following:

• Database Engine: MySQL 8.0.2 or later

• Database Edition: Enterprise Edition

• Edition: Cluster Edition

• All nodes in the cluster have the same specifications

Limitations

Quick start

This section walks through enabling multi-tenant mode and setting up two isolated tenants end-to-end.

1. Enable the standalone multi-tenant mode on the cluster. See Enable multi-tenant mode.

2. Connect to the cluster using the privileged account.

3. Create resource configurations r1 and r2.

   Set min_cpu and max_cpu based on your cluster's node specifications. The min_cpu value for each tenant must not exceed the number of CPU cores of the node specifications minus 1. Reserve at least one CPU core for the system tenant.

   CREATE resource_config r1 min_cpu 1 max_cpu 2;
   CREATE resource_config r2 min_cpu 1 max_cpu 4;

4. Create tenants tn1 and tn2, each bound to a resource configuration.

   The sum of min_cpu across all tenants must not exceed the number of CPU cores of the node specifications minus 1.

   CREATE tenant tn1 resource_config r1;
   CREATE tenant tn2 resource_config r2;

5. Create users and databases for each tenant.

   When using the privileged account to create users or databases for a regular tenant, append @<tenant_name> to the username or database name.

   -- Create user u1 in tenant tn1
   CREATE USER 'u1@tn1' IDENTIFIED BY 'password';
   
   -- Create database db1 in tenant tn1
   CREATE DATABASE `db1@tn1`;
   
   -- Create user u2 in tenant tn2
   CREATE USER 'u2@tn2' IDENTIFIED BY 'password';
   
   -- Create database db2 in tenant tn2
   CREATE DATABASE `db2@tn2`;

6. Grant permissions to u1 and u2.

   -- Grant all permissions on tenant tn1 to user u1
   GRANT ALL PRIVILEGES ON `%@tn1`.* TO 'u1@tn1'@'%' WITH GRANT OPTION;
   
   -- Grant all permissions on tenant tn2 to user u2
   GRANT ALL PRIVILEGES ON `%@tn2`.* TO 'u1@tn2'@'%' WITH GRANT OPTION;

7. Log in as u1@tn1 and u2@tn2 to verify that data and resource isolation are in effect between tn1 and tn2.

8. (Optional) View the tenants, users, and databases you created.

   • Tenants: Connect with the privileged account and run: ``sql SELECT * FROM mysql.tenants; ``

   • Users: In the PolarDB console, go to Settings and Management > Accounts for your cluster.

     • Account names without an @<tenant_name> suffix belong to the system tenant.

     • Account names with an @<tenant_name> suffix belong to the corresponding tenant.

   • Databases: In the PolarDB console, go to Settings and Management > Databases for your cluster.

     • Database names without an @<tenant_name> suffix belong to the system tenant.

     • Database names with an @<tenant_name> suffix belong to the corresponding tenant.

Enable the standalone multi-tenant mode

Multi-tenant mode is in canary release. To use this feature, join the DingTalk group 59535005981 for technical support. After enabling the feature, restart the cluster for it to take effect.

Multi-tenant mode has two layers: data isolation and resource isolation. Enable them in order.

Enable resource isolation

Resource isolation requires data isolation to be enabled first. Set enable_multi_tenant, thread_pool_multi_tenant_enabled, and thread_pool_enabled to ON. Also set thread_pool_size to match the number of CPU cores of your cluster's node specifications.

View node specifications

In the PolarDB console, go to the cluster's Basic Information page and check the Database Nodes section.

Configure the parameters

Parameter	How to set
enable_multi_tenant	Contact Technical Support to set to ON
thread_pool_multi_tenant_enabled	Contact Technical Support to set to ON
thread_pool_enabled	In the PolarDB console, go to Configuration and Management > Parameters
thread_pool_size	In the PolarDB console, go to Configuration and Management > Parameters. Set to match the number of CPU cores of your node specifications. For details, see Configure cluster and node parameters.

Manage resource configurations

A resource configuration defines the CPU limits for a tenant. When a tenant is bound to a resource configuration, all user connections in that tenant are subject to those limits. Only CPU resources are supported for isolation.

CREATE resource_config <resource_config_name> min_cpu <min_cpu> max_cpu <max_cpu>;

CREATE resource_config r1 min_cpu 0 max_cpu 1;
CREATE resource_config r2 min_cpu 1 max_cpu 4;

SELECT * FROM mysql.tenant_resource_config;

+----------------------+-------------------------+-------------------------+
| resource_config_name | resource_config_min_cpu | resource_config_max_cpu |
+----------------------+-------------------------+-------------------------+
| r1                   |                       0 |                       1 |
| r2                   |                       1 |                       4 |
+----------------------+-------------------------+-------------------------+

ALTER resource_config <resource_config_name> min_cpu <min_cpu> max_cpu <max_cpu>;

ALTER resource_config r2 min_cpu 1 max_cpu 2;

DROP resource_config <resource_config_name>;

DROP resource_config r1;

Manage tenants

This section covers managing regular tenants. The system tenant does not require management operations.

CREATE tenant <tenant_name> resource_config <resource_config_name>;

CREATE tenant tn1 resource_config r1;

SELECT * FROM mysql.tenants;

+-------------+----------------------+
| tenant_name | resource_config_name |
+-------------+----------------------+
| tn1         | r1                   |
+-------------+----------------------+

ALTER tenant <tenant_name> resource_config <resource_config_name>;

ALTER tenant tn1 resource_config r2;

DROP tenant <tenant_name>;

DROP tenant tn1;

Manage users

This section covers managing users in regular tenants. For system tenant user management, see Account management.

User management operations can be performed from the system tenant (using the privileged account) or from within a regular tenant (using a user with sufficient permissions).

In standalone multi-tenant mode, usernames can be up to 20 characters and contain only uppercase letters, lowercase letters, digits, and underscores (_).

-- Syntax
CREATE USER '<user_name>@<tenant_name>' [IDENTIFIED BY '<password>'];

-- Without a password
CREATE USER 'u1@tn1';

-- With a password
CREATE USER 'u1@tn1' IDENTIFIED BY 'password';

-- Syntax
DROP USER '<user_name>@<tenant_name>';

-- Example
DROP USER 'u1@tn1';

-- Syntax
CREATE USER '<user_name>' [IDENTIFIED BY '<password>'];

-- Without a password
CREATE USER 'u2';

-- With a password
CREATE USER 'u2' IDENTIFIED BY 'password';

-- Syntax
DROP USER '<user_name>';

-- Example
DROP USER 'u2';

mysql -h <host> -P <port> -u <user_name>@<tenant_name> -p

mysql -h pc... -P 3306 -u u1@tn1 -p
Enter password:

Manage databases

This section covers managing databases in regular tenants. For system tenant database management, see Database management.

Database management operations can be performed from the system tenant or from within a regular tenant.

In standalone multi-tenant mode, database names can be up to 50 characters and contain only uppercase letters, lowercase letters, digits, and underscores (_).

-- Syntax
CREATE DATABASE `<database_name>@<tenant_name>`;

-- Example
CREATE DATABASE `db1@tn1`;

-- Syntax
DROP DATABASE `<database_name>@<tenant_name>`;

-- Example
DROP DATABASE `db1@tn1`;

-- Syntax
CREATE DATABASE <database_name>;

-- Example
CREATE DATABASE `db2`;

-- Syntax
DROP DATABASE <database_name>;

-- Example
DROP DATABASE `db2`;

View thread group bindings

When resource isolation is enabled, the thread pool assigns thread groups to tenants. Use this query to check which thread groups are assigned to which tenants.

1. Connect with the privileged account and run:

   SELECT * FROM information_schema.thread_pool_status;

2. In the output, the TENANT_NAME column shows the tenant that exclusively uses that thread group. An empty TENANT_NAME means the thread group is shared across tenants. Sample output:

   +----+--------------+---------------------+----------------------+-------------------+----------------+-------------+
   | ID | THREAD_COUNT | ACTIVE_THREAD_COUNT | WAITING_THREAD_COUNT | DUMP_THREAD_COUNT |      ...       | TENANT_NAME |
   +----+--------------+---------------------+----------------------+-------------------+----------------+-------------+
   |  0 |            2 |                   0 |                    0 |                 0 |      ...       |             |
   |  1 |            2 |                   1 |                    0 |                 0 |      ...       | tn1         |
   |  2 |            4 |                   0 |                    0 |                 1 |      ...       |             |
   |  3 |            2 |                   0 |                    0 |                 0 |      ...       |             |
   |  4 |            3 |                   0 |                    0 |                 1 |      ...       |             |
   +----+--------------+---------------------+----------------------+-------------------+----------------+-------------+

What's next

• Account management

• Database management

• Configure cluster and node parameters