A method, system, device, and medium for resource management of a database

Abstract

The application discloses a kind of to database is carried out resource management method, system, equipment and medium;The method comprises: creating at least one resource group in database, and the configuration information of each resource group is defined;Based on each resource group binding relationship table, each resource group is bound, and the binding type of each resource group is determined;Based on the binding type of each resource group, the configuration information of each resource group is updated by resource group operation interface, and the target configuration information of each resource group is obtained;Based on the target configuration information of each resource group and the state of each resource group, the corresponding query operation is executed;Whether the state of each resource group changes is monitored, when the state of each resource group changes, then trigger corresponding resource adjustment strategy, and record change information;Different tenants (database) are isolated by resource group mechanism, realize database level resource isolation and fine management, improve the stability of multi-tenant environment.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database resource management, and particularly relates to a method, system, device and medium for managing resources of a database. BACKGROUND

[0002] With the continuous expansion of database application scale and the popularity of multi-tenant environment, database resource management is facing severe challenges. In particular, in the SaaS (Software as a Service) scenario, each database (database) in the PostgreSQL database instance usually corresponds to an independent tenant, and the resource isolation and management problem under this architecture is particularly prominent.

[0003] Although the traditional PostgreSQL database system provides basic resource control mechanisms, there are still the following problems in complex multi-tenant production environments, for example, insufficient resource isolation between tenants: in the same PostgreSQL instance, there is a lack of effective resource isolation mechanism between different tenants (databases), so that high-load queries of a tenant may consume too much system resources, affecting the service quality of other tenants. Lack of database-level resource control: PostgreSQL does not support fine-grained CPU and memory resource limits at the database level by nature, and cannot perform differentiated resource allocation according to the service level agreement (SLA) of tenants. Incomplete monitoring of tenant resource usage: there is a lack of real-time monitoring and historical data analysis capabilities for the resource usage of each tenant, making it difficult to accurately plan capacity and optimize resources. Low resource usage efficiency: lack of dynamic resource adjustment mechanism, system resources are often in a state of "over-allocation" or "resource shortage", and cannot be optimized according to the actual tenant load. Inefficient complex query optimization: for complex queries, the query execution efficiency is low.

[0004] Although there are some third-party tools and extensions trying to solve the above problems, most of the solutions are either single-functioned, focusing on resource control in one aspect; or complex to implement, requiring deep modification of the database kernel; or bound to specific operating systems or hardware platforms, lacking universality.

[0005] Therefore, there is an urgent need for a solution that can implement database-level resource isolation in a PostgreSQL instance, including CPU isolation and supporting monitoring, migration and audit solutions, to solve the resource contention problem of PostgreSQL databases in a multi-tenant environment. SUMMARY

[0006] The present application provides a method, system, device and medium for managing resources of a database to solve the resource contention problem of PostgreSQL databases in a multi-tenant environment.

[0007] In a first aspect, embodiments of the present application provide a method for resource management of a database, comprising:

[0008] creating at least one resource group in the database and defining configuration information of each of the resource groups, wherein the configuration information comprises resource group ID, state, priority, CPU quota, memory quota and parallelism;

[0009] binding each of the resource groups based on a binding relationship table of each of the resource groups, and determining a binding type of each of the resource groups;

[0010] updating the configuration information of each of the resource groups based on the binding type of each of the resource groups through a resource group operation interface, to obtain target configuration information of each of the resource groups;

[0011] performing a corresponding query operation based on the target configuration information of each of the resource groups and the state of each of the resource groups;

[0012] monitoring whether a state change occurs in each of the resource groups, and when the state of each of the resource groups changes, triggering a corresponding resource adjustment strategy and recording the change information.

[0013] Optionally, after the target configuration information of each of the resource groups is obtained, the method further comprises:

[0014] registering a CPU monitoring process, and periodically collecting CPU usage of each of the resource groups by the process;

[0015] periodically updating the CPU quota in the target configuration information of each of the resource groups based on the CPU usage of each of the resource groups and a pre-set CPU resource control strategy.

[0016] Optionally, after the target configuration information of each of the resource groups is obtained, the method further comprises:

[0017] registering a memory allocation hook function to determine memory usage data of each of the resource groups in real time;

[0018] judging whether the memory usage data of each of the resource groups is outside a pre-set memory range;

[0019] if yes, triggering an early warning and performing a corresponding first processing strategy.

[0020] Optionally, after the target configuration information of each of the resource groups is obtained, the method further comprises:

[0021] determining a query type based on a query parser;

[0022] periodically monitor resource usage of the resource group to which the query type belongs, and perform statistics, storage and analysis;

[0023] The resource usage includes CPU usage of the resource group to which the query type belongs and memory usage data of the resource group to which the query type belongs.

[0024] Optionally, the CPU resource control strategy includes a CPU resource guarantee mechanism, a CPU resource dynamic adjustment mechanism and a CPU resource strict limitation mechanism.

[0025] The CPU resource guarantee mechanism is used to ensure that each resource group obtains at least the CPU quota requested by each resource group.

[0026] The CPU resource dynamic adjustment mechanism is used to automatically adjust the CPU quota of each resource group based on the CPU usage of each resource group.

[0027] The CPU resource strict limitation mechanism is used to strictly sort the queries in the resource group queue according to priority and waiting time, and when the CPU usage of the resource group reaches a hard limit threshold, newly submitted queries will be put into a waiting queue until the CPU usage decreases below the hard limit threshold.

[0028] Optionally, after the memory allocation hook function is registered and the memory usage data of each resource group is determined in real time, the method further includes:

[0029] determining whether the memory usage data of each resource group exceeds the memory quota of each resource group;

[0030] If yes, a corresponding second processing strategy is executed according to the memory type of the memory usage data.

[0031] Optionally, after the resource usage of the resource group to which the query type belongs is periodically monitored, the method further includes:

[0032] updating the parallelism of the resource group to which the query type belongs based on the CPU usage of the resource group to which the query type belongs;

[0033] parallelly executing each work process in the resource group to which the query type belongs based on the updated parallelism of the resource group to which the query type belongs.

[0034] In a second aspect, an embodiment of the present application provides a system for resource management of a database, which is used to execute the method for resource management of a database according to any embodiment of the present application.

[0035] The resource group management module is configured to create at least one resource group in the database, bind each of the resource groups based on the resource group binding relationship table, and monitor whether a state change occurs in each of the resource groups, and trigger a corresponding resource adjustment strategy when a state change occurs in each of the resource groups.

[0036] The CPU resource management control module is configured to register a CPU monitoring process, periodically collect CPU usage of each of the resource groups, periodically update CPU quotas in target configuration information of each of the resource groups based on the CPU usage of each of the resource groups and a pre-set CPU resource control strategy, and register a memory allocation hook function.

[0037] The memory resource management control module is configured to register a memory allocation hook function, determine memory usage data of each of the resource groups in real time, determine whether the memory usage data of each of the resource groups is outside a pre-set memory range, and trigger a pre-warning and perform a corresponding first processing strategy if the memory usage data of each of the resource groups is outside the pre-set memory range.

[0038] The query execution resource control module is configured to determine a query type based on a query parser, periodically monitor resource usage of a resource group to which the query type belongs, and perform statistics, storage and analysis, wherein the resource usage includes CPU usage of the resource group to which the query type belongs and memory usage data of the resource group to which the query type belongs.

[0039] In a third aspect, an electronic device is provided, and the electronic device includes:

[0040] at least one processor; and

[0041] a memory connected to the at least one processor in communication; wherein

[0042] The memory stores a computer program executable by the at least one processor, and the computer program is executed by the at least one processor to enable the at least one processor to execute the method for managing resources of a database according to any one of the embodiments of the present application.

[0043] In a fourth aspect, a computer readable storage medium is provided, and the computer readable storage medium stores computer instructions, and the computer instructions are used to enable a processor to implement the method for managing resources of a database according to any one of the embodiments of the present application when executed.

[0044] (1) The application creates resource groups, binds each resource group based on the binding relationship table, isolates the resources of different tenants (databases), prevents a single tenant from over-consuming system resources and affecting other tenants, and makes the PostgreSQL instance in the multi-tenant environment more stable and reliable, without interference between tenants, realizes database-level resource isolation, and improves the stability of the multi-tenant environment.

[0045] (2) Based on the binding type of each resource group, the configuration information of each resource group is updated through the resource group operation interface to obtain the target configuration information of each resource group; different resource quotas are allocated to different tenants according to the importance of the binding business type of the resource group and the service level agreement (SLA); high-priority businesses can obtain more resource guarantees to ensure the performance and response time of critical businesses; at the same time, low-priority businesses are appropriately limited to realize reasonable allocation of resources and meet the needs of different business scenarios, improving resource utilization efficiency.

[0046] (3) The application periodically updates the CPU quota in the target configuration information of each resource group based on the CPU usage of each resource group and the pre-set CPU resource control strategy, and can automatically adjust resource allocation according to the actual load. When the system load is low, the resource group is allowed to break through the basic quota and use more resources; when the system load is high, resource compression is performed according to the priority, which significantly improves the overall utilization of resources and avoids waste of resources.

[0047] (4) The application determines the memory usage data of each resource group in real time by registering a memory allocation hook function; determines whether the memory usage data of each resource group is outside the preset memory range; if yes, triggers an early warning and executes the corresponding first processing strategy; realizes millisecond-level accurate capture of the memory usage of the resource group, and in combination with the dynamic judgment of the preset water level, triggers an early warning and executes a mild intervention strategy (such as limiting new query memory and postponing non-critical operations) when the risk of memory overrun occurs early (low water level, such as 80% of the quota), so as to achieve "prevention" rather than "remedy", effectively avoid query interruption or system crash caused by sudden memory depletion, and significantly improve the real-time performance and system stability of resource isolation in the multi-tenant environment.

[0048] (5) The application updates the parallelism of the resource group to which the query type belongs based on the CPU usage of the resource group to which the query type belongs, automatically selects the best parallelism for complex queries according to the resource condition, avoids resource contention caused by excessive parallelism, and ensures sufficient parallelism to improve query performance, thereby optimizing the overall query execution efficiency.

[0049] (6) The application determines the query type based on a query parser; periodically monitors the resource usage of the resource group to which the query type belongs, and performs statistics, storage and analysis; wherein the resource usage includes: the CPU usage of the resource group to which the query type belongs and the memory usage data of the resource group to which the query type belongs; by combining query semantic understanding and resource state tracking depth, a "perception-decision-control" closed loop is constructed; through the analysis hook + background monitoring process, fine-grained (tenant / query type level) and full-dimensional (CPU / memory / I / O) data collection is realized; the historical statistics are used to predict the resource demand, and the execution strategy is dynamically optimized; the database level SLA guarantee is realized under the premise of zero kernel modification.

[0050] It should be understood that the content described in this part is not intended to identify key or important features of the embodiments of the application, nor is it used to limit the scope of the application. Other features of the application will become apparent from the following description. BRIEF DESCRIPTION OF DRAWINGS

[0051] In order to more clearly illustrate the technical solutions in the embodiments of the application, the drawings needed in the embodiment description will be briefly introduced below. Obviously, the drawings in the following description are only some embodiments of the application, and other drawings can be obtained by those skilled in the art without creative labor.

[0052] Figure 1 A method flow chart for managing resources of a database is provided for the first embodiment of the application;

[0053] Figure 2 A framework diagram of a system for managing resources of a database is provided for the third embodiment of the application;

[0054] Figure 3 A resource group management flow chart is provided for the third embodiment of the application;

[0055] Figure 4 A CPU resource management control flow chart is provided for the third embodiment of the application;

[0056] Figure 5 A memory resource management control flow chart is provided for the third embodiment of the application;

[0057] Figure 6 A query execution resource control flow chart is provided for the third embodiment of the application;

[0058] Figure 7 A structural schematic diagram of an electronic device that can be used to implement the embodiments of the application is shown. DETAILED DESCRIPTION

[0059] In order to make the person skilled in the art better understand the present application, the technical solutions in the embodiments of the present application will be described clearly and completely in the following with reference to the drawings in the embodiments of the present application. Obviously, the described embodiments are only a part of the embodiments of the present application, not all. Based on the embodiments in the present application, all other embodiments obtained by the person skilled in the art without creative labor should belong to the scope of protection of the present application.

[0060] It should be noted that the terms "first", "second" and the like in the specification and claims of the present application and the above-described drawings are used to distinguish similar objects, and do not necessarily indicate a specific order or a chronological sequence. It should be understood that the data thus used can be interchanged under appropriate circumstances, so that the embodiments of the present application described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "include" and "have" and any variations thereof are intended to cover non-exclusive inclusion, for example, a process, method, system, product or device that includes a series of steps or units does not necessarily have to be limited to those steps or units clearly listed, but can include other steps or units that are not clearly listed or inherent to these processes, methods, products or devices.

[0061] Embodiment 1:

[0062] Figure 1 A flow chart of a method for resource management of a database is provided for the first embodiment of the present application. The present embodiment can be applied to the case of resource management of a database. The method can be executed by a device for resource management of a database. The device for resource management of a database can be realized in the form of hardware and / or software. The device for resource management of a database can be configured in an electronic device. As shown in the figure, the method comprises: Figure 1

[0063] S110, creating at least one resource group in the database, and defining configuration information of each resource group, wherein the configuration information comprises resource group ID, state, priority, CPU quota, memory quota and parallelism;

[0064] ​Wherein, the database can refer to a PostgreSQL database. The resource group can refer to a logical resource container for tenants (databases / roles / applications) implemented within the PostgreSQL database. The resource group ID can refer to a unique name of the resource group. The resource group state can refer to a running state of the resource group, such as active (ACTIVE), suspended (SUSPENDED), maintaining (MAINTAINING), and disabled (DISABLED). The priority can refer to an allocation order in resource contention. The CPU quota can refer to a rated configuration of resource group CPU, including a hard limit value, a request value, and a soft limit value (capable of dynamic adjustment). The memory quota can refer to a rated configuration of resource group memory, including a total limit value, a request value, and a quota of each memory type (the memory type can be divided into shared buffer, temporary buffer, and working memory). The parallelism can be understood as the maximum number of connections and the maximum number of parallel working processes.

[0065] Specifically, the resource group includes a resource group definition table, the resource group definition table is designed in a flat structure, and stores core configuration information of the resource group, including a unique name ID, a state, a priority (determining an allocation order in resource contention), a CPU quota parameter (a hard limit value, a request value, and a soft limit value), a memory quota parameter (a total limit value, a request value, and a quota of each type of memory), and a concurrency control parameter (i.e. parallelism, which can be understood as the maximum number of connections and the maximum number of parallel working processes). Further, the resource group definition table can further include an excess control parameter, such as an excess allocation ratio and a speed limit level, for fine adjustment of resource control behavior.

[0066] In the embodiment, at least one resource group is created in the database, and resources of different tenants (databases) are isolated through the resource group mechanism, so as to prevent a single tenant from excessively consuming system resources and affecting other tenants. The isolation mechanism makes the PostgreSQL instance in the multi-tenant environment more stable and reliable, and the tenants do not interfere with each other, thereby significantly improving the overall service quality and availability of the system.

[0067] S120, binding each resource group based on the resource group binding relationship table, and determining a binding type of each resource group;

[0068] Wherein, the binding relationship table can refer to a mapping relationship table of the resource group. The binding type can be database (tenant) binding, role binding, and application binding.

[0069] Specifically, the resource binding relationship table includes a database binding table, a role binding table, and an application binding table, which respectively establish the mapping relationship between the resource group and the database, the role, and the application. The database binding is that one resource group binds one database (corresponding to one tenant). The role binding is that the resource is allocated according to the user role (such as administrator / ordinary user). The application binding is that the resource is allocated according to the application name (differentiating different business systems).

[0070] In the embodiment, through the resource group binding relationship table, multi-dimensional resource isolation is supported, and the resource allocation and control can be performed according to the database (tenant), the user role, or the application name.

[0071] S130, based on the binding type of each resource group, the configuration information of each resource group is updated through a resource group operation interface to obtain target configuration information of each resource group;

[0072] The resource group operation interface can refer to an API that provides creation, modification, and deletion, supports dynamic adjustment of the quota at runtime (that is, the configuration information of each resource group is updated). The creation function executed through the resource group operation interface includes the following steps: first, verify the uniqueness of the resource group name, then check whether the CPU and memory quota parameters are within a reasonable range, and verify that the request value does not exceed the limit value. To ensure that the total resources of all resource groups do not exceed the total amount of available resources of the system, after verification, create a resource group record and initialize the related statistical data and state information.

[0073] The modification function executed through the resource group operation interface supports dynamic adjustment of various parameters of the resource group, including CPU and memory quota, priority, state, and the like. The modification operation adopts transaction processing (Transaction) to ensure atomicity, and checks the current usage amount when adjusting the resource quota to avoid resource overrun caused by quota reduction. Further, the modification function executed through the resource group operation interface also supports execution when the state of the resource group changes, such as activation, suspension, maintenance, and disablement. Each state conversion triggers a corresponding resource adjustment operation.

[0074] The deletion function executed through the resource group operation interface includes the following steps: first, check whether there is a binding relationship, if there is, provide a forced unbinding option or refuse to delete. Before deletion, all resources occupied by the resource group are released, and the system resource allocation state is updated. The deletion operation also adopts transaction processing (Transaction) to ensure data consistency.

[0075] Specifically, based on the binding type of each resource group, through the resource group operation interface, the configuration information of each resource group is locked and updated by using transaction processing, and after the update is completed, the total resource constraint is checked. If the configuration information of each resource group is within the total resource constraint, the target configuration information of each resource group is obtained.

[0076] In this embodiment, based on the binding type of each resource group, the configuration information of each resource group is updated through the resource group operation interface to obtain the target configuration information of each resource group. Through the binding type directional update, atomicity is ensured by using transaction processing, the dynamic adjustment of resource configuration in the multi-tenant environment is realized, and the data consistency is strictly guaranteed. In addition, different resource quotas are allocated to different tenants (different binding types) according to the business importance and service level agreement (SLA); high-priority businesses can obtain more resource guarantees to ensure the performance and response time of critical businesses; at the same time, low-priority businesses are appropriately limited to realize the reasonable allocation of resources. This differentiated allocation mechanism meets the needs of different business scenarios and improves the resource utilization efficiency.

[0077] S140, based on the target configuration information of each resource group and the state of each resource group, performing corresponding operations;

[0078] The corresponding operation can refer to the operation corresponding to the current state of the resource group, such as activation, suspension, maintenance, and disablement; and state change operation. The operation corresponding to the activation state can be normal resource allocation and allow new queries; the operation corresponding to the suspension state can be to limit new queries and allow existing queries to complete; the operation corresponding to the maintenance state can be to allow only management operations; and the operation corresponding to the disablement state can be to release all resources and prohibit any operation.

[0079] Specifically, the current state is obtained and the state transition history record is loaded; the control hook is initialized, wherein the control hook includes an executor hook (query execution control) and a memory management hook (memory control); and the resource group is locked. The state of the locked resource group is checked to determine the current state type of the locked resource group, and preprocessing is performed, for example, if the state is active, dynamic resource allocation is performed; if the state is suspended, new resources are frozen; if the state is maintenance, resource types are limited; and if the state is disabled, all resources are released. The target configuration information of the locked resource group is loaded, the state duration is determined based on the state transition history record, and state-specific operations are performed. Further, when the state changes, the corresponding resource adjustment strategy is triggered, for example, when the state changes from active to suspended, new query execution is limited but existing query completion is allowed.

[0080] In this embodiment, by executing corresponding operations based on the target configuration information of each resource group and the state of each resource group, intelligent adaptation and precise control of resource allocation in a multi-tenant environment are achieved by dynamically combining the target configuration and real-time state of the resource group, which significantly improves resource utilization efficiency and solves the resource contention problem in a fully automated manner.

[0081] In S150, whether a state change occurs in each resource group is monitored, and when a state change occurs in each resource group, a corresponding resource adjustment strategy is triggered, and the change information is recorded.

[0082] The resource adjustment strategy can refer to an automatic control rule triggered when the state of the resource group changes, and strictly follows the transaction atomicity to ensure the consistency of resource operation and state change, without manual intervention throughout the process. The change information can refer to the state change history, which can be understood as the full-link audit tracking record of the state conversion process of the resource group, for example, the accurate time stamp of each state change, the original state and new state type, the trigger condition, the resource adjustment strategy execution details, the operation subject, the influence range and the resource release amount, etc.

[0083] Specifically, a state monitoring process can be registered to periodically check the state of the resource group, and when the state changes, the corresponding resource adjustment strategy is triggered, for example, when the state changes from suspended to active, the query execution function is re-enabled and the resource allocation is resumed, and the state change history is recorded. Further, an alarm can be triggered based on a preset threshold and rule when the state is abnormal, for example, the preset rule can refer to triggering a warning when the disabled state is stuck and the duration of the disabled state of the resource group is greater than 1 hour. In addition, the state query interface can be used to filter and count the resource group state information according to different conditions; the resource group state information can refer to the real-time state type (active, suspended, maintenance and disabled) of the resource group, the state duration, the dynamically calculated parameters (such as CPU soft limit value), the resource usage (CPU core seconds, memory peak value), the bound object (database / role / application), etc., which can be stored and updated in real time through shared memory.

[0084] In this embodiment, through full-link closed-loop management of creation, binding, configuration, execution and monitoring, extreme automation of resource isolation and scheduling in a multi-tenant scenario is achieved; based on the binding type (database / role / application) to update the configuration to ensure tenant-level policy isolation; combined with the target configuration and real-time state to drive query operation, the resource utilization is improved while ensuring SLA; intelligent resource adjustment strategy is triggered by state change to reduce manual intervention; the state change history is recorded to provide data cornerstone for fault tracing and capacity planning, and the database resource management and control is realized through plug-in design.

[0085] Embodiment 2:

[0086] The technical solution of the embodiment is further refined on the basis of the above-mentioned embodiment. The method for managing resources of a database further comprises:

[0087] Optionally, after obtaining the target configuration information of each resource group, the method further comprises:

[0088] registering a CPU monitoring process that periodically collects the CPU usage of each resource group;

[0089] The CPU usage can refer to an aggregated indicator in units of resource groups, and the CPU usage of a specific resource group is (total CPU time of all processes in the comprehensive resource group / system time increment) x 100%. The total CPU time is the sum of the user state time increment (Δutime) and the kernel state time increment (Δstime), the user state time increment (Δutime) can refer to the time increment of the process executing code in the user space, and the kernel state time increment (Δstime) can refer to the time increment of the process executing calls in the kernel space. The system time increment is the difference between the total system running time of two samplings. The CPU monitoring process can refer to a process registered through the PostgreSQL background worker mechanism to collect the CPU time of all processes in each resource group at a fixed time (for example, 5 seconds) as a periodic increment, aggregate the CPU usage of the resource group according to the CPU usage determination formula, and update the resource group state information in the shared memory in real time.

[0090] Specifically, the monitoring process first initializes the shared memory area to store the CPU usage data; then enters the main loop, and each loop period performs the following steps: connects to the database, queries all active processes and their resource group mapping relationship, obtains the CPU usage of each process, accumulates the CPU usage according to the resource group, updates the resource group statistics table, checks whether the soft limit needs to be adjusted, and finally waits for the next sampling period. The method for obtaining the CPU usage of the process varies with the operating system; for example, on a Linux system, the user state and kernel state CPU time of the process can be obtained by parsing the / proc / [pid] / stat file, the time difference from the last sampling is calculated, and the CPU usage is converted. Further, a process mapping table is created to record the resource group to which each PostgreSQL process belongs, support dynamic updating of the resource group to which the process belongs, and handle the automatic inheritance relationship of parallel query sub-processes, which can avoid CPU statistical deviation and ensure the correctness of resource attribution after process migration.

[0091] Based on the CPU usage of each resource group and the pre-set CPU resource control strategy, the CPU quota in the target configuration information of each resource group is periodically updated.

[0092] The CPU resource control strategy refers to a hierarchical control system that dynamically adjusts CPU resource allocation based on resource group priority and CPU utilization. It includes a three-level CPU limiting mechanism: Request Guarantee Mechanism, Soft Limit Dynamic Adjustment, and Hard Limit Strict Limit. The Request Guarantee Mechanism ensures that a resource group receives at least a set CPU request value (e.g., 30%). Soft Limit Dynamic Adjustment automatically adjusts the soft limit value based on system load. When the total system load is high (e.g., exceeding 90%), the soft limit for all resource groups decreases proportionally; when the system load is low, the soft limit gradually increases. Total system load = ∑(current CPU utilization of all resource groups). Hard Limit Strict Limit is implemented through a query queuing mechanism. When the CPU utilization of a resource group approaches or reaches the hard limit, newly submitted queries are placed in a waiting queue until the resource utilization drops below the threshold.

[0093] Specifically, based on the CPU utilization rate of each resource group, it is determined whether it meets the update trigger conditions of the pre-set CPU resource control policy. If it does, the corresponding CPU resource adjustment operation is executed to update the CPU quota in the target configuration information of each resource group. For example, if the CPU utilization rate of a resource group is less than the set CPU request value, the Request guarantee mechanism is triggered, the corresponding CPU resource adjustment operation is executed, and the CPU quota in the target configuration information of that resource group is updated.

[0094] In this embodiment, a CPU monitoring process is registered, which periodically collects the CPU utilization rate of each resource group; based on the CPU utilization rate of each resource group and the pre-set CPU resource control policy, the CPU quota in the target configuration information of each resource group is periodically updated; the resource allocation can be automatically adjusted according to the actual load, which significantly improves the overall utilization rate of system resources and avoids resource waste.

[0095] Optionally, after obtaining the target configuration information for each of the resource groups, the method further includes:

[0096] Register a memory allocation hook function to determine the memory usage data of each resource group in real time;

[0097] In this context, "hook function" refers to a function that is part of the Windows message handling mechanism. By setting up "hooks," applications can filter all messages and events at the system level, accessing messages that are normally inaccessible. Essentially, a hook is a program used to process system messages; it is attached to the system through system calls.

[0098] Specifically, after obtaining the target configuration information for each resource group, a memory allocation hook function (palloc_hook) is registered to intercept all memory allocation requests. The hook function records the size, allocation context, and purpose of each allocated memory, and performs statistics by resource group and memory type. To improve performance, a batch update strategy can be adopted to periodically write memory usage data to the shared memory area. Memory usage data can be distinguished between shared memory and session memory, and statistically categorized by memory type (shared buffer, temporary buffer, working memory). Memory usage data can include real-time memory usage, peak usage, and average usage, and can be stored at different time granularities (minutes, hours, days) for historical trend analysis. Furthermore, a memory context hook (MemoryContextCallback) can be registered to record the call stack and holding time of large memory allocations to identify potential memory leak points and handle memory leak issues.

[0099] Determine whether the memory usage data of each resource group is outside the preset memory range;

[0100] The preset memory range refers to the pre-set memory over-limit warning range, specifically including a low watermark and a high watermark. The low watermark is usually 80% of the memory quota, and the high watermark is usually 95% of the memory quota.

[0101] Specifically, memory-related parameters such as the maximum memory available for a single query operation (work_mem) and the maximum memory available for maintenance operations (e.g., index creation, VACUUM) (maintenance_work_mem) can be dynamically set based on the configuration of the resource group to which the session belongs. Additionally, for parallel queries, the degree of parallelism must be considered. The memory allocation ratio is calculated in real-time based on the degree of parallelism, and working memory is allocated proportionally to ensure that the total memory usage does not exceed the resource group quota. After determining the resource group memory quota, a preset memory range is determined. Memory usage data for the resource group is obtained in real-time through a memory allocation hook function. This memory usage data is compared with the low-watermark and high-watermark of the preset memory range. Based on the comparison results, it is determined whether the memory usage data of each resource group is outside the preset memory range.

[0102] If so, an alert will be triggered and the corresponding first processing strategy will be executed.

[0103] Here, early warning can refer to a risk alert signal; the corresponding first processing strategy can refer to the intervention measures when the data used in memory exceeds the memory range.

[0104] Specifically, when memory usage reaches the low watermark, an alert is triggered, and mild measures are taken, such as reducing memory allocation for new queries and postponing non-critical memory operations. When memory usage reaches the high watermark, a protection mechanism is triggered, and mandatory measures are taken, such as canceling low-priority queries and dumping memory-intensive operations to disk. Furthermore, predictive algorithms can be used to assess memory requirements before executing memory queries. Based on the memory query plan, table size, and historical execution data, the algorithm can predict peak memory usage for queries and automatically adjust execution strategies when memory is insufficient, such as reducing parallelism, enabling batch processing, or increasing disk overflow.

[0105] In this embodiment, by registering a memory allocation hook function, the memory usage data of each resource group is determined in real time; it is determined whether the memory usage data of each resource group is outside the preset memory range; if so, an early warning is triggered and the corresponding first processing strategy is executed; this achieves millisecond-level accurate capture of the memory usage of resource groups. Combined with the dynamic judgment of the preset water level, an early warning can be triggered and a mild intervention strategy (such as limiting new query memory and postponing non-critical operations) can be executed at an early stage when the risk of memory exceeding the limit occurs (low water level, such as 80% of the quota). This achieves "prevention" rather than "remediation" with extremely low performance loss, effectively avoiding query interruption or system crash caused by sudden memory exhaustion, and significantly improving the real-time performance and system stability of resource isolation in a multi-tenant environment.

[0106] Optionally, after obtaining the target configuration information for each of the resource groups, the method further includes:

[0107] Based on the query parser, determine the query type;

[0108] The query parser can refer to the core module in the PostgreSQL kernel responsible for lexical analysis, syntax parsing, and generating the initial query tree (Parse Tree); the query type can refer to the category based on the query syntax structure and execution characteristics, including two dimensions: operation type (SELECT: data retrieval; INSERT: data insertion; UPDATE: data update; DELETE: data deletion) and complexity level (simple query / complex query), which are used to allocate computing resources differently.

[0109] Specifically, by extending PostgreSQL's query parser, a preliminary query tree can be generated. By registering a hook function (post_parse_analyze_hook), the query tree structure can be analyzed to identify the query type (SELECT, INSERT, UPDATE, DELETE, etc.) and complexity. For SELECT queries, a further distinction can be made between simple and complex queries. Complex queries typically include aggregations, window functions, complex joins, or subqueries. Furthermore, by analyzing the query plan tree, potential resource-intensive operations (such as hash joins, sorting, and aggregation) can be identified, and the CPU and memory requirements for each operation can be estimated. Table size, indexes, and query complexity can also be considered, and historical execution data can be used to optimize the accuracy of the estimated CPU and memory requirements for each operation. For example, the corrected memory requirement = basic estimate × (historical peak memory / historical estimated memory).

[0110] Periodically monitor the resource usage of the resource group to which the query type belongs, and perform statistics, storage, and analysis;

[0111] The resource usage information includes: the CPU utilization rate of the resource group to which the query type belongs, and the memory usage data of the resource group to which the query type belongs.

[0112] Specifically, resource checks can be performed before a query begins execution by registering a hook function (ExecutorStart_hook). The ExecutorStart_hook retrieves the current resource usage of the resource group to which the query belongs, verifies whether the query's resource requirements are met, and reserves the necessary resource quota. If resources are insufficient, it can decide whether to place the query in a waiting queue, reduce resource allocation, or return an error directly, based on the resource group configuration and query priority. By registering a hook function (ExecutorRun_hook), resource usage can be checked periodically, and intervention measures can be taken when resource usage exceeds expectations, such as pausing execution, reducing parallelism, or adjusting memory allocation. For long-running queries, the execution state can be saved periodically, supporting pauses and later resumption of execution when resources are scarce. After the query is completed, resource release and statistical updates are performed by registering hook functions (ExecutorFinish_hook and ExecutorEnd_hook). These functions record the query's resource usage, including CPU time, peak memory usage, I / O operations, and execution time, update resource group statistics, and release the reserved resource quota.

[0113] Furthermore, resource group statistics can adopt a multi-level statistical table structure, including query-level statistics, session-level statistics, resource group-level statistics, and system-level statistics. Query-level statistics record detailed resource usage for each query; session-level statistics aggregate resource usage for a single session; resource group-level statistics provide an overall view of the resource group; and system-level statistics reflect the resource usage status of the entire database instance. Statistical data can be stored using a time-partitioned table design, supporting efficient storage of large amounts of historical data. Data aggregation and compression algorithms can also be used to periodically aggregate fine-grained data into coarse-grained statistics and compress long-term historical data, balancing storage space and query performance. Through multi-dimensional query and analysis interfaces, resource usage can be analyzed by time, resource group, database, user, and query type. Specific analyses include trend analysis, anomaly detection, correlation analysis, and resource usage prediction, helping administrators gain a deeper understanding of resource usage patterns and optimize resource allocation.

[0114] In this embodiment, the query type is determined based on the query parser; the resource usage of the resource group to which the query type belongs is periodically monitored, and statistics, storage, and analysis are performed; the resource usage includes the CPU utilization rate and memory usage data of the resource group to which the query type belongs; by deeply combining query semantic understanding with resource status tracking, a "perception-decision-control" closed loop is constructed; through parsing hooks and background monitoring processes, fine-grained (tenant / query type level) and full-dimensional (CPU / memory / I / O) data collection is achieved; historical statistics are used to predict resource demand and dynamically optimize execution strategies; and database-level SLA guarantees are achieved without any kernel modifications.

[0115] Optionally, the CPU resource control strategy includes: a CPU resource guarantee mechanism, a CPU resource dynamic adjustment mechanism, and a CPU resource strict limitation mechanism;

[0116] The CPU resource guarantee mechanism is used to ensure that each resource group obtains at least the CPU quota requested by each resource group.

[0117] The CPU quota requested by the resource group can refer to the minimum guaranteed quota of the resource group in a resource contention scenario. Specifically, it can be understood as the baseline CPU capacity preset through the resource group creation interface (e.g., 30%).

[0118] Specifically, the CPU monitoring process periodically checks the CPU utilization of each resource group. When it finds that the utilization of a resource group is lower than its requested value, it identifies other resource groups whose CPU usage exceeds the soft limit, calculates the amount of CPU resources that the resource group needs to give up according to priority and over-utilization ratio, and then adjusts the soft limit values ​​of other resource groups to ensure that resource-deficient groups can obtain sufficient resources.

[0119] The CPU resource dynamic adjustment mechanism is used to automatically adjust the CPU quota of each resource group based on the CPU utilization rate of each resource group.

[0120] Specifically, the total system load is determined based on CPU utilization. When the total system load is high (e.g., exceeding 90%), the soft limits for all resource groups will be reduced proportionally to prioritize the resource needs of high-priority groups. When the total system load is low, the soft limits will gradually increase, approaching the hard limit value, allowing resource groups to fully utilize idle resources. Furthermore, the SoftLimit dynamic adjustment algorithm can use an exponential smoothing approach to avoid system instability caused by frequent fluctuations in the soft limit value.

[0121] The CPU resource strict limit mechanism is used to strictly sort the queries in the resource group queue according to priority and waiting time. When the CPU utilization of the resource group reaches the hard limit threshold, the newly submitted query will be placed in the waiting queue until the CPU utilization drops below the hard limit threshold.

[0122] Among them, the hard limit threshold can refer to 95% of the hard limit.

[0123] Specifically, when a new query arrives, the priority is first determined. If they are the same, the queries in the resource group queue are sorted by waiting time, with the longest waiting time at the top. If not, the queries in the resource group queue are sorted by priority, with higher priority at the top. If the new query has a higher priority, it can be directly queued. For example, the sorting weight of queries in the query queue can be: sorting weight = priority × 1000 + min (waiting time, 300 seconds). If the CPU utilization is greater than or equal to 95% of the hard limit, the newly submitted query is placed in the waiting queue. If the CPU utilization is less than or equal to 85% of the hard limit, the query at the head of the queue is released, and the system will take N queries from the head of the queue for execution. The value of N can be dynamically calculated based on the current idle CPU to avoid over-wake-up and exceeding the limit again. Furthermore, in the query queue, every 5 minutes, the "effective priority" of low-priority queries will be temporarily increased by one level. This ensures that even if there are 1000 high-priority queries ahead, low-priority queries will eventually be executed. This ensures that high-priority queries are executed first, while avoiding long-term starvation of low-priority queries.

[0124] In this embodiment, a three-level CPU limiting mechanism is implemented through a CPU resource control strategy. The optimal balance of resource allocation is achieved through a layered collaborative design. The guarantee mechanism provides an SLA backup for critical business operations, ensuring that request quotas are not preempted. The dynamic adjustment mechanism maximizes resource utilization, allowing over-use under low load and compressing resources according to priority under high load. The strict limiting mechanism mitigates overload risks through intelligent queuing. The queue hierarchical management is triggered by hard limit thresholds. The three mechanisms form a closed-loop control, which prevents low-priority queries from starving and avoids high-priority queries from being blocked, effectively improving query response rates and CPU utilization. Moreover, the entire process requires no manual intervention.

[0125] Optionally, after registering the memory allocation hook function and determining the memory usage data of each resource group in real time, the method further includes:

[0126] Determine whether the memory usage data of each resource group exceeds the memory quota of each resource group;

[0127] Specifically, the target configuration information for each resource group can include the memory quota for each resource group. The memory usage data for each resource group, determined through the memory allocation hook function, is compared with the memory quota for each resource group. Based on the comparison result, it is determined whether the memory usage data for each resource group exceeds the memory quota for each resource group.

[0128] If so, the corresponding second processing strategy is executed according to the memory type of the memory usage data.

[0129] The memory type can include shared buffers, temporary buffers, and working memory. The corresponding second processing strategy can refer to the intervention measures when the memory usage exceeds the memory quota. Different memory types have different second processing strategies for exceeding the limit, such as buffer cleanup, query cancellation, and memory parameter tuning.

[0130] Specifically, for shared buffer overflows, a buffer cleanup mechanism can be employed. This mechanism first identifies the shared buffer pages used by resource groups, then selects cleanup targets based on the least recently used principle, writes dirty pages back to disk, and releases the buffer. Furthermore, the weight of resource groups excessively consuming buffers can be dynamically reduced; the greater the overflow, the more significant the weight reduction, creating negative feedback regulation. This also restricts excessively used resource groups from acquiring new buffer pages. While ensuring global stability, this achieves tenant-level isolation of shared buffer resources, effectively solving the "cache pollution" problem caused by the lack of tenant awareness in traditional PostgreSQL, and significantly improving I / O performance fairness in high-concurrency, multi-tenant scenarios.

[0131] If the temporary buffer exceeds its limit, a temporary operation cancellation mechanism can be used. This mechanism involves identifying the operation consuming the temporary buffer (such as sorting or hash join), selecting a cancellation target based on priority, sending a cancellation signal, and releasing the relevant resources. The cancellation operation will be logged, and the client will be notified of relevant error information (i.e., the limit exceeding information).

[0132] To address the issue of working memory exceeding limits, a dynamic memory adjustment mechanism can be employed. This mechanism can automatically reduce the `work_mem` parameter when working memory is exceeded, employ batch processing strategies for memory-intensive operations, and overflow some data to disk when necessary. Furthermore, the query execution plan can be adjusted to select algorithms with lower memory consumption, such as replacing quicksort with merge sort or hash joins with nested loop joins.

[0133] In this embodiment, it is determined whether the memory usage data of each resource group exceeds the memory quota of each resource group; if so, the corresponding second processing strategy is executed according to the memory type of the memory usage data; by accurately identifying the type of memory exceeding the limit (shared buffer / temporary buffer / working memory), and dynamically matching differentiated processing strategies, the precise loss prevention of resource isolation is achieved with minimal business interruption cost. This avoids the crude operation of "one-size-fits-all" forced termination of queries, and ensures the stable operation of the core functions of the system, significantly improving the intelligence and reliability of memory isolation in a multi-tenant environment.

[0134] Optionally, after periodically monitoring the resource usage of the resource group to which the query type belongs, the method further includes:

[0135] Update the parallelism of the resource group to which the query type belongs based on the CPU utilization of the resource group to which the query type belongs.

[0136] Parallelism can refer to the maximum number of parallel working processes in a resource group.

[0137] Specifically, the parallelism of the query request is first obtained (from the query itself or the target resource group configuration parameters). Then, the current CPU utilization and soft limit of the resource group to which the query request belongs are checked to calculate the available CPU resources and convert them into the number of supportable parallel worker processes. Factors such as query complexity, table size, and resource group configuration are considered simultaneously to determine the final parallelism, ensuring that excessive parallelism does not lead to resource contention. Furthermore, for long-running parallel queries, the parallelism can be dynamically adjusted during execution based on the CPU utilization of the query request. When the CPU utilization of the resource group changes significantly, the number of active parallel worker processes can be increased or decreased to achieve real-time optimization of resource usage. The query type defines the operation category of the query request, while the query request is the specific execution instance under that type.

[0138] Based on the updated parallelism of the resource group to which the query type belongs, each worker process in the resource group to which the query type belongs is executed in parallel.

[0139] Specifically, a dynamic process binding mechanism can be adopted. Through a process mapping table, each resource group maintains an independent pool of worker processes (not globally shared). Each process executes using a hot-adjustment strategy for parallelism, including increasing and decreasing parallelism. Increasing parallelism: when the remaining CPU quota of a resource group > 20%, the process pool is automatically expanded; decreasing parallelism: when the CPU usage of a resource group > 85%, excess processes are marked as draining. Marked processes do not interrupt their current task but no longer accept new tasks, and automatically hibernate after completing their task.

[0140] In this embodiment, the parallelism of the resource group to which the query type belongs is updated based on the CPU utilization rate of the resource group to which the query type belongs; based on the updated parallelism of the resource group to which the query type belongs, each working process in the resource group to which the query type belongs is executed in parallel; by deeply binding the parallelism control with the real-time resource status, the current CPU load of the resource group (such as the amount of idle resources or the state of over-utilization) is perceived in real time, and the parallelism is dynamically increased or decreased; by continuously monitoring load changes and adjusting the number of parallel processes in real time, performance degradation caused by the failure of the initial plan is avoided, and a balance between resource utilization and query performance is achieved with lossless compatibility (achieved through PostgreSQL hooks).

[0141] Example 3:

[0142] Figure 2 This is a framework diagram of a system for managing database resources according to Embodiment 3 of the present invention. Figure 2 As shown, the system 200 includes a resource group management module 210, a CPU resource management control module 220, a memory resource management control module 230, and a query execution resource control module 240. The system is used to execute the database resource management method described in any embodiment of the present invention, specifically including:

[0143] The resource group management module 210 is used to create at least one resource group in the database, bind each resource group based on the resource group binding relationship table, and monitor whether the status of each resource group changes. When the status of each resource group changes, the corresponding resource adjustment strategy is triggered.

[0144] For example, Figure 3 This is a flowchart of resource group management provided in Embodiment 3 of the present invention, as follows: Figure 3 As shown, the specific steps for resource group management performed by the resource group management module 210 are as follows:

[0145] 1. Begin.

[0146] 2. Initialize the resource group system.

[0147] 3. Create a resource group.

[0148] 4. Configure CPU and memory resource parameters.

[0149] CPU parameters: hard limit (limit), request value (request), soft limit (soft_limit);

[0150] Memory parameters: limit value, requested value, and type quota (shared / temporary / working memory).

[0151] 5. Bind resource groups based on the resource group binding relationship table.

[0152] Choose a binding type (select one of three):

[0153] Database binding, which is to associate with a specific tenant (Database);

[0154] Role binding, which is to associate a user with a role.

[0155] Application binding is the process of associating an application with a client application.

[0156] 6. Determine the status of the resource group

[0157] Traffic is routed based on the current status:

[0158] Suspend: Prevents new queries and waits for existing operations to complete;

[0159] ACTIVE: Enables resource allocation and allows query execution;

[0160] Maintenance in progress: Temporarily freezing configuration modifications or resource adjustments;

[0161] Activate the resource group (only if status = "Active").

[0162] 7. Resource group monitoring and statistics (i.e., monitoring whether the status of each resource group has changed; when the status of each resource group changes, the corresponding resource adjustment strategy is triggered, and the change information is recorded).

[0163] 8. End.

[0164] CPU resource management and control module 220 is used to register a CPU monitoring process. The process periodically collects the CPU utilization rate of each resource group, and based on the CPU utilization rate of each resource group and the pre-set CPU resource control policy, periodically updates the CPU quota in the target configuration information of each resource group.

[0165] For example, Figure 4 This is a CPU resource management control flowchart provided in Embodiment 3 of the present invention, as follows: Figure 4 As shown, the specific steps of CPU resource management and control module 220 in performing CPU resource management and control are as follows:

[0166] 1. Begin.

[0167] 2. Obtain CPU usage information.

[0168] Real-time collection of CPU data for resource groups: current utilization (user mode + kernel mode), historical trends (e.g., average within 1 minute), and process-level consumption (associated with parallel worker processes).

[0169] 3. Determine if the CPU_limit (hard CPU limit) has been exceeded.

[0170] If so, the speed limiting policy is triggered, and proceed to step 4;

[0171] If not, skip to step 5.

[0172] 4. Implement CPU speed limiting policy

[0173] Strict interception:

[0174] New queries are added to the waiting queue (sorted by priority);

[0175] During runtime, query the degraded parallelism or pause execution.

[0176] 5. Determine if the value is lower than cpu_request (CPU request value).

[0177] If so, it means that the resource group has not used up the basic quota, proceed to step 6;

[0178] If not, then only update the statistics and skip to step 7.

[0179] 6. Adjust the cpu_soft_limit for other groups (dynamic reallocation)

[0180] Resource allocation mechanism:

[0181] Increase the soft limit of low-priority groups to allow them to borrow free resources;

[0182] Ensure that high-priority groups always meet the request value (SLA guarantee).

[0183] 7. Update CPU usage statistics

[0184] Record real-time / historical data (peak, average, queue time, etc.);

[0185] Persist to the statistics table (driving subsequent dynamic adjustment decisions).

[0186] 8. End.

[0187] The memory resource management and control module 230 is used to register memory allocation hook functions, determine the memory usage data of each resource group in real time, and determine whether the memory usage data of each resource group is outside the preset memory range. If so, an early warning is triggered and the corresponding first processing strategy is executed.

[0188] For example, Figure 5 This is a flowchart of a memory resource management control system provided in Embodiment 3 of the present invention, as follows: Figure 5 As shown, the specific steps of memory resource management and control module 230 in performing memory resource management and control are as follows:

[0189] 1. Begin.

[0190] 2. Obtain memory usage information

[0191] Real-time collection of categorized memory data from resource groups:

[0192] Shared buffers;

[0193] Working memory (Work Mem);

[0194] Temporary buffers;

[0195] 3. Check if the memory_limit (hard memory limit) has been exceeded.

[0196] If so, proceed to the over-limit handling branch;

[0197] If not, skip to step 6.

[0198] 4. Memory over-limit handling

[0199] Implement a tiered recycling strategy:

[0200] Determine the memory type and perform targeted cleanup:

[0201] If it is a shared buffer, then LRU page eviction is triggered (dirty pages are released first).

[0202] If it is another value (such as Work Mem), the work_mem parameter will be dynamically reduced, and the overflow data will be pushed to disk.

[0203] Adjusting the memory configuration of other groups will reduce the quota of lower priority resource groups according to their priority (e.g., borrowing memory to over-limit groups).

[0204] 5. Check if the value is lower than memory_request (memory request value).

[0205] If so, a resource replenishment mechanism is triggered (such as allocating additional memory from the free pool).

[0206] If not, continue with routine monitoring.

[0207] 6. Update memory usage statistics

[0208] Record real-time / peak memory usage;

[0209] Persist to a statistics table (supports historical analysis and capacity planning).

[0210] 7. End.

[0211] The query execution resource control module 240 is used to determine the query type based on the query parser, periodically monitor the resource usage of the resource group to which the query type belongs, and perform statistics, storage and analysis. The resource usage includes the CPU utilization rate and memory usage data of the resource group to which the query type belongs.

[0212] For example, Figure 6 This is a flowchart of a query execution resource control method provided in Embodiment 3 of the present invention, as follows: Figure 6 As shown, the specific steps of query execution resource control performed by the query execution resource control module 240 are as follows:

[0213] 1. Begin.

[0214] 2. Receive SQL queries and obtain the SQL statements submitted by the client.

[0215] 3. Parse the SQL statement, using a query parser to analyze the syntax and generate a query tree.

[0216] 4. Determine the query type and categorize the query types:

[0217] DML queries (Data Manipulation Language: INSERT / UPDATE / DELETE);

[0218] Complex queries (including complex operations such as aggregation, join, and subqueries);

[0219] Other queries (such as DDL or simple SELECT).

[0220] 5. Check resource group configuration

[0221] Determine the resource group to which the query belongs based on the binding relationship (database / role / application) and obtain its CPU / memory quota, parallelism policy and other configurations.

[0222] 6. Determine whether parallel execution is required.

[0223] Evaluation only for complexity queries:

[0224] If not, then execute sequentially;

[0225] If so, then proceed with the parallel execution process.

[0226] 7. Configure the parallelism based on the resource group configuration.

[0227] The optimal number of parallel worker processes is dynamically calculated based on the CPU utilization, memory availability, and query complexity of the resource group.

[0228] 8. Apply resource control strategies

[0229] Implement resource group restriction rules:

[0230] CPU limiting: hard limit interception, soft limit dynamic adjustment;

[0231] Memory limit: Quota is pre-allocated by type (working memory / temporary buffer).

[0232] 9. Determine if resources are sufficient.

[0233] Real-time check of available resources in the resource group:

[0234] If not, the query will be placed in the waiting queue (sorted by priority).

[0235] If so, allocate resources and execute the query.

[0236] 10. Execute the query, running the query (serial or parallel) under resource constraints.

[0237] 11. Resource usage monitoring

[0238] Periodic data collection:

[0239] CPU utilization (consumption of each working process).

[0240] Peak memory usage (shared buffer / working memory, etc.).

[0241] 12. Update resource usage statistics

[0242] Record the resource consumption for this query;

[0243] Persist to multi-level statistics tables (query level / resource group level / system level).

[0244] 13. End.

[0245] Optionally, the CPU resource control strategy includes: a CPU resource guarantee mechanism, a CPU resource dynamic adjustment mechanism, and a CPU resource strict limitation mechanism;

[0246] The CPU resource guarantee mechanism is used to ensure that each resource group obtains at least the CPU quota requested by each resource group.

[0247] The CPU resource dynamic adjustment mechanism is used to automatically adjust the CPU quota of each resource group based on the CPU utilization rate of each resource group.

[0248] The CPU resource strict limit mechanism is used to strictly sort the queries in the resource group queue according to priority and waiting time. When the CPU utilization of the resource group reaches the hard limit threshold, the newly submitted query will be placed in the waiting queue until the CPU utilization drops below the hard limit threshold.

[0249] Optional, the memory resource management and control module 230 is specifically used for:

[0250] Determine whether the memory usage data of each resource group exceeds the memory quota of each resource group;

[0251] If so, then the corresponding second processing strategy is executed according to the memory type of the memory usage data.

[0252] Optionally, query the execution resource control module 240, specifically for:

[0253] Update the parallelism of the resource group to which the query type belongs based on the CPU utilization of the resource group to which the query type belongs.

[0254] Based on the updated parallelism of the resource group to which the query type belongs, each worker process in the resource group to which the query type belongs is executed in parallel.

[0255] This invention provides a system for database resource management. Through a resource group management module, it supports multi-dimensional binding by tenant (Database), role (Role), and application (Application), automatically triggering resource reclamation or allocation upon state changes, enabling flexible construction of tenant-level resource pools. A CPU resource management control module collects data in real-time based on monitoring processes and dynamically allocates quotas through a three-level strategy (Request / Soft Limit / Hard Limit) to ensure SLAs for high-priority tenants. A memory resource management control module uses hook functions to capture real-time memory allocation and triggers tiered processing (such as shared buffer cleanup or work_mem degradation) based on a watermark mechanism (low / high watermark thresholds) to avoid cascading failures. A query execution resource control module identifies query types (such as complex aggregations), associates them with real-time resource group load (CPU / memory), and dynamically adjusts parallelism or execution strategies to eliminate resource conflicts before execution. Furthermore, the functions of these modules can be implemented through PostgreSQL extension mechanisms (hook functions / background worker processes) without modifying the database kernel, ensuring compatibility and upgrade security.

[0256] In one specific embodiment, the system for managing database resources can use PostgreSQL's extension mechanism to encapsulate all module functions, enabling modular design and easy deployment.

[0257] Specifically, the extension implementation conforms to the PostgreSQL extension specification and includes a control file, SQL scripts, and shared libraries. The control file defines extension metadata, version information, and dependencies; the SQL scripts create the necessary database objects; and the shared libraries provide core functionality implemented in C. The system extends PostgreSQL's standard behavior by registering various hook functions. Key hooks include the executor hook (controlling query execution), memory management hook (monitoring memory usage), query planning hook (optimizing the execution plan), and process startup hook (registering background worker processes). Furthermore, the extension supports standard installation, update, and uninstallation operations, provides version compatibility checks and upgrade paths, and ensures a smooth transition when upgrading PostgreSQL versions.

[0258] Furthermore, the system supports multi-level configuration and dynamic updates. The system configuration adopts a hierarchical structure, including global configuration, resource group-level configuration, and session-level configuration. Lower-level configurations can override higher-level configurations, enabling fine-grained control. Configuration items are stored in system tables and can be queried and modified via an SQL interface. In addition, the system implements a hot-loading mechanism for configuration, allowing most configuration items to be dynamically updated at runtime without restarting the database. Configuration updates are notified to all relevant processes via shared memory and signal mechanisms, ensuring timely application of configuration changes. Configuration management includes comprehensive validation and conflict detection functions, verifying parameter validity and detecting potential conflicts before configuration updates to ensure system consistency and stability. The system also provides a configuration history, supporting viewing configuration change history and rolling back to previous configurations.

[0259] Furthermore, the system's operational status is visible in real time, enabling comprehensive monitoring and alerting functions. The system collects various metrics through monitoring, including resource utilization, query performance, wait events, and system status. These metrics are updated in real time via a shared memory area and periodically persisted to a monitoring table. The system also provides multiple viewing methods, including system views, management functions, and external interfaces. The system's alerting mechanism triggers alerts based on preset thresholds and rules when abnormal conditions occur. Alert levels include informative, warning, error, and critical error, with different handling strategies for different alert levels. Additionally, alerts can be sent through various channels, including database logs, email, message queues, and external monitoring integration. The system also implements a health check function, periodically verifying the operational status of each component, detecting potential problems, and providing early warnings. Health checks include resource group status, configuration consistency, data integrity, and system performance, ensuring long-term stable system operation.

[0260] Furthermore, the system supports long-term evolution and maintenance, implementing a comprehensive upgrade and maintenance mechanism. The upgrade mechanism supports online upgrades, minimizing service interruptions. It also implements version compatibility checks, verifying the compatibility of the new version with the current environment before upgrading. The upgrade process includes data structure migration and configuration conversion, ensuring that existing data and settings function correctly in the new version. The system also provides complete backup and recovery capabilities, supporting the backup and recovery of resource group configurations, statistics, and system settings. Backups can be performed on demand or scheduled for automatic periodic backups to prevent accidental configuration loss or corruption. The maintenance toolset includes resource group diagnostic tools, performance analysis tools, and configuration optimization assistants. Diagnostic tools help identify resource group configuration problems and abnormal resource usage; performance analysis tools provide in-depth analysis of resource usage; and the configuration optimization assistant recommends optimal configuration parameters based on system load characteristics.

[0261] In this embodiment, a complete PostgreSQL database resource isolation and management system is constructed, which realizes precise control of database-level resources and is particularly suitable for resource management needs in SaaS multi-tenant environments. Moreover, the system adopts a flat resource group structure design and realizes differentiated allocation of resources through a priority mechanism. It does not require modification of the PostgreSQL kernel and has good compatibility and scalability.

[0262] Example 4:

[0263] Figure 7 A schematic diagram of an electronic device that can be used to implement embodiments of the present invention is shown. The electronic device 10 is intended to represent various forms of digital computers, such as laptop computers, desktop computers, workstations, personal digital assistants, servers, blade servers, mainframe computers, and other suitable computers. The electronic device may also represent various forms of mobile devices, such as personal digital processors, cellular phones, smartphones, wearable devices (e.g., helmets, glasses, watches, etc.), and other similar computing devices. The components shown herein, their connections and relationships, and their functions are merely illustrative and are not intended to limit the implementation of the invention described and / or claimed herein.

[0264] like Figure 7 As shown, the electronic device 10 includes at least one processor 11 and a memory, such as a read-only memory (ROM) 12, a random access memory (RAM) 13, etc., which is communicatively connected to the at least one processor 11. The memory stores a computer program that can be executed by the at least one processor 11, and the computer program is executed by the at least one processor 11 to enable the at least one processor 11 to perform the method provided by the present invention.

[0265] The processor 11 can perform various appropriate actions and processes based on a computer program stored in the read-only memory (ROM) 12 or a computer program loaded from the storage unit 18 into the random access memory (RAM) 13. The RAM 13 can also store various programs and data required for the operation of the electronic device 10. The processor 11, ROM 12, and RAM 13 are interconnected via a bus 14. An input / output (I / O) interface 15 is also connected to the bus 14.

[0266] Multiple components in electronic device 10 are connected to I / O interface 15, including: input unit 16, such as keyboard, mouse, etc.; output unit 17, such as various types of displays, speakers, etc.; storage unit 18, such as disk, optical disk, etc.; and communication unit 19, such as network card, modem, wireless transceiver, etc. Communication unit 19 allows electronic device 10 to exchange information / data with other devices through computer networks such as the Internet and / or various telecommunications networks.

[0267] Processor 11 can be a variety of general-purpose and / or special-purpose processing components with processing and computing capabilities. Some examples of processor 11 include, but are not limited to, a central processing unit (CPU), a graphics processing unit (GPU), various special-purpose artificial intelligence (AI) computing chips, various processors running machine learning model algorithms, digital signal processors (DSPs), and any suitable processor, controller, microcontroller, etc. Processor 11 performs the various methods and processes described above, such as methods for resource management of a database.

[0268] In some embodiments, the method for managing database resources can be implemented as a computer program tangibly contained in a computer-readable storage medium, such as storage unit 18. In some embodiments, part or all of the computer program can be loaded and / or installed on electronic device 10 via ROM 12 and / or communication unit 19. When the computer program is loaded into RAM 13 and executed by processor 11, one or more steps of the method for managing database resources described above can be performed. Alternatively, in other embodiments, processor 11 can be configured to perform the method for managing database resources by any other suitable means (e.g., by means of firmware).

[0269] Various embodiments of the systems and techniques described above herein can be implemented in digital electronic circuit systems, integrated circuit systems, field programmable gate arrays (FPGAs), application-specific integrated circuits (ASICs), application-specific standard parts (ASSPs), systems-on-chip (SoCs), complex programmable logic devices (CPLDs), computer hardware, firmware, software, and / or combinations thereof. These various embodiments may include implementations in one or more computer programs that can be executed and / or interpreted on a programmable system including at least one programmable processor, which may be a dedicated or general-purpose programmable processor, capable of receiving data and instructions from a storage system, at least one input device, and at least one output device, and transmitting data and instructions to the storage system, the at least one input device, and the at least one output device.

[0270] Computer programs used to implement the methods of the present invention may be written in any combination of one or more programming languages. These computer programs may be provided to a processor of a general-purpose computer, a special-purpose computer, or other programmable data processing device, such that when executed by the processor, the computer programs cause the functions / operations specified in the flowcharts and / or block diagrams to be performed. The computer programs may be executed entirely on a machine, partially on a machine, or as a standalone software package, partially on a machine and partially on a remote machine, or entirely on a remote machine or server.

[0271] In the context of this invention, a computer-readable storage medium stores computer instructions that, when executed by a processor, implement the method for resource management of a database provided by this invention. The computer-readable storage medium may be a tangible medium that may contain or store a computer program for use by or in conjunction with an instruction execution system, apparatus, or device. The computer-readable storage medium may include, but is not limited to, electronic, magnetic, optical, electromagnetic, infrared, or semiconductor systems, apparatus, or devices, or any suitable combination of the foregoing. Alternatively, the computer-readable storage medium may be a machine-readable signal medium. More specific examples of machine-readable storage media include electrical connections based on one or more wires, portable computer disks, hard disks, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fibers, portable compact disk read-only memory (CD-ROM), optical storage devices, magnetic storage devices, or any suitable combination of the foregoing.

[0272] To provide interaction with a user, the systems and techniques described herein can be implemented on an electronic device having: a display device (e.g., a cathode ray tube (CRT) or a liquid crystal display (LCD)) for displaying information to the user; and a keyboard and pointing device (e.g., a mouse or trackball) through which the user provides input to the electronic device. Other types of devices can also be used to provide interaction with the user; for example, feedback provided to the user can be any form of sensory feedback (e.g., visual feedback, auditory feedback, or tactile feedback); and input from the user can be received in any form (including sound input, voice input, or tactile input).

[0273] The systems and technologies described herein can be implemented in computing systems that include backend components (e.g., as data servers), or middleware components (e.g., application servers), or frontend components (e.g., user computers with graphical user interfaces or web browsers through which users can interact with implementations of the systems and technologies described herein), or any combination of such backend, middleware, or frontend components. The components of the system can be interconnected via digital data communication of any form or medium (e.g., communication networks). Examples of communication networks include local area networks (LANs), wide area networks (WANs), blockchain networks, and the Internet.

[0274] A computing system can include clients and servers. Clients and servers are generally geographically separated and typically interact via communication networks. The client-server relationship is established by computer programs running on the respective computers and having a client-server relationship with each other. The server can be a cloud server, also known as a cloud computing server or cloud host, which is a hosting product within the cloud computing service system. It addresses the shortcomings of traditional physical hosts and Virtual Private Server (VPS) services, such as high management difficulty and weak business scalability.

[0275] It should be understood that the various forms of processes shown above can be used, with steps reordered, added, or deleted. For example, the steps described in this invention can be executed in parallel, sequentially, or in different orders, as long as the desired result of the technical solution of this invention can be achieved, and this is not limited herein.

[0276] The specific embodiments described above do not constitute a limitation on the scope of protection of this invention. Those skilled in the art should understand that various modifications, combinations, sub-combinations, and substitutions can be made according to design requirements and other factors. Any modifications, equivalent substitutions, and improvements made within the spirit and principles of this invention should be included within the scope of protection of this invention.

Claims

1. A method for resource management of a database, characterized in that, include: Create at least one resource group in the database and define the configuration information for each resource group, wherein the configuration information includes: resource group ID, status, priority, CPU quota, memory quota, and parallelism. Based on the resource group binding relationship table, each resource group is bound and the binding type of each resource group is determined; Based on the binding type of each resource group, the configuration information of each resource group is updated through the resource group operation interface to obtain the target configuration information of each resource group; Based on the target configuration information of each resource group and the status of each resource group, perform the corresponding query operation; Monitor whether the status of each resource group has changed. When the status of each resource group changes, trigger the corresponding resource adjustment strategy and record the change information. After obtaining the target configuration information for each of the resource groups, the method further includes: Register a CPU monitoring process, which periodically collects the CPU utilization of each resource group; Based on the CPU utilization rate of each resource group and the pre-set CPU resource control policy, the CPU quota in the target configuration information of each resource group is periodically updated. After obtaining the target configuration information for each of the resource groups, the method further includes: Register a memory allocation hook function to determine the memory usage data of each resource group in real time; Determine whether the memory usage data of each resource group is outside the preset memory range; If so, an alert will be triggered and the corresponding first processing strategy will be executed; After obtaining the target configuration information for each of the resource groups, the method further includes: Based on the query parser, determine the query type; Periodically monitor the resource usage of the resource group to which the query type belongs, and perform statistics, storage, and analysis; The resource usage information includes: the CPU utilization rate of the resource group to which the query type belongs, and the memory usage data of the resource group to which the query type belongs.

2. The method for resource management of a database according to claim 1, characterized in that, The CPU resource control strategy includes: a CPU resource guarantee mechanism, a CPU resource dynamic adjustment mechanism, and a CPU resource strict limitation mechanism; The CPU resource guarantee mechanism is used to ensure that each resource group obtains at least the CPU quota requested by each resource group. The CPU resource dynamic adjustment mechanism is used to automatically adjust the CPU quota of each resource group based on the CPU utilization rate of each resource group. The CPU resource strict limit mechanism is used to strictly sort the queries in the resource group queue according to priority and waiting time. When the CPU utilization of the resource group reaches the hard limit threshold, the newly submitted query will be placed in the waiting queue until the CPU utilization drops below the hard limit threshold.

3. The method for resource management of a database according to claim 1, characterized in that, After the registered memory allocation hook function determines the memory usage data of each resource group in real time, the following is also included: Determine whether the memory usage data of each resource group exceeds the memory quota of each resource group; If so, the corresponding second processing strategy is executed according to the memory type of the memory usage data.

4. The method for resource management of a database according to claim 1, characterized in that, After periodically monitoring the resource usage of the resource group to which the query type belongs, the method further includes: Update the parallelism of the resource group to which the query type belongs based on the CPU utilization of the resource group to which the query type belongs. Based on the updated parallelism of the resource group to which the query type belongs, each worker process in the resource group to which the query type belongs is executed in parallel.

5. A system for resource management of a database, characterized in that, The system is used to perform the method for resource management of a database as described in any one of claims 1-4, comprising: The resource group management module is used to create at least one resource group in the database, bind each resource group based on the binding relationship table of each resource group, and monitor whether the status of each resource group has changed. When the status of each resource group changes, the corresponding resource adjustment strategy is triggered. The CPU resource management and control module is used to register a CPU monitoring process. The process periodically collects the CPU utilization rate of each resource group, and based on the CPU utilization rate of each resource group and the pre-set CPU resource control policy, periodically updates the CPU quota in the target configuration information of each resource group. The memory resource management and control module is used to register memory allocation hook functions, determine the memory usage data of each resource group in real time, and determine whether the memory usage data of each resource group is outside the preset memory range. If so, an alert is triggered and the corresponding first processing strategy is executed. The query execution resource control module is used to determine the query type based on the query parser, periodically monitor the resource usage of the resource group to which the query type belongs, and perform statistics, storage and analysis. The resource usage includes the CPU utilization rate and memory usage data of the resource group to which the query type belongs.

6. An electronic device, characterized in that, include: At least one processor; as well as A memory communicatively connected to the at least one processor; wherein, The memory stores a computer program that can be executed by the at least one processor, the computer program being executed by the at least one processor to enable the at least one processor to perform the method for resource management of the database as described in any one of claims 1-4.

7. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores computer instructions that, when executed by a processor, implement the method for resource management of a database as described in any one of claims 1-4.

Images