CN116126890A - Positioning method and device of abnormal SQL statement, electronic equipment and storage medium - Google Patents

Positioning method and device of abnormal SQL statement, electronic equipment and storage medium Download PDF

Info

Publication number
CN116126890A
CN116126890A CN202211726734.9A CN202211726734A CN116126890A CN 116126890 A CN116126890 A CN 116126890A CN 202211726734 A CN202211726734 A CN 202211726734A CN 116126890 A CN116126890 A CN 116126890A
Authority
CN
China
Prior art keywords
serial number
transaction
sql statement
service request
transaction serial
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN202211726734.9A
Other languages
Chinese (zh)
Inventor
董结旺
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
Jinzhuan Xinke Co Ltd
Original Assignee
Jinzhuan Xinke Co Ltd
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by Jinzhuan Xinke Co Ltd filed Critical Jinzhuan Xinke Co Ltd
Priority to CN202211726734.9A priority Critical patent/CN116126890A/en
Publication of CN116126890A publication Critical patent/CN116126890A/en
Pending legal-status Critical Current

Links

Images

Classifications

    • YGENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
    • Y02TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
    • Y02DCLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
    • Y02D10/00Energy efficient computing, e.g. low power processors, power management or thermal management

Landscapes

  • Information Retrieval, Db Structures And Fs Structures Therefor (AREA)

Abstract

The invention discloses a positioning method and device of abnormal SQL sentences, electronic equipment and a storage medium, and belongs to the field of distributed database application. Wherein the method comprises the following steps: 1) The user defines abnormal SQL scenes, such as data definition language DDL/data operation language DML card slow sentences, overlarge scanning record quantity and the like. 2) Receiving a service request sent by a client, acquiring a transaction serial number TSN corresponding to the service request as a main line, and completely marking SQL sentences and resource use conditions thereof in the service request by combining other identification information; 3) Carrying out association according to the identification information to generate a statistical report; 4) And finally locating the abnormal SQL statement. The method and the device solve the technical problems of low efficiency in the related art of locating abnormal SQL sentences by means of manual analysis.

Description

Positioning method and device of abnormal SQL statement, electronic equipment and storage medium
Technical Field
The invention relates to the technical field of distributed database application, in particular to a positioning method and device of abnormal SQL sentences, electronic equipment and a storage medium.
Background
Each service request needs to pass through four levels of a client, a network, a database and a basic resource, and the levels are mutually independent. The client side often feeds back slow or abnormal errors of the service operation card, which is usually caused by problems on the database side, especially for the distributed database side, a plurality of nodes are involved: computing nodes (CN, computer Node), data nodes (DN, data Node), transaction nodes (GTM, general Transition Manager) and management nodes MN, even deployed across cities, across machine rooms. Under such complex networking, it is difficult to quickly locate which of the possible transactions in the distributed database is specifically, which of the possible structured query language (SQL, structure Query Language) anomalies caused, and thus the problem cannot be solved in time.
In the related art, database administrators (DBA, database Administrator) can only passively check slow logs on each node and analyze binlog (binary log) to locate problems, and the database administrators have huge data volume, such as the fishing of needles in the sea, and have huge workload and low problem locating efficiency by manual processing.
In view of the above problems in the related art, no effective solution has been found yet.
Disclosure of Invention
The invention provides a method, a device, electronic equipment and a storage medium for locating abnormal SQL sentences, which are used for solving the technical problem that the efficiency is low because the related technology relies on manual analysis to locate the abnormal SQL sentences.
According to one aspect of the embodiment of the application, there is provided a method for locating an abnormal SQL statement, including: receiving a service request sent by a client, and acquiring a transaction serial number corresponding to the service request, wherein the transaction serial number is used for identifying an SQL statement in the service request; correlating the index information corresponding to the SQL statement obtained by the target node in the distributed database with the transaction serial number, and generating a statistical statement; and positioning the abnormal SQL statement according to the statistical report.
Further, the target node includes a data node, associating the index information corresponding to the SQL statement counted by the target node in the distributed database with the transaction serial number, and generating the statistical report includes: acquiring execution information corresponding to the SQL statement executed by the data node, and acquiring a global transaction ID corresponding to the SQL statement, wherein the execution information is used as first index information; and associating the first index information with the transaction serial number and the global transaction ID to generate a first statistical report.
Further, the target node comprises a computing node, and before obtaining the execution information corresponding to the execution of the SQL statement by the data node, the method further comprises: applying for a global transaction ID from a global transaction node in the distributed database through the computing node; and sending the SQL statement carrying the global transaction ID to the data node corresponding to the global transaction ID.
Further, locating the abnormal SQL statement according to the statistical report includes: acquiring a preset abnormal SQL scene condition, and judging whether index information in the statistical report accords with the preset abnormal SQL scene condition; if the index information in the statistical report accords with the preset abnormal SQL scene conditions, positioning the index information which accords with the preset abnormal SQL scene conditions into abnormal SQL sentences, and outputting corresponding abnormal alarm prompts.
Further, before the transaction serial number corresponding to the service request is obtained, the method further includes: detecting whether the service request carries a first service serial number or not; if the service request carries a first transaction serial number, the first transaction serial number is used as the transaction serial number corresponding to the service request; if the service request does not carry the first transaction serial number, a second transaction serial number is generated, the second transaction serial number is used as the transaction serial number corresponding to the service request, and the second serial number comprises a computing node identifier, a session ID and a time stamp.
Further, the target node includes a computing node, associating the index information corresponding to the SQL statement counted by the target node in the distributed database with the transaction serial number, and generating the statistical report includes: the method comprises the steps of obtaining session information, transaction information, link information and global transaction ID (identity) corresponding to the SQL statement, which are obtained by a computing node, wherein the session information, the transaction information, the link information and the global transaction ID are used as second index information; and associating the second index information with the transaction serial number to generate a second statistical report.
Further, associating the index information corresponding to the SQL statement obtained by the target node in the distributed database with the transaction serial number, and generating the statistical report includes: acquiring resource information which is monitored by a host and corresponds to the SQL statement through a target node, wherein the resource information is used as third index information, and the resource information comprises a process ID, a client ID and a CPU utilization rate; and associating the third index information with the transaction serial number to generate a third statistical report.
According to another aspect of the embodiments of the present application, there is also provided a positioning device for an abnormal SQL statement, including: the receiving module is used for receiving a service request sent by a client and acquiring a transaction serial number corresponding to the service request, wherein the transaction serial number is used for identifying an SQL statement in the service request; the association module is used for associating the index information corresponding to the SQL statement acquired by the target node in the distributed database with the transaction serial number and generating a statistical report; and the positioning module is used for positioning the abnormal SQL statement according to the statistical report.
Further, the association module comprises a first association unit, a second association unit and a third association unit, wherein the first association unit is used for acquiring execution information corresponding to the SQL statement executed by the data node, and acquiring a global transaction ID corresponding to the SQL statement, and the execution information is used as first index information; and associating the first index information with the transaction serial number and the global transaction ID to generate a first statistical report.
Further, the first association unit is further configured to apply, by the computing node, a global transaction ID to a global transaction node in the distributed database; and sending the SQL statement carrying the global transaction ID to the data node corresponding to the global transaction ID.
Further, the positioning module comprises a first positioning unit, a second positioning unit and a third positioning unit, wherein the first positioning unit is used for acquiring preset abnormal SQL scene conditions and judging whether index information in the statistical report accords with the preset abnormal SQL scene conditions or not; if the index information in the statistical report accords with the preset abnormal SQL scene conditions, positioning the index information which accords with the preset abnormal SQL scene conditions into abnormal SQL sentences, and outputting corresponding abnormal alarm prompts.
Further, the positioning device of the abnormal SQL statement further comprises a detection module, which is used for detecting whether the service request carries a first service serial number; if the service request carries a first transaction serial number, the first transaction serial number is used as the transaction serial number corresponding to the service request; if the service request does not carry the first transaction serial number, a second transaction serial number is generated, the second transaction serial number is used as the transaction serial number corresponding to the service request, and the second serial number comprises a computing node identifier, a session ID and a time stamp.
Further, the association module comprises a second association unit, which is used for acquiring session information, transaction information, link information and global transaction ID (identity) corresponding to the SQL statement acquired by the computing node, wherein the session information, the transaction information, the link information and the global transaction ID are used as second index information; and associating the second index information with the transaction serial number to generate a second statistical report.
Further, the association module comprises a third association unit, wherein the third association unit is used for acquiring resource information which is monitored by the host and corresponds to the SQL statement through the target node, the resource information is used as third index information, and the resource information comprises a process ID, a client ID and a CPU utilization rate; and associating the third index information with the transaction serial number to generate a third statistical report.
According to another aspect of the embodiments of the present application, there is also provided a storage medium including a stored program that performs the steps described above when running.
According to another aspect of the embodiments of the present application, there is also provided an electronic device, including a processor, a communication interface, a memory, and a communication bus, where the processor, the communication interface, and the memory complete communication with each other through the communication bus; wherein: a memory for storing a computer program; and a processor for executing the steps of the method by running a program stored on the memory.
Embodiments of the present application also provide a computer program product comprising instructions which, when run on a computer, cause the computer to perform the steps of the above method.
According to the invention, the service request sent by the client is received, the transaction serial number corresponding to the service request is obtained, the transaction serial number is used for identifying the SQL statement in the service request, the index information corresponding to the SQL statement obtained by the target node in the distributed database is associated with the transaction serial number, a statistical report is generated, and the abnormal SQL statement is positioned according to the statistical report. The information from the client to each node in the distributed database is associated, rather than isolated, so that the analysis of slow logs, transaction logs and the like from each node is avoided, and the capability of automatic positioning is greatly improved.
Drawings
The accompanying drawings, which are included to provide a further understanding of the invention and are incorporated in and constitute a part of this application, illustrate embodiments of the invention and together with the description serve to explain the invention and do not constitute a limitation on the invention. In the drawings:
FIG. 1 is a block diagram of the hardware architecture of a computer according to an embodiment of the present invention;
FIG. 2 is a flow chart of a method of locating an abnormal SQL statement according to an embodiment of the invention;
FIG. 3 is a schematic diagram of the location of a distributed database in a network according to an embodiment of the present invention;
FIG. 4 is a schematic diagram of a distributed database service SQL process flow according to an embodiment of the invention;
FIG. 5 is a block diagram of a localization device of an abnormal SQL statement according to an embodiment of the invention.
Detailed Description
In order to make the present application solution better understood by those skilled in the art, the following description will be made in detail and with reference to the accompanying drawings in the embodiments of the present application, it is apparent that the described embodiments are only some embodiments of the present application, not all embodiments. All other embodiments, which can be made by one of ordinary skill in the art based on the embodiments herein without making any inventive effort, shall fall within the scope of the present application. It should be noted that, in the case of no conflict, the embodiments and features in the embodiments may be combined with each other.
It should be noted that the terms "first," "second," and the like in the description and claims of the present application and the above figures are used for distinguishing between similar objects and not necessarily for describing a particular sequential or chronological order. It is to be understood that the data so used may be interchanged where appropriate such that embodiments of the present application described herein may be implemented in sequences other than those illustrated or otherwise described herein. Furthermore, the terms "comprises," "comprising," and "having," and any variations thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of steps or elements is not necessarily limited to those steps or elements expressly listed but may include other steps or elements not expressly listed or inherent to such process, method, article, or apparatus.
Example 1
The method embodiment provided in the first embodiment of the present application may be executed in a mobile phone, a computer, a tablet or a similar computing device. Taking a computer as an example, fig. 1 is a block diagram of a hardware structure of a computer according to an embodiment of the present invention. As shown in fig. 1, the computer may include one or more processors 102 (only one is shown in fig. 1) (the processor 102 may include, but is not limited to, a microprocessor MCU or a processing device such as a programmable logic device FPGA) and a memory 104 for storing data, and optionally, a transmission device 106 for communication functions and an input-output device 108. It will be appreciated by those of ordinary skill in the art that the configuration shown in FIG. 1 is merely illustrative and is not intended to limit the configuration of the computer described above. For example, the computer may also include more or fewer components than shown in FIG. 1, or have a different configuration than shown in FIG. 1.
The memory 104 may be used to store a computer program, for example, a software program of application software and a module, such as a computer program corresponding to a video dynamic and static rate identifying method in an embodiment of the present invention, and the processor 102 executes the computer program stored in the memory 104 to perform various functional applications and data processing, that is, implement the method described above. Memory 104 may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some examples, memory 104 may further include memory located remotely from processor 102, which may be connected to the computer via a network. Examples of such networks include, but are not limited to, the internet, intranets, local area networks, mobile communication networks, and combinations thereof.
The transmission device 106 is used to receive or transmit data via a network. Specific examples of the network described above may include a wireless network provided by a communications provider of a computer. In one example, the transmission device 106 includes a network adapter (Network Interface Controller, simply referred to as NIC) that can connect to other network devices through a base station to communicate with the internet. In one example, the transmission device 106 may be a Radio Frequency (RF) module, which is configured to communicate with the internet wirelessly.
In this embodiment, a method for locating an abnormal SQL statement is provided, and fig. 2 is a flowchart of a method for locating an abnormal SQL statement according to an embodiment of the present invention, as shown in fig. 2, where the flowchart includes the following steps:
step S10, receiving a service request sent by a client, and acquiring a transaction serial number corresponding to the service request, wherein the transaction serial number is used for identifying an SQL statement in the service request;
transaction serial number TSN (Transition Serial Number) can be set at the client when the client initiates a service request, and can correlate the service with the database through the transaction serial number, so that the service statement problem can be conveniently and quickly positioned, and specifically, the transaction serial number is established when the client initiates the service request: set@transaction_serial_number=serial number, and the transaction serial number is built in the transaction, for example, is set before the first statement after the transaction is started, and is mainly used for tracking the whole transaction process from the service to the data layer, and optionally, since a transaction may comprise a plurality of sub-transactions, a plurality of transaction serial numbers can be set in one transaction according to actual requirements.
Step S20, associating the index information corresponding to the SQL statement obtained by the target node in the distributed database with the transaction serial number, and generating a statistical report;
and step S30, positioning the abnormal SQL statement according to the statistical report.
Index information corresponding to SQL (structured query language) sentences acquired by target nodes in the distributed database is associated with transaction serial numbers, a statistical report is generated, abnormal SQL sentences are positioned according to the statistical report, the target nodes can be computing nodes (CN, computer Node) and Data nodes (DN, data Node) in the distributed database, and the Data counted by each Node in the distributed database is associated with the transaction serial numbers corresponding to service requests, so that the service tracking of all links such as unified monitoring, analysis and the like is realized, the abnormal SQL sentences positioned by analyzing slow logs, transaction logs and the like from each Node can be avoided, and the abnormal positioning efficiency is improved.
Through the steps, the service request sent by the client is received, the transaction serial number corresponding to the service request is obtained, the transaction serial number is used for identifying the SQL statement in the service request, index information corresponding to the SQL statement obtained by the target node in the distributed database is associated with the transaction serial number, a statistical report is generated, and the abnormal SQL statement is located according to the statistical report. The information from the client to each node in the distributed database is associated, rather than isolated, so that the analysis of slow logs, transaction logs and the like from each node is avoided, and the capability of automatic positioning is greatly improved.
In one implementation of this embodiment, before parsing the transaction serial number in the service request, the method further includes:
step A, detecting whether the service request carries a first service serial number;
step B, if the service request carries a first service serial number, the first service serial number is used as the service serial number corresponding to the service request;
and C, if the service request does not carry the first transaction serial number, generating a second transaction serial number, wherein the second transaction serial number is used as the transaction serial number corresponding to the service request, and comprises a computing node identifier, a session ID and a time stamp.
After receiving a service request sent by a client, detecting whether the service request carries a first service serial number, namely detecting whether the service serial number is established on the client side, if the service request carries the first service serial number, which indicates that the service serial number is established on the client side, using the first service serial number as the service serial number corresponding to the service request, wherein the first service serial number can be a unique character string which is mainly used for uniquely identifying an SQL statement in the service request, and can also be established in a mode of referring to a second serial number; if the service request does not carry the first transaction serial number, which indicates that the transaction serial number is not established at the client side, generating a second transaction serial number by a computing node in the database, wherein the second transaction serial number is used as the transaction serial number corresponding to the service request, and comprises a computing node identifier, a session ID and a time stamp. In this embodiment, when the transaction serial number TSN is not set at the client side, the transaction serial number TSN may be automatically set by the computing node.
In an implementation manner of this embodiment, the target node includes a data node, associating the index information corresponding to the SQL statement counted by the target node in the distributed database with the transaction serial number, and generating the statistical report includes:
step S201, obtaining execution information corresponding to the SQL statement executed by the data node, and obtaining a global transaction ID corresponding to the SQL statement, wherein the execution information is used as the first index information;
step S202, associating the first index information with the transaction serial number and the global transaction ID, and generating a first statistical report.
The target node comprises a data node, the data node in the distributed database is mainly used for processing transactions, executing SQL sentences, storing data and the like, executing information corresponding to the SQL sentences executed by the data node is obtained, the data node is associated with the SQL sentences according to a client user ID, a thread ID, the SQL ID, a sub SQL ID and resource monitoring and counting resource use conditions, and the SQL sentence executing details specifically comprise: the SQL belongs to session ID, TSN_ID (serial number), specific SQL sentences, the execution times of the SQL sentences, the execution time (minimum time, maximum time, average time), the record number of the scan table, the record number of the return record, the CPU utilization rate, the memory utilization rate, the SQL_ID, the execution success rate, the index hit rate, the partition hit rate and the like. And associating the execution information with the transaction serial number and the global transaction ID to generate a first statistical report. The data nodes count the execution information of the SQL statement execution layer, so that the abnormal SQL statement in the subsequent positioning execution process is facilitated. The abnormality can be located by the statistical report: custom statement exception, scan table record count over threshold or return record count over threshold, etc. The method includes the steps that according to the statistical index information, the statement execution duration is judged to be longer than the preset duration (for example, 30 minutes), abnormal SQL scene conditions are met, abnormal alarm prompts are output, the problem is located through the performance of a server without manually analyzing resources, time is saved through automatic location, and the efficiency of abnormal location is improved.
In this embodiment, the target node includes a computing node, and before obtaining the execution information corresponding to the execution of the SQL statement by the data node, the method further includes:
step D, applying for global transaction ID to global transaction nodes in the distributed database through the computing node;
and E, sending the SQL statement carrying the global transaction ID to the data node corresponding to the global transaction ID.
The computing node analyzes the information such as session ID, transaction serial number, SQL statement and the like in the service request sent by the client. Optionally, the grammar structure of each SQL sentence can be analyzed through the computing node, the long sentence is analyzed and split, and one long SQL sentence is decomposed into a plurality of SQL sentences to be executed downwards. And then the computing node applies for the global transaction ID (GTID, general Transition ID) to the global transaction node GTM (General Transition Manager) in the distributed database, receives the global transaction ID response of the global transaction node, and sends the SQL statement carrying the global transaction ID to the data node corresponding to the global transaction ID. And correlating SQL sentences corresponding to the service requests in each node in the distributed database through the global transaction ID. The computing node sends SQL sentences carrying the global transaction ID to the data nodes corresponding to the global transaction ID in parallel, the corresponding data nodes execute the SQL sentences, and the computing node receives sentence execution results sent by the data nodes. The computing node judges whether the SQL statement is successfully executed by each data node, and sends the commit transaction commit to the relevant data nodes in parallel to inform each data node of the execution conditions of other data nodes. If the data node fails to execute or overturns, the computing node returns a message of the execution failure or overtime to the client, and waits for the client to execute the rollback command rollback transaction. And the computing node receives the execution result of each data node executing the commit. After the data node judges that all the commit is completed, the global transaction node is informed to release the global transaction ID. If there is a commit failure, the compute node initiates a committed transaction rollback flow. The computing node receives the global transaction node release global transaction ID result.
In another implementation of this embodiment, locating the abnormal SQL statement from the statistics includes:
step F, acquiring preset abnormal SQL scene conditions, and judging whether index information in the statistical report accords with the preset abnormal SQL scene conditions or not;
and G, if the index information in the statistical report accords with the preset abnormal SQL scene conditions, positioning the index information which accords with the preset abnormal SQL scene conditions into abnormal SQL sentences, and outputting a corresponding abnormal alarm prompt.
Acquiring preset abnormal SQL scene conditions, wherein a user can customize the scene conditions of the abnormal SQL according to actual needs, for example, the custom abnormal SQL scene conditions are as follows: abnormal SQL causes CPU to rush up to a preset threshold (the preset threshold can be customized, such as 90%), and then relevant alarm prompt is triggered. The preset abnormal SQL scene conditions can also be default conditions set by the system, whether index information in the statistical report accords with the preset abnormal SQL scene conditions is judged, if the index information in the statistical report accords with the preset abnormal SQL scene conditions, the index information which accords with the preset abnormal SQL scene conditions is positioned as an abnormal SQL statement, and a corresponding abnormal alarm prompt is output. In the distributed database system, the user can customize abnormal SQL according to the requirement and then carry out full-link tracking analysis. The abnormal SQL custom scene is commonly provided with DDL/DML card slow sentences, large scanning record quantity, huge return record quantity, execution failure, no index running, over high resource utilization rate (such as CPU (Central processing Unit) flushing to 100%) and the like. And recording all execution conditions corresponding to the service request in the statistical report, realizing the service tracking analysis capability of realizing full links, such as unified monitoring, analysis, report, alarm and the like, and rapidly positioning abnormal SQL problems, such as slow card and the like. Ending the era of positioning problems by means of manual, script and file, the database manager DBA is liberated to do more meaningful work. Four tiers of clients (clinet), networks (networks), distributed databases, and base resources (hosts) and their common problems are exemplified in the following table:
Figure BDA0004027158930000101
referring to fig. 3, fig. 3 is a position of a distributed database in a network, where a service request sequentially passes through a client, the network, the distributed database, and a base resource (host), and the distributed database includes a computing node CN, a data node DN, a global transaction node GTM, and a management node MN.
In another implementation manner of this embodiment, the target node includes a computing node, associating the index information corresponding to the SQL statement counted by the target node in the distributed database with the transaction serial number, and generating the statistical report includes:
step H, session information, transaction information, link information and global transaction ID which are acquired by the computing node and correspond to the SQL statement are acquired, wherein the session information, the transaction information, the link information and the global transaction ID are used as second index information;
and step I, associating the second index information with the transaction serial number to generate a second statistical report.
And acquiring session information, transaction information, link information and global transaction ID corresponding to the SQL statement in the computing node, taking the session information, the transaction information, the link information and the global transaction ID as second index information, and associating the second index information with the transaction serial number to generate a second statistical statement. The computing node counts the transaction processed by the service request: session, transaction, SQL, link, etc. use cases.
In another implementation manner of this embodiment, associating the index information corresponding to the SQL statement obtained by the target node in the distributed database with the transaction serial number, and generating the statistical report includes:
step J, acquiring resource information which is monitored by a host and corresponds to the SQL statement through a target node, wherein the resource information is used as third index information, and the resource information comprises a process ID, a client ID and a CPU utilization rate;
and step K, associating the third index information with the first transaction serial number to generate a third statistical report.
And acquiring resource information corresponding to the SQL statement monitored by the host through the target node, wherein the resource information is used as third index information, the resource information comprises a process ID, a client ID and a CPU utilization rate, and can also comprise a memory utilization rate and a disk IO, and the third index information is associated with the transaction serial number to generate a third statistical report. And acquiring the resource use condition of the base resource layer statistics, and correlating the specific SQL with the use rate of the resource. Anomalies in resource usage may be facilitated to be located. The iteration foundation is provided for further refining and counting each transaction and each SQL operation bottleneck situation, and data support is provided for subsequent performance optimization.
Referring to fig. 4, fig. 4 is a distributed database service SQL processing flow, where a client sends a service request to a CN (computing node), and a transaction serial number TSN may be established at the client, where the request includes a user identifier; the computing node analyzes SQL sentences in the service request; the computing node applies for a GTID (global transaction ID) to a GTM (global transaction node); distributing SQL to corresponding DN (data node) according to the global transaction ID; the computing node performs transaction statistics: including but not limited to session information, slow SQL, and link information; SQL statistics is carried out on the data nodes: including, but not limited to, user information user, thread, SQL, and sub SQL (subsql) information; host (host) performs resource statistics: including but not limited to PID processes, user information user, CPU usage, etc.; and reporting the generated statistical monitoring data to the big data operation and maintenance platform in the processes of the nodes and the processes. Abnormal SQL causes CPU flush to 90% (customizable) to trigger the relevant alarms. From the business application request to each functional node in the distributed database, to the basic resource (Host), the whole flow links are connected in series and layer by layer. The business processing flow full links are connected in series through the session ID, the transaction serial number TSN, the global transaction ID, the thread ID, the process identifier PID, user, SQL statement and other information, so that the global capability of covering the full link tracking analysis problem of each level is provided, and the independent analysis statistics are not provided. Analysis of slow logs, transaction logs, etc. from individual nodes is avoided. Greatly improving the capability of automatic positioning. The technical threshold of operation and maintenance personnel is reduced, and the operation and maintenance cost is reduced. And the operation and maintenance automation efficiency is improved.
From the description of the above embodiments, it will be clear to a person skilled in the art that the method according to the above embodiments may be implemented by means of software plus the necessary general hardware platform, but of course also by means of hardware, but in many cases the former is a preferred embodiment. Based on such understanding, the technical solution of the present invention may be embodied essentially or in a part contributing to the prior art in the form of a software product stored in a storage medium (e.g. ROM/RAM, magnetic disk, optical disk) comprising instructions for causing a terminal device (which may be a mobile phone, a computer, a server, or a network device, etc.) to perform the method according to the embodiments of the present invention.
Example 2
The embodiment also provides a positioning device for abnormal SQL statements, which is used for implementing the foregoing embodiments and preferred embodiments, and is not described in detail. As used below, the term "module" may be a combination of software and/or hardware that implements a predetermined function. While the means described in the following embodiments are preferably implemented in software, implementation in hardware, or a combination of software and hardware, is also possible and contemplated.
FIG. 5 is a block diagram of a device for locating abnormal SQL statements according to an embodiment of the invention, as shown in FIG. 5, the device comprises: a receiving module 60, an associating module 61, a positioning module 62, wherein,
the receiving module 60 is configured to receive a service request sent by a client, and obtain a transaction serial number corresponding to the service request, where the transaction serial number is used to identify an SQL statement in the service request;
the association module 61 is configured to associate the index information corresponding to the SQL statement obtained by the target node in the distributed database with the transaction serial number, and generate a statistical report;
and the positioning module 62 is used for positioning the abnormal SQL statement according to the statistical report.
Optionally, the association module includes a first association unit, configured to obtain execution information corresponding to the execution of the SQL statement by the data node, and obtain a global transaction ID corresponding to the SQL statement, where the execution information is used as first index information; and associating the first index information with the transaction serial number and the global transaction ID to generate a first statistical report.
Optionally, the first association unit is further configured to apply, by using the computing node, a global transaction ID to a global transaction node in the distributed database; and sending the SQL statement carrying the global transaction ID to the data node corresponding to the global transaction ID.
Optionally, the positioning module includes a first positioning unit, configured to obtain a preset abnormal SQL scene condition, and determine whether index information in the statistical report accords with the preset abnormal SQL scene condition; if the index information in the statistical report accords with the preset abnormal SQL scene conditions, positioning the index information which accords with the preset abnormal SQL scene conditions into abnormal SQL sentences, and outputting corresponding abnormal alarm prompts.
Optionally, the positioning device of the abnormal SQL statement further includes a detection module, configured to detect whether the service request carries a first service serial number; if the service request carries a first transaction serial number, the first transaction serial number is used as the transaction serial number corresponding to the service request; if the service request does not carry the first transaction serial number, a second transaction serial number is generated, the second transaction serial number is used as the transaction serial number corresponding to the service request, and the second serial number comprises a computing node identifier, a session ID and a time stamp.
Optionally, the association module includes a second association unit, configured to acquire session information, transaction information, link information and global transaction ID corresponding to the SQL statement, where the session information, transaction information, link information and global transaction ID are acquired by the computing node, and are used as second index information; and associating the second index information with the transaction serial number to generate a second statistical report.
Optionally, the association module includes a third association unit, configured to obtain, by using a target node, resource information corresponding to the SQL statement monitored by the host, where the resource information is used as third index information, and the resource information includes a process ID, a client ID, and a CPU utilization rate; and associating the third index information with the transaction serial number to generate a third statistical report.
It should be noted that each of the above modules may be implemented by software or hardware, and for the latter, it may be implemented by, but not limited to: the modules are all located in the same processor; alternatively, the above modules may be located in different processors in any combination.
Example 3
An embodiment of the invention also provides a storage medium having a computer program stored therein, wherein the computer program is arranged to perform the steps of any of the method embodiments described above when run.
Alternatively, in the present embodiment, the above-described storage medium may be configured to store a computer program for performing the steps of:
s1, receiving a service request sent by a client, and acquiring a transaction serial number corresponding to the service request, wherein the transaction serial number is used for identifying an SQL statement in the service request;
s2, associating index information corresponding to the SQL statement obtained by the target node in the distributed database with the transaction serial number, and generating a statistical report;
s3, positioning the abnormal SQL statement according to the statistical report.
Alternatively, in the present embodiment, the storage medium may include, but is not limited to: a usb disk, a Read-Only Memory (ROM), a random access Memory (Random Access Memory, RAM), a removable hard disk, a magnetic disk, or an optical disk, or other various media capable of storing a computer program.
An embodiment of the invention also provides an electronic device comprising a memory having stored therein a computer program and a processor arranged to run the computer program to perform the steps of any of the method embodiments described above.
Optionally, the electronic device may further include a transmission device and an input/output device, where the transmission device is connected to the processor, and the input/output device is connected to the processor.
Alternatively, in the present embodiment, the above-described processor may be configured to execute the following steps by a computer program:
s1, receiving a service request sent by a client, and acquiring a transaction serial number corresponding to the service request, wherein the transaction serial number is used for identifying an SQL statement in the service request;
s2, associating index information corresponding to the SQL statement obtained by the target node in the distributed database with the transaction serial number, and generating a statistical report;
s3, positioning the abnormal SQL statement according to the statistical report.
Alternatively, specific examples in this embodiment may refer to examples described in the foregoing embodiments and optional implementations, and this embodiment is not described herein.
The foregoing embodiment numbers of the present application are merely for describing, and do not represent advantages or disadvantages of the embodiments.
In the foregoing embodiments of the present application, the descriptions of the embodiments are emphasized, and for a portion of this disclosure that is not described in detail in this embodiment, reference is made to the related descriptions of other embodiments.
In the several embodiments provided in the present application, it should be understood that the disclosed technology content may be implemented in other manners. The above-described embodiments of the apparatus are merely exemplary, and the division of the units, such as the division of the units, is merely a logical function division, and may be implemented in another manner, for example, multiple units or components may be combined or may be integrated into another system, or some features may be omitted, or not performed. Alternatively, the coupling or direct coupling or communication connection shown or discussed with each other may be through some interfaces, units or modules, or may be in electrical or other forms.
The units described as separate units may or may not be physically separate, and units shown as units may or may not be physical units, may be located in one place, or may be distributed on a plurality of network units. Some or all of the units may be selected according to actual needs to achieve the purpose of the solution of this embodiment.
In addition, each functional unit in each embodiment of the present application may be integrated in one processing unit, or each unit may exist alone physically, or two or more units may be integrated in one unit. The integrated units may be implemented in hardware or in software functional units.
The integrated units, if implemented in the form of software functional units and sold or used as stand-alone products, may be stored in a computer readable storage medium. Based on such understanding, the technical solution of the present application may be embodied in essence or a part contributing to the prior art or all or part of the technical solution in the form of a software product stored in a storage medium, including several instructions to cause a computer device (which may be a personal computer, a server or a network device, etc.) to perform all or part of the steps of the methods described in the embodiments of the present application. And the aforementioned storage medium includes: a U-disk, a Read-Only Memory (ROM), a random access Memory (RAM, random Access Memory), a removable hard disk, a magnetic disk, or an optical disk, or other various media capable of storing program codes.
The foregoing is merely a preferred embodiment of the present application and it should be noted that modifications and adaptations to those skilled in the art may be made without departing from the principles of the present application and are intended to be comprehended within the scope of the present application.

Claims (10)

1. A method for locating an abnormal SQL statement, the method comprising:
receiving a service request sent by a client, and acquiring a transaction serial number corresponding to the service request, wherein the transaction serial number is used for identifying an SQL statement in the service request;
correlating the index information corresponding to the SQL statement obtained by the target node in the distributed database with the transaction serial number, and generating a statistical statement;
and positioning the abnormal SQL statement according to the statistical report.
2. The method of claim 1, wherein the target node comprises a data node, wherein associating the index information corresponding to the SQL statement counted by the target node in the distributed database with the transaction serial number, and wherein generating the statistics comprises:
acquiring execution information corresponding to the SQL statement executed by the data node, and acquiring a global transaction ID corresponding to the SQL statement, wherein the execution information is used as first index information;
and associating the first index information with the transaction serial number and the global transaction ID to generate a first statistical report.
3. The method of claim 2, wherein the target node comprises a computing node, and wherein prior to obtaining the execution information corresponding to the execution of the SQL statement by the data node, the method further comprises:
applying for a global transaction ID from a global transaction node in the distributed database through the computing node;
and sending the SQL statement carrying the global transaction ID to the data node corresponding to the global transaction ID.
4. The method of claim 1, wherein locating an abnormal SQL statement from the statistics comprises:
acquiring a preset abnormal SQL scene condition, and judging whether index information in the statistical report accords with the preset abnormal SQL scene condition;
if the index information in the statistical report accords with the preset abnormal SQL scene conditions, positioning the index information which accords with the preset abnormal SQL scene conditions into abnormal SQL sentences, and outputting corresponding abnormal alarm prompts.
5. The method of claim 1, wherein prior to obtaining the transaction serial number corresponding to the service request, the method further comprises:
detecting whether the service request carries a first service serial number or not;
if the service request carries a first transaction serial number, the first transaction serial number is used as the transaction serial number corresponding to the service request;
if the service request does not carry the first transaction serial number, a second transaction serial number is generated, the second transaction serial number is used as the transaction serial number corresponding to the service request, and the second serial number comprises a computing node identifier, a session ID and a time stamp.
6. The method of claim 1, wherein the target node comprises a computing node, wherein associating the index information corresponding to the SQL statement counted by the target node in the distributed database with the transaction serial number, and wherein generating the statistics comprises:
the method comprises the steps of obtaining session information, transaction information, link information and global transaction ID (identity) corresponding to the SQL statement, which are obtained by a computing node, wherein the session information, the transaction information, the link information and the global transaction ID are used as second index information;
and associating the second index information with the transaction serial number to generate a second statistical report.
7. The method of claim 1, wherein associating the index information corresponding to the SQL statement obtained by the target node in the distributed database with the transaction serial number, and generating the statistical report comprises:
acquiring resource information which is monitored by a host and corresponds to the SQL statement through a target node, wherein the resource information is used as third index information, and the resource information comprises a process ID, a client ID and a CPU utilization rate;
and associating the third index information with the transaction serial number to generate a third statistical report.
8. A locating device for an abnormal SQL statement, comprising:
the receiving module is used for receiving a service request sent by a client and acquiring a transaction serial number corresponding to the service request, wherein the transaction serial number is used for identifying an SQL statement in the service request;
the association module is used for associating the index information corresponding to the SQL statement acquired by the target node in the distributed database with the transaction serial number and generating a statistical report;
and the positioning module is used for positioning the abnormal SQL statement according to the statistical report.
9. The electronic equipment is characterized by comprising a processor, a communication interface, a memory and a communication bus, wherein the processor, the communication interface and the memory are communicated with each other through the communication bus; wherein:
a memory for storing a computer program;
a processor for executing the method steps of any one of claims 1 to 7 by running a program stored on a memory.
10. A storage medium comprising a stored program, wherein the program when run performs the method steps of any of the preceding claims 1 to 7.
CN202211726734.9A 2022-12-29 2022-12-29 Positioning method and device of abnormal SQL statement, electronic equipment and storage medium Pending CN116126890A (en)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN202211726734.9A CN116126890A (en) 2022-12-29 2022-12-29 Positioning method and device of abnormal SQL statement, electronic equipment and storage medium

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN202211726734.9A CN116126890A (en) 2022-12-29 2022-12-29 Positioning method and device of abnormal SQL statement, electronic equipment and storage medium

Publications (1)

Publication Number Publication Date
CN116126890A true CN116126890A (en) 2023-05-16

Family

ID=86302213

Family Applications (1)

Application Number Title Priority Date Filing Date
CN202211726734.9A Pending CN116126890A (en) 2022-12-29 2022-12-29 Positioning method and device of abnormal SQL statement, electronic equipment and storage medium

Country Status (1)

Country Link
CN (1) CN116126890A (en)

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN118227657A (en) * 2024-05-27 2024-06-21 成都中科极云软件有限公司 Custom SQL statement data isolation construction method under low code platform multi-tenant data table isolation mode
CN118245267A (en) * 2024-05-23 2024-06-25 天津南大通用数据技术股份有限公司 Method, device and program product for actively avoiding abnormal occupation of database resources

Cited By (2)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
CN118245267A (en) * 2024-05-23 2024-06-25 天津南大通用数据技术股份有限公司 Method, device and program product for actively avoiding abnormal occupation of database resources
CN118227657A (en) * 2024-05-27 2024-06-21 成都中科极云软件有限公司 Custom SQL statement data isolation construction method under low code platform multi-tenant data table isolation mode

Similar Documents

Publication Publication Date Title
CN116126890A (en) Positioning method and device of abnormal SQL statement, electronic equipment and storage medium
CN107094158B (en) Automatic change intranet security fragile analytic system
CN107508722B (en) Service monitoring method and device
CN110096419A (en) Acquisition methods, interface log management server and the service server of interface log
CN111355622A (en) Container traffic monitoring method, system and computer readable storage medium
CN111538563A (en) Event analysis method and device for Kubernetes
CN105719072B (en) System and method for associating multi-segment component transactions
CN110825466A (en) Program jamming processing method and jamming processing device
CN106789395B (en) A kind of Distributed PDM system monitoring data transmission method based on Web
CN106304122B (en) Business data analysis method and system
CN108989116B (en) Big data collection and analysis system and method
WO2023093527A1 (en) Alarm association rule generation method and apparatus, and electronic device and storage medium
CN111858199A (en) Self-adaptive hard disk log collection method, system, terminal and storage medium
CN116302652A (en) System alarm information processing method and device and electronic equipment
CN116089446A (en) Optimization control method and device for structured query statement
CN115220992A (en) Interface change monitoring method and device, computer equipment and storage medium
KR102331347B1 (en) An extraction-system using dispersion deep learning information analysis management based cloud
CN110233747B (en) Data reporting method and cloud platform
CN111625412A (en) Flume-based data acquisition method, system, device and storage medium
CN112988828A (en) Message pushing method and electronic equipment
CN113742400A (en) Network data acquisition system and method based on self-adaptive constraint conditions
CN112597532A (en) Monitoring method and device for sensitive data access
CN114928523B (en) 5G OAM-based network element upgrading method and upgrading device
CN110708208B (en) Monitoring data acquisition method and device, storage medium and terminal
CN111045950B (en) Performance problem point determining method, device, data analysis system and storage medium

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination