Target transaction detection method and device

Abstract

The invention provides a target transaction detection method and device, and the method comprises the steps: obtaining a first information table which comprises a transaction information table and an active process information table, detecting the transaction information table and the active process information table, obtaining active transactions, and obtaining a target transaction from the active transactions. In this way, the target transaction can be flexibly, comprehensively and simply obtained according to the transaction information table and the activity information table, and therefore optimization of database performance is achieved.

Description

Technical Field

[0001] The present application relates to the field of database technology, and in particular to a method and device for detecting a target transaction. Background Art

[0002] In current database systems, transactions are a key factor affecting database performance and stability. As the core feature that distinguishes database systems from other file systems, transactions ensure the reliability and integrity of data processing by meeting consistency requirements (ACID features, i.e. atomicity, consistency, isolation, and persistence). However, with the complexity of database applications and the rapid growth of data volume, the problem of large transactions has become increasingly prominent and has become a bottleneck affecting database performance. The existence of large transactions will cause lock resource occupation, master-slave synchronization delays, and high rollback costs. For this reason, detecting large transactions is imminent.

[0003] In the related technologies, the existing large transaction detection focuses on post-analysis, which makes it difficult to respond in time when problems occur. It is not comprehensive enough and may miss some potential problems. In addition, the operation is complicated and is not conducive to promotion and application. Summary of the invention

[0004] The purpose of the embodiments of the present application is to provide a method and device for detecting a target transaction to optimize database performance.

[0005] In a first aspect of an embodiment of the present application, a method for detecting a target transaction is first proposed, comprising:

[0006] Acquire a first information table, wherein the first information table includes a transaction information table and an activity process information table;

[0007] Detecting the transaction information table and the active process information table to obtain active transactions;

[0008] A target transaction is obtained from the active transactions.

[0009] In an optional implementation, the detecting the transaction information table and the active process information table to obtain the active transaction includes:

[0010] Obtaining a first connection identifier contained in the transaction information table;

[0011] Obtaining a second connection identifier included in the active process information table;

[0012] Comparing the first connection identifier of the transaction information table with the second connection identifier of the active process information table;

[0013] If the first connection identifier in the transaction information table and the second connection identifier in the active process information table have the same connection identifier, the transaction corresponding to the same connection identifier is determined as the active transaction.

[0014] In an optional implementation, obtaining a target transaction from the active transaction includes:

[0015] Obtaining the transaction size of the active transaction;

[0016] comparing the transaction size of the active transaction with a preset size threshold;

[0017] If the transaction size of the active transaction is greater than or equal to the preset size threshold, the active transaction is determined as the target transaction.

[0018] In an optional embodiment, the method further comprises:

[0019] If the preset kill switch is in the on state, a kill operation is performed on the transaction of the target transaction, the kill operation includes disconnecting a connection identifier corresponding to the target transaction;

[0020] If the preset alarm switch is in the on state, target transaction alarm information is generated and reported to the alarm display page.

[0021] In an optional embodiment, the method further comprises:

[0022] Acquire a second information table, wherein the second information table includes a thread state table;

[0023] Detect the thread state table to obtain candidate SQL;

[0024] The target SQL is obtained from the candidate SQLs.

[0025] In an optional implementation, detecting the thread state table to obtain a candidate SQL includes:

[0026] Obtaining the process owner identifier in the thread status table;

[0027] Compare the process owner identifier with a preset killing user object identifier and an internal user identifier;

[0028] If the process owner ID is consistent with the internal user ID, the SQL corresponding to the process owner ID being executed is ignored;

[0029] If the process owner identifier is consistent with the preset killing user object identifier, the SQL corresponding to the process owner identifier is used as the candidate SQL.

[0030] In an optional implementation, obtaining the target SQL from the candidate SQL includes:

[0031] Obtaining the running time of the candidate SQL;

[0032] Compare the running time of the candidate SQL with a preset time threshold;

[0033] If the running time of the candidate SQL is greater than or equal to the preset time threshold, the candidate SQL is determined as the target SQL.

[0034] In an optional embodiment, the method further comprises:

[0035] The busyness of the current system is detected, and the time threshold is dynamically adjusted according to the busyness of the system to adapt to changes in the system state.

[0036] In an optional embodiment, the method further comprises:

[0037] If the preset kill switch is in the on state, a kill operation is performed on the SQL determined to be the target SQL, and the kill operation includes disconnecting the process corresponding to the target SQL;

[0038] If the preset alarm switch is in the on state, the alarm information of the target SQL is generated, and the alarm information of the target SQL is reported to the alarm display page.

[0039] In a second aspect of this embodiment, a target transaction detection device is further provided, the device comprising:

[0040] Acquisition module: Acquisition of a first information table, wherein the first information table includes a transaction information table and an activity process information table;

[0041] Processing module: detects the transaction information table and the active process information table to obtain active transactions;

[0042] Detection module: obtains a target transaction from the active transactions.

[0043] In a third aspect of an embodiment of the present invention, there is further provided an electronic device, comprising a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory communicate with each other via the communication bus;

[0044] Memory, used to store computer programs;

[0045] The processor is used to implement the steps of the target transaction detection method described in any one of the first aspects above when executing the program stored in the memory.

[0046] In a fourth aspect of an embodiment of the present invention, a storage medium is further provided, wherein instructions are stored in the storage medium, and when the storage medium is executed on a computer, the computer executes the steps of the target transaction detection method described in any one of the first aspects above.

[0047] In a fifth aspect of an embodiment of the present invention, a computer program product comprising instructions is also provided. When the computer program product is run on a computer, the computer executes the target transaction detection method described in any one of the first aspects above.

[0048] The technical solution provided by the embodiment of the present invention obtains a first information table, the first information table includes a transaction information table and an active process information table, detects the transaction information table and the active process information table, obtains an active transaction, and obtains a target transaction from the active transaction. In this way, the target transaction can be obtained flexibly, comprehensively and simply according to the transaction information table and the active information table, thereby optimizing the database performance. BRIEF DESCRIPTION OF THE DRAWINGS

[0049] The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the invention and, together with the description, serve to explain the principles of the invention.

[0050] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the drawings required for use in the embodiments or the description of the prior art will be briefly introduced below. Obviously, for ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative labor.

[0051] Figure 1 A schematic diagram of an implementation flow of a target transaction detection method provided in an embodiment of the present application;

[0052] Figure 2 A schematic diagram of an implementation flow of another target transaction detection method provided in an embodiment of the present application;

[0053] Figure 3 A schematic diagram of an implementation process of obtaining a target transaction provided in an embodiment of the present application;

[0054] Figure 4 A schematic diagram of an implementation flow of a target SQL detection method provided in an embodiment of the present application;

[0055] Figure 5 A schematic diagram of an implementation process for obtaining a target SQL provided in an embodiment of the present application;

[0056] Figure 6 A schematic diagram of the structure of a target transaction detection device provided in an embodiment of the present application;

[0057] Figure 7 It is a schematic diagram of the structure of an electronic device shown in an embodiment of the present invention. DETAILED DESCRIPTION

[0058] In order to make the purpose, technical solution and advantages of the embodiments of the present application clearer, the technical solution in the embodiments of the present application will be clearly and completely described below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by ordinary technicians in this field without making creative work are within the scope of protection of this application.

[0059] To facilitate understanding of the embodiments of the present application, further explanation will be given below with reference to specific embodiments in conjunction with the accompanying drawings. The embodiments do not constitute a limitation on the embodiments of the present application.

[0060] like Figure 1 FIG. 1 is a schematic diagram of an implementation flow of a method for detecting a target transaction provided in an embodiment of the present application. The method may specifically include the following steps:

[0061] S101, obtaining a first information table, where the first information table includes a transaction information table and an activity process information table.

[0062] In the embodiment of the present application, a first information table is obtained, and the first information table includes a transaction information table and an active process information table. The transaction information table and the active process information table may be transaction tables of the database GoldenDB, the transaction information table may be a table storing transactions in various execution states, and the active process information table may be a table storing the currently executing process and its key information, which is not limited in the present application example.

[0063] S102, checking the transaction information table and the active process information table to obtain active transactions.

[0064] In the embodiment of the present application, active transactions are obtained by detecting the transaction information table and the active process information table. Active transactions refer to transactions that exist in both tables.

[0065] For example, transaction 1 and transaction 4 exist in the transaction information table and the active process information table, transaction 2 only exists in the transaction information table, and transaction 3 only exists in the active process information table. Therefore, the active transactions are transaction 1 and transaction 4.

[0066] S103, obtaining a target transaction from active transactions.

[0067] In an embodiment of the present application, a target transaction is obtained from an active transaction, wherein the target transaction is obtained based on the active transaction, and the target transaction is a transaction that affects database performance. For example, the active transactions are transaction 1 and transaction 4, and the target transaction is obtained from transaction 1 and transaction 4, assuming that the target transaction is transaction 4.

[0068] Through the above description of the technical solution provided by the embodiment of the present application, a first information table is obtained, the first information table includes a transaction information table and an active process information table, the transaction information table and the active process information table are detected, and active transactions are obtained, and target transactions are obtained from active transactions. In this way, the target transaction can be obtained flexibly, comprehensively and easily according to the transaction information table and the active process information table, thereby optimizing the database performance.

[0069] like Figure 2 FIG. 1 is a schematic diagram of an implementation flow of another target transaction detection method provided in an embodiment of the present application, which may specifically include the following steps:

[0070] S201, obtaining a first information table, where the first information table includes a transaction information table and an activity process information table.

[0071] In the embodiment of the present application, this step is similar to the above-mentioned step S101, that is, obtaining the first information table, including the transaction information table and the activity process information table. Therefore, the embodiment of the present application will not be described one by one here.

[0072] S202: Obtain a first connection identifier included in the transaction information table.

[0073] In an embodiment of the present application, a first connection identifier included in a transaction information table is obtained, wherein the transaction information table may include information such as a transaction ID (Identity, abbreviated as: unique code), a connection ID, and a transaction size, and the first connection identifier may be a connection ID in the transaction information table, which is not limited in the present application example.

[0074] S203: Obtain a second connection identifier included in the active process information table.

[0075] In an embodiment of the present application, a second connection identifier included in the active process information table is obtained, wherein the active process information table may include information such as a connection ID, and the second connection identifier may be a connection ID in the active process information table, which is not limited in the present application example.

[0076] S204: Compare the first connection identifier in the transaction information table with the second connection identifier in the active process information table.

[0077] In this embodiment, the first connection identifier included in the acquired transaction information table is compared with the second connection identifier included in the acquired active process information table.

[0078] For example, as shown in Table 1, a connection identifier comparison schematic table provided in an embodiment of the present application, the transaction connection identifier column corresponds to the connection ID of the transaction information table, and the process connection identifier column corresponds to the connection ID of the active process information table. The connection ID (transaction identifier 1, transaction identifier 2, transaction identifier 3) of the transaction information table is compared with the connection ID (process identifier 1, process identifier 2, process identifier 3) of the active process information table.

[0079] Transaction connection identifier Process connection ID Transaction ID 1 Process ID 1 Transaction ID 2 Process ID 2 Transaction ID 3 Process ID 3

[0080] Table 1

[0081] S205: If the first connection identifier in the transaction information table and the second connection identifier in the active process information table have the same connection identifier, determine the transaction corresponding to the same connection identifier as an active transaction.

[0082] In an embodiment of the present application, if the first connection identifier of the transaction information table and the second connection identifier of the active process information table have the same connection identifier, the transaction corresponding to the same connection identifier is determined as an active transaction; if the first connection identifier of the transaction information table and the second connection identifier of the active process information table do not have the same connection identifier, then there is no active transaction.

[0083] For example, as shown in Table 1, if transaction identifier 1 is the same as process identifier 1, transaction identifier 2 is the same as process identifier 2, and transaction identifier 3 is different from process identifier 3, then the first connection identifier in the transaction information table and the second connection identifier in the active process information table have the same connection ID (transaction identifier 1, transaction identifier 2), and the transactions corresponding to transaction identifier 1 and transaction identifier 2 are determined as active transactions.

[0084] S206, obtaining a target transaction from active transactions.

[0085] In the embodiment of the present application, the target transaction is obtained based on the active transaction, and the target transaction is a transaction that affects the database performance.

[0086] For example, the active transactions are transaction 1 and transaction 4. The target transaction is obtained from transaction 1 and transaction 4. Assuming that the target transaction is transaction 4 and transaction 4 is a transaction that affects database performance, transaction 4 can be regarded as a large transaction.

[0087] Specifically, obtain the target transaction from the active transaction, such as Figure 3 As shown, it is a schematic diagram of an implementation process of obtaining a target transaction provided by an embodiment of the present application, which may specifically include the following steps:

[0088] S301, obtaining the transaction size of the active transaction.

[0089] In an embodiment of the present application, the transaction size of the active transaction is obtained, wherein the transaction size can be obtained through a transaction information table, which is not limited in the example of the present application.

[0090] S302: Compare the transaction size of the active transaction with a preset size threshold.

[0091] In an embodiment of the present application, the transaction size of the active transaction is compared with a preset size threshold, wherein the size threshold may be manually set according to specific circumstances, and the example of the present application is not limited to this.

[0092] For example, as shown in Table 1, if the transactions corresponding to transaction identifiers 1 and 2 are active transactions, the transaction sizes of the transactions corresponding to transaction identifiers 1 and 2 are compared with a preset size threshold.

[0093] S303: If the transaction size of the active transaction is greater than or equal to a preset size threshold, the active transaction is determined as a target transaction.

[0094] In an embodiment of the present application, if the transaction size of the active transaction is greater than or equal to a preset size threshold, the active transaction is determined as a target transaction; if the transaction size of the active transaction is less than the preset size threshold, the active transaction is ignored.

[0095] For example, as shown in Table 1, if the transactions corresponding to transaction identifier 1 and transaction identifier 2 are active transactions, and the transaction sizes of the transactions corresponding to transaction identifier 1 and transaction identifier 2 are greater than a preset size threshold, then the transactions corresponding to transaction identifier 1 and transaction identifier 2 are target transactions (or large transactions); if the transaction size corresponding to transaction identifier 1 is greater than the preset size threshold, and the transaction size of the transaction corresponding to transaction identifier 2 is less than the preset size threshold, then the transaction corresponding to transaction identifier 1 is the target transaction, and the transaction corresponding to transaction identifier 2 is not the target transaction.

[0096] In addition, after the target transaction is detected, the following operations can be performed to improve database performance:

[0097] If the preset kill switch is in the on state, a kill operation is performed on the transaction of the target transaction, and the kill operation includes disconnecting the connection identifier corresponding to the target transaction.

[0098] Specifically, when the preset kill switch is on, the transaction corresponding to the target transaction is killed, and the connection identifier corresponding to the target transaction is disconnected. When the preset kill switch is off, there is no need to perform a kill operation on the transaction corresponding to the target transaction.

[0099] For example, when the preset kill switch is on, transaction 1 corresponding to target transaction 1 needs to execute a kill operation, that is, disconnect the connection identifier corresponding to transaction 1, thereby completing the killing of transaction 1 and optimizing database performance; when the preset kill switch is off, transaction 1 corresponding to the target transaction does not need to execute a kill operation.

[0100] If the preset alarm switch is in the on state, the target transaction alarm information is generated and reported to the alarm display page.

[0101] Specifically, when the preset alarm switch is in the on state, the alarm information corresponding to the target transaction will be generated, and the alarm information will be reported to the alarm display page. When the preset alarm switch is in the off state, the alarm information corresponding to the target transaction does not need to be generated.

[0102] For example, when the preset alarm switch is on, transaction 1 corresponding to target transaction 1 needs to perform an alarm operation, that is, report the alarm information corresponding to transaction 1 to the alarm display page, thereby completing the alarm operation of transaction 1; when the preset alarm switch is off, transaction 1 corresponding to the target transaction does not need to perform an alarm operation.

[0103] Corresponding to the above method embodiment, the present application embodiment also provides a method for detecting a target SQL, such as Figure 4 As shown, it is a schematic diagram of an implementation process of a target SQL detection method provided in an embodiment of the present application, which may specifically include the following steps:

[0104] S401, obtaining a second information table, where the second information table includes a thread state table.

[0105] In an embodiment of the present application, a second information table is obtained, and the second information table includes a thread status table. The second information table may be a thread status table, and the thread status table may include information such as process ID, process owner ID, command line, running time, process status, etc., which is not limited in the present application example.

[0106] S402, checking the thread status table to obtain candidate SQL.

[0107] In the embodiment of the present application, for the thread state table, the thread state table is detected to obtain the candidate SQL.

[0108] Specifically, detecting the thread state table to obtain the candidate SQL may include the following steps:

[0109] Get the process owner ID from the thread state table.

[0110] Among them, the process owner identifier in the thread status table is obtained. The process owner identifier can be the process owner id in the thread status table, and this application example does not limit this.

[0111] For example, when there are 5 processes in the thread status table (process 1, process 2, process 3, process 4, process 5), the owner IDs corresponding to process 1, process 2, process 3, process 4, and process 5 will be obtained.

[0112] The process owner ID is compared with the preset kill user object ID and the internal user ID.

[0113] In an embodiment of the present application, the killing user object identifier can be manually set to identify a user with killing authority; and an internal user can be a user without killing authority, which is not limited in the present application example.

[0114] If the process owner ID is the same as the internal user ID, the SQL corresponding to the process owner ID being executed is ignored.

[0115] Specifically, when the process owner id is consistent with the internal user id, the SQL corresponding to the process owner id being executed is not a candidate SQL, and this SQL is ignored.

[0116] If the process owner identifier is consistent with the preset killing user object identifier, the SQL corresponding to the process owner identifier is used as the candidate SQL.

[0117] Specifically, when the process owner id is consistent with the internal user id, the SQL corresponding to the process owner id being executed is not a candidate SQL and is ignored; when the process owner id is consistent with the tracing user object id, the SQL corresponding to the process owner id is a candidate SQL. For example, when the process owner id1 is consistent with the internal user id1, the process owner id2 is consistent with the tracing user object id2, and the process owner id3 is consistent with the tracing user object id3, then SQL1 corresponding to process owner id1 is not a candidate SQL, and SQL2 corresponding to process owner id2 and SQL3 corresponding to process owner id3 are candidate SQLs.

[0118] S403, obtaining a target SQL from the candidate SQLs.

[0119] In the embodiment of the present application, for the candidate SQLs, the target SQL is obtained from the candidate SQLs.

[0120] Specifically, from the candidate SQL, obtain the target SQL, such as Figure 5 As shown, it is a schematic diagram of an implementation process of obtaining a target SQL provided in an embodiment of the present application, which may specifically include the following steps:

[0121] S501, obtaining the running time of the candidate SQL.

[0122] Among them, the running time can be obtained from the thread status table, and this application example does not limit this.

[0123] For example, in the thread state table, the running time corresponding to the candidate SQL1 is 1 minute, and the running time of the obtained candidate SQL1 is 1 minute.

[0124] S502: Compare the running time of the candidate SQL with a preset time threshold.

[0125] In the embodiment of the present application, the time threshold can be set manually, and the example of the present application is not limited to this.

[0126] S503: If the running time of the candidate SQL is greater than or equal to a preset time threshold, the candidate SQL is determined as a target SQL.

[0127] Specifically, SQL2 and SQL3 are candidate SQLs. If the running time of SQL2 is greater than a preset time threshold and the running time of SQL3 is less than the preset time threshold, SQL2 is the target SQL (or slow SQL).

[0128] In addition, the current system busyness can be detected, and the time threshold can be dynamically adjusted according to the system busyness to adapt to changes in the system status.

[0129] After the target SQL is detected, you can also perform the following operations to improve database performance:

[0130] If the preset kill switch is in the on state, a kill operation is performed on the SQL determined as the target SQL, and the kill operation includes disconnecting the process corresponding to the target SQL.

[0131] Specifically, when the preset kill switch is on, the SQL corresponding to the target SQL is killed, and the connection identifier corresponding to the target SQL is disconnected. When the preset kill switch is off, there is no need to perform a kill operation on the SQL corresponding to the target SQL.

[0132] For example, when the preset kill switch is on, the SQL1 corresponding to the target SQL1 needs to perform a kill operation, that is, disconnect the connection ID corresponding to SQL1, thereby completing the killing of SQL1 and optimizing the database performance; when the preset kill switch is off, the SQL1 corresponding to the target SQL does not need to perform a kill operation.

[0133] If the preset alarm switch is turned on, the alarm information of the target SQL will be generated and reported to the alarm display page.

[0134] Specifically, when the preset alarm switch is turned on, the alarm information corresponding to the target SQL will be generated and reported to the alarm display page. When the preset alarm switch is turned off, the alarm information corresponding to the target SQL does not need to be generated.

[0135] For example, when the preset alarm switch is on, SQL1 corresponding to the target SQL1 needs to perform an alarm operation, that is, report the alarm information corresponding to SQL1 to the alarm display page, thereby completing the alarm operation of SQL1; when the preset alarm switch is off, SQL1 corresponding to the target SQL does not need to perform an alarm operation.

[0136] By checking and killing the detected target SQL, the database performance can be optimized.

[0137] In the embodiment of the present application, the parameter configuration and processing process, especially the parameter configuration and parameter processing before the large transaction detection method, is described in detail using the GoldenDB database as an example. In addition, it also includes a dynamic adjustment mechanism of the system busyness to the time threshold, and detailed steps on how the system obtains the current busyness.

[0138] Parameter configuration

[0139] Parameter configuration is mainly performed on the Insight (intelligent operation and maintenance system of distributed database clusters) page. These parameters include but are not limited to:

[0140] Detection cycle: It is divided into target transaction detection cycle and target SQL detection cycle, which are used to periodically detect target transactions and target SQL.

[0141] Time threshold: used to filter target SQL statements through candidate SQL statements.

[0142] Size threshold: used to filter target transactions by active transactions.

[0143] Internal users and detection users: artificially preset users used to detect target SQL.

[0144] Alarm switch: includes target transaction alarm switch and target SQL alarm switch. The alarm switch is used to alarm the detected target transaction or target SQL. When the target transaction or target SQL is obtained and the alarm switch is turned on, the alarm information of the target transaction or target SQL is reported to the alarm display page.

[0145] Kill switch: including target transaction kill switch and target SQL kill switch. The kill switch is used to select whether to kill the detected target transaction or target SQL. When the kill switch is turned on, the target transaction or target SQL can be killed to kill the thread ID corresponding to the target transaction or target SQL, thereby optimizing database performance.

[0146] Parameter processing

[0147] The parameter processing process involves multiple components, including MDS (MetaDataServer, referred to as metadata and task management center) node, CM (ClusterManager, referred to as cluster management center), DBAgent (DataNode Agent, referred to as data node agent), primary DN (DataNode, referred to as data node), OMMAgent (Operations MonitoringManagement Agent, referred to as component agent)

[0148] Insight page configuration: Users send the configured parameters on the Insight page to the MDS node through the signal channel.

[0149] MDS node storage: The MDS node transfers the received parameters to the metadata RDB table for storage.

[0150] CM reception and distribution: The RDB table sends parameters to the CM through the message interface. The CM is responsible for receiving and processing the change messages of cluster configuration parameters, and then sends the corresponding configuration item values ​​to the DBAgent through the message interface.

[0151] DBAgent conversion and application: Some parameters are of string type during message transmission. After receiving the parameters, DBAgent converts some string type parameters to integer type, and the switches on the Insight interface are also converted to integer type.

[0152] When the user modifies the parameters through the interface, the parameters are changed, and the DBAgent receives the parameter changes. The parameter changes are detection switches or parameter changes. A new timer is created. The timer is used to ensure that the target transaction and target SQL are automatically checked and killed at specific intervals.

[0153] DBAgent is a daemon process used to monitor and start and stop database instances. It connects to the data node DN through DBAgent to detect and kill target transactions and target SQL, save target transactions and target SQL that affect database performance, integrate and filter the queried large transactions and slow SQL information, and choose whether to kill according to the configured kill switch and whether to perform alarm processing according to the configured alarm switch.

[0154] If the kill switch is on, the kill is performed. The DBAgent connects to the primary DN and performs a KILL operation to kill the thread ID corresponding to the large transaction or slow SQL.

[0155] If the alarm switch is on, an alarm will be issued, and DBAgent will report the alarm information to OMMAgent, which will report the alarm information to Insight for display on the page. If the target transaction detection is performed, the transaction id, thread id, currently executed SQL statement, transaction status, transaction size and other information will be reported as alarm information; if the target SQL is detected, the thread id, SQL statement, and execution time will be reported as alarm information.

[0156] System busyness and time threshold adjustment

[0157] When performing target SQL detection, the time threshold can be adjusted accordingly according to the system's busyness to improve the flexibility and accuracy of detection, including:

[0158] System busyness judgment

[0159] When the system busyness is less than 30%, the system is in idle state; when the system busyness is between 30% and 70%, the system is in normal state; when the system busyness is greater than 70%, the system is in busy state.

[0160] Time threshold adjustment

[0161] You can choose whether to adjust the time threshold for detecting the target SQL based on the current system busyness. When the system is busy, the time threshold can be automatically reduced to lower the tolerance for the target SQL so that problems can be discovered more quickly.

[0162] When the system is idle, the time threshold can be automatically expanded to increase the tolerance for the target SQL and reduce unnecessary alarms.

[0163] Get system busyness

[0164] Each server obtains the instantaneous values ​​of CPU, memory, disk latency, and network card rate at the sampling time.

[0165] Within a statistical granularity (such as every minute or every hour), calculate the average value (for CPU and memory) or maximum value (for disk latency) and average value (for network card rate) of these indicators.

[0166] Based on the value of each indicator, its level (such as low, medium, high) is calculated separately.

[0167] Finally, the highest level among the four indicators is taken as the busyness level of the current server.

[0168] Automatic modification time threshold

[0169] Insight obtains the current server busyness and automatically modifies it according to the parameter delivery process described above. When the system status is idle, the time threshold is increased, and when the system status is busy, the time threshold is reduced.

[0170] In this way, the system can dynamically adjust the time threshold according to the current busyness, thereby reducing unnecessary alarms and interference while ensuring detection efficiency.

[0171] Since large transactions involve the reading and writing of a large amount of data, they often hold a large number of lock resources, which can easily lead to lock blocking and lock timeout, thus affecting the execution of other concurrent transactions. The execution time of large transactions is long, which will significantly extend the data synchronization delay between the standby machine and the master machine, affecting the high availability and data consistency of the database. If a large transaction encounters an exception during execution and needs to be rolled back, its rollback operation will also consume a lot of time and resources, placing an additional burden on the database system. In addition, slow SQL will occupy system resources such as CPU and memory for a long time, causing the response speed of the database server to decrease and affecting the overall performance. The execution of slow SQL will block the execution of other SQL statements, resulting in increased delays in business operations, especially for high-concurrency business scenarios. This impact is particularly significant. If slow SQL occurs frequently, it will occupy a large number of database connection resources, causing other users to be unable to access the database normally, causing the risk of service interruption. The above method can be used to automatically detect and kill large transactions and slow SQL on the database master DN, aiming to release database resources, improve database performance and stability, and ensure global consistency and master-slave consistency.

[0172] This application implements the automatic killing technology of large transactions and slow SQL by detecting the target transaction and target SQL, reduces the burden of manual troubleshooting that affects database performance, and improves database performance. The embodiment of this application performs automatic killing operations on the primary DN, automatically kills the detected target transaction and target SQL, and after the killing is completed, avoids data inconsistencies between the backup machine and the host by quickly rolling back the data of the host, reduces delays, and ensures global consistency of data by using the same parameters in each primary DN in the same cluster, that is, meets the requirements of database consistency. At the same time, since the parameters are designed to be optional, configurable, and independent of each other, users can flexibly configure the parameters.

[0173] Corresponding to the above method embodiment, the present application embodiment also provides a target transaction detection device, such as Figure 6 As shown, the device may include a table group acquisition module 601 , a processing module 602 , and a detection module 603 .

[0174] Acquisition module 601: Acquisition of a first information table, the first information table including a transaction information table and an activity process information table;

[0175] Processing module 602: Detect the transaction information table and the active process information table to obtain active transactions;

[0176] Detection module 603: Obtain a target transaction from active transactions.

[0177] The embodiment of the present invention further provides an electronic device, such as Figure 7 As shown, it includes a processor 701, a communication interface 702, a memory 703 and a communication bus 704, wherein the processor 701, the communication interface 702, and the memory 703 communicate with each other through the communication bus 704.

[0178] Memory 703, used for storing computer programs;

[0179] In one embodiment of the present invention, the processor 701, when used to execute the program stored in the memory 703, implements the following steps:

[0180] A first information table is obtained, the first information table includes a transaction information table and an active process information table, the transaction information table and the active process information table are detected, an active transaction is obtained, and a target transaction is obtained from the active transaction.

[0181] The communication bus mentioned in the above electronic device can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. The communication bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus.

[0182] The communication interface is used for communication between the above electronic device and other devices.

[0183] The memory may include a random access memory (RAM) or a non-volatile memory, such as at least one disk memory. Optionally, the memory may also be at least one storage device located away from the aforementioned processor.

[0184] The above-mentioned processor can be a general-purpose processor, including a central processing unit (CPU), a network processor (NP), etc.; it can also be a digital signal processor (DSP), an application specific integrated circuit (ASIC), a field programmable gate array (FPGA) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0185] In another embodiment of the present invention, a storage medium is provided. The storage medium stores instructions. When the storage medium is executed on a computer, the computer executes the target transaction detection method described in any one of the above embodiments.

[0186] In another embodiment provided by the present invention, a computer program product including instructions is also provided, which, when executed on a computer, enables the computer to execute the target transaction detection method described in any one of the above embodiments.

[0187] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented by software, it can be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, the process or function described in the embodiment of the present invention is generated in whole or in part. The computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable device. The computer instructions can be stored in a storage medium, or transmitted from one storage medium to another storage medium. For example, the computer instructions can be transmitted from one website site, computer, server or data center to another website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (DSL)) or wireless (e.g., infrared, wireless, microwave, etc.). The storage medium can be any available medium that can be accessed by a computer or a data storage device such as a server or data center that includes one or more available media integrated. The available medium can be a magnetic medium (e.g., a floppy disk, a hard disk, a tape), an optical medium (e.g., a DVD), or a semiconductor medium (e.g., a solid-state hard disk Solid State Disk (SSD)), etc.

[0188] It should be noted that, in this article, relational terms such as first and second, etc. are only used to distinguish one entity or operation from another entity or operation, and do not necessarily require or imply any such actual relationship or order between these entities or operations. Moreover, the terms "include", "comprise" or any other variants thereof are intended to cover non-exclusive inclusion, so that a process, method, article or device including a series of elements includes not only those elements, but also other elements not explicitly listed, or also includes elements inherent to such process, method, article or device. In the absence of further restrictions, the elements defined by the sentence "comprise a ..." do not exclude the existence of other identical elements in the process, method, article or device including the elements.

[0189] Each embodiment in this specification is described in a related manner, and the same or similar parts between the embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. In particular, for the system embodiment, since it is basically similar to the method embodiment, the description is relatively simple, and the relevant parts can be referred to the partial description of the method embodiment.

[0190] The above description is only a preferred embodiment of the present application and is not intended to limit the protection scope of the present application. Any modification, equivalent replacement, improvement, etc. made within the spirit and principle of the present application are included in the protection scope of the present application.

Claims

1. A method for detecting a target transaction, characterized in that: The method comprises: Acquire a first information table, wherein the first information table includes a transaction information table and an activity process information table; Detecting the transaction information table and the active process information table to obtain active transactions; A target transaction is obtained from the active transactions.

2. The method according to claim 1, characterized in that The detecting the transaction information table and the active process information table to obtain active transactions includes: Obtaining a first connection identifier contained in the transaction information table; Obtaining a second connection identifier included in the active process information table; Comparing the first connection identifier of the transaction information table with the second connection identifier of the active process information table; If the first connection identifier in the transaction information table and the second connection identifier in the active process information table have the same connection identifier, the transaction corresponding to the same connection identifier is determined as the active transaction.

3. The method according to claim 1, characterized in that The acquiring a target transaction from the active transaction includes: Obtaining the transaction size of the active transaction; comparing the transaction size of the active transaction with a preset size threshold; If the transaction size of the active transaction is greater than or equal to the preset size threshold, the active transaction is determined as the target transaction.

4. The method according to claim 1, characterized in that: The method further comprises: If the preset kill switch is in the on state, a kill operation is performed on the transaction of the target transaction, the kill operation includes disconnecting a connection identifier corresponding to the target transaction; If the preset alarm switch is in the on state, target transaction alarm information is generated and reported to the alarm display page.

5. The method according to claim 1, characterized in that: The method further comprises: Acquire a second information table, wherein the second information table includes a thread state table; Detect the thread state table to obtain candidate SQL; The target SQL is obtained from the candidate SQLs.

6. The method according to claim 5, characterized in that The detecting the thread state table to obtain a candidate SQL comprises: Obtaining the process owner identifier in the thread status table; Compare the process owner identifier with a preset killing user object identifier and an internal user identifier; If the process owner ID is consistent with the internal user ID, the SQL corresponding to the process owner ID being executed is ignored; If the process owner identifier is consistent with the preset killing user object identifier, the SQL corresponding to the process owner identifier is used as the candidate SQL.

7. The method according to claim 5, characterized in that The step of obtaining a target SQL from the candidate SQLs includes: Obtaining the running time of the candidate SQL; Compare the running time of the candidate SQL with a preset time threshold; If the running time of the candidate SQL is greater than or equal to the preset time threshold, the candidate SQL is determined as the target SQL.

8. The method according to claim 5, characterized in that The method further comprises: The busyness of the current system is detected, and the time threshold is dynamically adjusted according to the busyness of the system to adapt to changes in the system state.

9. The method according to claim 5, characterized in that The method further comprises: If the preset kill switch is in the on state, a kill operation is performed on the SQL determined to be the target SQL, and the kill operation includes disconnecting the process corresponding to the target SQL; If the preset alarm switch is in the on state, the alarm information of the target SQL is generated, and the alarm information of the target SQL is reported to the alarm display page.

10. A target transaction detection device, characterized in that: include: Acquisition module: Acquisition of a first information table, wherein the first information table includes a transaction information table and an activity process information table; Processing module: detects the transaction information table and the active process information table to obtain active transactions; Detection module: obtains a target transaction from the active transactions.

Images