CN117349308A - Method and device for monitoring slow query of database - Google Patents
Method and device for monitoring slow query of database Download PDFInfo
- Publication number
- CN117349308A CN117349308A CN202311278856.0A CN202311278856A CN117349308A CN 117349308 A CN117349308 A CN 117349308A CN 202311278856 A CN202311278856 A CN 202311278856A CN 117349308 A CN117349308 A CN 117349308A
- Authority
- CN
- China
- Prior art keywords
- query
- slow
- binlog file
- query statement
- binlog
- 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
- 238000012544 monitoring process Methods 0.000 title claims abstract description 136
- 238000000034 method Methods 0.000 title claims abstract description 57
- 210000001503 joint Anatomy 0.000 claims abstract description 8
- 230000006870 function Effects 0.000 claims description 20
- 230000004044 response Effects 0.000 claims description 11
- 238000005457 optimization Methods 0.000 claims description 9
- 238000012545 processing Methods 0.000 claims description 9
- 238000004891 communication Methods 0.000 claims description 7
- 238000012806 monitoring device Methods 0.000 claims description 5
- 230000000977 initiatory effect Effects 0.000 claims description 3
- 238000010586 diagram Methods 0.000 description 8
- 230000008569 process Effects 0.000 description 6
- 230000008859 change Effects 0.000 description 4
- 238000005516 engineering process Methods 0.000 description 3
- 239000000284 extract Substances 0.000 description 3
- 238000012360 testing method Methods 0.000 description 3
- 230000005540 biological transmission Effects 0.000 description 2
- 238000001514 detection method Methods 0.000 description 2
- OKTJSMMVPCPJKN-UHFFFAOYSA-N Carbon Chemical compound [C] OKTJSMMVPCPJKN-UHFFFAOYSA-N 0.000 description 1
- 238000013475 authorization Methods 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000001413 cellular effect Effects 0.000 description 1
- 238000004590 computer program Methods 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 229910021389 graphene Inorganic materials 0.000 description 1
- 230000007334 memory performance Effects 0.000 description 1
- 230000003287 optical effect Effects 0.000 description 1
- 230000003068 static effect Effects 0.000 description 1
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
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/3003—Monitoring arrangements specially adapted to the computing system or computing system component being monitored
- G06F11/302—Monitoring arrangements specially adapted to the computing system or computing system component being monitored where the computing system component is a software system
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/30—Monitoring
- G06F11/3051—Monitoring arrangements for monitoring the configuration of the computing system or of the computing system component, e.g. monitoring the presence of processing resources, peripherals, I/O links, software programs
-
- Y—GENERAL 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
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE 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/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computing Systems (AREA)
- Mathematical Physics (AREA)
- Quality & Reliability (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The specification provides a method and a device for monitoring slow query of a database, which are applied to a monitoring system, wherein the monitoring system is in butt joint with a target database. The method comprises the following steps: receiving a binlog file sent by a target database, and locally storing the binlog file; the binlog file contains execution information of query sentences aiming at the target database; analyzing the binlog file to obtain execution information contained in the binlog file; and determining the slow query statement meeting the slow query condition from the query statements based on the execution information and the pre-configured slow query condition.
Description
Technical Field
The present disclosure relates to the field of database technologies, and in particular, to a method and apparatus for monitoring slow database query, an electronic device, and a machine-readable storage medium.
Background
The database is a warehouse for storing and managing data, the data in the database is stored according to a certain format, and a user can perform operations such as adding, modifying, deleting, inquiring and the like on the data in the database. In practical applications, the requirements of users on the execution time and stability of structured query language (StructuredQuery Language, SQL) sentences are getting higher and higher. However, with the increase of data volume and the change of business scene, the situation that the execution time of a plurality of SQL sentences is too long easily occurs, namely slow query occurs, and the database performance and user experience are seriously affected.
Disclosure of Invention
In a first aspect, the present application provides a method for monitoring slow query of a database, applied to a monitoring system, where the monitoring system interfaces with a target database; the method comprises the following steps:
receiving a binlog file sent by the target database, and locally storing the binlog file; the binlog file contains execution information of query sentences aiming at the target database;
analyzing the binlog file to obtain the execution information contained in the binlog file;
and determining a slow query statement meeting the slow query condition from the query statements based on the execution information and a pre-configured slow query condition.
Optionally, the execution information includes an execution duration of the query statement; the slow query condition comprises a time length threshold corresponding to the execution time length of the query statement;
based on the execution information and a pre-configured slow query condition, determining a slow query statement from the query statements, including:
judging whether the execution time length of the query statement is greater than the time length threshold value, and if so, determining that the query statement is a slow query statement.
Optionally, the execution information includes execution duration of the query statement and device performance information of the hardware device corresponding to the target database; the slow query condition comprises a plurality of time length thresholds corresponding to the execution time length of the query statement, and the time length thresholds are in one-to-one correspondence with a plurality of equipment performance ranges;
Based on the execution information and a pre-configured slow query condition, determining a slow query statement from the query statements, including:
the device of the hardware device corresponding to the target database determines a target performance range corresponding to the hardware device from the plurality of device performance ranges;
judging whether the execution time length of the query statement is greater than a time length threshold corresponding to the target performance range, and if so, determining that the query statement is a slow query statement.
Optionally, the target database opens a query statement recording function in the binlog function;
receiving a binlog file sent by the target database, including:
responding to the target database, generating a corresponding binlog file based on the executed query statement, sending the binlog file to the monitoring system, and receiving the binlog file sent by the target database.
Optionally, the method further comprises:
and periodically determining a target binlog file meeting the conditions from the stored binlog files according to a fixed time period, and deleting the target binlog file.
Optionally, the method further comprises:
and determining a target binlog file meeting the condition from the stored binlog files and deleting the target binlog file in response to the total data quantity of the binlog files stored by the monitoring system being larger than a preset threshold.
Optionally, the method further comprises: and initiating query optimization processing aiming at the target database based on the determined slow query statement.
In a second aspect, the present application provides a method for monitoring slow query of a database, which is applied to a target database, wherein the target database is in butt joint with a monitoring system; the method comprises the following steps:
executing a query statement aiming at the target database, and generating a corresponding binlog file in response to completion of the execution of the query statement; the binlog file contains the execution information of the query statement;
and sending the binlog file to the monitoring system so that the monitoring system locally stores the received binlog file, analyzes the binlog file, and further determines a slow query statement meeting the slow query condition from the query statement based on the execution information obtained by analysis and a pre-configured slow query condition.
In a third aspect, the present application provides a database slow query monitoring device, applied to a monitoring system, where the monitoring system interfaces with a target database; the device comprises:
the receiving unit is used for receiving the binlog file sent by the target database and locally storing the binlog file; the binlog file contains execution information of query sentences aiming at the target database;
The analyzing unit is used for analyzing the binlog file to obtain the execution information contained in the binlog file;
and the determining unit is used for determining a slow query statement meeting the slow query condition from the query statements based on the execution information and a pre-configured slow query condition.
In a fourth aspect, the present application provides a database slow query monitoring device, applied to a target database, where the target database is in butt joint with a monitoring system; the device comprises:
the generation unit is used for executing the query statement aiming at the target database, responding to the completion of the execution of the query statement and generating a corresponding binlog file; the binlog file contains the execution information of the query statement;
the sending unit is used for sending the binlog file to the monitoring system so that the monitoring system locally stores the received binlog file, analyzes the binlog file, and further determines a slow query statement meeting the slow query condition from the query statement based on the analyzed execution information and a pre-configured slow query condition.
The application also provides electronic equipment, which comprises a communication interface, a processor, a memory and a bus, wherein the communication interface, the processor and the memory are connected with each other through the bus;
The memory stores machine readable instructions, and the processor executes the database slow query monitoring method according to the first or second aspect by invoking the machine readable instructions.
The present application also provides a machine-readable storage medium storing machine-readable instructions that, when invoked and executed by a processor, implement the database slow query monitoring method of the first or second aspect.
The technical scheme provided by the application at least can comprise the following beneficial effects:
through the above embodiment, the monitoring system may receive the binlog file sent by the target database, and store the binlog file locally. The binlog file may contain execution information of the query term for the target database. Further, the monitoring system may parse the received binlog file to obtain execution information contained in the binlog file, and then determine a slow query statement from query statements for the target database based on the execution information and a pre-configured slow query condition. Therefore, the database does not need to start an additional slow query log function, namely, the database does not need to bear slow query monitoring, only the binlog file recorded with query sentences is transmitted to the monitoring system, and the monitoring system extracts the slow query sentences according to the received binlog file, so that real-time slow query monitoring under the condition of not increasing the load of the database is realized.
Drawings
FIG. 1 is a system architecture diagram illustrating a slow query monitoring of a database in accordance with an exemplary embodiment.
FIG. 2 is a flow chart illustrating a method of database slow query monitoring in accordance with an exemplary embodiment.
Fig. 3 is a hardware configuration diagram of an electronic device in which a database slow query monitoring apparatus is located, according to an exemplary embodiment.
FIG. 4 is a block diagram illustrating a database slow query monitoring apparatus in accordance with an exemplary embodiment.
FIG. 5 is a block diagram of another database slow query monitoring apparatus, as shown in an exemplary embodiment.
Detailed Description
Reference will now be made in detail to exemplary embodiments, examples of which are illustrated in the accompanying drawings. When the following description refers to the accompanying drawings, the same numbers in different drawings refer to the same or similar elements, unless otherwise indicated. The implementations described in the following exemplary examples are not representative of all implementations consistent with the present application. Rather, they are merely examples of apparatus and methods consistent with some aspects of the present application as detailed in the accompanying claims.
It should be noted that: in other embodiments, the steps of the corresponding method are not necessarily performed in the order shown and described in this specification. In some other embodiments, the method may include more or fewer steps than described in this specification. Furthermore, individual steps described in this specification, in other embodiments, may be described as being split into multiple steps; while various steps described in this specification may be combined into a single step in other embodiments.
As described above, the database is a repository for storing and managing data, and the data in the database is stored according to a certain format, so that a user can perform operations of adding, modifying, deleting, querying and the like on the data in the database. In practical application, the requirements of users on the execution time and stability of SQL sentences are higher and higher. However, with the increase of data volume and the change of business scene, the situation that the execution time of a plurality of SQL sentences is too long easily occurs, namely slow query occurs, and the database performance and user experience are seriously affected.
Moreover, with the development of the internet, more and more application programs need to interact with the database, and slow query not only affects the performance of the application program, but also may cause the user port to consume more time and resources.
In view of this, the present application aims to propose a method for monitoring slow queries of a database. The method records the execution information of the query statement by adopting a binary log (binlog) file through a database, sends the binlog file to a monitoring system, and analyzes the binlog file by the monitoring system to determine a slow query statement from the executed query statement, thereby realizing real-time slow query monitoring under the condition of not increasing the burden of the database.
When implemented, the method is applied to a monitoring system that interfaces with a target database. The monitoring system can receive the binlog file sent by the target database and store the binlog file locally; the binlog file contains execution information of query sentences aiming at the target database.
Further, the monitoring system may parse the binlog file to obtain the execution information contained in the binlog file.
Further, the monitoring system may determine a slow query statement that satisfies the slow query condition from the query statements based on the execution information and a pre-configured slow query condition.
Through the above embodiment, the monitoring system may receive the binlog file sent by the target database, and store the binlog file locally. The binlog file may contain execution information of the query term for the target database. Further, the monitoring system may parse the received binlog file to obtain execution information contained in the binlog file, and then determine a slow query statement from query statements for the target database based on the execution information and a pre-configured slow query condition. Therefore, the database does not need to start an additional slow query log function, namely, the database does not need to bear slow query monitoring, only the binlog file recorded with query sentences is transmitted to the monitoring system, and the monitoring system extracts the slow query sentences according to the received binlog file, so that real-time slow query monitoring under the condition of not increasing the load of the database is realized.
The following describes the present application through specific embodiments and in connection with specific application scenarios.
Referring to fig. 1, fig. 1 is a system architecture diagram illustrating a database slow query monitoring in accordance with an exemplary embodiment. One or more embodiments provided herein may be embodied in the system architecture shown in fig. 1 or a similar system architecture. As shown in fig. 1, the system architecture includes a monitoring system 10 and a data query system 20. Wherein the communication connection between the monitoring system 10 and the data query system 20 may be established in any possible way. In one illustrated embodiment, a communication connection may be established between the monitoring system 10 and the data interrogation system 20 by way of a wireless network.
As shown in fig. 1, the monitoring system 10 may include a plurality of electronic devices, specifically including an electronic device 101, an electronic device 102, and the like; the data query system 20 may include a plurality of electronic devices, including in particular, the electronic device 201, the electronic device 202, and so on. As shown in fig. 1, the electronic device 201 and the electronic device 202 may have a target database mounted thereon, and illustratively, the electronic device 201 and the electronic device 202 may have data stored in the target database, illustratively, the electronic device 201 and the electronic device 202 may also have corresponding query engines running thereon, and so on, which are not described in detail herein.
It should be noted that the specific type of the target database is not particularly limited in the present application. In an illustrated embodiment, the target database may be a relational database, such as a MySQL database, etc., as not specifically limited in this disclosure.
In an illustrated embodiment, a target database in the data query system 20 may obtain a query statement for the target database and execute the query statement.
In an illustrated embodiment, the target database may have a binlog function enabled, and in particular, may have a query statement record function in the binlog function enabled. In an embodiment, the present application may make corresponding settings in the configuration file of the target database to enable binlog to record the query operation for the target database.
Further, in response to completion of the query statement execution described above, the target database may generate a corresponding binlog file. Wherein, the binlog file can include the execution information of the executed query statement.
In addition, the specific type of the query sentence is not particularly limited in the present application. In an illustrated embodiment, the query statement may be any type of SQL statement, such as a select statement, a join statement, an agg statement, etc., as not specifically limited in this specification.
Further, the target database may send the generated binlog file to the monitoring system 10 with which it interfaces. Accordingly, the monitoring system 10 receives the binlog file sent by the target database. In an illustrated embodiment, the target database may send the generated binlog file to the monitoring system 10 with which it interfaces in real-time.
Further, the monitoring system 10 may parse the binlog file to obtain the execution information of the query sentence contained in the binog file.
In one illustrated embodiment, the monitoring system 10 may have pre-configured slow query conditions stored therein.
Further, the monitoring system 10 may determine, from the query sentences, a slow query sentence satisfying the slow query condition based on the analyzed execution information and the pre-configured slow query condition, and the description of the corresponding embodiment of fig. 2 will be omitted herein.
Therefore, the database in the method and the system can avoid starting an additional slow query log function, namely, load slow query monitoring is not needed, the database only needs to transmit the binlog file recorded with the query statement to the monitoring system, and the monitoring system detects the slow query statement according to the received binlog file, so that real-time slow query monitoring under the condition of not increasing the load of the database is realized, and the query performance of the database is further improved.
Further, the monitoring system 10 may store the execution information related to the determined slow query statement locally, or store the execution information in a database interfaced with the monitoring system 10, etc., which is not specifically limited in this specification.
In one illustrated embodiment, in response to the monitoring system 10 determining a slow query statement, the monitoring system 10 may further notify the associated database maintainer of the currently detected slow query condition in the form of a mail or message.
Further, the monitoring system 10 may perform query optimization on the target database based on the determined slow query statement, and specifically, reference may be made to the following description of the corresponding embodiment of fig. 2, which is not repeated herein.
In the illustrated embodiment, the electronic device 101, the electronic device 102, the electronic device 201, the electronic device 202, and the like may be a tablet computer, a notebook computer, a desktop computer, a server, or a server cluster formed by a plurality of servers, which are not specifically limited in this specification.
It should be understood that the system architecture shown in fig. 1 is merely illustrative, and in some possible embodiments, more or fewer devices than those shown in fig. 1 may be included in the system architecture, for example, a database that interfaces with the monitoring system 10 for storing execution information related to slow query statements, etc., which is not specifically limited in this specification.
Referring to fig. 2, fig. 2 is a flow chart illustrating a method for monitoring slow queries in a database according to an exemplary embodiment. The method can be applied to the system architecture shown in fig. 1, and particularly can be applied to a monitoring system, and the monitoring system can be in butt joint with a target database. As shown in fig. 2, the method may specifically include the following steps S202-S206.
Step 202, receiving a binlog file sent by a target database, and locally storing the binlog file; the binlog file contains execution information of query sentences aiming at the target database.
In an illustrated embodiment, as described above, the target database may have a binlog function enabled, and in particular, may have a query statement record function in the binlog function enabled. In an embodiment, the present application may perform corresponding settings in a configuration file of the target database to enable binlog to record the execution of the query operation with respect to the target database.
Further, the target database may receive a query statement for the target database and execute the query statement. In response to completion of the query statement execution, the target database may generate a corresponding binlog file.
Wherein, the binlog file can include the execution information of the executed query statement. Specifically, each binlog file may include execution information of one or more query sentences, which is not specifically limited in this specification.
By way of example, the execution information may include a combination of one or more of the information shown below: the execution time of the query term, the query object of the query term, the device performance information of the hardware device corresponding to the target database for executing the query term, the query term itself, and the like, which are not particularly limited in this specification. Wherein the query object of the query statement may be one or more data tables in the target database. By way of example, the device performance information of the hardware device may include, for example, CPU performance, memory performance, network performance, storage performance, etc., which is not specifically limited in this specification.
Further, in an illustrated embodiment, the target database may send the generated binlog file to a monitoring system that interfaces with it. Correspondingly, the monitoring system receives the binlog file sent by the target database.
Further, the monitoring system may store the received binlog file locally. In this way, the target database does not need to locally store the binlog file containing the execution information of the query statement, but can transmit the execution information to the monitoring system, and the monitoring system is responsible for storing the execution information. It should be appreciated that query operations tend to be much more frequent than data write operations, change operations, and delete operations to the database, and thus the present application may effectively reduce the binlog file storage pressure locally to the database.
And 204, analyzing the binlog file to obtain the execution information contained in the binlog file.
In one illustrated embodiment, the monitoring system may parse the received binlog file to obtain the execution information contained in the binlog file.
In an embodiment shown, a binlog parsing tool corresponding to the target database may be installed in the monitoring system, and accordingly, the monitoring system may parse the received binlog file through the binlog parsing tool. Illustratively, the parsing tool may be an Open replier of MySQL database, etc., which is not specifically limited in this specification.
And 206, determining a slow query statement meeting the slow query condition from the query statements based on the execution information and a pre-configured slow query condition.
In an illustrated embodiment, the monitoring system may have pre-configured slow query conditions stored therein. Correspondingly, the monitoring system can determine the slow query statement meeting the slow query condition from the query statement contained in the binlog file based on the execution information obtained by analyzing the binlog file and the pre-configured slow query condition.
In an embodiment, the execution information may include an execution duration of the query term; the slow query condition may include a duration threshold corresponding to an execution duration of the query statement. Correspondingly, when the monitoring system determines a slow query statement meeting the slow query condition from query statements contained in the binlog file based on the execution information and the pre-configured slow query condition, the monitoring system specifically may include: judging whether the execution time length of the query statement is greater than a time length threshold value in the slow query condition, and if so, determining that the query statement is a slow query statement.
The time period threshold may be 2s, 5s or 10s, etc., which is not specifically limited in this specification, and may be specifically set according to actual situations and requirements.
In an embodiment, the execution information may include an execution duration of the query statement and device performance information of the hardware device corresponding to a target database for executing the query statement; the slow query condition may include a plurality of time duration thresholds corresponding to execution time durations of the query statement, where the plurality of time duration thresholds may correspond to a plurality of device performance ranges. Correspondingly, when the monitoring system determines a slow query statement meeting the slow query condition from query statements contained in the binlog file based on the execution information and the pre-configured slow query condition, the monitoring system specifically may include: determining a target performance range corresponding to the hardware device from the plurality of device performance ranges based on the device performance information of the hardware device corresponding to the target database executing the query statement; further, the monitoring system may determine whether the execution duration of the query statement is greater than a duration threshold corresponding to the target performance range in the slow query condition, and if so, may determine that the query statement is a slow query statement.
By way of example, the plurality of duration thresholds corresponding to the plurality of device performance ranges may be 1s, 5s, 10s, etc., which are not specifically limited in this specification. It will be appreciated that the better the device performance, the shorter its corresponding duration threshold will generally be, and the worse the device performance, the longer its corresponding duration threshold will generally be.
In an embodiment, the execution information may not include the device performance information, but after the target database and the monitoring system establish a communication connection, the target database may send the device performance information of the hardware device to the monitoring system, and the monitoring system may store the device performance information of the hardware device corresponding to the target database, which is not limited in this specification.
In addition, in some possible embodiments, the execution information may further include a query object, i.e. a data table, of the query statement. Considering that the execution of a query sentence generally takes a long time when the data table size is very large, for example, the data amount reaches TB and above, if the execution duration of the query sentence is longer than the above-described duration threshold value and the data amount of the data table queried by the query sentence is smaller than a preset threshold value (for example, smaller than 1TB or smaller than 256 GB), it can be determined that the query sentence is a slow query sentence.
In one illustrated embodiment, the monitoring system may perform a slow query statement detection every time a binlog file is received; or the monitoring system can also uniformly detect the slow query statement after receiving a preset number (for example, 3, 5 or 20) binlog files; or, the monitoring system may also perform detection of the slow query statement periodically for all binlog files received in the period according to a preset duration period (for example, 10 minutes, 20 minutes, or 1 hour, etc.), which is not specifically limited in this specification.
Further, the monitoring system may store the execution information related to the determined slow query term (including, for example, the execution duration, the query object, the slow query term itself, etc.) locally, or store the execution information in another database that interfaces with the monitoring system, where the database may be a relational database, and this specification is not limited specifically.
In an embodiment, in response to the monitoring system determining the slow query statement, the monitoring system may further notify the relevant database maintainer of the currently detected slow query condition in the form of a mail or a short message, so that the maintainer can perform query optimization processing on the target database in time.
Further, the monitoring system can initiate query optimization processing for the target database based on the determined slow query statement. Specifically, the monitoring system may analyze the slow query statement to obtain a query type and a data type of each slow query statement, and so on, so as to determine a common slow query cause, and perform query optimization processing on the target database based on the analysis result.
By way of example, changes may be made to the data types in the data table, multiple data tables often related to join type queries may be integrated into one data table, fuzzy matching statements using like operators in select statements may be reduced, etc., as this specification is not specifically limited.
In an embodiment shown, the monitoring system may also periodically determine a target binlog file (e.g., the first received and stored binlog file, i.e., the oldest binlog file) that meets the condition from the stored binlog files according to a fixed period of time, and delete the target binlog file. Therefore, the storage pressure of the monitoring system can be effectively relieved, and the defect that the storage space is insufficient due to excessive data is prevented, so that the slow query monitoring performance of the monitoring system is affected.
The fixed duration period may be 10h, 24h or three days, etc., which is not specifically limited in this specification, and may be specifically set according to actual situations and requirements.
In an embodiment, the monitoring system may also determine a target binlog file meeting the condition from the stored binlog files and delete the target binlog file in response to the total data amount of the stored binlog files being greater than a preset threshold, which is not specifically limited in this specification.
Similarly, the method and the device can also regularly clean the execution information of the slow query statement stored in the monitoring system or another database so as to effectively relieve the storage pressure of the monitoring system or the database.
In an embodiment, the steps 202-206 may be performed during a test phase of the target database, and accordingly, the query statement may be a preset test statement. After the slow query test and the query optimization for the target database are completed, the query statement recording function in the binlog function of the target database can be closed, so that the burden of the target database in formal operation is reduced.
In summary, the monitoring system in the present application may receive the binlog file sent by the target database, and store the binlog file locally. The binlog file may contain execution information of the query term for the target database. Further, the monitoring system may parse the received binlog file to obtain execution information contained in the binlog file, and then determine a slow query statement from query statements for the target database based on the execution information and a pre-configured slow query condition. Therefore, the database does not need to start an additional slow query log function, namely, the database does not need to bear slow query monitoring, only the binlog file recorded with query sentences is transmitted to the monitoring system, and the monitoring system extracts the slow query sentences according to the received binlog file, so that real-time slow query monitoring under the condition of not increasing the load of the database is realized.
Corresponding to the embodiment of the database slow query monitoring method, the specification also provides an embodiment of the database slow query monitoring device.
Referring to fig. 3, fig. 3 is a hardware configuration diagram of an electronic device where a database slow query monitoring apparatus is located in an exemplary embodiment. The electronic device may be the electronic device in the monitoring system 10 shown in fig. 1 or the electronic device in the data query system 20. At the hardware level, the device includes a processor 302, an internal bus 304, a network interface 306, memory 308, and non-volatile storage 310, although other hardware required for the service is possible. One or more embodiments of the present description may be implemented in a software-based manner, such as by the processor 302 reading a corresponding computer program from the non-volatile storage 310 into the memory 308 and then running. Of course, in addition to software implementation, one or more embodiments of the present disclosure do not exclude other implementation manners, such as a logic device or a combination of software and hardware, etc., that is, the execution subject of the following processing flow is not limited to each logic unit, but may also be hardware or a logic device.
Referring to fig. 4, fig. 4 is a block diagram illustrating a database slow query monitoring apparatus according to an exemplary embodiment. The apparatus 40 may be applied to the electronic device shown in fig. 3, so as to implement the technical solution of the present specification. Wherein the apparatus 40 may be applied to a monitoring system that interfaces with a target database; as shown in fig. 4, the apparatus 40 may include:
a receiving unit 402, configured to receive a binlog file sent by the target database, and store the binlog file locally; the binlog file contains execution information of query sentences aiming at the target database;
the parsing unit 404 is configured to parse the binlog file to obtain the execution information included in the binlog file;
and a determining unit 406, configured to determine, from the query sentences, a slow query sentence that satisfies the slow query condition based on the execution information and a pre-configured slow query condition.
In this embodiment, the execution information includes an execution duration of the query statement; the slow query condition comprises a time length threshold corresponding to the execution time length of the query statement;
the determining unit 406 is specifically configured to:
judging whether the execution time length of the query statement is greater than the time length threshold value, and if so, determining that the query statement is a slow query statement.
In this embodiment, the execution information includes an execution duration of a query statement and device performance information of a hardware device corresponding to the target database; the slow query condition comprises a plurality of time length thresholds corresponding to the execution time length of the query statement, and the time length thresholds are in one-to-one correspondence with a plurality of equipment performance ranges;
the determining unit 406 is specifically configured to:
determining a target performance range corresponding to the hardware equipment from the plurality of equipment performance ranges based on equipment performance information of the hardware equipment corresponding to the target database;
judging whether the execution time length of the query statement is greater than a time length threshold corresponding to the target performance range, and if so, determining that the query statement is a slow query statement.
In this embodiment, the target database opens the query statement record function in the binlog function;
the receiving unit 402 is specifically configured to:
responding to the target database, generating a corresponding binlog file based on the executed query statement, sending the binlog file to the monitoring system, and receiving the binlog file sent by the target database.
In this embodiment, the apparatus 40 further includes a first file deleting unit configured to:
And periodically determining a target binlog file meeting the conditions from the stored binlog files according to a fixed time period, and deleting the target binlog file.
In this embodiment, the apparatus 40 further includes a second file deleting unit configured to:
and determining a target binlog file meeting the condition from the stored binlog files and deleting the target binlog file in response to the total data quantity of the binlog files stored by the monitoring system being larger than a preset threshold.
In this embodiment, the apparatus 40 further includes a query optimization unit, configured to: and initiating query optimization processing aiming at the target database based on the determined slow query statement.
Referring to fig. 5, fig. 5 is a block diagram of another database slow query monitoring apparatus according to an exemplary embodiment. The apparatus 50 may be applied to the electronic device shown in fig. 3 to implement the technical solution of the present specification. Wherein the apparatus 50 may be applied to a target database that interfaces with a monitoring system; as shown in fig. 5, the apparatus 50 may include:
a generating unit 502, configured to execute a query statement for the target database, and generate a corresponding binlog file in response to completion of execution of the query statement; the binlog file contains the execution information of the query statement;
And a sending unit 504, configured to send the binlog file to the monitoring system, so that the monitoring system locally stores the received binlog file, parses the binlog file, and further determines a slow query statement that meets the slow query condition from the query statement based on the parsed execution information and a pre-configured slow query condition.
The implementation process of the functions and roles of the units in the above-mentioned device 40 and device 50 is specifically described in detail in the implementation process of the corresponding steps in the method, and will not be described herein again.
For the device embodiments, reference is made to the description of the method embodiments for the relevant points, since they essentially correspond to the method embodiments. The apparatus embodiments described above are illustrative only, in that the elements illustrated as separate elements may or may not be physically separate, and the elements shown as elements may or may not be physical elements, may be located in one place, or may be distributed over a plurality of network elements. Some or all of the modules may be selected according to actual needs to achieve the purposes of the present description. Those of ordinary skill in the art will understand and implement the present invention without undue burden.
The system, apparatus, module or unit illustrated in the embodiments may be implemented in particular by a computer chip or entity or by a product having a certain function. A typical implementation device is a computer, which may be in the form of a personal computer, laptop computer, cellular telephone, camera phone, smart phone, personal digital assistant, media player, navigation device, email 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 volatile memory in a computer-readable medium, random Access Memory (RAM) and/or nonvolatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). Memory is an example of computer-readable media.
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 storage media for a computer 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, read only 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 or other magnetic storage devices, or any other non-transmission medium, which can be used to store information that can be accessed by the computing device. Computer-readable media, as defined herein, does not include transitory computer-readable media (transmission media), such as modulated data signals and carrier waves.
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 one … …" does not exclude the presence of other like elements in a process, method, article or apparatus that comprises the element.
Specific embodiments of the present description have been described. Other embodiments are within the scope of the following claims. In some cases, the actions or steps recited in the claims can 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 are also possible or may be advantageous.
The terminology used in the one or more embodiments of the specification is for the purpose of describing particular embodiments only and is not intended to be limiting of the one or more embodiments of the specification. As used in this specification, one or more embodiments 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 or 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, these 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 of the present description. The word "if" as used herein may be interpreted as "at … …" or "at … …" or "responsive to a determination", depending on the context.
The foregoing description of the preferred embodiment(s) is (are) merely intended to illustrate the embodiment(s) of the present invention, and it is not intended to limit the embodiment(s) of the present invention to the particular embodiment(s) described.
User information (including but not limited to user equipment information, user personal information, etc.) and data (including but not limited to data for analysis, stored data, presented data, etc.) referred to herein are both user-authorized or fully authorized information and data by parties, and the collection, use and processing of relevant data requires compliance with relevant laws and regulations and standards of the relevant country and region, and is provided with corresponding operation portals for user selection of authorization or denial.
Claims (12)
1. The slow query monitoring method of the database is applied to a monitoring system, and the monitoring system is in butt joint with a target database; the method comprises the following steps:
receiving a binlog file sent by the target database, and locally storing the binlog file; the binlog file contains execution information of query sentences aiming at the target database;
Analyzing the binlog file to obtain the execution information contained in the binlog file;
and determining a slow query statement meeting the slow query condition from the query statements based on the execution information and a pre-configured slow query condition.
2. The method of claim 1, the execution information comprising an execution duration of a query statement; the slow query condition comprises a time length threshold corresponding to the execution time length of the query statement;
based on the execution information and a pre-configured slow query condition, determining a slow query statement from the query statements, including:
judging whether the execution time length of the query statement is greater than the time length threshold value, and if so, determining that the query statement is a slow query statement.
3. The method of claim 1, the execution information comprising an execution duration of a query statement and device performance information of a hardware device corresponding to the target database; the slow query condition comprises a plurality of time length thresholds corresponding to the execution time length of the query statement, and the time length thresholds are in one-to-one correspondence with a plurality of equipment performance ranges;
based on the execution information and a pre-configured slow query condition, determining a slow query statement from the query statements, including:
Determining a target performance range corresponding to the hardware equipment from the plurality of equipment performance ranges based on equipment performance information of the hardware equipment corresponding to the target database;
judging whether the execution time length of the query statement is greater than a time length threshold corresponding to the target performance range, and if so, determining that the query statement is a slow query statement.
4. The method of claim 1, wherein the target database opens a query statement record function in a binlog function;
receiving a binlog file sent by the target database, including:
responding to the target database, generating a corresponding binlog file based on the executed query statement, sending the binlog file to the monitoring system, and receiving the binlog file sent by the target database.
5. The method of claim 1, the method further comprising:
and periodically determining a target binlog file meeting the conditions from the stored binlog files according to a fixed time period, and deleting the target binlog file.
6. The method of claim 1, the method further comprising:
and determining a target binlog file meeting the condition from the stored binlog files and deleting the target binlog file in response to the total data quantity of the binlog files stored by the monitoring system being larger than a preset threshold.
7. The method of any of claims 1-6, the method further comprising: and initiating query optimization processing aiming at the target database based on the determined slow query statement.
8. The slow query monitoring method of the database is applied to a target database, and the target database is in butt joint with a monitoring system; the method comprises the following steps:
executing a query statement aiming at the target database, and generating a corresponding binlog file in response to completion of the execution of the query statement; the binlog file contains the execution information of the query statement;
and sending the binlog file to the monitoring system so that the monitoring system locally stores the received binlog file, analyzes the binlog file, and further determines a slow query statement meeting the slow query condition from the query statement based on the execution information obtained by analysis and a pre-configured slow query condition.
9. The slow database query monitoring device is applied to a monitoring system, and the monitoring system is in butt joint with a target database; the device comprises:
the receiving unit is used for receiving the binlog file sent by the target database and locally storing the binlog file; the binlog file contains execution information of query sentences aiming at the target database;
The analyzing unit is used for analyzing the binlog file to obtain the execution information contained in the binlog file;
and the determining unit is used for determining a slow query statement meeting the slow query condition from the query statements based on the execution information and a pre-configured slow query condition.
10. The slow query monitoring device for the database is applied to a target database, and the target database is in butt joint with a monitoring system; the device comprises:
the generation unit is used for executing the query statement aiming at the target database, responding to the completion of the execution of the query statement and generating a corresponding binlog file; the binlog file contains the execution information of the query statement;
the sending unit is used for sending the binlog file to the monitoring system so that the monitoring system locally stores the received binlog file, analyzes the binlog file, and further determines a slow query statement meeting the slow query condition from the query statement based on the analyzed execution information and a pre-configured slow query condition.
11. An electronic device comprises a communication interface, a processor, a memory and a bus, wherein the communication interface, the processor and the memory are connected with each other through the bus;
The memory stores machine readable instructions, the processor performing the method of any of claims 1 to 7 or performing the method of claim 8 by invoking the machine readable instructions.
12. A machine-readable storage medium storing machine-readable instructions which, when invoked and executed by a processor, implement the method of any one of claims 1 to 7 or perform the method of claim 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311278856.0A CN117349308A (en) | 2023-09-28 | 2023-09-28 | Method and device for monitoring slow query of database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202311278856.0A CN117349308A (en) | 2023-09-28 | 2023-09-28 | Method and device for monitoring slow query of database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN117349308A true CN117349308A (en) | 2024-01-05 |
Family
ID=89370345
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202311278856.0A Pending CN117349308A (en) | 2023-09-28 | 2023-09-28 | Method and device for monitoring slow query of database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN117349308A (en) |
-
2023
- 2023-09-28 CN CN202311278856.0A patent/CN117349308A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| EP3158480B1 (en) | Data query method and apparatus | |
| CN107145489B (en) | Information statistics method and device for client application based on cloud platform | |
| CN110019218B (en) | Data storage and query method and equipment | |
| CN109582470B (en) | Data processing method and data processing device | |
| CN108228322B (en) | Distributed link tracking and analyzing method, server and global scheduler | |
| CN107688626B (en) | Slow query log processing method and device and electronic equipment | |
| CN110554948B (en) | Vdbranc performance test result analysis method, system and equipment | |
| CN111382182A (en) | Data processing method and device, electronic equipment and storage medium | |
| CN112346884A (en) | Message reach data processing method and device | |
| CN114297204A (en) | Data storage and retrieval method and device for heterogeneous data source | |
| CN111581226B (en) | Data sharing method and device based on big data platform and administrative enterprise cloud platform | |
| CN113297245A (en) | Method and device for acquiring execution information | |
| CN111435327B (en) | Log record processing method, device and system | |
| CN112130759A (en) | Parameter configuration method, system and related device of storage system | |
| CN110457279B (en) | Data offline scanning method and device, server and readable storage medium | |
| CN117271584A (en) | Data processing method and device, computer readable storage medium and electronic equipment | |
| CN117349308A (en) | Method and device for monitoring slow query of database | |
| CN113419924B (en) | Database operation risk prompt processing method and device based on session maintenance | |
| CN108021464B (en) | Bottom-pocketing processing method and device for application response data | |
| CN114895852A (en) | Data processing method, system, device, storage medium and electronic equipment | |
| CN114116646A (en) | Log data processing method, device, equipment and storage medium | |
| CN112699129A (en) | Data processing system, method and device | |
| CN117251384B (en) | Interface automation test case generation method and system | |
| CN114936212B (en) | Audit data synchronous processing method and device | |
| CN115038089B (en) | Multi-terminal data monitoring and collecting method based on information extraction |
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 |