If you want to be a DBA, you must know these for multi-tenant management

Abstract: multi-tenant was born to meet customers' mixed load processing needs. By providing a two-tier user mechanism, hierarchical resource isolation, to meet customers' autonomous control requirements for computing and storage resources.

This article is shared from the HUAWEI cloud community " about GaussDB (DWS) multi-tenant management, you must know ", the original author: a little bit of Macabaca.

1. The background and significance of multi-tenancy

Multi-tenant is born to meet customers' mixed load processing needs. By providing a two-tier user mechanism and hierarchical resource isolation, it can meet customers' autonomous control requirements for computing and storage resources. Two-level users are respectively associated with different resource pools and storage spaces to realize resource grouping management under multi-tenancy.

2. Multi-tenant of GaussDB (DWS)

At present, the resources that can be isolated by multi-tenant of GaussDB (DWS) include: CPU resources, memory resources, IO resources, and storage resources.

GaussDB's multi-tenancy is realized by binding resource pools. The specific principle is shown in the following figure:

Use the above figure to illustrate the management and control method of multi-tenancy, see the next three, four, and five chapters.

Three, control group settings

The following resource isolation can be performed by setting the control group:

(1) Set the number of CPU cores and the CPU share of the control group, and perform CPU resource management;

(2) Set exception rules to terminate or downgrade abnormal operations;

1. Class control group

Create a sub-Class control group named "class_a", and the CPU resource quota is 40% of the Class.
gs_cgroup -c -S class_a -s 40

2. Workload control group

Create a Workload control group named "workload_a1" under the sub-Class control group "class_a", and the CPU resource quota is 20% of the "class_a" control group.

gs_cgroup -c -S class_a -G workload_a1 -g 20

3. Exception rules

Set the job of "workload_a1" under "class_a" to block to 1200 seconds or execute termination action when it executes for 2400 seconds.

gs_cgroup -S class_a -G workload_a1 -E "blocktime=1200,elapsedtime=2400" -a

Supported control group exception rules:

Four, resource pool settings

The following resource isolation can be performed by setting resource pool properties:

(1) Manage memory resources by setting mem_percent and memory_limit;

(2) IO management and control by setting io_limits and io_priority;

(3) Concurrent control is performed by setting active_statements and max_dop;

(4) A designated control group can be set to manage CPU and exception rules;

(5) Set exception rules for resource pools;

1. Group resource pool

Create a group resource pool named "resource_pool_a" and associate it with the "class_a" control group.

CREATE RESOURCE POOL resource_pool_a WITH (control_group='class_a');

2. Business resource pool

(1) Create a business resource pool named "resource_pool_a1" and associate it with the "workload_a1" control group

CREATE RESOURCE POOL resource_pool_a1 WITH (control_group='class_a:workload_a1');

(2) Update a resource pool, and its control group is designated as the "High" Timeshare Workload control group under the "DefaultClass" group.

ALTER RESOURCE POOL pool1 WITH (CONTROL_GROUP="High");

(3) Modify the memory ratio of the resource pool "resource_pool_a1" to 20% of the available memory size, that is, set the value of MEM_PERCEN to 20.

ALTER RESOURCE POOL resource_pool_a1 WITH (MEM_PERCENT=20);

3. Resource pool properties that can be set

4. Configurable resource pool exception rules

Five, user settings

Storage space management and control by setting user attributes:

1. Create a group user association group resource pool

CREATE USER tenant_a RESOURCE POOL 'resource_pool_a' PASSWORD 'Gauss_234';

2. Create a business resource pool associated with business users

CREATE USER tenant_a1 RESOURCE POOL 'resource_pool_a1' USER GROUP 'tenant_a' PASSWORD 'Gauss_234';

3. By setting user attributes (PERM SPACE, TEMP SPACE, SPILL SPACE), storage space management and control can be performed:

(1) Modify user "tenant_space_test" permanent table space quota is not restricted.

ALTER USER tenant_space_test PERM SPACE 'unlimited';

(2) Modify the temporary table space quota of user "tenant_space_test" to 100G.

ALTER USER tenant_space_test TEMP SPACE '100G';

(3) Modify the space limit of the user "tenant_space_test" operator to 200G.

ALTER USER tenant_space_test SPILL SPACE '200G';

Six, summary

The above mainly introduces the architecture and usage method of multi-tenant technology. Through the tenant-associated control group and resource pool, the setting of multi-tenant effectively isolates CPU resources, memory resources, IO resources and storage resources, and provides powerful mixed load management capabilities. .

For more information about GuassDB (DWS), welcome to search "GaussDB DWS" on WeChat and follow the WeChat official account to share with you the latest and most complete PB-level data warehouse black technology~

Click to follow and learn about Huawei Cloud's fresh technology for the first time~