CN113297245A - Method and device for acquiring execution information - Google Patents
Method and device for acquiring execution information Download PDFInfo
- Publication number
- CN113297245A CN113297245A CN202010478675.2A CN202010478675A CN113297245A CN 113297245 A CN113297245 A CN 113297245A CN 202010478675 A CN202010478675 A CN 202010478675A CN 113297245 A CN113297245 A CN 113297245A
- Authority
- CN
- China
- Prior art keywords
- sql
- execution information
- log
- database
- time period
- 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
Links
- 238000000034 method Methods 0.000 title claims abstract description 65
- 230000002776 aggregation Effects 0.000 claims description 9
- 238000004220 aggregation Methods 0.000 claims description 9
- 238000004458 analytical method Methods 0.000 claims description 8
- 230000004931 aggregating effect Effects 0.000 claims description 7
- 238000006243 chemical reaction Methods 0.000 claims description 2
- 238000010586 diagram Methods 0.000 description 12
- 230000008569 process Effects 0.000 description 11
- 235000019580 granularity Nutrition 0.000 description 5
- 238000004590 computer program Methods 0.000 description 4
- 238000005516 engineering process Methods 0.000 description 3
- 230000006870 function Effects 0.000 description 3
- 238000007726 management method Methods 0.000 description 3
- 230000009466 transformation Effects 0.000 description 3
- 230000002159 abnormal effect Effects 0.000 description 2
- 230000009471 action Effects 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 2
- 230000005540 biological transmission Effects 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- OKTJSMMVPCPJKN-UHFFFAOYSA-N Carbon Chemical compound [C] OKTJSMMVPCPJKN-UHFFFAOYSA-N 0.000 description 1
- 230000001413 cellular effect Effects 0.000 description 1
- 230000008859 change Effects 0.000 description 1
- 238000013500 data storage Methods 0.000 description 1
- 229910021389 graphene Inorganic materials 0.000 description 1
- 230000006872 improvement Effects 0.000 description 1
- 230000003993 interaction Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000004044 response Effects 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
- 238000006467 substitution reaction Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/248—Presentation of query results
Abstract
The present specification provides a method and an apparatus for acquiring execution information, and the method may include: acquiring an SQL log collected from a target database; analyzing the SQL log to obtain the execution information of the SQL statement corresponding to the SQL log; and storing the execution information.
Description
Technical Field
One or more embodiments of the present disclosure relate to the field of software processing technologies, and in particular, to a method and an apparatus for acquiring execution information.
Background
The business system usually needs to interact with the database, and the operation of saving data or Query result needs to be completed through a database SQL (Structured Query Language) statement. In a database scenario, SQL execution information is an important means for users to troubleshoot problems. When a user finds out a business data logic problem and a database performance problem, SQL execution information can provide reference for the user, so that the corresponding problem is solved. The SQL execution information refers to information generated by the database management system in the process of analyzing and executing the SQL statement and returning a result.
Disclosure of Invention
In view of this, one or more embodiments of the present disclosure provide a method and apparatus for obtaining execution information.
Specifically, the description is realized by the following technical scheme:
according to a first aspect of the present specification, there is provided a method of acquiring execution information, the method comprising:
acquiring an SQL log collected from a target database;
analyzing the SQL log to obtain the execution information of the SQL statement corresponding to the SQL log;
and storing the execution information.
According to a second aspect of the present specification, there is provided a method of acquiring execution information, the method comprising
Acquiring an SQL log generated by a target database deployed on local equipment, wherein the SQL log comprises execution information of SQL statements;
and sending the collected SQL log to a demand party, and analyzing the SQL log by the demand party to obtain corresponding execution information.
According to a third aspect of the present specification, there is provided an apparatus for acquiring execution information, the apparatus including:
the log acquisition unit is used for acquiring SQL logs acquired from a target database;
the log analysis unit is used for analyzing the SQL log to obtain the execution information of the SQL statement corresponding to the SQL log;
and the storage unit is used for storing the execution information.
According to a fourth aspect of the present specification, there is provided an apparatus for acquiring execution information, the apparatus including:
the system comprises a log acquisition unit, a database management unit and a database management unit, wherein the log acquisition unit is used for acquiring an SQL log generated by a target database deployed on local equipment, and the SQL log comprises execution information of SQL statements;
and the sending unit is used for sending the acquired SQL log to a demand party so that the demand party can analyze the SQL log to obtain corresponding execution information.
According to a fifth aspect of the present specification, there is provided a system for acquiring execution information, the system comprising:
the cloud database cluster comprises a plurality of cloud database servers, a log acquisition component runs in each cloud database server, the log acquisition component is used for acquiring SQL logs generated by cloud databases deployed in the corresponding cloud database servers, and each SQL log comprises execution information of SQL statements;
and the query server is used for receiving the SQL log sent by the log acquisition component, respectively storing the execution information obtained by analyzing the SQL log into a log service and a log database, and querying the execution information stored in the log service and/or the log database according to the received query request.
According to a sixth aspect of the present specification, there is provided an electronic apparatus. The electronic device includes:
a processor;
a memory for storing processor-executable instructions;
wherein the processor implements the method of the first aspect by executing the executable instructions.
According to a seventh aspect of the present description, a computer-readable storage medium is presented, having stored thereon computer instructions which, when executed by a processor, implement the steps of the method according to the first aspect.
According to an eighth aspect of the present specification, there is provided an electronic apparatus. The electronic device includes:
a processor;
a memory for storing processor-executable instructions;
wherein the processor implements the method according to the second aspect by executing the executable instructions.
According to a ninth aspect of the present description, a computer-readable storage medium is presented, having stored thereon computer instructions which, when executed by a processor, carry out the steps of the method according to the second aspect.
Drawings
Fig. 1 is a schematic diagram of an architecture for acquiring execution information shown in this specification.
Fig. 2 is a flow chart illustrating a method of obtaining execution information.
Fig. 3 is a flow chart illustrating another method of obtaining execution information.
Fig. 4 is a schematic diagram of an architecture for acquiring execution information in a cloud database scenario according to an exemplary embodiment of the present specification.
Fig. 5 is a flowchart of a method for obtaining execution information according to an exemplary embodiment of the present disclosure.
FIG. 6 is a schematic diagram of a relational database provided by an exemplary embodiment of the present description.
Fig. 7 is a schematic diagram of a log service provided by an exemplary embodiment of the present specification.
Fig. 8 is a schematic structural diagram of an electronic device according to an exemplary embodiment of the present disclosure.
Fig. 9 is a block diagram of an apparatus for acquiring execution information according to an exemplary embodiment of the present disclosure.
Fig. 10 is a schematic structural diagram of another electronic device shown in an exemplary embodiment of the present specification.
Fig. 11 is a block diagram illustrating another apparatus for acquiring execution information according to an exemplary embodiment of the present disclosure.
Detailed Description
Reference will now be made in detail to the exemplary embodiments, examples of which are illustrated in the accompanying drawings. When the following description refers to the accompanying drawings, like numbers in different drawings represent the same or similar elements unless otherwise indicated. The implementations described in the following exemplary embodiments do not represent all implementations consistent with one or more embodiments of the present specification. Rather, they are merely examples of apparatus and methods consistent with certain aspects of one or more embodiments of the specification, as detailed in the claims which follow.
It should be noted that: in other embodiments, the steps of the corresponding methods are not necessarily performed in the order shown and described herein. In some other embodiments, the method may include more or fewer steps than those described herein. Moreover, a single step described in this specification may be broken down into multiple steps for description in other embodiments; multiple steps described in this specification may be combined into a single step in other embodiments.
Fig. 1 is a schematic diagram of an architecture for acquiring execution information shown in this specification. As shown in fig. 1, may include a server 11, a network 12, and a cloud database 13.
The server 11 may be a physical server comprising a separate host, or the server 11 may be a virtual server carried by a cluster of hosts. In operation, the server 11 may be configured with an execution information obtaining device, which may be implemented in software and/or hardware, to obtain and store the SQL execution information generated on the cloud database 13, and provide an external query function for the SQL execution information.
The cloud database 13 may be deployed on one or more physical servers including independent hosts, and each physical server may have a log collection component running thereon, where the log collection component may be used to collect SQL logs generated by the cloud database 13.
And the network 12 for interaction between the server 11 and the cloud database 13 may include various types of wired or wireless networks.
Fig. 2 is a flow chart illustrating a method of obtaining execution information. As shown in fig. 2, the method may be applied to a server (e.g., the server 11 shown in fig. 1, etc.); the method may comprise the steps of:
In one embodiment, a server may send a log collection instruction to a log collection component running on a target device, where the target device may be used to deploy a target database, and then the server may receive an SQL log collected by the log collection component from the target database of the target device. The target database may be a common database constructed by itself, or may be a cloud database, which is not limited in this specification.
In another embodiment, the target database may be a cloud database, and when the cloud database is deployed on a plurality of target devices, the server may send a log collection instruction to the log collection component running on each target device, so that each log collection component collects an SQL log on each target device.
And 204, analyzing the SQL log to obtain the execution information of the SQL statement corresponding to the SQL log.
In an embodiment, the server may parse the received SQL log to obtain execution information of the SQL statement corresponding to the SQL log. Wherein the execution information may include at least one of: SQL statements, SQL execution time and return time, target database names to execute the SQL statements, etc.
In an embodiment, the server may store the parsed execution information through a log service, and/or the server may store the parsed execution information through a relational database. Of course, the server may also store the analyzed execution information in other types of databases, such as a non-relational database, which is not limited in this specification. Wherein the log service can support semi-structured data storage and querying of log-like data.
In another embodiment, the server may obtain a predefined stored template and may perform a structured transformation on the execution information according to the stored template to obtain structured execution information, which the server may store.
In another embodiment, the server may store the execution information corresponding to each SQL statement. The server may also receive a query request for the execution information, and the server may determine a time period included in the query request, and then the server may output the execution information in the time period in a bar manner or aggregate the execution information according to requirements included in the query request.
In another embodiment, the server may aggregate the execution information according to a preset time period to obtain aggregated execution information, and the server may store the aggregated execution information.
The server may respectively aggregate the execution information corresponding to each SQL statement according to a preset time period to obtain aggregated execution information corresponding to each SQL statement, and then the server may record each aggregated execution information to an execution information table, where the execution information table records aggregated execution information of each SQL statement aggregated according to the preset time period.
The server can also record each SQL statement to the SQL statement table, and the SQL statement table is stored in association with the execution information table, and the SQL statement table and the execution information table respectively record the statement identifier of the corresponding SQL statement, so that the SQL statement table and the execution information table can be associated with each other through the recorded statement identifier of the SQL statement.
The server may further receive a query request for the execution information, and the server may determine a time period included in the query request, and then the server may query the SQL statement table and the execution information table respectively, and output the SQL statement in the corresponding time period in the SQL statement table and the aggregated execution information in the corresponding time period in the execution information table respectively, or output the queried SQL statement and the aggregated execution information in an associated manner according to the statement identifier of the corresponding SQL statement.
According to the technical scheme, the SQL log collected from the target database can be obtained, the execution information of the SQL sentences corresponding to the SQL log can be obtained through analysis of the SQL log, the SQL sentences which consume much time in operation can be quickly determined according to the obtained SQL execution information, the SQL sentences with abnormal operation can be favorably positioned, a user can conveniently inquire and analyze the SQL sentence execution information, the database business data logic problem and the database performance problem can be found according to the SQL execution information, meanwhile, the execution information of each SQL sentence can be stored, and the inquiry and analysis requirements of the user on the SQL execution information strip-level granularity can be met.
Fig. 3 is a flow chart illustrating another method of obtaining execution information. As shown in fig. 3, the method is applied to a physical device that deploys a target database; the method may comprise the steps of:
In one embodiment, the local device is the above-mentioned physical device for deploying the target database. The local device can be operated with a log collection component, and the collection operation of the SQL log is completed through the log collection component, so that the collected SQL log is sent to a demand side to be analyzed to obtain corresponding SQL execution information. Of course, the local device may also complete the collection and transmission operations of the SQL log through other software or hardware schemes, so as to replace the above-mentioned log collection component, which is not limited in this specification.
In an embodiment, the log collection component may collect a target database deployed on the local device to generate an SQL log, where the target database may be a common database or a cloud database, and this description does not limit this. The SQL log may contain execution information of the SQL statement. The log collection component may collect the SQL log from the target device according to a received log collection instruction from the demand party, or the log collection component may collect the SQL log from the target device periodically, which is not limited in this specification.
In another embodiment, the log collection component may periodically collect SQL logs generated by the target database, where each cycle includes a collection time period and a waiting time period, if the data amount of the SQL logs collected in the current cycle is not less than the preset data amount threshold, continue to collect the next cycle after the waiting time period of the first waiting time period, and if the data amount of the SQL logs collected is less than the preset data amount threshold, continue to collect the next cycle after the waiting time period of the second waiting time period. And the second waiting time length is longer than the first waiting time length.
And 304, sending the collected SQL log to a demand party, and analyzing the SQL log by the demand party to obtain corresponding execution information.
In an embodiment, the log collection component may send the collected SQL log to the demand side, and the demand side analyzes the SQL log to obtain corresponding execution information.
In another embodiment, the log collection component may send the SQL log to the demand side when the data volume of the collected SQL log is not less than a preset data volume threshold; and under the condition that the data volume of the acquired SQL log is smaller than a preset data volume threshold, the log acquisition component does not send the SQL log to a demand side. The requesting party may be a server or other device that needs to obtain the SQL log, which is not limited in this specification.
According to the technical scheme, the SQL log generated by the target database deployed on the local equipment can be collected, the collected SQL log is sent to the demand party, the demand party analyzes the SQL log to obtain corresponding execution information, the SQL sentences which consume a lot of running time can be quickly determined according to the obtained SQL execution information, the SQL sentences with abnormity can be favorably positioned, a user can conveniently inquire and analyze the SQL sentence execution information, database business data logic problems and database performance problems can be found according to the SQL execution information, meanwhile, the execution information of each SQL sentence can be stored, and the inquiry and analysis requirements of the user on the SQL execution information strip-level granularity can be met.
For the convenience of understanding, the technical solutions in the present specification are further described below with reference to the accompanying drawings. Fig. 4 is a schematic diagram of an architecture for acquiring execution information in a cloud database scenario according to an exemplary embodiment of the present specification. As shown in fig. 4, may include: cloud database servers 41-43 in the cloud database cluster, log collection components 44-46, SQL log collection and processing programs 47-49 in the query servers, relational database a, and log service B. In this embodiment, the relational database a is used as an example, but the database may be other types of databases such as a non-relational database, and the present specification is not limited thereto.
As shown in fig. 4, each cloud database server runs a corresponding log collection component, a corresponding cloud database is deployed in the cloud database server, and the log collection component in the cloud database server can collect SQL logs generated by the corresponding cloud database. Each cloud database server may involve one or more cloud databases. The log collection component 44 may receive a log collection instruction from the SQL log collection and processing program 47 in the query server, the log collection component 44 may collect SQL logs from the corresponding cloud database server 41 according to the log collection instruction, or the log collection component 44 may periodically collect SQL logs from the corresponding cloud database server 41. The log collection component 44 may send the collected SQL log to the SQL log collection and processing program 47, and then the SQL log collection and processing program 47 may analyze the SQL log to obtain execution information of SQL statements corresponding to the SQL log, and the SQL log collection and processing program 47 may store the obtained execution information to the relational database a and the log service B, respectively, so that the user may query and obtain execution information of corresponding SQL statements according to the query service provided by the relational database a and the log service B, and operations executed by other cloud database servers and corresponding log collection components, and the SQL log collection and processing program are the same as the above processes.
Fig. 5 is a flowchart of a method for obtaining execution information according to an exemplary embodiment of the present disclosure. The acquisition of execution information in a cloud database scenario is taken as an example for explanation. As shown in fig. 5, the following steps may be included:
In this embodiment, the log collection component may directly collect the SQL logs generated by the cloud database deployed in the cloud database server, or the cloud database deployed in the cloud database server may also asynchronously output the generated SQL logs to the local log record table, and may decouple the process of executing the SQL statements by the cloud database from the process of outputting the SQL logs, so as to avoid the process of outputting the SQL logs from affecting the operation performance of the cloud database, and ensure the execution efficiency of the SQL statements, so that the log collection component may also collect the SQL logs from the local log record table.
In this embodiment, a developer may preset a cloud database server that needs to acquire SQL logs, so that the cloud database server may periodically scan all cloud databases that need to be acquired, and send a log acquisition instruction to a corresponding log acquisition component, so that the log acquisition component acquires the SQL logs from the corresponding cloud database. The cloud database server may also send an instruction to stop collecting SQL logs to a cloud database that does not need to collect SQL logs, so that the corresponding log collection component stops collecting SQL logs from the cloud database.
In this embodiment, the log collection component may periodically collect SQL logs generated by the target database, where each cycle may include a collection period and a waiting period, and assuming that the first waiting duration is 100 milliseconds, the second waiting duration is 1 second, and the preset data amount threshold is set to 1M. The log collection component can obtain the data volume of the collected SQL log, if the data volume of the SQL log collected by the log collection component is not smaller than a preset data volume threshold value 1M, the log collection component can upload the SQL log to the query server for processing, and after waiting for the first waiting time, namely 100 milliseconds, the log collection component continues to collect the SQL log in the next period. If the data volume of the SQL log acquired by the log acquisition component is smaller than the preset data volume threshold value 1M, the log acquisition component does not upload the SQL log to the query server, and the log acquisition component needs to continue to acquire the data in the next period after waiting for the second waiting time, i.e., 1 second. And the second waiting time length is longer than the first waiting time length.
The log collection component compares the collected data volume of the SQL log with a preset data volume threshold value, so that the data volume of the SQL log obtained by the query server can meet the requirement, the query server is prevented from being frequently operated to analyze the SQL log, and the consumption of resources can be saved.
In this embodiment, the query server may obtain the storage structure and the storage content of the relational database a. In this embodiment, the relational database a is used as an example, but the database may be other types of databases such as a non-relational database, and the present specification is not limited thereto.
Assume that two tables are maintained in the relational database a, as shown in fig. 6, wherein the SQL statement table records SQL statement information and may include field information related to SQL statements; it is assumed that the execution information table records execution information corresponding to each SQL aggregated for one minute according to a preset time period, and may include field information related to the SQL execution information. Of course, the preset time period may be any time period such as five minutes, ten minutes, one hour, etc., and this is not limited in this specification.
As shown in fig. 6, the SQL statement table may contain field information:
template _ id, a value obtained by calculating a templated SQL statement by using a hash algorithm;
sample _ SQL, example SQL, is an example of a templated SQL statement;
SQL template, the templated SQL statement;
db _ name, the name of the target database for executing the SQL statement;
GMT _ modified, GMT (Greenwich Mean Time ) modification Time;
GMT _ create, GMT creation time;
SQL _ type, SQL statement type.
In this embodiment, the SQL statements stored in the SQL statement table may be templated SQL statements converted according to a predefined storage template. For example, SQL statement 1: SELECT FROM test WHERE ID 123; SQL statement 2: SELECT FROM test WHERE ID 23. At this time, after the SQL statement 1 and the SQL statement 2 are converted according to the predefined storage template, the same templated SQL statement is obtained: SELECT FROM test WHERE ID? . The statement 1 and the statement 2 can correspond to the same templated SQL statement in the SQL statement table, the data amount of the stored SQL statement in the SQL data table can be reduced, and the execution information of the statement 1 and the statement 2 in the same minute can be aggregated, which can significantly reduce the storage space occupied by the SQL data table and the execution information table, facilitate the user to obtain the execution information of the same type of SQL statement, obtain the execution information of the same type of SQL statement aggregated in minutes, and meet the requirement of the user to obtain the execution information of different granularities.
As shown in fig. 6, the field information that may be contained in the execution information table:
template _ id, relating the SQL statement table and the execution information table;
minute _ of _ day, minutes of day, e.g., 5 minutes of day, for a period of time of 00:05 to 00: 06;
GMT _ create, GMT creation time;
GMT _ modified, GMT modified time;
count, the number of times the SQL statement corresponding to the template id is executed in the minute;
rt _ sum, the sum of the SQL statements return time for the template id in this minute.
As shown in fig. 6, the execution information of the SQL statement on the same date may be stored in the same execution information table, and assuming that the name of the execution information table on each date is SQL _ history _ $ { date }, for example, SQL _ history _ $ {2020-02-02} stores the execution information after aggregating all the executed templated SQ L statements on 2/2020 by one minute for granularity, which is beneficial for the subsequent query of the execution information according to the date, or may delete all the execution information on a certain date, which is convenient for the user to manage the execution information.
Of course, in this embodiment, the execution information of the SQ L statement acquired on different dates or different time periods may also be directly recorded in the same execution information table, which is not limited in this specification.
In this embodiment, the template id may be a value obtained by calculating a templated SQL statement using a hash algorithm, and the template id may associate an SQL statement table in a relational database with an execution information table, which may save a storage space and reduce storage overhead caused by storing the SQL statement table and the execution information.
As shown in fig. 7, the storage content of the log service B may contain field information:
SQL, SQL statements executed;
template _ id, template id;
db _ name, the name of the target database for executing the SQL statement;
SQL _ type, SQL statement type;
time, the time when the SQL statement begins to execute;
rt, return time of SQL statement execution.
In this embodiment, the log service B may directly record the corresponding SQL statement, so that the user can conveniently query the execution information of each SQL statement, which is beneficial for the user to perform finer-grained analysis and meets the query requirement of the user.
And step 514, storing the data into the relational database A and the log service B respectively.
In this embodiment, the query server may receive the SQL log uploaded by the log collection component, and the query server may analyze the SQL log, so as to obtain the execution information list X of the SQL statement corresponding to the SQL log. The query server may perform structured transformation on the execution information of each SQL statement in the execution information list X according to the field information included in the log service B, and may obtain information such as SQL statement, template _ id, db _ name, SQL _ type, time, and rt, respectively. The query server may store the converted structured execution information in the corresponding log service B.
In this embodiment, the query server may first detect whether the sql _ history _ $ { date } table of the current date exists in the relational database a, and if not, create the sql _ history _ $ { date } table of the current date. The query server can receive the SQL log uploaded by the log acquisition component, and the query server can analyze the SQL log so as to obtain an execution information list X of the SQL statement corresponding to the SQL log. The query server may perform structured transformation on the execution information of each SQL statement in the execution information list X according to the field information included in the relational database a, and may obtain information such as sample _ SQL, db _ name, rt, minute _ of _ day, time execution time, and SQL statement, respectively. The query server may convert the obtained SQL statement into a templated SQL statement according to a predefined storage template, and perform a hash operation according to the templated SQL statement to obtain a template _ id.
The query server may query whether records corresponding to template _ id and minute _ of _ day exist in the corresponding sql _ history _ $ { date } table, and if not, create a corresponding record in the sql _ history _ $ { date } table; if so, gmt _ modified, count, and rt _ sum of the record are updated.
At step 516, a query request is received.
At step 518, query results are obtained.
In this embodiment, a user may send a query request for execution information in the relational database a, the query server may determine a start time and an end time included in the query request, and a target database name for executing an SQL statement, and the query server may calculate a corresponding time period, so that the query server may query an execution information table in the relational database a, obtain aggregated execution information corresponding to the target database name in the execution information table within the time period, and obtain aggregated execution information of each SQL statement aggregated in one minute. The query server can also query the SQL statement information in the corresponding SQL statement table in the time period according to the template id contained in the obtained aggregation execution information.
In this embodiment, the user may further send a query request for the log service B, the query server may determine a start time and an end time included in the query request, and a target database name for executing an SQL statement, and the query server may obtain execution information of each piece of SQL in the time period, where the query server may output the obtained execution information in a piece or output the execution information after aggregating the execution information according to requirements included in the query request.
Of course, the user may send a corresponding query request by combining the storage structures and the storage contents of the relational database a and the log service B, which is not limited in this specification.
In the technical solution of this specification, the relational database may also store execution information corresponding to each SQL statement, the log service may store execution information of aggregated SQL statements, and the execution information of SQL statements may be only stored in the relational database or the log service, where the database storing the execution information of SQL statements may also be other types of databases such as a non-relational database, which is not limited in this specification, the principle of the technical solution is similar to the above embodiment, and the related implementation details may refer to the above embodiment, which is not described herein again.
According to the technical scheme, the SQL log collected from the target database can be obtained, the execution information of the SQL sentences corresponding to the SQL log can be obtained through analysis of the SQL log, the SQL sentences which consume much time in operation can be quickly determined according to the obtained SQL execution information, the SQL sentences with abnormal operation can be favorably positioned, a user can conveniently inquire and analyze the SQL sentence execution information, the database business data logic problem and the database performance problem can be found according to the SQL execution information, meanwhile, the execution information of each SQL sentence can be stored, and the inquiry and analysis requirements of the user on the SQL execution information strip-level granularity can be met.
Fig. 8 shows a schematic structural diagram of an electronic device according to an exemplary embodiment of the present description. Referring to fig. 8, at the hardware level, the electronic device includes a processor 802, an internal bus 804, a network interface 806, a memory 808, and a non-volatile memory 810, but may also include hardware required for other services. The processor 802 reads a corresponding computer program from the nonvolatile memory 810 into the memory 808 and then runs the computer program, thereby forming an execution information acquisition apparatus on a logical level. Of course, besides the software implementation, the present specification does not exclude other implementations, such as logic devices or a combination of software and hardware, and the like, that is, the execution subject of the following processing flow is not limited to each logic unit, and may be hardware or logic devices.
Referring to fig. 9, in a software implementation, the execution information acquiring apparatus may include a log acquiring unit 902, a log parsing unit 904, and a storage unit 906. Wherein:
a log obtaining unit 902, configured to obtain an SQL log collected from a target database;
a log analyzing unit 904, configured to analyze the SQL log to obtain execution information of an SQL statement corresponding to the SQL log;
a storage unit 906 configured to store the execution information.
Optionally, the log obtaining unit 902 is specifically configured to:
sending a log collection instruction to a log collection component running on a target device, the target device being used to deploy the target database;
and receiving the SQL log collected from the target equipment by the log collection component.
Optionally, the target database is a cloud database, the cloud database is deployed on multiple target devices, and the log obtaining unit 902 is specifically configured to:
and respectively sending log acquisition instructions to the log acquisition components operated on each target device so as to respectively acquire the SQL logs on each target device.
Optionally, the execution information includes at least one of: SQL statement, SQL execution time and return time, target database name for executing SQL statement.
Optionally, the storage unit 906 is specifically configured to:
the execution information is stored by a logging service and/or by a relational database.
Optionally, the storage unit 906 is specifically configured to:
acquiring a predefined storage template to perform structured conversion on the execution information to obtain structured execution information;
storing the structured execution information.
Optionally, the storage unit 906 is specifically configured to:
and respectively storing the execution information corresponding to each SQL statement.
Optionally, the method further includes:
a first request receiving unit 908 configured to receive a query request for execution information;
a first determining unit 910, configured to determine a time period included in the query request;
a first output unit 912, configured to output the execution information in the time period in a bar-by-bar manner or in an aggregation manner.
Optionally, the storage unit 906 is specifically configured to:
aggregating the execution information according to a preset time period to obtain aggregated execution information;
and storing the aggregation execution information.
Optionally, the storage unit 906 is specifically configured to:
respectively aggregating the execution information corresponding to each SQL statement according to a preset time period to obtain the aggregated execution information corresponding to each SQL statement;
and recording each piece of aggregation execution information into an execution information table respectively.
Optionally, the method further includes:
a recording unit 914, configured to record each SQL statement into the SQL statement table;
optionally, the storage unit 906 is specifically configured to: and storing the SQL statement table and the execution information table in an associated manner.
Optionally, statement identifiers of corresponding SQL statements are recorded in the SQL statement table and the execution information table, respectively.
Optionally, the method further includes:
a second request receiving unit 916, configured to receive a query request for the execution information;
a second determining unit 918, configured to determine a time period included in the query request;
the query unit 920 is configured to query the SQL statement table and the execution information table, and output the SQL statement in the corresponding time period in the SQL statement table and the aggregated execution information in the corresponding time period in the execution information table, respectively.
The implementation process of the functions and actions of each unit in the above device is specifically described in the implementation process of the corresponding step in the above method, and is not described herein again.
Fig. 10 shows a schematic structural diagram of an electronic device according to an exemplary embodiment of the present description. Referring to fig. 10, at the hardware level, the electronic device includes a processor 1002, an internal bus 1004, a network interface 1006, a memory 1008, and a non-volatile memory 1010, but may also include hardware required for other services. The processor 1002 reads a corresponding computer program from the nonvolatile memory 1010 into the memory 908 and then runs the computer program, thereby forming an execution information acquisition apparatus on a logical level. Of course, besides the software implementation, the present specification does not exclude other implementations, such as logic devices or a combination of software and hardware, and the like, that is, the execution subject of the following processing flow is not limited to each logic unit, and may be hardware or logic devices.
Referring to fig. 11, in a software implementation, the execution information acquiring apparatus may include a feature information log collecting unit 1102 and a sending unit 1104. Wherein:
a log collection unit 1102, configured to collect an SQL log generated by a target database deployed on a local device, where the SQL log includes execution information of SQL statements;
the sending unit 1104 is configured to send the acquired SQL log to a requesting party, so that the requesting party analyzes the SQL log to obtain corresponding execution information.
Optionally, the acquisition unit 1102 is specifically configured to:
periodically collecting SQL logs generated by the target database, wherein each period comprises a collection time period and a waiting time period; if the data volume of the SQL log collected in the current period is not smaller than the preset data volume threshold, the waiting time period is set as a first waiting time period, and if the data volume of the SQL log collected is smaller than the preset data volume threshold, the waiting time period is set as a second waiting time period larger than the first waiting time period.
Optionally, the sending unit 1104 is specifically configured to:
and under the condition that the data volume of the acquired SQL log is larger than a preset data volume threshold, transmitting the acquired SQL log to a demand side.
The systems, devices, modules or units illustrated in the above embodiments may be implemented by a computer chip or an entity, or by a product with certain functions. A typical implementation device is a computer, which may take the form of a personal computer, laptop computer, cellular telephone, camera phone, smart phone, personal digital assistant, media player, navigation device, email messaging device, game console, tablet computer, wearable device, or a combination of any of these devices.
In a typical configuration, a computer includes one or more processors (CPUs), input/output interfaces, network interfaces, and memory.
The memory may include forms of volatile memory in a computer readable medium, Random Access Memory (RAM) and/or non-volatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). Memory is an example of a computer-readable medium.
Computer-readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), Read Only Memory (ROM), Electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), Digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic disk storage, quantum memory, graphene-based storage media or other magnetic storage devices, or any other non-transmission medium that can be used to store information that can be accessed by a computing device. As defined herein, a computer readable medium does not include a transitory computer readable medium such as a modulated data signal and a carrier wave.
It should also be noted that the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.
The foregoing description has been directed to specific embodiments of this disclosure. Other embodiments are within the scope of the following claims. In some cases, the actions or steps recited in the claims may be performed in a different order than in the embodiments and still achieve desirable results. In addition, the processes depicted in the accompanying figures do not necessarily require the particular order shown, or sequential order, to achieve desirable results. In some embodiments, multitasking and parallel processing may also be possible or may be advantageous.
The terminology used in the description of the one or more embodiments is for the purpose of describing the particular embodiments only and is not intended to be limiting of the description of the one or more embodiments. As used in one or more embodiments of the present specification and the appended claims, the singular forms "a," "an," and "the" are intended to include the plural forms as well, unless the context clearly indicates otherwise. It should also be understood that the term "and/or" as used herein refers to and encompasses any and all possible combinations of one or more of the associated listed items.
It should be understood that although the terms first, second, third, etc. may be used in one or more embodiments of the present description to describe various information, such information should not be limited to these terms. These terms are only used to distinguish one type of information from another. For example, first information may also be referred to as second information, and similarly, second information may also be referred to as first information, without departing from the scope of one or more embodiments herein. The word "if" as used herein may be interpreted as "at … …" or "when … …" or "in response to a determination", depending on the context.
The above description is only for the purpose of illustrating the preferred embodiments of the one or more embodiments of the present disclosure, and is not intended to limit the scope of the one or more embodiments of the present disclosure, and any modifications, equivalent substitutions, improvements, etc. made within the spirit and principle of the one or more embodiments of the present disclosure should be included in the scope of the one or more embodiments of the present disclosure.
Claims (22)
1. A method of obtaining execution information, comprising:
acquiring an SQL log collected from a target database;
analyzing the SQL log to obtain the execution information of the SQL statement corresponding to the SQL log;
and storing the execution information.
2. The method of claim 1, the obtaining the SQL log collected from the target database, comprising:
sending a log collection instruction to a log collection component running on a target device, the target device being used to deploy the target database;
and receiving the SQL log collected from the target equipment by the log collection component.
3. The method of claim 2, the target database being a cloud database deployed on a plurality of target devices; sending a log collection instruction to a log collection component running on a target device, comprising:
and respectively sending log acquisition instructions to the log acquisition components operated on each target device so as to respectively acquire the SQL logs on each target device.
4. The method of claim 1, the execution information comprising at least one of: SQL statement, SQL execution time and return time, target database name for executing SQL statement.
5. The method of claim 1, storing the execution information, comprising:
the execution information is stored by a logging service and/or by a relational database.
6. The method of claim 1, storing the execution information, comprising:
acquiring a predefined storage template to perform structured conversion on the execution information to obtain structured execution information;
storing the structured execution information.
7. The method of claim 1, storing the execution information, comprising:
and respectively storing the execution information corresponding to each SQL statement.
8. The method of claim 7, further comprising:
receiving a query request for execution information;
determining a time period contained in the query request;
and outputting the execution information in the time period in a strip-by-strip manner or in an aggregation manner.
9. The method of claim 1, storing the execution information, comprising:
aggregating the execution information according to a preset time period to obtain aggregated execution information;
and storing the aggregation execution information.
10. The method of claim 9, wherein the aggregating the execution information according to a preset time period comprises:
respectively aggregating the execution information corresponding to each SQL statement according to a preset time period to obtain the aggregated execution information corresponding to each SQL statement;
and recording each piece of aggregation execution information into an execution information table respectively.
11. The method of claim 10, further comprising:
respectively recording each SQL statement into an SQL statement table;
the storing the aggregation execution information includes: and storing the SQL statement table and the execution information table in an associated manner.
12. The method according to claim 11, wherein the SQL statement table and the execution information table respectively record statement identifiers of corresponding SQL statements.
13. The method of claim 11, further comprising:
receiving a query request for the execution information;
determining a time period contained in the query request;
and querying the SQL statement table and the execution information table, and respectively outputting the SQL statement in the corresponding time period in the SQL statement table and the aggregation execution information in the corresponding time period in the execution information table.
14. A method of obtaining execution information, comprising:
acquiring an SQL log generated by a target database deployed on local equipment, wherein the SQL log comprises execution information of SQL statements;
and sending the collected SQL log to a demand party, and analyzing the SQL log by the demand party to obtain corresponding execution information.
15. The method of claim 14, the collecting SQL logs generated by a target database deployed on a local device, comprising: periodically collecting SQL logs generated by the target database, wherein each period comprises a collection time period and a waiting time period; if the data volume of the SQL log collected in the current period is not less than a preset data volume threshold, setting the waiting time period as a first waiting time, and if the data volume of the SQL log collected is less than the preset data volume threshold, setting the waiting time period as a second waiting time period which is greater than the first waiting time period;
the sending the collected SQL log to a demand side comprises the following steps: and under the condition that the data volume of the acquired SQL log is larger than a preset data volume threshold, transmitting the acquired SQL log to a demand side.
16. An apparatus for acquiring execution information, comprising:
the log acquisition unit is used for acquiring SQL logs acquired from a target database;
the log analysis unit is used for analyzing the SQL log to obtain the execution information of the SQL statement corresponding to the SQL log;
and the storage unit is used for storing the execution information.
17. An apparatus for acquiring execution information, comprising:
the system comprises a log acquisition unit, a database management unit and a database management unit, wherein the log acquisition unit is used for acquiring an SQL log generated by a target database deployed on local equipment, and the SQL log comprises execution information of SQL statements;
and the sending unit is used for sending the acquired SQL log to a demand party so that the demand party can analyze the SQL log to obtain corresponding execution information.
18. A system for obtaining execution information, comprising:
the cloud database cluster comprises a plurality of cloud database servers, a log acquisition component runs in each cloud database server, the log acquisition component is used for acquiring SQL logs generated by cloud databases deployed in the corresponding cloud database servers, and each SQL log comprises execution information of SQL statements;
and the query server is used for receiving the SQL log sent by the log acquisition component, respectively storing the execution information obtained by analyzing the SQL log into a log service and a log database, and querying the execution information stored in the log service and/or the log database according to the received query request.
19. An electronic device, comprising:
a processor;
a memory for storing processor-executable instructions;
wherein the processor implements the method of any one of claims 1-13 by executing the executable instructions.
20. A computer readable storage medium having stored thereon computer instructions which, when executed by a processor, carry out the steps of the method according to any one of claims 1 to 13.
21. An electronic device, comprising:
a processor;
a memory for storing processor-executable instructions;
wherein the processor implements the method of any one of claims 14-15 by executing the executable instructions.
22. A computer readable storage medium having stored thereon computer instructions which, when executed by a processor, carry out the steps of the method according to any one of claims 14-15.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010478675.2A CN113297245A (en) | 2020-05-29 | 2020-05-29 | Method and device for acquiring execution information |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202010478675.2A CN113297245A (en) | 2020-05-29 | 2020-05-29 | Method and device for acquiring execution information |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113297245A true CN113297245A (en) | 2021-08-24 |
Family
ID=77318044
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202010478675.2A Pending CN113297245A (en) | 2020-05-29 | 2020-05-29 | Method and device for acquiring execution information |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113297245A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116431698A (en) * | 2023-02-03 | 2023-07-14 | 北京优特捷信息技术有限公司 | Data extraction method, device, equipment and storage medium |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105653607A (en) * | 2015-12-23 | 2016-06-08 | 北京奇虎科技有限公司 | SQL (Structured Query Language) log collection and analysis method and device |
| WO2018058959A1 (en) * | 2016-09-28 | 2018-04-05 | 平安科技（深圳）有限公司 | Sql auditing method and apparatus, server and storage device |
| CN108182215A (en) * | 2017-12-22 | 2018-06-19 | 微梦创科网络科技（中国）有限公司 | A kind of method and device of structured query language SQL performance statistics |
-
2020
- 2020-05-29 CN CN202010478675.2A patent/CN113297245A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105653607A (en) * | 2015-12-23 | 2016-06-08 | 北京奇虎科技有限公司 | SQL (Structured Query Language) log collection and analysis method and device |
| WO2018058959A1 (en) * | 2016-09-28 | 2018-04-05 | 平安科技（深圳）有限公司 | Sql auditing method and apparatus, server and storage device |
| US20190228008A1 (en) * | 2016-09-28 | 2019-07-25 | Ping An Technology (Shenzhen) Co., Ltd. | Method, device, server and storage apparatus of reviewing sql |
| CN108182215A (en) * | 2017-12-22 | 2018-06-19 | 微梦创科网络科技（中国）有限公司 | A kind of method and device of structured query language SQL performance statistics |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN116431698A (en) * | 2023-02-03 | 2023-07-14 | 北京优特捷信息技术有限公司 | Data extraction method, device, equipment and storage medium |
| CN116431698B (en) * | 2023-02-03 | 2024-01-30 | 北京优特捷信息技术有限公司 | Data extraction method, device, equipment and storage medium |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107145489B (en) | Information statistics method and device for client application based on cloud platform | |
| CN108009236B (en) | Big data query method, system, computer and storage medium | |
| CN106202235B (en) | Data processing method and device | |
| CN108228322B (en) | Distributed link tracking and analyzing method, server and global scheduler | |
| CN111881011A (en) | Log management method, platform, server and storage medium | |
| CN111241122B (en) | Task monitoring method, device, electronic equipment and readable storage medium | |
| CN110147470B (en) | Cross-machine-room data comparison system and method | |
| CN106503008B (en) | File storage method and device and file query method and device | |
| US20150199408A1 (en) | Systems and methods for a high speed query infrastructure | |
| CN110807145A (en) | Query engine acquisition method, device and computer-readable storage medium | |
| CN111897867A (en) | Database log statistical method, system and related device | |
| CN113312376A (en) | Method and terminal for real-time processing and analysis of Nginx logs | |
| CN115168338A (en) | Data processing method, electronic device and storage medium | |
| CN111083008A (en) | Nginx-based traffic collection and analysis method | |
| CN114297204A (en) | Data storage and retrieval method and device for heterogeneous data source | |
| CN113297245A (en) | Method and device for acquiring execution information | |
| CN104317820B (en) | Statistical method and device for report forms | |
| CN110019045B (en) | Log floor method and device | |
| CN110019497B (en) | Data reading method and device | |
| US11645283B2 (en) | Predictive query processing | |
| CN113220530B (en) | Data quality monitoring method and platform | |
| CN114116646A (en) | Log data processing method, device, equipment and storage medium | |
| CN111782588A (en) | File reading method, device, equipment and medium | |
| CN112765200A (en) | Data query method and device based on Elasticissearch | |
| CN106557469B (en) | Method and device for processing data in data warehouse |
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 | ||
| REG | Reference to a national code |
Ref country code: HK Ref legal event code: DE Ref document number: 40057905 Country of ref document: HK |