A dynamic PID tracking database SQL execution process kernel level observation method and system

Abstract

The application discloses a dynamic PID tracking database SQL execution process kernel level observation method and system. The method comprises the following steps: capturing a session identifier through a self-referential query matching, combining a database session view and an operating system thread view to dynamically poll a target thread identifier; sending a start instruction to a kernel probe agent deployed on a database node, driving a kernel observation tool such as eBPF / BCC to start accurate sampling with the target thread identifier as a parameter; automatically terminating the probe and returning the collected results by monitoring the target thread life cycle; and triggering multidimensional analysis based on the data ready gating mechanism of the diagnosis server to generate a cross-layer diagnosis conclusion. The application realizes automatic and accurate association of SQL session semantics and operating system kernel observation data, automatic synchronization of observation life cycle and SQL execution life cycle, and accurate positioning of multidimensional kernel level bottlenecks, thereby effectively improving the efficiency and accuracy of database performance diagnosis.

Description

Technical Field

[0001] This application relates to the field of database performance diagnostic technology, specifically to a kernel-level observation method and system for dynamically tracking the SQL execution process in a database using PID. Background Technology

[0002] As the scale of business carried by enterprise-level database systems continues to grow, the execution performance of SQL statements has become a key factor affecting the overall system throughput and user experience. The root causes of slow SQL execution can be distributed across multiple layers, including structural defects in application-layer SQL statements, deviations in query plan selection at the database kernel layer, and resource bottlenecks at the operating system layer such as CPU scheduling, I / O processing, and memory allocation.

[0003] Traditional SQL performance diagnostic methods mainly include the following categories:

[0004] (1) Diagnostic methods based on built-in database statistics. For example, obtaining the query execution plan and the time distribution of each operator through EXPLAIN ANALYZE. This method can only reflect the logical execution path inside the database engine and cannot observe underlying factors such as CPU scheduling latency, I / O blocking, and memory allocation bottlenecks at the operating system level.

[0005] (2) Diagnostic methods based on general operating system monitoring tools. For example, tools such as top, iostat, and vmstat are used to observe the overall system load. These tools run from a global perspective and cannot establish a precise correlation between system-level metrics and the execution process of specific SQL statements. When multiple SQL statements are executed concurrently on a database instance, the observation results are mixed with resource consumption data from multiple unrelated processes, making it impossible to pinpoint the bottleneck of a specific SQL statement.

[0006] (3) Diagnostic methods based on performance profiling tools. For example, using tools such as perf and strace to sample or trace database processes. These tools usually require manual specification of the target process identifier (PID), and the mapping relationship between database backend processes and SQL sessions becomes complex under mechanisms such as connection pools and thread pools, making manual specification prone to errors. In addition, traditional profiling tools cannot perceive the execution boundaries of SQL statements (when they start and when they end), resulting in the collected data containing a large amount of noise information from irrelevant time periods.

[0007] (4) Observation methods based on eBPF (Extended Berkeley Packet Filter) technology. eBPF technology allows custom programs to run safely in the kernel and provides low-overhead system-level observation capabilities. However, existing eBPF observation schemes (such as the BCC toolset) also face the problem of target localization: they require explicit specification of the process or thread identifier to be observed, and lack an automatic association mechanism with upper-layer database session semantics.

[0008] In summary, existing technologies lack a technical solution that can automatically and accurately correlate semantic information at the SQL session level with observation data at the operating system kernel level. As a result, database performance diagnosis still suffers from low efficiency, poor accuracy, and high cost of manual intervention in cross-layer positioning scenarios. Summary of the Invention

[0009] To overcome the aforementioned deficiencies in the existing technology, this application proposes a novel dynamic PID tracking database SQL execution process kernel-level observation method and system.

[0010] This invention aims to solve the following technical problems:

[0011] (1) How to automatically and in real time capture the operating system-level thread identifier (Lightweight Process ID) corresponding to the SQL statement in the scenario of asynchronous execution of SQL statement, and establish a dynamic mapping relationship between SQL session and operating system thread;

[0012] (2) How to automatically drive kernel-level probe tools (such as eBPF / BCC toolset) to accurately observe target threads based on captured thread identifiers, and avoid data noise caused by global collection;

[0013] (3) How to automatically terminate the kernel-level probe tool and reclaim the collected data after the SQL statement is executed, so as to achieve automatic synchronization between the observation process and the SQL execution lifecycle;

[0014] (4) How to ensure that the thread identification tracking of each SQL diagnostic task and the kernel observation process are isolated from each other and do not interfere with each other in a multi-threaded, multi-session concurrent database environment.

[0015] Specifically, this application provides the following technical solutions:

[0016] The first aspect of this application provides a kernel-level observation method for dynamically tracking the SQL execution process in a database using PID tracking, such as... Figure 1 As shown, it includes the following steps:

[0017] S1. Session ID Capture: Submit the SQL statement to be diagnosed to an asynchronous execution thread for execution. By querying the database session view, the session ID of the current session is captured using a self-referencing query matching method.

[0018] S2. Dynamic polling and capture of thread identifiers: After obtaining the session identifier, a mapping relationship between the session identifier and the thread identifier is established by jointly querying the database session view and the operating system thread view. The target thread identifier is dynamically polled and captured with a preset time interval as the polling period.

[0019] S3. Kernel probe tool startup: Using the acquired thread identifier as the target parameter, a startup command is sent to the kernel probe agent deployed on the database node. The kernel probe agent selects the corresponding kernel observation tool according to the probe type and starts the observation process with the target thread identifier as the parameter.

[0020] S4. Automatic termination of probe and return of results: The kernel probe agent monitors the life cycle of the target thread. When the target thread is detected to have disappeared from the process list, the corresponding kernel observation process is automatically terminated and the collected results are returned to the diagnostic server.

[0021] S5. Diagnosis Trigger: After receiving the collection results, the diagnostic server checks whether all the data items on which each diagnostic analysis point depends are ready. If they are ready, the analysis logic is triggered to generate a diagnostic conclusion.

[0022] Furthermore, the method of this application also includes a pre-processing verification step: querying the thread pool configuration parameters of the database; if the thread pool function is enabled, the kernel-level observation process is skipped; at the same time, it is verified whether the target database node has deployed a kernel probe agent; if not, the kernel-level observation is skipped.

[0023] Furthermore, in the method of this application, the self-reference query matching method in step S1 specifically includes: constructing a query statement containing a random feature value, referencing the feature value in the query conditions, and simultaneously searching for the statement itself in the query results through fuzzy matching, so as to locate the session record of the current connection in the session view and obtain its session identifier; if a single query fails to find the match, it is retried with a new random feature value until the preset maximum number of retries is reached.

[0024] Furthermore, in the method of this application, the dynamic polling capture of thread identifiers in step S2 includes:

[0025] S21. Construct a joint query statement to perform a query that associates the database session view with the operating system thread view, using the session identifier as the filter condition to obtain the corresponding lightweight process identifier.

[0026] S22. Repeatedly execute the joint query statement with a preset time interval as the polling period;

[0027] S23. If the query returns a valid result, the lightweight process identifier is recorded as the target thread identifier, and the polling ends.

[0028] S24. If the number of polling attempts exceeds the preset threshold and no valid result is obtained, the thread identifier capture is determined to have failed, the current diagnostic task is terminated and an exception message is returned.

[0029] Furthermore, in the method of this application, the startup instruction in step S3 includes a target thread identifier, a task identifier, and a probe type; the kernel probe agent records the correspondence between the observed process identifier, the probe type, and the target thread identifier to the process trace file;

[0030] The kernel observation tools include at least one of the following types:

[0031] CPU in-state analysis tools can perform CPU sampling on the target thread and generate a function call stack flame graph.

[0032] CPU off-state analysis tools track the time distribution of a target thread in an off-state state and generate off-state flame diagrams;

[0033] Block device I / O latency tools, which collect latency distribution histograms of block device I / O requests;

[0034] Block device I / O detail tools record the device number, sector, size, and time consumption of each I / O request;

[0035] Memory leak detection tools track the memory allocation and deallocation operations of a target process and identify unreleased memory blocks;

[0036] A tool for handling queue latency, used to collect the latency distribution of the CPU scheduler's run queue.

[0037] Page cache hit tools that count the number of page cache hits and misses;

[0038] TCP connection lifecycle tools record the entire process of establishing, transmitting, and closing a TCP connection.

[0039] Furthermore, in the method of this application, the CPU in-state analysis tools and CPU out-of-state analysis tools take the target thread identifier as the direct observation target to achieve accurate sampling of specific SQL execution threads; the block device I / O latency tools, block device I / O detail tools, run queue latency tools, page cache hit tools, and TCP connection lifecycle tools run in global mode, and their collection results are combined with the SQL execution time window for post-processing filtering.

[0040] Furthermore, in the method of this application, the automatic termination of the probe in step S4 includes:

[0041] S41. Send a termination signal to the corresponding kernel observation background process to stop data acquisition;

[0042] S42. For probes that generate flame graphs, convert the collected function call stack data into vector graphics;

[0043] S43. The collected results file is sent back to the diagnostic server via HTTP multipart protocol.

[0044] Furthermore, in the method of this application, step S5 specifically includes:

[0045] S51. The diagnostic server maintains a data storage area for each diagnostic task, wherein each collected data item is associated with a reference count value;

[0046] S52. When the kernel probe agent returns the collection results, the data is stored in the storage area.

[0047] S53. Traverse all diagnostic analysis points and check whether all data items that each diagnostic analysis point depends on have arrived in the storage area through the set inclusion relationship. If they are ready, immediately trigger the analysis logic of that analysis point and generate a diagnostic conclusion.

[0048] S54. When the reference count of a data item reaches zero, it is automatically removed from the storage area.

[0049] Furthermore, in a multi-threaded, multi-session concurrent database environment, the thread identification tracking of each SQL diagnostic task and the kernel observation process are isolated from each other and do not interfere with each other.

[0050] The second aspect of this application provides a kernel-level observation system for dynamic PID tracking of database SQL execution processes. The system, when running, implements the steps of the aforementioned kernel-level observation method for dynamic PID tracking of database SQL execution processes, such as... Figure 2 As shown, the system includes:

[0051] The session identifier capture module is used to submit the SQL statement to be diagnosed to the asynchronous execution thread for execution. It captures the session identifier of the current session by querying the database session view and using a self-referencing query matching method.

[0052] The thread identifier polling and capturing module is used to obtain the session identifier, and then establish a mapping relationship between the session identifier and the thread identifier by jointly querying the database session view and the operating system thread view. The target thread identifier is dynamically polled and captured with a preset time interval as the polling period.

[0053] The kernel probe startup module is used to send a startup command to the kernel probe agent deployed on the database node, using the acquired thread identifier as the target parameter. The kernel probe agent selects the corresponding kernel observation tool according to the probe type, starts the observation process with the target thread identifier as the parameter, and records the correspondence between the observation process identifier, probe type and target thread identifier to the process trace file.

[0054] The probe termination and result feedback module is used by the kernel probe agent to monitor the life cycle of the target thread. When the target thread is detected to have disappeared from the process list, the corresponding kernel observation process is automatically terminated and the collected results are fed back to the diagnostic server.

[0055] The diagnostic trigger module is used by the diagnostic server to check whether all the data items on which each diagnostic analysis point depends are ready after receiving the collection results. If they are ready, the analysis logic is triggered to generate a diagnostic conclusion.

[0056] A third aspect of this application provides an electronic device, including: a memory and a processor;

[0057] Memory: Used to store computer programs;

[0058] Processor: Used to execute the computer program to implement the steps of the aforementioned kernel-level observation method for the dynamic PID tracking database SQL execution process.

[0059] A fourth aspect of this application provides a computer-readable storage medium having a computer program stored thereon, wherein when the computer program is executed by a processor, it implements the steps of the aforementioned dynamic PID tracking database SQL execution process kernel-level observation method.

[0060] In summary, compared with the prior art, the solution of the present invention has the following advantages:

[0061] (1) Automatic and accurate association between SQL session semantics and operating system kernel observation data was achieved. Session identifiers were captured by a self-reference query matching method, and target thread identifiers were captured by dynamic polling based on the joint query of session view and operating system thread view. A dynamic mapping relationship between SQL session and operating system thread was established, which solved the problem that system-level indicators and specific SQL statements could not be accurately associated in traditional methods, and avoided data mixing and noise interference caused by global monitoring.

[0062] (2) Automatic synchronization of the observation lifecycle and the SQL execution lifecycle is achieved. The kernel probe agent automatically terminates the probe and returns the results by monitoring the liveness status of the target thread, without the need for manual intervention to specify the process identifier or manually start and stop the observation tool, which reduces the complexity of operation and the risk of human error. At the same time, it avoids the collection of data in irrelevant time periods and improves the purity and effectiveness of diagnostic data.

[0063] (3) Accurate localization of multi-dimensional kernel-level bottlenecks. Based on dynamically captured thread identifiers and kernel probe tools such as eBPF / BCC, multi-dimensional observations such as CPU in-state / out-state analysis, I / O latency tracing, and memory leak detection can be performed on specific SQL execution threads, extending performance diagnosis from the database engine to the operating system kernel level, and achieving accurate localization of cross-layer root causes.

[0064] (4) Task isolation and efficient resource management in a concurrent environment are achieved. The thread identification tracking and kernel observation processes of each diagnostic task are independent of each other. The diagnostic server manages the life cycle of the collected data items through a reference counting mechanism. Once the data is ready, analysis is automatically triggered and resources are reclaimed, ensuring isolation and efficient utilization of system resources when multiple tasks are executed concurrently.

[0065] Other features and advantages of this application will be set forth in detail in the following description, or will become apparent through the implementation of the relevant technical solutions of this application. The objectives and other advantages of this application can be achieved through the technical features and means explicitly pointed out in the description, claims, and drawings, and will be obtained through the implementation of these technical contents. Attached Figure Description

[0066] To more clearly illustrate the technical solution of this application, the accompanying drawings involved in the description of this invention will be briefly introduced below. It should be noted that the drawings only show some embodiments of the invention. For those skilled in the art, other related drawings can be derived from these drawings without creative effort.

[0067] Figure 1 This diagram illustrates the operational steps of the kernel-level observation method for the dynamic PID tracking database SQL execution process in this application.

[0068] Figure 2 This is a structural diagram of the kernel-level observation system for the dynamic PID tracking database SQL execution process in this application.

[0069] Figure 3 This is a schematic diagram of the structure of an electronic device provided in an embodiment of this application.

[0070] Caption: Processor-310, Communication Interface-320, Memory-330, Communication Bus-340. Detailed Implementation

[0071] To make the objectives, technical solutions, and advantages of the embodiments of this application clearer, the technical solutions in the embodiments of this application will be clearly and completely described below. It should be understood that the described embodiments are only some embodiments of this application, not all embodiments. All other embodiments obtained by those skilled in the art based on the embodiments of this application without creative effort are within the protection scope of this application.

[0072] In this document, the term "comprising" and any variations thereof (such as "including," "including," etc.) are open-ended expressions and should be understood as "including but not limited to," meaning that the listed content is not exhaustive and may include other content not explicitly mentioned. The term "based on" should be understood as "at least partially based on," meaning that the basis or condition referred to may not be the only factor and may involve other relevant factors. The term "one embodiment" should be understood as "at least one embodiment," meaning that the described embodiment is not the only possible implementation, and other similar embodiments may exist.

[0073] In this application, the terms "a" and "a plurality of" are used to modify related elements or features, and their expression is illustrative rather than restrictive. Unless otherwise expressly stated in the context, "a" should be understood as "at least one," and "a plurality of" should be understood as "at least two." Those skilled in the art should reasonably interpret these terms based on the semantic and logical relationships of the context to ensure that they cover the possibility of "one or more."

[0074] This invention provides the following technical solution:

[0075] This invention provides a kernel-level observation method for database SQL execution process based on dynamic thread identifier tracing, comprising the following steps:

[0076] Step S1: Environment Pre-verification. Before starting the observation process, query the database's thread pool configuration parameters (e.g., enable_thread_pool). If the thread pool function is enabled, skip the kernel-level observation process. This is because in thread pool mode, operating system threads are shared and reused by multiple database sessions, and there is no stable one-to-one mapping between thread identifiers and sessions, rendering precise observation based on thread identifiers meaningless. Simultaneously, verify whether the target database node has deployed a kernel probe agent; if not, skip kernel-level observation as well.

[0077] Step S2: Asynchronous SQL Statement Execution and Session ID Capture. The SQL statement to be diagnosed is submitted to an asynchronous execution thread and executed through the database connection. After the SQL statement begins execution, the session ID of the current session is captured by querying the database session view (e.g., pg_stat_activity) using a self-referencing query matching method. Specifically, the self-referencing query matching method involves constructing a query statement containing random feature values, which are referenced in the query conditions. Simultaneously, the query results are searched for using fuzzy matching (LIKE) to locate the current connection's session record in the session view and obtain its session ID. If a single query fails to find the match, it is retried using the random feature value, with a maximum retry count of a preset threshold (e.g., 100 times).

[0078] Step S3: Dynamically poll and capture thread identifiers. After obtaining the session identifier, a mapping relationship between the session identifier and the operating system thread identifier is established by jointly querying the database session view and the operating system thread view, and the target thread identifier is dynamically polled and captured. The specific process is as follows:

[0079] S3.1: Construct a union query statement to perform a query that associates the database session view (which provides a mapping between session identifiers and backend process identifiers) with the operating system thread view (which provides a mapping between process identifiers and lightweight process identifiers), using the session identifier as the filter condition to obtain the corresponding lightweight process identifier (lwpid).

[0080] S3.2: Repeatedly execute the joint query statement with a first preset time interval (e.g., 10 milliseconds) as the polling period;

[0081] S3.3: If the query returns a valid result, the lightweight process identifier is recorded as the target thread identifier, and the polling ends;

[0082] S3.4: If no valid result is obtained after the number of polling attempts exceeds the first preset threshold (e.g., 1000 times), the thread identifier capture is determined to have failed, the current diagnostic task is terminated, and an exception message is returned.

[0083] Step S4: Remotely start the kernel probe tool. Using the thread identifier obtained in step S3 as the target parameter, send a start command to the kernel probe agent deployed on the database node via HTTP protocol. The start command includes the target thread identifier (tid), task identifier (taskId), and probe type (monitorType). After receiving the command, the kernel probe agent selects the corresponding kernel observation tool (such as eBPF / BCC tool) according to the probe type, starts the observation process in the background with the target thread identifier as the parameter, and records the correspondence between the observation process identifier, probe type, and target thread identifier to the process trace file.

[0084] The kernel observation tools include, but are not limited to, the following types:

[0085] (1) CPU in-state analysis class: CPU sampling of the target thread is performed to generate a function call stack flame graph (On-CPUFlame Graph), which is used to analyze CPU hotspot functions during SQL execution;

[0086] (2) CPU Off-State Analysis Class: Track the time distribution of the target thread in the non-running state (such as waiting for lock, waiting for I / O) and generate an off-state flame graph.

[0087] (3) Block device I / O latency class: Collect the latency distribution histogram of block device I / O requests;

[0088] (4) Block device I / O details: Record the device number, sector, size and time of each I / O request;

[0089] (5) Memory leak detection class: Track the memory allocation (malloc) and deallocation (free) operations of the target process and identify unreleased memory blocks;

[0090] (6) Run queue latency class: Collects the run queue latency distribution of the CPU scheduler;

[0091] (7) Page cache hit category: Count the number of page cache hits and misses;

[0092] (8) TCP connection lifecycle class: records the entire process of establishing, transmitting, and closing a TCP connection.

[0093] Among them, the CPU in-state analysis and CPU out-of-state analysis tools use the target thread identifier (tid) as the direct observation target to achieve accurate sampling of specific SQL execution threads; the block device I / O, run queue, page cache, and TCP connection tools run in global mode, and their collection results are combined with the SQL execution time window for post-processing filtering.

[0094] Step S5: SQL Execution Lifecycle Monitoring and Automatic Probe Termination. The kernel probe agent scans all active process trace files via a scheduled task (e.g., executing once per second), reads the target thread identifier recorded therein, and queries the operating system process list to see if an active thread corresponding to that thread identifier exists. When it is detected that the target thread has disappeared from the process list (i.e., the SQL statement has been executed, the database backend thread has been released, or it has returned to an idle state), the following operations are performed automatically:

[0095] S5.1: Send a termination signal (such as SIGINT) to the corresponding kernel observation daemon to stop data acquisition;

[0096] S5.2: For probes that generate flame graphs (CPU in state, CPU out state, memory leak), convert the collected function call stack data into SVG vector graphics;

[0097] S5.3: The collected results file is sent back to the diagnostic server via the HTTP multipart protocol.

[0098] Step S6: Asynchronous Data Readiness Gating and Diagnostic Triggering. The diagnostic server maintains a data storage area for each diagnostic task, where each collected data item is associated with a reference count (indicating how many diagnostic analysis points depend on that data item). When the kernel probe agent sends back the collected results, the server stores the data in the storage area and iterates through all diagnostic analysis points, checking whether all the data items they depend on are ready (i.e., the storage area contains all the data items required by the analysis point). If ready, the analysis logic for that analysis point is immediately triggered, generating a diagnostic conclusion. When the reference count of a data item reaches zero, it is automatically removed from the storage area, releasing memory resources.

[0099] To more clearly illustrate the technical solution of this application, the following will provide further explanation through specific scenario embodiments.

[0100] This example uses the diagnosis of a slow SQL statement to illustrate the complete execution flow of this solution:

[0101] Step 1: Users submit SQL diagnostic tasks through the management platform. The system creates a diagnostic task record and sets its status to "CREATE".

[0102] Step 2: The task scheduler polls the pending tasks every 5 seconds, assigns tasks with the status of CREATE to the execution thread pool (maximum of 5 concurrent nodes), and changes the task status to "WAITING".

[0103] Step 3: The execution entry point first queries the database parameter `enable_thread_pool`. If it is on, kernel-level observation is marked as unavailable; otherwise, it queries whether the target node has registered an Agent environment. Assuming both conditions are met, the kernel-level observation process begins.

[0104] Step 4: Submit the SQL execution task via an asynchronous thread pool. After obtaining a JDBC connection to the database, the asynchronous thread constructs a self-referencing query statement:

[0105] SELECT sessionid FROM pg_stat_activity

[0106] WHERE 12345=12345

[0107] AND query LIKE 'select sessionid from pg_stat_activity where 12345=12345%'

[0108] Here, 12345 is a random integer generated at runtime. This query locates the current connection's session record in pg_stat_activity and retrieves the session ID. If a match is not found (possibly because the query is not yet in the activity list), a new random number is used to retry, up to a maximum of 100 retries.

[0109] Step 5: After submitting the asynchronous SQL execution, the main thread polls every 500 milliseconds to check whether the sessionid has been captured, waiting for a maximum of 25 seconds (50 times × 500 milliseconds).

[0110] Step 6: After obtaining the sessionid, proceed to the thread identifier capture phase. Construct a union query:

[0111] SELECT b.lwpid FROM pg_stat_activity a, dbe_perf.os_threads b

[0112] WHERE a.pid = b.pid AND a.sessionid = '{sessionid}'

[0113] The query is executed in a poll at 10-millisecond intervals, up to a maximum of 1000 times (approximately 10 seconds). Once the lwpid is obtained, it is recorded as the target thread identifier for the current task.

[0114] Step 7: Send a logging command to the kernel probe agent to create a process trace file (.pid file) named after the task identifier on the agent side, in preparation for launching multiple probe tools later.

[0115] Step 8: Based on the diagnostic analysis points required for the current diagnostic task, determine the types of kernel observation tools that need to be launched. For each tool, send a launch command to the agent:

[0116] POST / monitor / v1 / startMonitor

[0117] Parameters: tid={lwpid}, taskId={taskId}, monitorType={toolType}

[0118] After receiving the instruction, the agent constructs and executes the BCC tool commands. Taking CPU in-state analysis (profile) as an example:

[0119] set -m; cd / usr / share / bcc / tools;

[0120] . / profile -af -L {lwpid} > / output / {taskId} / profile.stacks 2>&1 &

[0121] echo $!,profile,{lwpid} >> / pid / {taskId}.pid

[0122] The `set -m` option enables shell job control, the `-L` parameter specifies thread-level sampling, `$!` captures the PID of the background process, and the last line appends the background process PID, tool type, and target thread identifier to the trace file.

[0123] Step 9: The agent's scheduled monitoring task scans all .pid files every second, reads the target thread identifier recorded within, and checks if the target thread is still alive by executing the `ps -eLf` command. Once the SQL execution is complete, the database backend thread is released or returns to idle status, and the target lwpid disappears from the active thread list.

[0124] Step 10: After detecting that the target thread has exited, the agent executes the following sequentially:

[0125] Send a kill -2 (SIGINT) signal to the corresponding BCC background process to gracefully terminate it and output the final result;

[0126] For the three types of tools—profile, offcputime, and memleak—the flamegraph.pl script is called to convert the .stacks file into an SVG flame graph.

[0127] The callback interface that sends the collected results file back to the diagnostic server via HTTP multipart POST:

[0128] POST / callback / {taskId} / result

[0129] Content-Type: multipart / form-data

[0130] Parameters: type={monitorType}, file={collection result file}

[0131] Step 11: After receiving the callback data, the diagnostic server stores it in the data storage area and updates the status of the corresponding data item. Then, it iterates through all diagnostic analysis points, using a set containment check (HashSet.containsAll) to determine if all data items required for each analysis point have been reached. If ready, the analysis logic for that analysis point is immediately triggered, for example:

[0132] OnCpu analysis points: Relying on ProfileItem data, after it is ready, the call stack data of the CPU in-state flame graph is parsed to identify hot functions and determine whether CPU consumption is concentrated on specific operators (such as hash join, sorting, index scan);

[0133] OffCpu Analysis Points: Relying on OffCpuItem data, analyze the CPU off-state time distribution after it is ready to identify whether there are blocking factors such as lock wait, I / O wait, etc.

[0134] BioLatency Analysis Points: Relying on BioLatencyItem data, analyze the block device I / O latency distribution after it is ready to determine whether there is an I / O bottleneck.

[0135] Step 12: Once all diagnostic analysis points have been analyzed (status: completed, unmatched, conditions not met, or no data), the task status changes to "Completed" (FINISH), and a comprehensive report containing multi-dimensional kernel-level diagnostic conclusions is returned.

[0136] The flowcharts and block diagrams in the accompanying drawings illustrate possible implementations of systems, methods, and computer program products according to various embodiments of this application, including architecture, functionality, and operation. In these figures, each block may represent a module, program segment, or portion of code containing one or more executable instructions for implementing a specified logical function. It should be noted that each block in the block diagrams and / or flowcharts, and combinations thereof, can be implemented using either a dedicated hardware-based system or a combination of dedicated hardware and computer instructions to achieve the specified function or operation.

[0137] like Figure 3 As shown, embodiments of this application also disclose an electronic device, including: a processor 310, a communication interface 320, a memory 330 for storing a processor-executable computer program, and a communication bus 340. The processor 310, communication interface 320, and memory 330 communicate with each other via the communication bus 340. The processor 310 executes the executable computer program to implement the steps of the aforementioned dynamic PID tracking database SQL execution process kernel-level observation method.

[0138] It is understood that, in addition to memory and a processor, this electronic device may also include input devices (such as a keyboard), output devices (such as a display), and other communication modules. These input devices, output devices, and other communication modules all communicate with the processor through I / O interfaces (i.e., input / output interfaces).

[0139] The operations described in this application can be implemented by writing computer program code using one or more programming languages ​​or a combination thereof. The programming languages ​​include, but are not limited to, the following types:

[0140] Object-oriented programming languages, such as Java, Smalltalk, C++, etc.

[0141] Conventional procedural programming languages, such as "C" or similar programming languages.

[0142] The execution methods of program code include, but are not limited to:

[0143] It runs entirely on the user's computer;

[0144] Part of it executes on the user's computer, and part of it executes on a remote computer;

[0145] Execute as a standalone software package;

[0146] It is executed entirely on a remote computer or server.

[0147] In scenarios involving remote computers, the remote computer can connect to the user's computer via any type of network, including but not limited to local area networks (LANs) or wide area networks (WANs). Furthermore, the remote computer can also connect to external computers via an internet service provider, for example, by utilizing the internet.

[0148] Furthermore, this application also discloses a computer-readable storage medium, wherein when the instructions in the computer-readable storage medium are executed by a processor of an electronic device, the electronic device is able to execute the various steps of the kernel-level observation method for the dynamic PID tracking database SQL execution process disclosed in this application.

[0149] In the context of this application, a computer-readable storage medium refers to a tangible medium capable of storing computer program code and related data. Specific examples include, but are not limited to, the following:

[0150] (1) Portable computer disk: such as floppy disks and other removable magnetic storage media.

[0151] (2) Hard disk: including mechanical hard disks and solid-state hard disks and other fixed storage devices.

[0152] (3) Random Access Memory (RAM): A volatile storage medium used for temporary storage of data and program code.

[0153] (4) Read-only memory (ROM): a non-volatile storage medium used to store fixed programs and data.

[0154] (5) Erasable programmable read-only memory (EPROM) or flash memory: non-volatile storage media that supports multiple erasures and reprogrammings.

[0155] (6) Fiber optic storage devices: storage media based on fiber optic technology.

[0156] (7) Portable compact disc read-only memory (CD-ROM): a read-only medium that stores data in the form of an optical disc.

[0157] (8) Optical storage devices: such as DVDs, Blu-ray discs and other storage media based on optical principles.

[0158] (9) Magnetic storage devices: such as magnetic tapes, disks and other storage media based on magnetic principles.

[0159] (10) Any suitable combination of the above: for example, combining multiple storage media to meet different storage needs.

[0160] These computer-readable storage media can be used to store the program code and related data described in this application to support program execution and persistent data storage.

[0161] Specifically, according to embodiments of this application, the processes described in the flowcharts can be implemented as computer software programs. For example, embodiments of this application relate to a computer program product comprising a computer program carried on a non-transitory computer-readable medium. This computer program contains program code for executing the kernel-level observation method for the dynamic PID tracking database SQL execution process disclosed in this application. When this computer program is executed by a processing system, it can achieve the functions defined in the embodiments of this application.

[0162] While the foregoing discussion contains several specific implementation details, these details should not be construed as limiting the scope of this application. The above description is merely a preferred embodiment of this application and an explanation of the technical principles employed. Those skilled in the art should understand that the scope of this application is not limited to technical solutions formed by specific combinations of the above-described technical features. Furthermore, this application should also cover other technical solutions formed by any combination of the above-described technical features or their equivalents without departing from the foregoing disclosed concept.

[0163] Those skilled in the art should also understand that modifications can be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some of the technical features, without departing from the spirit and scope of the technical solutions of the embodiments of this application. These modifications or substitutions will not cause the essence of the corresponding technical solutions to deviate from the core spirit and scope of the technical solutions of the embodiments of this application.

Claims

1. A kernel-level observation method for dynamically tracking the SQL execution process in a database using PID control, characterized in that, Includes the following steps: S1. Session ID Capture: Submit the SQL statement to be diagnosed to an asynchronous execution thread for execution. By querying the database session view, the session ID of the current session is captured using a self-referencing query matching method. S2. Dynamic polling and capture of thread identifiers: After obtaining the session identifier, a mapping relationship between the session identifier and the thread identifier is established by jointly querying the database session view and the operating system thread view. The target thread identifier is dynamically polled and captured with a preset time interval as the polling period. S3. Kernel probe tool startup: Using the acquired thread identifier as the target parameter, a startup command is sent to the kernel probe agent deployed on the database node. The kernel probe agent selects the corresponding kernel observation tool according to the probe type and starts the observation process with the target thread identifier as the parameter. S4. Automatic termination of probe and return of results: The kernel probe agent monitors the life cycle of the target thread. When the target thread is detected to have disappeared from the process list, the corresponding kernel observation process is automatically terminated and the collected results are returned to the diagnostic server. S5. Diagnosis Trigger: After receiving the collection results, the diagnostic server checks whether all the data items on which each diagnostic analysis point depends are ready. If they are ready, the analysis logic is triggered to generate a diagnostic conclusion.

2. The method according to claim 1, characterized in that, The method also includes an environment pre-verification step: querying the thread pool configuration parameters of the database; if the thread pool function is enabled, skipping the kernel-level observation process; and simultaneously verifying whether the target database node has deployed a kernel probe agent; if not, skipping kernel-level observation.

3. The method according to claim 1, characterized in that, The self-referencing query matching method described in step S1 specifically includes: constructing a query statement containing a random feature value, referencing the feature value in the query conditions, and simultaneously searching for the statement itself in the query results through fuzzy matching to locate the session record of the current connection in the session view and obtain its session identifier; if a single query fails to find the match, it is retried with a new random feature value until the preset maximum number of retries is reached.

4. The method according to claim 1, characterized in that, The thread identifier dynamic polling capture mentioned in step S2 includes: S21. Construct a joint query statement to perform a query that associates the database session view with the operating system thread view, using the session identifier as the filter condition to obtain the corresponding lightweight process identifier. S22. Repeatedly execute the joint query statement with a preset time interval as the polling period; S23. If the query returns a valid result, the lightweight process identifier is recorded as the target thread identifier, and the polling ends. S24. If the number of polling attempts exceeds the preset threshold and no valid result is obtained, the thread identifier capture is determined to have failed, the current diagnostic task is terminated and an exception message is returned.

5. The method according to claim 1, characterized in that, The startup instruction in step S3 includes the target thread identifier, task identifier, and probe type; the kernel probe agent records the correspondence between the observed process identifier, probe type, and target thread identifier to the process trace file; The kernel observation tools include at least one of the following types: CPU in-state analysis tools can perform CPU sampling on the target thread and generate a function call stack flame graph. CPU off-state analysis tools track the time distribution of a target thread in an off-state state and generate off-state flame diagrams; Block device I / O latency tools, which collect latency distribution histograms of block device I / O requests; Block device I / O detail tools record the device number, sector, size, and time consumption of each I / O request; Memory leak detection tools track the memory allocation and deallocation operations of a target process and identify unreleased memory blocks; A tool for handling queue latency, used to collect the latency distribution of the CPU scheduler's run queue. Page cache hit tools that count the number of page cache hits and misses; TCP connection lifecycle tools record the entire process of establishing, transmitting, and closing a TCP connection.

6. The method according to claim 5, characterized in that, The CPU in-state analysis tools and CPU out-of-state analysis tools use the target thread identifier as the direct observation target to achieve accurate sampling of specific SQL execution threads; the block device I / O latency tools, block device I / O detail tools, run queue latency tools, page cache hit tools, and TCP connection lifecycle tools run in global mode, and their collection results are combined with the SQL execution time window for post-processing filtering.

7. The method according to claim 1, characterized in that, The automatic termination of the probe in step S4 includes: S41. Send a termination signal to the corresponding kernel observation background process to stop data acquisition; S42. For probes that generate flame graphs, convert the collected function call stack data into vector graphics; S43. The collected results file is sent back to the diagnostic server via HTTP multipart protocol.

8. The method according to claim 1, characterized in that, Step S5 specifically includes: S51. The diagnostic server maintains a data storage area for each diagnostic task, wherein each collected data item is associated with a reference count value; S52. When the kernel probe agent returns the collection results, the data is stored in the storage area. S53. Traverse all diagnostic analysis points and check whether all data items that each diagnostic analysis point depends on have arrived in the storage area through the set inclusion relationship. If they are ready, immediately trigger the analysis logic of that analysis point and generate a diagnostic conclusion. S54. When the reference count of a data item reaches zero, it is automatically removed from the storage area.

9. The method according to claim 1, characterized in that, In a multi-threaded, multi-session concurrent database environment, the described method ensures that the thread identification tracking of each SQL diagnostic task and the kernel observation process are isolated from each other and do not interfere with each other.

10. A kernel-level observation system for dynamically tracking the SQL execution process in a database using PID control, characterized in that... The system runtime implements the kernel-level observation method for dynamic PID tracking database SQL execution process as described in any one of claims 1-9, including: The session identifier capture module is used to submit the SQL statement to be diagnosed to the asynchronous execution thread for execution. It captures the session identifier of the current session by querying the database session view and using a self-referencing query matching method. The thread identifier polling and capturing module is used to obtain the session identifier, and then establish a mapping relationship between the session identifier and the thread identifier by jointly querying the database session view and the operating system thread view. The target thread identifier is dynamically polled and captured with a preset time interval as the polling period. The kernel probe startup module is used to send a startup command to the kernel probe agent deployed on the database node, using the acquired thread identifier as the target parameter. The kernel probe agent selects the corresponding kernel observation tool according to the probe type, starts the observation process with the target thread identifier as the parameter, and records the correspondence between the observation process identifier, probe type and target thread identifier to the process trace file. The probe termination and result feedback module is used by the kernel probe agent to monitor the life cycle of the target thread. When the target thread is detected to have disappeared from the process list, the corresponding kernel observation process is automatically terminated and the collected results are fed back to the diagnostic server. The diagnostic trigger module is used by the diagnostic server to check whether all the data items on which each diagnostic analysis point depends are ready after receiving the collection results. If they are ready, the analysis logic is triggered to generate a diagnostic conclusion.

Images