A resource consumption attribution method, device and medium of a relational database

Abstract

The application discloses a resource consumption attribution method and device of a relational database and a medium, relates to the technical field of database resource monitoring, and comprises the following steps: generating an SQL instance identifier of a current execution of a target SQL statement, and establishing an execution mapping relationship with a backend execution process; capturing kernel resource consumption events in real time, and accumulating resource consumption amounts corresponding to the kernel resource consumption events into accumulated resource consumption values; if the execution duration of the target SQL statement is less than a preset duration threshold, updating the survival duration of the execution mapping relationship based on a preset sliding time window; continuously capturing kernel resource consumption events of the backend execution process, and continuously accumulating the accumulated resource consumption values; deleting the execution mapping relationship from an event mapping buffer, and generating a resource attribution record of the current execution of the target SQL statement based on the accumulated resource consumption values. The CPU and IO resource accurate attribution of a single SQL execution instance level is realized, and the accuracy and real-time performance of database resource monitoring are improved.

Description

Technical Field

[0001] This application relates to the field of database resource monitoring technology, specifically to a method, device, and medium for attributing resource consumption in relational databases. Background Technology

[0002] Relational databases serve as the core data foundation for various information systems, handling complex high-concurrency query loads. PostgreSQL, with its open-source nature, powerful functional extensibility, and concurrency processing capabilities, is widely deployed across various fields. As business systems evolve towards high-concurrency, microservice architectures, database operating scenarios become increasingly complex, with database CPU and disk I / O resources being consumed by numerous Structured Query Language (SQL) execution instances. Therefore, accurately tracking the resource consumption of each SQL execution instance, identifying abnormal SQL queries, and pinpointing the causes of database performance bottlenecks are core operational requirements for ensuring stable and efficient database operation.

[0003] Currently, monitoring and attribution solutions for database resource consumption mainly fall into two categories. One category is statistical solutions based on built-in database extensions and system views, such as pg_stat_statements and pg_stat_monitor. These solutions collect cumulative metrics of SQL execution through internal database hooks, periodically query views, and perform differential calculations to obtain estimated resource consumption values ​​within a time window. The other category is sampling and analysis solutions based on kernel performance tools such as perf. These solutions obtain resource consumption data by sampling kernel-level events of database processes, and then manually correlate the corresponding SQL statements with database process views.

[0004] However, the first type of solution is limited by the mechanisms of aggregation statistics and periodic differencing, making it difficult to cover individual execution instances with statistical granularity. Furthermore, because the sampling period is much longer than the execution duration of short SQL statements, the resource consumption of short SQL statements is easily diluted or even lost in cumulative statistics. Additionally, the statistical results include waiting time, making it difficult to reflect the true CPU and I / O resource usage. The second type of solution can only achieve process or thread resource statistics. In high-concurrency scenarios, situations such as multiple SQL statements executed by the same process in a short period, parallel worker threads sharing resources, and connection pools reusing processes make it difficult to accurately match kernel events with individual SQL execution instances, leading to chaotic resource attribution. Therefore, how to attribute resource consumption in high-concurrency scenarios to specific SQL execution instances without modifying the database kernel or significantly increasing the runtime load is a pressing technical problem in the field of relational database performance monitoring. Summary of the Invention

[0005] To address the aforementioned issues, this application proposes a resource consumption attribution method for relational databases, comprising: Capture the context information when the target SQL statement starts execution, generate the SQL instance identifier for the current execution, determine the backend execution process of the target SQL statement, establish the execution mapping relationship between the SQL instance identifier and the backend execution process, and store it in the kernel-mode event mapping buffer; During the execution of the target SQL statement, the kernel resource consumption events of the backend execution process are captured in real time through a pre-registered event capture probe. The resource consumption corresponding to each kernel resource consumption event is accumulated into the cumulative resource consumption value corresponding to the SQL instance identifier in the event mapping buffer. After the target SQL statement is executed, the execution time of the target SQL statement is compared with a preset time threshold. If the execution time is less than the preset time threshold, the lifespan of the execution mapping relationship in the event mapping buffer is updated based on a preset sliding time window. Within the preset sliding time window, kernel resource consumption events of the backend execution process are continuously captured, and the corresponding resource consumption is continuously added to the cumulative resource consumption value corresponding to the SQL instance identifier. When the preset sliding time window expires or the execution time is not less than the preset time threshold, the execution mapping relationship is deleted from the event mapping buffer, and a resource attribution record for the current execution of the target SQL statement is generated based on the cumulative resource consumption value corresponding to the SQL instance identifier in the event mapping buffer.

[0006] On the other hand, this application also proposes a resource consumption attribution device for relational databases, comprising: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, which enables the at least one processor to perform a resource consumption attribution method for a relational database as described in the above example.

[0007] On the other hand, this application also proposes a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as: a resource consumption attribution method for a relational database as described in the above example.

[0008] The resource consumption attribution method for relational databases proposed in this application can bring the following benefits: By generating an independent SQL instance identifier for each execution of the target SQL statement and establishing a mapping relationship between the SQL instance identifier and the backend execution process in kernel mode, the kernel resource consumption events captured subsequently are attributed to the corresponding SQL instance identifier in real time through process identifier and thread identifier lookup. This achieves accurate attribution of CPU and IO resource consumption at the single SQL execution instance level, solving the problem that existing solutions can only attribute to the level of normalized SQL aggregation or process with insufficient granularity. It effectively supports the fine-grained operation and maintenance needs of resource cost allocation and performance tuning by SQL instance.

[0009] A sliding window mechanism was designed for short SQL scenarios. After the SQL execution is completed, the lifespan of the mapping relationship is dynamically updated based on the comparison between the execution time and the preset time threshold. The mapping relationship is retained within the window period to accommodate late kernel resource consumption events. An expiration reverse deduction mechanism ensures that the cumulative resource consumption value always reflects the actual consumption within the window. This eliminates the problem of resource statistics loss caused by excessively long sampling periods or kernel event scheduling delays in millisecond-level short SQL. Moreover, the above mechanism is implemented through kernel-mode event driving, without modifying the database source code, and has minimal impact on the database's own operating load. It is suitable for long-term deployment in high-concurrency production environments. Attached Figure Description

[0010] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings: Figure 1 This is a flowchart illustrating a resource consumption attribution method for a relational database according to an embodiment of this application. Figure 2 This is a schematic diagram of a resource consumption attribution device for a relational database in an embodiment of this application. Detailed Implementation

[0011] To make the objectives, technical solutions, and advantages of this application clearer, the technical solutions of this application will be clearly and completely described below in conjunction with specific embodiments and corresponding drawings. Obviously, the described embodiments are only a part of the embodiments of this application, and not all of them. Based on the embodiments in this application, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this application.

[0012] The technical solutions provided by the various embodiments of this application are described in detail below with reference to the accompanying drawings.

[0013] like Figure 1 As shown in the figure, this application provides a resource consumption attribution method for relational databases, including: S101. Capture the context information when the target SQL statement starts execution, generate the SQL instance identifier for the current execution, determine the backend execution process of the target SQL statement, establish the execution mapping relationship between the SQL instance identifier and the backend execution process, and store it in the kernel-mode event mapping buffer.

[0014] Specifically, a user-space dynamic probe (uprobe) pre-attached to the query execution entry function of the relational database is triggered when the target SQL statement begins execution, capturing the execution context information of the SQL statement. The execution context information includes the query text of the target SQL statement, the process ID (PID) and thread ID (TID) of the backend execution process executing the SQL statement, and the start time of the SQL statement's execution. Based on the captured query text, it is normalized, and a hash operation is performed on the normalized query text, the process ID and thread ID of the current backend execution process, and the current timestamp to generate the SQL instance identifier (QueryID) for the current execution of the target SQL statement.

[0015] While generating the SQL instance identifier, a key-value execution mapping relationship is constructed using the combination of the process identifier and thread identifier of the backend execution process as the key, and the SQL instance identifier, the start time of the target SQL statement, and the end time of execution as the value. The event mapping buffer is implemented using a hash map table (BPF_MAP_TYPE_HASH) of the kernel-mode extended Berkeley Packet Filter (eBPF). Key-value pairs are written to this hash map table by calling the atomic update operation (bpf_map_update_elem). This execution mapping relationship remains valid throughout the execution of the target SQL statement, allowing subsequently captured kernel resource consumption events to look up the mapping relationship using the process identifier and thread identifier to obtain the corresponding SQL instance identifier.

[0016] In a preferred embodiment, the user-space dynamic probe is attached to the ExecutorRun or PortalRun function, the query execution entry point of the PostgreSQL database. When the target SQL statement enters the execution phase, the probe is triggered. It reads the query descriptor (QueryDesc) or executor status structure generated in the PostgreSQL kernel to obtain the query text of the current SQL statement and its normalized queryid field. If the pg_stat_statements extension is enabled, pg_stat_statements.queryid is directly reused as the SQL instance identifier; if not enabled, the probe program generates the queryid by performing a hash calculation based on the normalized query text. The probe program is deployed using a compile-once, run-anywhere (CO-RE) compilation method, and the probe attachment is completed through a dynamic binding mechanism without modifying the relational database source code or restarting the database service process.

[0017] In one alternative implementation, for PostgreSQL database instances where the pg_stat_statements extension is not enabled, or for other relational databases such as MySQL, the captured SQL query text can be normalized by a user-space agent, and a stable hash algorithm such as xxHash, MurmurHash, or SipHash can be used to generate an SQL instance identifier. The generated SQL instance identifier is then passed to the kernel-space event mapping buffer through the eBPF ring buffer or hash map table, thereby achieving the association storage of the SQL instance identifier with the process identifier and thread identifier of the backend execution process.

[0018] S102. During the execution of the target SQL statement, the kernel resource consumption events of the backend execution process are captured in real time through the pre-registered event capture probe, and the resource consumption corresponding to each kernel resource consumption event is accumulated into the cumulative resource consumption value corresponding to the SQL instance identifier in the event mapping buffer.

[0019] Specifically, a first probe is registered on the CPU scheduling event path of the operating system kernel. This first probe uses a preset fixed clock cycle as the trigger node to periodically capture the CPU usage events of the backend execution process, record the CPU usage duration of the backend execution process in each clock cycle, and synchronously mark the timestamp, process identifier, and thread identifier of the CPU usage event.

[0020] Simultaneously, a second probe is registered on the block device input / output event path of the operating system kernel. This second probe takes the initiation and completion of block device input / output requests as the trigger nodes, captures the block device read / write events of the backend execution process, records the data transfer volume and request time corresponding to a single input / output request, and synchronously marks the timestamp, process identifier, and thread identifier of the block device read / write event.

[0021] Once any kernel resource consumption event is captured, the process identifier and thread identifier carried by the event are extracted. Using the extracted process identifier and thread identifier as keys, the corresponding SQL instance identifier is searched in the event mapping buffer. After obtaining the SQL instance identifier, the resource consumption corresponding to the kernel resource consumption event is added to the cumulative resource consumption value corresponding to that SQL instance identifier in the event mapping buffer. The cumulative resource consumption value includes the cumulative CPU consumption value and the cumulative block device consumption value, which are used to record the total CPU usage time and total block device data transfer volume attributed to that SQL instance identifier, respectively.

[0022] In a preferred embodiment, the first probe is attached to the Linux kernel's CPU-clock tracepoint or perf_event event path to trigger the capture of CPU usage events of the backend executing process at a fixed clock cycle. When a CPU usage event is captured, the eBPF program extracts the process ID (PID) and thread ID (TID) of the current process from the event context, and reads the number of CPU nanoseconds actually used by the process in the current clock cycle from perf_event. Subsequently, the eBPF program uses the extracted process ID and thread ID as keys to search for a corresponding key-value pair entry in the kernel-mode event mapping buffer (BPF_MAP_TYPE_HASH). If found, the corresponding SQL instance ID is read from the matched key-value pair, and the current CPU usage duration is added to the cumulative CPU consumption value corresponding to that SQL instance ID.

[0023] The accumulation operation of the cumulative consumption value of the central processing unit is represented as follows: , This is the cumulative CPU consumption value attributed to the SQL instance identifier. This represents the CPU time captured in this instance; the resource consumption of block device read / write events is accumulated into the cumulative block device consumption value, and the accumulation operation is represented as follows: , This is the cumulative consumption value of the block device attributed to the SQL instance identifier. This represents the amount of data transmitted in this capture.

[0024] In a preferred embodiment, the second probe is attached to the Linux kernel's `block:block_rq_issue` and `block:block_rq_complete` tracepoints. When a backend process initiates a block device I / O request, the `block_rq_issue` probe is triggered, recording the request's start time, device number, sector number, and data transfer volume. When the I / O request is completed, the `block_rq_complete` probe is triggered, matching the requested sector number and transfer volume against the corresponding request start record to calculate the I / O timeout and extract the process ID and thread ID of the current process. Subsequently, the eBPF program uses the extracted process ID and thread ID as keys to search for the corresponding SQL instance ID in the event mapping buffer, adding the data transfer volume of this block device read / write event to the cumulative block device consumption value corresponding to that SQL instance ID.

[0025] In a preferred embodiment, the cumulative resource consumption value is stored in an event mapping buffer as key-value pairs, where the key is an SQL instance identifier and the value is a structure containing a CPU cumulative consumption value field and a block device cumulative consumption value field. When a CPU occupancy event is attributed to a certain SQL instance identifier, the CPU cumulative consumption value field in the structure is atomically updated; when a block device read / write event is attributed to a certain SQL instance identifier, the block device cumulative consumption value field in the structure is atomically updated.

[0026] It should be noted that the same backend execution process may be scheduled to run on the CPU multiple times during the execution of the target SQL statement, and may also initiate multiple block device I / O requests. The above mechanism ensures that all kernel resource consumption events belonging to the same SQL instance identifier are accumulated into the same cumulative resource consumption value. The accumulation operation is completed in kernel space through atomic operation primitives provided by eBPF, ensuring that no data race occurs when multiple probe programs update the cumulative resource consumption values ​​of different SQL instance identifiers simultaneously in high-concurrency scenarios.

[0027] Within the preset sliding time window, the criteria for determining whether captured kernel resource consumption events belong to the target SQL statement are as follows: ,in This refers to the moment when kernel resource consumption events are captured. The start time of execution of the target SQL statement. This refers to the end execution time of the target SQL statement. To preset the length of the sliding time window, This refers to the current moment.

[0028] In one alternative implementation, for relational databases that support parallel queries, such as PostgreSQL's parallel worker mechanism, multiple worker threads belong to the same process group (tgid), and each worker thread executes different data shards of the same target SQL statement in parallel. The SQL instance identifier generated at the query execution entry point of the leader backend is inherited by all worker threads. Each worker thread triggers its own kernel resource consumption event during execution, and each worker thread corresponds to a different thread identifier (TID). When a kernel resource consumption event of any worker thread is captured, the event mapping buffer is looked up using the process identifier and thread identifier of the event as keys. Matches always return the same SQL instance identifier, thus ensuring that the kernel resources consumed by parallel workers for the same SQL statement are attributed to the same cumulative resource consumption value under the same SQL instance identifier.

[0029] It should be noted that this parallel worker mechanism is also applicable to database connection pool reuse scenarios. After the backend execution process in the connection pool finishes executing the first SQL statement, it may be immediately assigned to execute the second SQL statement. Since this application uses the combination of "process identifier + thread identifier" as the key to look up the mapping relationship, the corresponding mapping relationship may still be within the sliding time window after the first SQL statement is executed. When the second SQL statement starts to execute, a new SQL instance identifier will be generated and a new mapping relationship will be established. The old and new mapping relationships are stored independently in the event mapping buffer and do not affect each other. Therefore, the kernel resource consumption events corresponding to different SQL statements executed by the same backend execution process at different times will not be confused and attributed.

[0030] S103. After the target SQL statement is executed, the execution time of the target SQL statement is compared with a preset time threshold. If the execution time is less than the preset time threshold, the lifespan of the execution mapping relationship in the event mapping buffer is updated based on a preset sliding time window.

[0031] Specifically, when the target SQL statement finishes execution, a user-space dynamic probe captures the SQL termination signal at the query execution exit function, such as PostgreSQL's ExecutorEnd or PortalRun termination path, and records the termination time of the target SQL statement. The termination time is subtracted from the start time to obtain the execution duration of the target SQL statement. This execution duration is then compared to a preset duration threshold.

[0032] If the execution time is less than the preset time threshold, it indicates that the target SQL statement is a short SQL statement. After its execution is completed, it may still receive kernel resource consumption events belonging to the SQL statement within the sliding time window, such as late events caused by kernel scheduling delays or interrupt handling lags. Therefore, the execution mapping relationship in the event mapping buffer is not deleted immediately, but its lifespan is extended.

[0033] In a preferred embodiment, the expiration time of the execution mapping relationship is calculated using the end execution time of the target SQL statement as the starting time and the length of a preset sliding time window as the survival span. Specifically, the end execution time is added to the preset sliding time window length to obtain the expiration time. The calculated expiration time is appended to the storage entry corresponding to the execution mapping relationship in the event mapping buffer as an append field, serving as a survival deadline identifier for the execution mapping relationship and instructing the event mapping buffer to retain the execution mapping relationship before the expiration time.

[0034] Even if the target SQL statement has finished executing before the expiration time arrives, the execution mapping relationship between its corresponding SQL instance identifier and process identifier and thread identifier remains valid in the event mapping buffer. Subsequent kernel resource consumption events can still find the SQL instance identifier through the process identifier and thread identifier, thus achieving continuous attribution after the short SQL execution ends.

[0035] If the execution time is not less than the preset time threshold, it indicates that the target SQL statement is a long SQL statement. The capture of its kernel resource consumption event has been basically completed before its execution ends. There is no need to extend the lifespan of the execution mapping relationship. The execution process jumps to step S105 to directly delete the execution mapping relationship.

[0036] S104. Within the preset sliding time window, continuously capture the kernel resource consumption events of the backend execution process, and continuously accumulate the corresponding resource consumption to the cumulative resource consumption value corresponding to the SQL instance identifier.

[0037] Specifically, after the execution of the target SQL statement ends and the lifespan of the execution mapping relationship is extended to the preset sliding time window length, the execution mapping relationship in the event mapping buffer remains valid.

[0038] During this period, if the backend execution process generates new kernel resource consumption events, the first and second probes will continue to capture these events. Whenever a new kernel resource consumption event is captured, the event mapping buffer is searched using the process identifier and thread identifier carried by the event as keys. If the corresponding execution mapping relationship is matched, the corresponding SQL instance identifier is obtained, and the resource consumption corresponding to the event is added to the cumulative resource consumption value corresponding to the SQL instance identifier.

[0039] In a preferred embodiment, the expiration time of the preset sliding time window is calculated starting from the end execution time of the target SQL statement, and the expiration time is written into the storage entry corresponding to the execution mapping relationship in the event mapping buffer. The execution mapping relationship remains valid until the expiration time arrives; when the system time exceeds the expiration time, the execution mapping relationship becomes invalid. After the expiration time arrives, the user-mode agent process triggers a mapping cleanup process, deleting the execution mapping relationship from the event mapping buffer.

[0040] In a preferred embodiment, within a preset sliding time window, the accumulated resource consumption value is simultaneously subject to an expired event deduction operation. Specifically, when any kernel resource consumption event is added to the accumulated resource consumption value, if the capture time of the event is earlier than the current valid boundary of the sliding time window, it is determined that the event has exceeded the sliding time window, and the resource consumption corresponding to the event should be subtracted from the accumulated resource consumption value.

[0041] Furthermore, the user-mode agent process scans the list of accumulated events corresponding to each SQL instance identifier in the event mapping buffer according to a preset period, obtains the capture time of each accumulated event, and compares each capture time with the current valid boundary of the sliding time window. When the capture time of any accumulated event is earlier than the current valid boundary, it is deducted based on the expired event deduction formula: Subtract the resource consumption corresponding to the event from the cumulative resource consumption value, where This represents the cumulative resource consumption value. This represents the resource consumption amount corresponding to kernel resource consumption events that exceed the window limit.

[0042] It should be noted that the expired event deduction mechanism ensures that the cumulative resource consumption value always includes only the resource consumption corresponding to kernel resource consumption events that fall within the preset sliding time window. This solves the problems of event delays caused by scheduling delays after short SQL execution and confusion between old and new SQL events caused by connection pool reuse, thus ensuring the accuracy of statistical precision.

[0043] Within a preset sliding time window, the cumulative resource consumption value corresponding to the SQL instance identifier is collected according to a preset sampling interval, generating a resource consumption sequence in which the cumulative resource consumption value changes over time; based on a preset smoothing coefficient... The resource consumption sequence is calculated using an exponentially weighted moving average: , , The current cumulative resource consumption value Compared with the baseline value of resource consumption Compare and calculate the current resource consumption deviation value. When the current resource consumption deviation value meets the following conditions: When this occurs, an alarm is triggered. Among them... and These are the baseline and fluctuation threshold corresponding to the cumulative resource consumption value of the block device. Alarms must include at least the SQL instance identifier, query text, current cumulative resource consumption value, and trigger time.

[0044] In a preferred embodiment, when the eBPF program corresponding to the first probe accumulates the CPU usage time to the cumulative resource consumption value, it simultaneously records the capture time of the CPU usage event in the event record list corresponding to the SQL instance identifier in the event mapping buffer in the form of a timestamp; when the eBPF program corresponding to the second probe accumulates the block device data transfer amount to the cumulative resource consumption value, it simultaneously records the capture time of the block device read / write event in the event record list.

[0045] When the expired event deduction operation is triggered, the user-space agent process iterates through the event record list, marks event records whose capture time is earlier than the current valid boundary as expired and removes them from the list, and simultaneously deducts the corresponding resource consumption from the accumulated resource consumption value. Both the accumulation operation and the expired event deduction operation are completed through atomic operations to ensure data consistency in high-concurrency scenarios.

[0046] S105. When the preset sliding time window expires or the execution time is not less than the preset time threshold, the execution mapping relationship is deleted from the event mapping buffer, and a resource attribution record for the current execution of the target SQL statement is generated based on the cumulative resource consumption value corresponding to the SQL instance identifier in the event mapping buffer.

[0047] Specifically, when the execution time of the target SQL statement is not less than the preset duration threshold, it indicates that the target SQL statement is a long SQL statement with a long execution time. Before the execution ends, all kernel resource consumption events have been captured, and there is no need to retain the execution mapping relationship. The mapping entry corresponding to the SQL instance identifier is directly deleted from the event mapping buffer. When the execution time of the target SQL statement is less than the preset duration threshold, the execution mapping relationship is retained within the preset sliding time window.

[0048] During the operation of the sliding time window, the user-mode agent process continuously monitors whether the current system time has exceeded the expiration time. When the system time reaches or exceeds the expiration time, the sliding time window is determined to have expired, and the user-mode agent process triggers the mapping cleanup process, deleting the mapping entry corresponding to the SQL instance identifier from the event mapping buffer.

[0049] In a preferred embodiment, the user-space agent process reads the cumulative resource consumption value corresponding to each SQL instance identifier from the event mapping buffer at a preset period. During reading, the cumulative resource consumption value stored in kernel space is transferred to user space through the eBPF ring buffer or performance buffer perfbuf. The user-space agent process obtains the pre-stored query text of the target SQL statement corresponding to the SQL instance identifier, as well as the start and end times of the target SQL statement. The cumulative resource consumption value, query text, start and end times are correlated to generate structured resource attribution data. The structured resource attribution data is written to a time-series database, such as ClickHouse, SQLite, or InfluxDB, to generate the resource attribution record for the current execution of the target SQL statement.

[0050] It should be noted that the resource attribution record includes at least the SQL instance identifier, query text, cumulative CPU consumption value, cumulative block device consumption value, start execution time, end execution time, and execution duration fields. Each record corresponds to a single execution instance of a SQL statement and supports retrieval and sorting by SQL instance identifier, time range, or resource consumption value.

[0051] In a preferred embodiment, the user-space agent process also provides a query interface for receiving query requests from operations personnel or external systems for a target time range. The query request carries an SQL instance identifier or query text keywords. The user-space agent process parses the query request, uses the parsed SQL instance identifier or query text keywords as an index to retrieve matching structured resource attribution data from the time-series database, and sorts the retrieved structured resource attribution data in descending order of cumulative resource consumption values ​​to generate a resource consumption ranking list. This list is then output through a visual interface or API interface, allowing database administrators to quickly locate SQL statements with abnormal resource consumption, thus achieving a closed-loop operation and maintenance process.

[0052] In one optional implementation, the event capture probe is deployed using a compile-once, run-anywhere (CO-RE) approach, and is dynamically bound to the query execution entry function of the relational database. This allows the event capture probe to be mounted without modifying the relational database source code or restarting the relational database service process. When monitoring needs to be stopped, the probe is also removed via a dynamic unloading mechanism, without affecting the normal operation of the database. The user-space agent process and the eBPF probe program interact with each other through a circular buffer. The eBPF probe program is only responsible for event capture and accumulation, while the user-space agent process handles window management, expired event decrement, data persistence, and query response. This separation of responsibilities reduces the instruction complexity of the eBPF probe program and ensures that it passes the kernel verifier's check and runs stably.

[0053] This application generates an independent SQL instance identifier for each execution of the target SQL statement and establishes a mapping relationship between the SQL instance identifier and the backend execution process in kernel mode. It then uses process identifier and thread identifier to look up and attribute subsequent kernel resource consumption events to the corresponding SQL instance identifier in real time. This achieves accurate attribution of CPU and IO resource consumption at the single SQL execution instance level, solving the problem that existing solutions can only attribute at the level of normalized SQL aggregation or process, which is insufficient in granularity. It effectively supports the fine-grained operation and maintenance needs of resource cost allocation and performance tuning by SQL instance.

[0054] Meanwhile, this application designs a sliding time window mechanism for short SQL scenarios. After the SQL execution ends, the lifespan of the mapping relationship is dynamically updated based on the comparison between the execution time and the preset time threshold. The mapping relationship is retained within the window period to accommodate late kernel resource consumption events. An expiration reverse deduction mechanism ensures that the cumulative resource consumption value always reflects the actual consumption within the window. This eliminates the problem of resource statistics loss caused by excessively long sampling periods or kernel event scheduling delays in millisecond-level short SQL. Moreover, the above mechanism is implemented through kernel-mode event driving, without modifying the database source code, and has minimal impact on the database's own operating load. It is suitable for long-term deployment in high-concurrency production environments.

[0055] like Figure 2 As shown in the embodiments of this application, a resource consumption attribution device for relational databases is also proposed, comprising: At least one processor; and, A memory communicatively connected to the at least one processor; wherein, The memory stores instructions that can be executed by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform a resource consumption attribution method for a relational database as described in any of the above embodiments.

[0056] This application also provides a non-volatile computer storage medium storing computer-executable instructions, wherein the computer-executable instructions are configured as: a resource consumption attribution method for a relational database as described in any of the above embodiments.

[0057] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the device and medium embodiments are basically similar to the method embodiments, so the description is relatively simple; relevant parts can be referred to the description of the method embodiments.

[0058] The devices and media provided in this application are one-to-one with the methods. Therefore, the devices and media also have similar beneficial technical effects as their corresponding methods. Since the beneficial technical effects of the methods have been described in detail above, the beneficial technical effects of the devices and media will not be repeated here.

[0059] Those skilled in the art will understand that embodiments of the present invention can be provided as methods, systems, or computer program products. Therefore, the present invention can take the form of a completely hardware embodiment, a completely software embodiment, or an embodiment combining software and hardware aspects. Furthermore, the present invention can take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0060] This invention is described with reference to flowchart illustrations and / or block diagrams of methods, apparatus (systems), and computer program products according to embodiments of the invention. It will be understood that each block of the flowchart illustrations and / or block diagrams, and combinations of blocks in the flowchart illustrations and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, special-purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, generate instructions for implementing the flowchart illustrations and / or block diagrams. Figure 1 One or more processes and / or boxes Figure 1 A device that provides the functions specified in one or more boxes.

[0061] These computer program instructions may also be stored in a computer-readable storage medium that can direct a computer or other programmable data processing device to function in a particular manner, such that the instructions stored in the computer-readable storage medium produce an article of manufacture including instruction means, which are implemented in a process Figure 1 One or more processes and / or boxes Figure 1 The function specified in one or more boxes.

[0062] These computer program instructions may also be loaded onto a computer or other programmable data processing equipment to cause a series of operational steps to be performed on the computer or other programmable equipment to produce a computer-implemented process, thereby providing instructions that execute on the computer or other programmable equipment for implementing the process. Figure 1 One or more processes and / or boxes Figure 1 The steps of the function specified in one or more boxes.

[0063] In a typical configuration, a computing device includes one or more processors (CPU), input / output interfaces, network interfaces, and memory.

[0064] Memory may include non-persistent storage in computer-readable media, such as random access memory (RAM) and / or non-volatile memory, such as read-only memory (ROM) or flash RAM. Memory is an example of computer-readable media.

[0065] Computer-readable media include both permanent and non-permanent, removable and non-removable media that can store information using any method or technology. Information can be computer-readable instructions, data structures, modules of programs, or other data. Examples of computer storage media include, but are not limited to, phase-change memory (PRAM), static random access memory (SRAM), dynamic random access memory (DRAM), other types of random access memory (RAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), flash memory or other memory technologies, CD-ROM, digital versatile optical disc (DVD) or other optical storage, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transferable medium that can be used to store information accessible by a computing device. As defined herein, computer-readable media does not include transient computer-readable media, such as modulated data signals and carrier waves.

[0066] It should also be noted that the terms "comprising," "including," or any other variations thereof are intended to cover non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements includes not only those elements but also other elements not expressly listed, or elements inherent to such a process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one..." does not exclude the presence of other identical elements in the process, method, article, or apparatus that includes said element.

[0067] The above description is merely an embodiment of this application and is not intended to limit the scope of this application. Various modifications and variations can be made to this application by those skilled in the art. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the scope of the claims of this application.

Claims

1. A resource consumption attribution method for relational databases, characterized in that, include: Capture the context information when the target SQL statement starts execution, generate the SQL instance identifier for the current execution, determine the backend execution process of the target SQL statement, establish the execution mapping relationship between the SQL instance identifier and the backend execution process, and store it in the kernel-mode event mapping buffer; During the execution of the target SQL statement, the kernel resource consumption events of the backend execution process are captured in real time through a pre-registered event capture probe. The resource consumption corresponding to each kernel resource consumption event is accumulated into the cumulative resource consumption value corresponding to the SQL instance identifier in the event mapping buffer. After the target SQL statement is executed, the execution time of the target SQL statement is compared with a preset time threshold. If the execution time is less than the preset time threshold, the lifespan of the execution mapping relationship in the event mapping buffer is updated based on a preset sliding time window. Within the preset sliding time window, kernel resource consumption events of the backend execution process are continuously captured, and the corresponding resource consumption is continuously added to the cumulative resource consumption value corresponding to the SQL instance identifier. When the preset sliding time window expires or the execution time is not less than the preset time threshold, the execution mapping relationship is deleted from the event mapping buffer, and a resource attribution record for the current execution of the target SQL statement is generated based on the cumulative resource consumption value corresponding to the SQL instance identifier in the event mapping buffer.

2. The resource consumption attribution method for relational databases according to claim 1, characterized in that, The kernel resource consumption events include CPU clock events and block device input / output events; The method of capturing kernel resource consumption events of the backend execution process in real time through pre-registered event capture probes specifically includes: Using a preset fixed clock cycle as the trigger node, capture the CPU usage event of the backend execution process, record the CPU usage duration of the backend execution process in each clock cycle, and synchronously mark the timestamp, process identifier, and thread identifier of the CPU usage event. Using the initiation and completion of block device input / output requests as trigger nodes, the block device read / write events of the backend execution process are captured, the data transmission volume and request time corresponding to a single input / output request are recorded, and the timestamp, process identifier, and thread identifier of the block device read / write event are marked synchronously.

3. The resource consumption attribution method for relational databases according to claim 2, characterized in that, The step of adding the resource consumption corresponding to each kernel resource consumption event to the cumulative resource consumption value corresponding to the SQL instance identifier in the event mapping buffer specifically includes: When the CPU occupancy event is captured, the first process identifier and the first thread identifier of the CPU occupancy event are extracted. Using the first process identifier and the first thread identifier as keys, the corresponding SQL instance identifier is searched in the event mapping buffer. The CPU occupancy duration is then added to the cumulative consumption value corresponding to the SQL instance identifier. When the block device read / write event is captured, the second process identifier and the second thread identifier of the block device read / write event are extracted. Using the second process identifier and the second thread identifier as keys, the corresponding SQL instance identifier is searched in the event mapping buffer, and the data transfer volume is added to the cumulative consumption value corresponding to the SQL instance identifier.

4. The resource consumption attribution method for relational databases according to claim 1, characterized in that, The step of updating the lifespan of the execution mapping relationship in the event mapping buffer based on a preset sliding time window specifically includes: Taking the end execution time of the target SQL statement as the starting time and the length of a preset sliding time window as the survival span, calculate the expiration time of the execution mapping relationship; The expiration time is written into the storage entry corresponding to the execution mapping relationship in the event mapping buffer, so as to instruct the event mapping buffer to retain the execution mapping relationship before the expiration time.

5. The resource consumption attribution method for a relational database according to claim 1, characterized in that, After continuously adding the corresponding resource consumption to the cumulative resource consumption value corresponding to the SQL instance identifier, the method further includes: Within the preset sliding time window, the capture time of each accumulated kernel resource consumption event is obtained; Each of the aforementioned capture moments is compared with the current valid boundary of the preset sliding time window; When the capture time of any of the kernel resource consumption events is earlier than the current valid boundary, it is determined that any kernel resource consumption event exceeds the preset sliding time window, and the resource consumption amount corresponding to any kernel resource consumption event is subtracted from the cumulative resource consumption value.

6. The resource consumption attribution method for a relational database according to claim 5, characterized in that, After subtracting the resource consumption amount corresponding to any kernel resource consumption event from the cumulative resource consumption value, the method further includes: Within the preset sliding time window, the cumulative resource consumption value corresponding to the SQL instance identifier is collected based on a preset sampling interval, and a resource consumption sequence in which the cumulative resource consumption value changes over time within the preset sliding time window is generated. Based on a preset smoothing coefficient, an exponentially weighted moving average is calculated on the resource consumption sequence to generate the resource consumption baseline value and resource consumption fluctuation threshold of the target SQL statement. The current cumulative resource consumption value is compared with the resource consumption baseline value to obtain the current resource consumption deviation value. When the current resource consumption deviation value exceeds a preset multiple of the resource consumption fluctuation threshold, an alarm is triggered.

7. The resource consumption attribution method for a relational database according to claim 1, characterized in that, The step of generating a resource attribution record for the current execution of the target SQL statement based on the cumulative resource consumption value corresponding to the SQL instance identifier in the event mapping buffer specifically includes: The user-mode agent process reads the cumulative resource consumption value corresponding to each SQL instance identifier from the event mapping buffer according to a preset period. Obtain the pre-stored query text of the target SQL statement corresponding to the SQL instance identifier; The cumulative resource consumption value, the query text, and the start and end times of the target SQL statement are correlated to generate structured resource attribution data; The structured resource attribution data is written into a time-series database to generate the resource attribution record for the current execution of the target SQL statement.

8. The resource consumption attribution method for a relational database according to claim 7, characterized in that, After writing the structured resource attribution data into the time-series database and generating the resource attribution records, the method further includes: Receive a query request for a target time range and parse the SQL instance identifier carried in the query request; Using the parsed SQL instance identifier as an index, retrieve matching structured resource attribution data from the time-series database; The retrieved structured resource attribution data are sorted from highest to lowest based on cumulative resource consumption values, and a resource consumption ranking list is generated and output.

9. A resource consumption attribution device for a relational database, 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 instructions executable by the at least one processor, which, when executed by the at least one processor, enables the at least one processor to perform a resource consumption attribution method for a relational database as described in any one of claims 1 to 8.

10. A non-volatile computer storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are configured to execute a resource consumption attribution method for a relational database as described in any one of claims 1 to 8.

Images