Resource Management

QHB provides management of shared resources of server processes based on Linux kernel capabilities (cgroups).

This feature is controlled by the resource_management configuration parameter.

Enabling:

ALTER SYSTEM SET resource_management TO ON;

Disabling:

ALTER SYSTEM SET resource_management TO OFF;

Management is performed by a special DBMS child process, which is implemented as an executable file group-agent.

Resource Management Groups

The concept of "group" has been introduced for resource management. For each resource management group, a restriction on access to system resources (CPU) is set, and then this groups are assigned to individual DBMS users to configure the priority of using CPU resources.

To create a resource management group:

qhb_resource_group_create ( group_name text )

To restrict CPU resource utilization for the created group:

qhb_resource_group_set_cpu_share ( group_nametext, cpu_share int4 )

To assign a resource management group to a DBMS user:

qhb_resource_group_user_set_group ( usernametext, group_name text )

where:

group_name — resource management group name;
cpu_share — group weight in the distribution of available CPU resources: integer value from 2 (~1%) to 1024 (~100%);
username — existing user name in the DBMS.

Example:

SELECT qhb_resource_group_create('abcd');

SELECT qhb_resource_group_set_cpu_share('abcd', 300);

SELECT qhb_resource_group_user_set_group('qhb', 'abcd');

After executing these commands as the DBMS superuser or administrator, subsequent connections by the user qhb will result in restrictions on CPU consumption by its server processes.

The restrictions apply individually to each server process.

Lifting Restrictions

To lift CPU usage restrictions for all users in a group:

qhb_resource_group_unset_cpu_share ( group_name text )

To unassign a resource management group from a user:

qhb_resource_group_user_unset_group ( username text )

The restriction lifting takes effect after the user next login to QHB.

Quotas for Access to Storage (Disk)

CPU load balancing is not the only way to restrict the resource consumption by QHB user's server processes. Limiting the speed of access to durable storage (disk) is also supported.

This requires knowing the device numbers that the Linux kernel has assigned to your disk or section. This can be done using the lsblk command on the command line.

To limit the disk access speed:

qhb_resource_group_set_blkio ( group_nametext,dev_maj_numint4,dev_min_numint4,bps_read_rateint4,bps_write_rate int4 )

To lift disk access restrictions in a group:

qhb_resource_group_unset_blkio ( group_name text )

where:

group_name — resource management group name;
dev_maj_num — major device number of your disk or section;
dev_min_num — minor device number of your disk or section;
bps_read_rate — desired maximum disk access speed for reading in bps (bytes per second);
bps_write_rate — desired maximum disk access speed for writing in bps.

Example:

Execute in the command line:

$ lsblk
NAME        MAJ:MIN
sda         254:0  
nvme0n1     259:0
|_nvme0n1p1 259:1

Now, knowing the major/minor device numbers, you can set a limit of 1 kilobyte per second for reading and 300 bytes per second for writing:

SELECT qhb_resource_group_set_blkio('abcd', 259, 1, 1024, 300); -- setting device restrictions to 259:1

SELECT qhb_resource_group_unset_blkio('abcd'); -- lifting restrictions

There is no need to separately associate a user with restrictions. With a resource management group assigned, the restrictions will take effect the next time the user connects to QHB.

Viewing Existing Restrictions

You can view the currently set restrictions using shared catalogs qhb_resource_group and qhb_resource_group_users.

Example:


SELECT * FROM qhb_resource_group; -- to view resource management groups

SELECT * FROM qhb_resource_group_users; -- to view groups assigned to specific users

Note
Assignment of resource management groups to hierarchical roles and information security roles is not implemented in the DBMS. The group is assigned to a separate user with LOGIN rights.