CN113986736A - Slow SQL monitoring system and method based on JAVA proxy and bytecode enhancement - Google Patents
Slow SQL monitoring system and method based on JAVA proxy and bytecode enhancement Download PDFInfo
- Publication number
- CN113986736A CN113986736A CN202111271312.2A CN202111271312A CN113986736A CN 113986736 A CN113986736 A CN 113986736A CN 202111271312 A CN202111271312 A CN 202111271312A CN 113986736 A CN113986736 A CN 113986736A
- Authority
- CN
- China
- Prior art keywords
- sql
- execution plan
- bytecode enhancement
- bytecode
- expert experience
- 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 39
- 238000012544 monitoring process Methods 0.000 title claims abstract description 18
- 238000004458 analytical method Methods 0.000 claims abstract description 22
- 238000004891 communication Methods 0.000 claims abstract description 7
- 238000004806 packaging method and process Methods 0.000 claims 1
- 238000011161 development Methods 0.000 abstract description 2
- 238000012360 testing method Methods 0.000 abstract description 2
- 230000006870 function Effects 0.000 description 11
- 239000003795 chemical substances by application Substances 0.000 description 10
- 238000005516 engineering process Methods 0.000 description 7
- 230000009545 invasion Effects 0.000 description 3
- 230000000694 effects Effects 0.000 description 2
- 238000004519 manufacturing process Methods 0.000 description 2
- 230000008569 process Effects 0.000 description 2
- 238000012552 review Methods 0.000 description 2
- 239000008186 active pharmaceutical agent Substances 0.000 description 1
- 230000006399 behavior Effects 0.000 description 1
- 230000009286 beneficial effect Effects 0.000 description 1
- 230000008859 change Effects 0.000 description 1
- 230000007547 defect Effects 0.000 description 1
- 230000001419 dependent effect Effects 0.000 description 1
- 238000011981 development test Methods 0.000 description 1
- 238000010586 diagram Methods 0.000 description 1
- 238000005538 encapsulation Methods 0.000 description 1
- 230000002708 enhancing effect Effects 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000012545 processing Methods 0.000 description 1
- 230000009467 reduction Effects 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F11/00—Error detection; Error correction; Monitoring
- G06F11/36—Preventing errors by testing or debugging software
- G06F11/362—Software debugging
- G06F11/366—Software debugging using diagnostics
-
- 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
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Mathematical Physics (AREA)
- Computational Linguistics (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Computer Hardware Design (AREA)
- Quality & Reliability (AREA)
- Debugging And Monitoring (AREA)
Abstract
The invention relates to the technical field of databases, in particular to a slow SQL monitoring system and a method based on JAVA proxy and bytecode enhancement, which can efficiently find the problem of slow SQL in a development and test stage, and comprises the steps of intercepting the preparsestat in Mysql Jdbc when the JVM loads an application system class; performing bytecode enhancement on an executeQuery () method in the PrepareStation.class after intercepting the PrepareState.class, wherein the bytecode enhancement method comprises the following steps: acquiring a connection example of a current database; rewriting SQL, and adding explain keywords in the original SQL statement; executing the modified SQL to obtain an execution plan returned by the MySQL; analyzing an execution plan of the SQL; matching the analysis result of the SQL execution plan with an expert experience rule; and (4) executing the SQL interruption matched with the analysis result and the expert experience rule, and sending out notification information through an internal timely communication system. The invention also discloses a slow SQL monitoring system based on the JAVA agent and the bytecode enhancement.
Description
Technical Field
The invention relates to the technical field of databases, in particular to a slow SQL monitoring system and a method based on JAVA proxy and bytecode enhancement.
Background
Slow SQL (i.e., slow SQL) and inefficient SQL are common problems in database applications, and in order to avoid the negative effects of slow SQL, the efficiency of SQL is usually evaluated and monitored at the application and database levels. The slow SQL monitoring function provided by the database can effectively acquire the information of the slow SQL at the database level, but the slow SQL monitoring function requires the application system to operate in the production environment and has certain hysteresis; in order to eliminate the influence of slow SQL at the application level, the SQL workflow method generally needs to be reviewed by organizing the SQL code Review or SQL. Generally, the increase of the process links inevitably results in the reduction of the overall efficiency and the risk of management failure due to the fact that the process is not executed in place, so that a real-time and efficient method and system for monitoring slow SQL is urgently needed.
Disclosure of Invention
The invention aims to provide a slow SQL monitoring system and method based on JAVA proxy and bytecode enhancement.
The slow SQL monitoring method based on the JAVA agent and the bytecode enhancement comprises the following contents:
intercepting a preparseStatment in Mysql Jdbc when the JVM loads an application system class;
performing bytecode enhancement on an executeQuery () method in the PrepareStation.class after intercepting the PrepareState.class, wherein the bytecode enhancement method comprises the following steps:
acquiring a connection example of a current database; obtaining a code
Rewriting SQL, and adding explain keywords in the original SQL statement;
executing the modified SQL to obtain an execution plan returned by the MySQL;
analyzing an execution plan of the SQL;
matching the analysis result of the SQL execution plan with an expert experience rule;
and (4) executing the SQL interruption matched with the analysis result and the expert experience rule, and sending out notification information through an internal timely communication system.
The invention has the beneficial effects that: in the invention, the connection example of the current database is obtained, and the purpose is to obtain the execution code of the current database, so that the database can still be read through the obtained code after the original SQL statement is modified.
The method comprises the steps that an explain keyword is added in an original SQL statement, the purpose is to obtain an execution plan of a current database, and the explain keyword provides functions for the existing database.
According to the method, through a JAVA Agent technology, a JAVA bytecode enhancement technology and an MySQL execution plan analysis function, an analysis result formed by analyzing an MySQL execution plan is integrated and matched with an expert experience rule in an SQL review expert experience system of an application layer, and SQL interruption execution of which the analysis result is matched with the expert experience rule is interrupted, namely, the execution interruption and notification function is utilized, and slow SQL is timely discovered in a development test environment.
Further, in analyzing the execution plan of the SQL, the rule matching is also carried out on the SQL of full-table scanning, large-table query and file sorting.
If the SQL matched with the SQL execution plan has SQL of full table scanning, large table query and file sorting, the SQL execution belongs to slow SQL and needs to be executed for interruption.
Further, the method further comprises the step of continuously executing SQL which is not matched with the expert experience rule according to the original logic. It shows that there is no problem of slow SQL, so there is no need to execute interruption, SQL can continue to execute as it is.
Further, the method also comprises the steps of marking the bytecode enhancement function into an sql code.
The slow SQL monitoring system based on JAVA agent and bytecode enhancement comprises an interception module, wherein the interception module intercepts the preparseStatment in Mysql Jdbc when the JVM loads an application system class;
the system comprises a bytecode enhancement module, wherein the bytecode enhancement module performs bytecode enhancement on an executeQuery () method in the PrepareStation.
Further, the bytecode enhancement module comprises a connection instance acquisition submodule for acquiring a connection instance of the current database;
the SQL rewriting submodule is used for adding explain keywords in the original SQL sentence;
the execution plan acquisition sub-module is used for executing the modified SQL and acquiring an execution plan returned by the MySQL;
the analysis submodule is used for analyzing the SQL execution plan;
the matching sub-module is used for matching the analysis result of the SQL execution plan with the expert experience rule, and if the expert experience rule is matched, the SQL which hits the expert experience rule is interrupted and executed;
and the notification submodule is used for sending notification information through an internal timely communication system after the SQL which hits the expert experience rule is interrupted and executed.
Furthermore, the analysis submodule is also used for carrying out rule matching on SQL of full-table scanning, large-table query and file sorting.
The invention makes full use of JAVA agent technology and bytecode enhancement technology, so that the functions of the invention have no invasion to the application system, including dependent invasion and code invasion of Jar of the application system, meanwhile, the execution plan analysis and expert experience rules of MySQL are matched and integrated, the SQLReview flow of the application layer is systematized, and meanwhile, the slow SQL is discovered and solved in the test development link by utilizing the execution interruption and notification functions.
Drawings
Fig. 1 is a schematic block diagram of an embodiment of the slow SQL monitoring system based on JAVA agent and bytecode enhancement of the present invention.
Detailed Description
The following is further detailed by way of specific embodiments:
the slow SQL monitoring method based on the JAVA agent and the bytecode enhancement comprises the following contents:
intercepting preparseStatment in Mysql Jdbc when the JVM (namely a JAVA virtual machine) loads an application system class;
after the prefix specification.class is intercepted, bytecode enhancement is performed on the execute query () method in the prefix specification.class.
The embodiment is implemented based on the ASM technology to perform bytecode enhancement on the executeQuery () method in the preparesstate. It can be used to dynamically generate classes or to enhance the functionality of existing classes. The ASM can either directly generate the binary class file or dynamically change the class behavior before the class is loaded into the Java virtual machine.
In this embodiment, Jdbc refers to Java Database Connectivity (Jdbc for short), which is an application program interface in Java language used to specify how a client program accesses a Database, and provides methods such as querying and updating data in the Database. Briefly, a class of Java APIs for executing SQL statements allows us to directly use Java programming to operate on relational databases through Jdbc. Through encapsulation, developers can complete SQL execution by using a pure Java API.
The prepareStatment is precompiled, the efficiency can be greatly improved for batch processing, the prepareStatement is used for precompiling SQL, and the SQL is not analyzed and compiled again every time.
The executeQuery () method is a statement, such as a SELECT statement, that is used to generate a single result set (ResultSet). Is the most used method of executing SQL statements. This method is used to execute a SELECT statement, execute a query statement, and return a ResultSet object representing the query result after execution.
The Java execution has two stages, namely a compilation period and a running period, wherein the compilation period is used for compiling Java type files into Java byte code files, namely class files; the run time is to load the bytecode file into the memory of the JVM and finally form the type that the virtual machine can execute. The byte code enhancing technology is to modify byte code files during operation to enhance code performance and reduce redundant operations of codes.
The bytecode enhancement method is as follows:
acquiring a connection example of a current database;
rewriting SQL, and adding explain keywords in the original SQL statement;
executing the modified SQL to obtain an execution plan returned by the MySQL;
analyzing an execution plan of the SQL; in the embodiment, analyzing the execution plan of the SQL further includes performing rule matching on the SQL of full-table scanning, large-table query and file sorting.
Full-table scanning: when a database table is queried, each row of data in one table needs to be scanned;
and (3) large table query: inquiring the number of the line data to be scanned, wherein the poorer the inquiring performance of the scanned line data is;
and (3) file sorting: a data sorting implementation mode can realize data set sorting exceeding the memory capacity by introducing a temporary sorting result of file storage, and has the defect of poor performance, and the data set sorting is avoided as much as possible in actual production.
Matching the analysis result of the SQL execution plan with an expert experience rule; the expert experience rules described in this embodiment are as follows: table look-up, file ordering, etc., for more than 10 ten thousand rows in a full table scan.
The SQL which is matched with the analysis result and the expert experience rule is interrupted and executed, and notification information is sent out through an internal timely communication system; and (4) continuously executing the SQL of which the analysis result is not matched with the expert experience rule according to the original logic.
The method disclosed in this embodiment further includes marking the bytecode enhancement function as an sql code. The expansion mode of bytecode enhancement utilized by the scheme is JVM Agent technology, the expansion specification of the method requires that Agent functions are constructed into JAVA jar packages, and the names of the function jar packages needing to be added are specified in the starting parameters of the expanded application.
As shown in fig. 1, this embodiment further discloses a slow SQL monitoring system based on JAVA agent and bytecode enhancement, which includes an interception module, where the interception module intercepts the prepadetatment in Mysql Jdbc when the JVM loads an application system class;
the system comprises a bytecode enhancement module, wherein the bytecode enhancement module performs bytecode enhancement on an executeQuery () method in the PrepareStation.
The slow SQL monitoring system based on JAVA agent and byte code enhancement comprises a connection instance acquisition submodule for acquiring a connection instance of a current database;
the SQL rewriting submodule is used for adding explain keywords in the original SQL sentence;
the execution plan acquisition sub-module is used for executing the modified SQL and acquiring an execution plan returned by the MySQL;
the analysis sub-module is used for analyzing the SQL execution plan and performing rule matching on the SQL of full-table scanning, large-table query and file sorting;
the matching sub-module is used for matching the analysis result of the SQL execution plan with the expert experience rule, and if the expert experience rule is matched, the SQL which hits the expert experience rule is interrupted and executed;
and the notification submodule is used for sending notification information through an internal timely communication system after the SQL which hits the expert experience rule is interrupted and executed.
The foregoing is merely an example of the present invention, and common general knowledge in the field of known specific structures and characteristics is not described herein in any greater extent than that known in the art at the filing date or prior to the priority date of the application, so that those skilled in the art can now appreciate that all of the above-described techniques in this field and have the ability to apply routine experimentation before this date can be combined with one or more of the present teachings to complete and implement the present invention, and that certain typical known structures or known methods do not pose any impediments to the implementation of the present invention by those skilled in the art. It should be noted that, for those skilled in the art, without departing from the structure of the present invention, several changes and modifications can be made, which should also be regarded as the protection scope of the present invention, and these will not affect the effect of the implementation of the present invention and the practicability of the patent. The scope of the claims of the present application shall be determined by the contents of the claims, and the description of the embodiments and the like in the specification shall be used to explain the contents of the claims.
Claims (7)
1. The slow SQL monitoring method based on the JAVA agent and the bytecode enhancement is characterized by comprising the following contents:
intercepting a preparseStatment in Mysql Jdbc when the JVM loads an application system class;
performing bytecode enhancement on an executeQuery () method in the PrepareStation.class after intercepting the PrepareState.class, wherein the bytecode enhancement method comprises the following steps:
acquiring a connection example of a current database;
rewriting SQL, and adding explain keywords in the original SQL statement;
executing the modified SQL to obtain an execution plan returned by the MySQL;
analyzing an execution plan of the SQL;
matching the analysis result of the SQL execution plan with an expert experience rule;
and (4) executing the SQL interruption matched with the analysis result and the expert experience rule, and sending out notification information through an internal timely communication system.
2. The method of claim 1, further comprising performing rule matching on SQL for full table scan, large table query and file ordering in analyzing SQL execution plan.
3. The method of claim 1, further comprising continuing to perform SQL in a primitive logic on the basis of the SQL that the analysis result does not match the expert experience rule.
4. The method of claim 3, further comprising packaging the bytecode enhancement function as an SQL code.
5. The slow SQL monitoring system based on JAVA proxy and bytecode enhancement is characterized by comprising an interception module, wherein the interception module intercepts the preparsestat in Mysql Jdbc when the JVM loads an application system class;
the system comprises a bytecode enhancement module, wherein the bytecode enhancement module performs bytecode enhancement on an executeQuery () method in the PrepareStation.
6. The slow SQL monitoring system based on JAVA agent and bytecode enhancement of claim 5, wherein the bytecode enhancement module includes a connection instance obtaining sub-module for obtaining a connection instance of a current database;
the SQL rewriting submodule is used for adding explain keywords in the original SQL sentence;
the execution plan acquisition sub-module is used for executing the modified SQL and acquiring an execution plan returned by the MySQL;
the analysis submodule is used for analyzing the SQL execution plan;
the matching sub-module is used for matching the analysis result of the SQL execution plan with the expert experience rule, and if the expert experience rule is matched, the SQL which hits the expert experience rule is interrupted and executed;
and the notification submodule is used for sending notification information through an internal timely communication system after the SQL which hits the expert experience rule is interrupted and executed.
7. The system according to claim 6, wherein the analysis sub-module is further configured to perform rule matching on full table scan, large table query, and file order SQL.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111271312.2A CN113986736A (en) | 2021-10-29 | 2021-10-29 | Slow SQL monitoring system and method based on JAVA proxy and bytecode enhancement |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111271312.2A CN113986736A (en) | 2021-10-29 | 2021-10-29 | Slow SQL monitoring system and method based on JAVA proxy and bytecode enhancement |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113986736A true CN113986736A (en) | 2022-01-28 |
Family
ID=79744318
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111271312.2A Pending CN113986736A (en) | 2021-10-29 | 2021-10-29 | Slow SQL monitoring system and method based on JAVA proxy and bytecode enhancement |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113986736A (en) |
Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20120166483A1 (en) * | 2010-12-28 | 2012-06-28 | Akshat Choudhary | Systems and Methods for Database Proxy Request Switching |
| CN110083436A (en) * | 2019-05-14 | 2019-08-02 | 上海理想信息产业（集团）有限公司 | A kind of business datum real-time monitoring system and method based on Java bytecode enhancing technology |
| CN111026601A (en) * | 2019-09-23 | 2020-04-17 | 拉扎斯网络科技（上海）有限公司 | Monitoring method and device for Java application system, electronic equipment and storage medium |
| CN111444066A (en) * | 2020-05-18 | 2020-07-24 | 江苏电力信息技术有限公司 | ByteBuddy-based server performance index monitoring method |
| CN112395305A (en) * | 2020-11-11 | 2021-02-23 | 中国人寿保险股份有限公司 | SQL statement analysis method and device, electronic equipment and storage medium |
| CN112527620A (en) * | 2020-12-24 | 2021-03-19 | 北京百度网讯科技有限公司 | Database performance analysis method and device, electronic equipment, medium and product |
| CN113076253A (en) * | 2021-04-16 | 2021-07-06 | 北京京东拓先科技有限公司 | Test method and test device |
| CN113094225A (en) * | 2020-01-09 | 2021-07-09 | 北京搜狗科技发展有限公司 | Abnormal log monitoring method and device and electronic equipment |
| CN113360357A (en) * | 2021-06-01 | 2021-09-07 | 深圳前海微众银行股份有限公司 | Data monitoring method, system, equipment and computer program product |
-
2021
- 2021-10-29 CN CN202111271312.2A patent/CN113986736A/en active Pending
Patent Citations (9)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20120166483A1 (en) * | 2010-12-28 | 2012-06-28 | Akshat Choudhary | Systems and Methods for Database Proxy Request Switching |
| CN110083436A (en) * | 2019-05-14 | 2019-08-02 | 上海理想信息产业（集团）有限公司 | A kind of business datum real-time monitoring system and method based on Java bytecode enhancing technology |
| CN111026601A (en) * | 2019-09-23 | 2020-04-17 | 拉扎斯网络科技（上海）有限公司 | Monitoring method and device for Java application system, electronic equipment and storage medium |
| CN113094225A (en) * | 2020-01-09 | 2021-07-09 | 北京搜狗科技发展有限公司 | Abnormal log monitoring method and device and electronic equipment |
| CN111444066A (en) * | 2020-05-18 | 2020-07-24 | 江苏电力信息技术有限公司 | ByteBuddy-based server performance index monitoring method |
| CN112395305A (en) * | 2020-11-11 | 2021-02-23 | 中国人寿保险股份有限公司 | SQL statement analysis method and device, electronic equipment and storage medium |
| CN112527620A (en) * | 2020-12-24 | 2021-03-19 | 北京百度网讯科技有限公司 | Database performance analysis method and device, electronic equipment, medium and product |
| CN113076253A (en) * | 2021-04-16 | 2021-07-06 | 北京京东拓先科技有限公司 | Test method and test device |
| CN113360357A (en) * | 2021-06-01 | 2021-09-07 | 深圳前海微众银行股份有限公司 | Data monitoring method, system, equipment and computer program product |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN106168965B (en) | Knowledge graph construction system | |
| US7007275B1 (en) | Method and apparatus for automatic execution of concatenated methods across multiple heterogeneous data sources | |
| US6697835B1 (en) | Method and apparatus for high speed parallel execution of multiple points of logic across heterogeneous data sources | |
| US10394694B2 (en) | Unexplored branch search in hybrid fuzz testing of software binaries | |
| US11693912B2 (en) | Adapting database queries for data virtualization over combined database stores | |
| CN107038222B (en) | Database cache implementation method and system | |
| CN110532038B (en) | Parallel execution method based on Java intelligent contract | |
| CN110554954B (en) | Test case selection method combining static dependency and dynamic execution rule | |
| KR20160130256A (en) | Managing data profiling operations related to data type | |
| CN111443901A (en) | Business expansion method and device based on Java reflection | |
| Pan et al. | Program-input generation for testing database applications using existing database states | |
| CN113934786A (en) | Implementation method for constructing unified ETL | |
| CN113986736A (en) | Slow SQL monitoring system and method based on JAVA proxy and bytecode enhancement | |
| Zhang et al. | Application of micro-specialization to query evaluation operators | |
| CN114924733A (en) | Form dependency relationship management and form accurate rendering method and system | |
| CN114328161A (en) | Java interface test method and device, storage medium and electronic equipment | |
| CN113626823A (en) | Reachability analysis-based inter-component interaction threat detection method and device | |
| He et al. | An fp-tree based approach for mining all strongly correlated item pairs | |
| CN112287012B (en) | Method for realizing http interface calling by Spark SQL mode | |
| CN112416362B (en) | PDK compiling function implementation method | |
| Nagy et al. | Translating description logic queries to Prolog | |
| CN110968634B (en) | Method for realizing ETL conversion processing by utilizing programmable function based on XML description in big data scene | |
| CN116560667B (en) | Splitting scheduling system and method based on precompiled delay execution | |
| CN114840219B (en) | Distributed event processing system | |
| CN101420454A (en) | Substitute Web service data processing method based on service grid |
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 |