This document provides a hands-on tutorial on using database resource isolation.
Feature Overview
In TencentDB for PostgreSQL, an instance typically hosts multiple business databases. When slow queries, batch tasks, or abnormal SQL statements occur in some databases, they can exhaust the instance's CPU resources, affecting the stability and response latency of other databases on the same instance. This leads to the problem where one database degrades the entire instance.
The database resource isolation capability is provided by the kernel plugin tencentdb_serverless, which enables fine-grained CPU quota management for each database in an instance. It can set a minimum guaranteed quota (min_cpu_cores) for critical business databases and a maximum usage limit (max_cpu_cores) for databases prone to uncontrolled resource consumption, thereby achieving resource isolation and mutual non-interference for multiple tenants/businesses within the same instance.
I. Enabling database Resource Isolation
How to Enable Instance-Level Resource Isolation for the First Time
To enable the database resource isolation schema, the backend must enable the tencentdb_serverless plugin and configure related parameters. For related operations and parameter descriptions, see database resource isolation. Checking Whether Resource Isolation Is Enabled for an Instance
Checking Whether the Plugin Is Installed
Check whether tencentdb_serverless has been installed successfully. Run \\dx in the PostgreSQL client to view the list of installed extensions. If tencentdb_serverless appears in the list, the plugin has been installed successfully.
postgres=> \\dx;
List of installed extensions
Name | Version | Schema | Description
pg_stat_log | 1.0 | public | track runtime execution statistics of all SQL statements executed
pg_stat_statements | 1.9 | public | track planning and execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
tencentdb_serverless | 1.0 | public | extension for serverless mode
tencentdb_system_stat | 1.0 | public | track execution statistics of querssy executed
(5 rows)
Checking Whether the Plugin Takes Effect
Refer to the following command to check whether the plugin is effective. If the target result is shown, the plugin is working properly.
postgres=> show tencentdb_serverless.min_cpu_cores;
tencentdb_serverless.min_cpu_cores
4
(1 row)
postgres=> show tencentdb_serverless.max_cpu_cores;
tencentdb_serverless.max_cpu_cores
4
(1 row)
II. Usage Reference
Set the CPU resource limit for the specified database.
Specific functions:
tencentdb_serverless.set_database_cpu_limit(database_name, min_cpu_cores, max_cpu_cores)
Parameter description:
|
database_name | Name of the specific database for which resource isolation needs to be configured. |
min_cpu_cores | This parameter specifies the minimum number of CPU cores available to the database. When the overall instance resources are insufficient, this quota ensures that the database can still receive its allocated share. The quota is allocated on A percentage basis. For example, if the current instance has three databases named A, B, and C, with minimum quotas of 2, 3, and 3 respectively, and the instance currently has 8 CPU cores, then the minimum number of cores available to A is calculated as (2 / (2 + 3 + 3)) * 8 = 2 cores. |
max_cpu_cores | This parameter specifies the maximum number of CPU cores available to the database. The value cannot exceed the current number of cores of the instance. If you do not need to set a maximum CPU resource limit for the database, set this parameter to -1. |
Reference example:
Set the parameters to limit the CPU of the tenant_001 database to 1c.
postgres=> select tencentdb_serverless.set_database_cpu_limit('tenant_001',0.1,1);
set_database_cpu_limit
(1 row)
View the configuration result:
postgres=> select * from tencentdb_serverless.resource_limit_view;
database_name | min_cpu_cores | max_cpu_cores | min_mem_kilobytes | max_mem_kilobytes
tenant_001 | 0.1 | 1.0 | |
(1 row)
Disable resource isolation:
Disable resource isolation for the tenant_001 database.
postgres=> select tencentdb_serverless.reset_database_limit('tenant_001');
reset_database_limit
(1 row)
III. Setting Resource Limits for Multiple Databases
Scenario
Set resource limits for two of the multiple databases. For example, for the ipb and ibp_hk databases, the two databases can use up to 50% of the CPU in total, with each database capped at 25%. No resource limits are set for the other databases.
Solution
Take the 4c instance as an example to configure resources as follows.
Query all databases:
postgres=> select datname from pg_database where datname not like 'template%';
datname
postgres
ipb
ibp_hk
tenant_001
(4 rows)
Isolate and limit database resources:
select tencentdb_serverless.set_database_cpu_limit('ipb',0.25,1);
select tencentdb_serverless.set_database_cpu_limit('ibp_hk',0.25,1);
Do not isolate or limit resources for other databases:
select tencentdb_serverless.set_database_cpu_limit('postgres',1,-1);
select tencentdb_serverless.set_database_cpu_limit('tenant_001',1,-1);
IV. FAQ
Q: Is the CPU limit for a database a hard limit?
A: The max_cpu_cores limited by set_database_cpu_limit is a hard limit, and a single database cannot exceed this limit.
Q: What Happens When Database CPU Usage Reaches Its Limit?
A: When the database CPU usage reaches its limit, tasks will slow down. Note that when the CPU is fully utilized, connections will not be killed and business will not be interrupted.
Q: Does database Resource Isolation Configuration Have Any Other Impacts?
A: It only limits resource-isolated databases and has no other impact.
Q: Does a Database with a CPU Limit of -1 Have Any Impact?
A: No impact. When the limit is -1, even if a restricted database reaches its CPU limit, the database can still use all remaining resources of the entire instance.
Q: Does Enabling the Plugin in the Background Affect Instances?
A: No impact.
Q: Does Resource Isolation Configuration Affect the Business Side?
A: It only affects the specified database, takes effect immediately after being set, and isolates resources for the database.
Q: Does Isolation Affect Newly Added Databases?
A: No impact. Newly added databases are not subject to resource isolation limits. If isolation is required, you can execute tencentdb_serverless.set_database_cpu_limit to isolate them.
Q: Do I Need to Clean Up Resource Isolation Configuration When Deleting a Database?
A: When a database in the instance is deleted, the system automatically deletes its CPU resource configuration data.