Full-link stress test data shunting system and method based on SQL engine

Abstract

The present invention relates to the technical field of full-link stress testing, and particularly to a full-link stress testing data shunting system and method based on an SQL engine. The system includes a parser, a configuration manager, a router, and an executor; the parser is used to parse the SQL statements of the incoming traffic and convert the SQL statements into an abstract syntax tree; the configuration manager is used to set a configuration file, and is also used to parse the configuration file to obtain configuration information and cache the configuration information in memory for use by the router; the router is used to obtain a routing result according to the configuration information and the parsed abstract syntax tree, and the content of the routing result includes routing the SQL statements to the corresponding databases at the bottom layer. The present application not only expands the scope of application, but also effectively improves the performance and efficiency of full-link stress testing.

Description

Technical Field

[0001] The present invention relates to the technical field of full-link stress testing, and particularly to a full-link stress testing data shunting system and method based on an SQL engine. Background Art

[0002] As an emerging software testing technology, full-link stress testing directly performs stress testing in the production system, aiming to accurately evaluate the performance of the online environment. Compared with conventional stress testing, full-link testing no longer deploys a separate test environment, but directly in the production environment, and performs stress testing on the system by simulating a large number of real user requests (for example, generating test cases based on real user requests through traffic playback technology). Through full-link stress testing, not only can the deployment cost be reduced, but also the authenticity of traffic and the environment can be ensured, and more reliable stress testing conclusions can be obtained.

[0003] When testing in the production environment, the most important thing is to ensure that the online service is not affected. One of the core issues is to ensure that the production data is not contaminated, that is, the data generated by testing should be distinguishable from the real production data. In the prior art, the data shunting technology based on the shadow database technology can ensure that the production data is not contaminated during the full-link stress testing process. The shadow database technology is to construct a database (referred to as a shadow database) that is exactly the same as the production database to store test data. The production database refers to the database used in the production environment to store production data. The same set of production environment systems can correspond to multiple different production databases. The shadow database is a database that stores test data and corresponds one-to-one with the production database. The shadow database and its corresponding production database usually have the same number of tables, table structures, table names, and other system configurations. The tables in the shadow database are called shadow tables. In the typical architecture of the shadow database technology, production traffic and test traffic flow into the production environment system at the same time. After the production environment system performs some business processing, it is converted into SQL (Structured Query Language) statements at the data layer. During the data shunting process, the SQL statements are analyzed, and after judging whether the data is test data or production data through the shadow algorithm, the traffic is correctly routed to the production database or the shadow database. Currently, there are mainly two types of shadow algorithms, the column-based shadow algorithm and the Hint-based shadow algorithm. Among them, the column-based shadow algorithm matches and routes to the shadow database by identifying the data in the SQL; the Hint-based shadow algorithm matches and routes to the shadow database by identifying the comments in the SQL.

[0004] However, implementing a complete data shunting system presents the following challenges: (1) High system complexity. Different projects may use different relational database systems (such as Oracle, MySQL, SQL Server, etc.), and the protocols and dialects of different database systems are not the same. In addition, even for the same database system, the types of its SQL statements are diverse, including simple selection or insertion operations, to complex aggregation or table association operations. It is extremely challenging to support data shunting for all these relational database systems and different types of SQL statements in the same framework. (2) Diverse shunting criteria. Different application scenarios may adopt different data shunting criteria. (3) Transparency requirements. After adding data shunting, it is required that the existing production environment system code does not need to be modified at all. (4) High-efficiency requirements. Since an additional layer of routing selection is added in the middle, it is necessary to minimize the performance loss caused by routing as much as possible to ensure the correctness of the full-link stress test results.

[0005] In recent years, major Internet companies have actively built full-link stress test platforms, such as Quake of Meituan Dianping, Rhino of ByteDance, ForceBot of JD.com, WeTest of Tencent, Amazon of Alibaba, and TestPG of AutoNavi. However, these platforms focus on the entire stress test process and their own business logics, pay little attention to the data shunting part, and the platforms are all closed-source, so other institutions cannot directly use them. The open-source Takin system only supports data shunting according to tags and adopts a web forwarding framework, and its efficiency is greatly affected. Summary of the Invention

[0006] Aiming at the deficiencies of the above-mentioned existing technologies, the present invention provides a full-link stress test shunting system and method based on an SQL engine, which not only expands the applicable scope but also effectively improves the performance and efficiency of the full-link stress test.

[0007] In order to solve the above technical problems, the present invention adopts the following technical solutions:

[0008] A full-link stress test data shunting system based on an SQL engine, including a parser, a configuration manager, a router, and an executor;

[0009] The parser is used to parse the SQL statements of the incoming traffic and convert the SQL statements into an abstract syntax tree;

[0010] The configuration manager is used to set a configuration file, and is also used to parse the configuration file to obtain configuration information and cache the configuration information in memory for the router to use;

[0011] The router is used to obtain a routing result according to the configuration information and the parsed abstract syntax tree. The content of the routing result includes routing the SQL statement to the corresponding underlying database.

[0012] The executor is used to obtain the routing result of the router, and call the interface of the corresponding database through the database connection technology JDBC to send the SQL statement to the corresponding database for execution by the corresponding database. The corresponding database is a production database or a shadow database. The executor is also used to encapsulate the execution result and return it to the requester in the manner of the standard JDBC database protocol.

[0013] Preferably, the content of the configuration file includes the attributes of the production database, the attributes of the shadow database, the attributes of the shadow table, and the shadow algorithm used. The shadow algorithm is a column-based shadow routing algorithm or a Hint-based shadow routing algorithm.

[0014] Preferably, when the shadow algorithm is a column-based shadow routing algorithm, the content of the configuration file further includes a routing rule for shadow database matching. When the router routes the SQL statement, for the column-based shadow routing algorithm, the router first determines the set of tables T involved in the parsed syntax tree ast ast and the set of shadow tables T in the configuration file config to see if there is an intersection. If there is no intersection, then directly route this SQL statement to the production database. Otherwise, traverse each table t in T ast ∩T config . If the shadow field value of t in the SQL statement conforms to any routing rule, then route this SQL statement to the shadow database and end the iteration process in advance.

[0015] Preferably, when the shadow algorithm is a Hint-based shadow routing algorithm, the content of the configuration file further includes a hint shadow marker f config ; when the router routes the SQL statement, for the Hint-based shadow routing algorithm, the router verifies one by one whether each key-value pair marker in F ast is the same as f config . If it is the same, route it to the shadow database; if all markers are not the same as f config , then route it to the production database. Among them, F ast is the set of all key-value pair markers of the abstract syntax tree ast.

[0016] Preferably, when the shadow algorithm is a Hint-based shadow algorithm, the parser is also used to parse the hint shadow marker f config in the configuration file and generate a separate annotation node to be mounted on the abstract syntax tree.

[0017] Preferably, a variety of different database dialects are preset in the parser; the variety of different database dialects include the dialects of MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, and openGauss.

[0018] This application also provides a full-link stress test data shunting method based on an SQL engine. Using the above full-link stress test data shunting system based on an SQL engine, it includes the following steps:

[0019] S1. Set up a configuration file according to requirements and store it on the disk;

[0020] S2. Parse the SQL statements of the access traffic through the parser and convert the SQL statements into an abstract syntax tree;

[0021] S3. Parse the configuration file through the configuration manager to obtain configuration information, and cache the configuration information in memory for the router to use;

[0022] S4. According to the configuration information and the parsed abstract syntax tree, route the SQL statements to the corresponding underlying database through the router;

[0023] S5. Receive the routing result of the router through the executor, and call the interface of the corresponding database through the database connection technology JDBC, and send the SQL statements to the production database or the shadow database for the underlying database to execute;

[0024] S6. After the underlying database finishes execution, encapsulate the execution result through the executor and return it to the requester in the manner of the standard JDBC database protocol.

[0025] Preferably, in S1, the content of the configuration file includes the attributes of the production database, the attributes of the shadow database, the attributes of the shadow table, and the shadow algorithm used; wherein, the shadow algorithm is a column-based shadow routing algorithm or a Hint-based shadow routing algorithm.

[0026] Preferably, in S4, when routing the SQL statements to the corresponding underlying database through the router, if the shadow algorithm in the configuration information is a column-based shadow routing algorithm, the router first determines the set of tables T involved in the parsed syntax tree ast ast and the set of shadow tables T in the configuration file config whether there is an intersection. If there is no intersection, then directly route this SQL statement to the production database. Otherwise, traverse T ast ∩T configFor each table t in [[]], if the value of the shadow field of t in the SQL statement conforms to any routing rule, route this SQL statement to the shadow database and end the iteration process in advance.

[0027] Preferably, in S4, when the router routes the SQL statement to the corresponding database at the bottom layer, if the shadow algorithm in the configuration information is the Hint-based shadow routing algorithm, the router verifies each key-value pair mark in F ast to determine whether it is the same as f config and if so, route it to the shadow database; if all the marks are not the same as f config , route it to the production database; where F ast is the set of all key-value pair marks of the abstract syntax tree ast; f config is the hint shadow mark in the configuration file.

[0028] Compared with the prior art, the present invention has the following beneficial effects:

[0029] 1. Compared with the prior art, the present application for the first time designs and implements a complete open-source data shunting system for full-link stress testing based on SQL engine technology. Its basic idea is to parse the SQL statement to identify whether it is a stress testing request or a production request, and then correctly route this SQL statement to the corresponding database for execution in the underlying database, without any modification to the upper-layer application code. Moreover, the present application can not only perform data shunting according to marks, but also for the first time proposes to perform data shunting according to the values of given fields. Different data shunting algorithms meet the requirements of a variety of different application scenarios.

[0030] To verify the excellent performance of the present application, the applicant adopted two common test benchmarks to compare the performance of this system and Takin. The experiments prove that the present application is superior to the Takin system in terms of three criteria: average response time, percentile response time, and number of transactions processed per second. Specifically, the experimental results show that the TPS loss of this system relative to the native MySQL direct connection query is between 1% and 6%, and the average response time and 90th percentile response time are hardly affected. Compared with the open-source full-link stress testing product Takin, this system has an average performance improvement of two orders of magnitude.

[0031] 2. This application designs and implements a complete full-link stress test data shunting system based on an SQL engine. This system adopts plug-in development and can dynamically support multiple different relational database systems. The system of this application implements all JDBC (Java Database Connectivity) interfaces. An application using native JDBC only needs to modify a small amount of code for initializing the data source to use this application. If frameworks such as Hibernate or MyBatis are used, only the configuration file needs to be modified without modifying any code.

[0032] 3. The system in this application is embedded in the user program without network forwarding for requests, so the impact on efficiency is very small, and the correctness of the full-link stress test results can be guaranteed.

[0033] 4. In addition to being able to be used for full-link stress testing, this application can also be used in scenarios such as A / B testing, system warm-up, and gray release for data shunting. BRIEF DESCRIPTION OF THE DRAWINGS

[0034] In order to make the objectives, technical solutions, and advantages of the invention clearer, the present invention will be further described in detail below with reference to the accompanying drawings, where:

[0035] Figure 1 is the logical block diagram of the full-link stress test data shunting system based on the SQL engine in the embodiment;

[0036] Figure 2 is the content example diagram of the configuration example in the embodiment;

[0037] Figure 3 is the schematic diagram of the performance comparison of several different system methods in different scenarios under the Sysbench dataset in the embodiment;

[0038] Figure 4 is the example diagram of the performance comparison of several different system methods in different scenarios of the TPCC dataset in the embodiment;

[0039] Figure 5 is the example diagram of the performance comparison of several different system methods with the change of request concurrency under the Sysbench dataset in the embodiment;

[0040] Figure 6 is the example diagram of the performance comparison of several different system methods with the change of data volume under the Sysbench dataset in the embodiment. DETAILED DESCRIPTION OF THE EMBODIMENTS

[0041] The following will be further described in detail through specific embodiments:

[0042] Embodiment:

[0043] As shown Figure 1 In this embodiment, a full-link stress test data shunting system based on an SQL engine is disclosed, including a parser, a configuration manager, a router, and an executor. For ease of explanation, in this embodiment, the full-link stress test data shunting system based on the SQL engine of the present invention is named ShadowDB.

[0044] A variety of different database dialects are preset in the parser; the variety of different database dialects include the dialects of MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, and openGauss. The parser is used to parse the SQL statements of the incoming traffic and convert the SQL statements into an abstract syntax tree.

[0045] In this embodiment, the parser is developed based on ANTLR (a powerful structured text parsing generator), which converts the user's SQL statements into an abstract syntax tree (AST, Abstract Syntax Tree) to facilitate understanding of the semantic information of the SQL. Compared with the parsers of other databases, the parser of ShadowDB has two main differences. First, to support a variety of different underlying database systems, a variety of different database dialects are preset. In this embodiment, the parsing of 6 different database dialects is supported, including: MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, and openGauss. Specifically, any other database that conforms to the SQL-92 standard and the JDBC programming interface can be easily supported with minor modifications. Second, for the Hint-based shadow algorithm marking, ShadowDB will parse it and then generate a separate annotation node to be mounted on the abstract syntax tree for subsequent routing operations.

[0046] The configuration manager is used to set the configuration file, and is also used to parse the configuration file to obtain the configuration information and cache the configuration information in memory for use by the router. Specifically, the content of the configuration file includes the attributes of the production database, the attributes of the shadow database, the attributes of the shadow table, and the shadow algorithm used; the shadow algorithm is a column-based shadow routing algorithm or a Hint-based shadow routing algorithm.

[0047] For ease of understanding, as Figure 2As shown in the figure, this embodiment provides a configuration example. Lines 1 to 6 of the code define the connection parameters of the database, including information such as the connection address, username, and password of the database. Among them, db and shadowDb are logical database names (which can be customized by users), and the real database name can be specified through the url attribute; Lines 9 to 12 of the code define the production database and the shadow database. Here, only a mapping relationship needs to be established with the database defined previously. For example, in this example, the production database is db and the shadow database is shadowDb, and this mapping relationship is named shadowDataSource (a user-defined name); Lines 13 to 18 of the code define the shadow table and the shadow algorithm it adopts. Here, it means that the shadow table t_order adopts the mapping relationship of shadowDataSource and adopts a shadow algorithm named match-algorithm (a user-defined name); Lines 19 to 25 of the code define the column-based regular matching shadow algorithm match-algorithm, indicating that for the SQL statement for inserting data, when the value of uid is 0, it is routed to the shadow database; Lines 26 to 28 of the code define the hint-based shadow algorithm, indicating that when the SQL statement contains the comment hintFlag:hint (the comment mark can be specified by oneself), it is routed to the shadow database. Lines 29 to 30 of the code specify that the SQL engine needs to parse the comment to facilitate the execution of the Hint routing operation. Otherwise, the SQL engine will directly ignore the comment. For example, given the SQL statement "SELECT * FROM t_order / *hintFlag:hint* / ", because the comment contains the shadow mark, it will be directly routed to the shadow database.

[0048] The router is used to obtain a routing result according to the configuration information and the parsed abstract syntax tree. The content of the routing result includes routing the SQL statement to the corresponding database at the bottom layer. During specific implementation, for different shadow algorithms, their routing judgment processes are different.

[0049] When the shadow algorithm is a column-based shadow routing algorithm, the content of the configuration file further includes a routing rule, and the routing rule is used for shadow database matching; for easy understanding, a content example of the column-based shadow routing algorithm is given in this embodiment, as shown in Algorithm 1.

[0050]

[0051] When the router routes the SQL statement, for the column-based shadow routing algorithm, the router first judges the table set T involved in the parsed syntax tree ast ast and the shadow table set T in the configuration config whether there is an intersection. If there is no intersection, that is Route this SQL statement directly to the production database (line 10 of the code). Otherwise, traverse each table t in T ast ∩T config (lines 3 - 9 of the code). If the shadow field value of t in the SQL statement conforms to any routing rule (such as meeting the defined regular expression matching or value matching, and operation type matching), then route this SQL statement to the shadow database and end the iteration process in advance to accelerate the verification efficiency (line 6 of the code).

[0052] The shadow routing algorithm based on Hint is slightly different because the shadow routing algorithm based on Hint is not bound to the data table. For ease of understanding, a content example of the shadow routing algorithm based on Hint is given in this embodiment, as shown in Algorithm 2.

[0053]

[0054]

[0055] When the shadow algorithm is the shadow routing algorithm based on Hint, the content of the configuration file further includes the hint shadow marker f config ; When the router routes the SQL statement, for the shadow routing algorithm based on Hint, the router verifies one by one whether each key - value pair marker in F ast is the same as f config . If they are the same, route to the shadow database; if all markers are not the same as f config , route to the production database; where F ast is the set of all key - value pair markers of the abstract syntax tree ast.

[0056] The parser is also used to parse the hint shadow marker f in the configuration file when the shadow algorithm is the shadow algorithm based on Hint config and generate a separate annotation node to be mounted on the abstract syntax tree.

[0057] The executor is used to obtain the routing result of the router and call the interface of the corresponding database through the database connection technology JDBC to send the SQL statement to the corresponding database for the corresponding database to execute. The corresponding database is the production database or the shadow database; the executor is also used to encapsulate the execution result and return it to the requester in the manner of the standard JDBC database protocol. Thus, the execution process of the entire ShadowDB ends.

[0058] The present invention also provides a full - link stress test data shunting method based on the SQL engine. Using the above - mentioned full - link stress test data shunting system based on the SQL engine, it includes the following steps:

[0059] S1. Set up a configuration file according to requirements and store it on disk; the content of the configuration file includes the attributes of the production database, the attributes of the shadow database, the attributes of the shadow tables, and the shadow algorithm used; among them, the shadow algorithm is a column-based shadow routing algorithm or a Hint-based shadow routing algorithm.

[0060] S2. Parse the SQL statements of the access traffic through a parser and convert the SQL statements into an abstract syntax tree.

[0061] S3. Parse the configuration file through a configuration manager to obtain configuration information, and cache the configuration information in memory for use by the router.

[0062] S4. According to the configuration information and the parsed abstract syntax tree, route the SQL statements to the corresponding underlying database through the router.

[0063] Specifically, when routing the SQL statements to the corresponding underlying database through the router, if the shadow algorithm in the configuration information is a column-based shadow routing algorithm, the router first determines the set of tables T involved in the parsed syntax tree ast ast and the set of shadow tables T in the configuration file config to see if there is an intersection. If there is no intersection, then directly route this SQL statement to the production database. Otherwise, traverse each table t in T ast ∩T config . If the shadow field value of t in the SQL statement conforms to any routing rule, route this SQL statement to the shadow database and end the iteration process in advance.

[0064] When routing the SQL statements to the corresponding underlying database through the router, if the shadow algorithm in the configuration information is a Hint-based shadow routing algorithm, the router verifies one by one whether each key-value pair marker in F ast is the same as f config . If it is the same, route it to the shadow database; if all markers are not the same as f config , route it to the production database; where F ast is the set of all key-value pair markers of the abstract syntax tree ast; f config is the hint shadow marker in the configuration file.

[0065] S5. Receive the routing result of the router through an executor, and call the interface of the corresponding database through the database connection technology JDBC to send the SQL statements to the production database or the shadow database for execution by the underlying database.

[0066] S6. After the underlying database finishes execution, encapsulate the execution result through the executor and return it to the requester in the manner of the standard JDBC database protocol.

[0067] Compared with the prior art, for the first time, the present application designs and implements a complete open-source data shunting system for full-link stress testing based on SQL engine technology. The basic idea is to parse SQL statements to identify stress testing requests or production requests, and then correctly route the SQL statements to the corresponding databases for execution in the underlying databases. No modification is required for the upper-layer application code throughout the process. Moreover, the present application not only shunts data according to tags, but also for the first time proposes to shunt data according to values. Different data shunting algorithms meet the requirements of a variety of different application scenarios. In addition, the present application designs and implements a complete full-link stress testing data shunting system based on the SQL engine. The system adopts plug-in development and can dynamically support a variety of different relational database systems. The system of the present application implements all JDBC (Java Database Connectivity) interfaces. An application program using native JDBC only needs to modify a small amount of code for initializing the data source to use the present application. If frameworks such as Hibernate or MyBatis are used, only the configuration file needs to be modified without modifying any code. In addition, the system in the present application is embedded in the user program without the need for network forwarding of requests, so the impact on efficiency is very small, and the correctness of the full-link stress testing results can be guaranteed.

[0068] In addition to being able to be used for full-link stress testing, the present application can also be used in scenarios such as A / B testing, system warm-up, and gray release for data shunting. The following specifically describes application warm-up and recovery release.

[0069] Application Warm-up

[0070] Application warm-up refers to the process from starting the program to the program entering the optimal running state. When an application program starts, it takes some time to load dynamic libraries or system caches, and the performance of the system is often not optimal during this period. How to shorten the application warm-up time as much as possible is a problem that program developers need to face.

[0071] ShadowDB can shorten the application warm-up time by simulating stress testing traffic. The specific method is that users can use ShadowDB to configure a shadow database to isolate the warm-up traffic and real traffic. Then start the application program and send warm-up traffic to the application program. During the process of the application program receiving the warm-up traffic, the program itself can be quickly activated, and the warm-up traffic and real traffic can also be isolated by ShadowDB, thus simply and safely completing the application warm-up process.

[0072] Gray Release

[0073] Gray release is a software release method with a smooth transition. For example, in A / B testing, some users will continue to use version A of the application, while another part of the users will start using version B. If users can accept version B, then gradually switch the users using version A to version B, and repeat this process until all users using version A are switched to version B.

[0074] However, gray release faces a big challenge: if the underlying data tables between different versions are different, it is necessary to isolate the data of the new and old versions. ShadowDB can solve this problem well. In specific operations, users can use the functions of shadow databases and shadow tables in ShadowDB to isolate the data corresponding to different versions of the application. During the gradual switching of users, gradually switch the underlying data synchronization to complete the update of the old version data.

[0075] To verify the excellent performance of this application, the applicant adopted two common test benchmarks and compared the performance of this system with that of Takin. For easy understanding, in this embodiment, the data set, comparison method, and experimental settings will be described first, and then the experimental results will be presented and analyzed.

[0076] Data Set

[0077] In this embodiment, two widely used benchmark testing tools are used to evaluate the performance of ShadowDB:

[0078] (1) TPCC (version v5.0), a well-known OLTP (Online Transaction Processing) benchmark testing tool, simulates several transaction types often used in stores, including new order (no), payment (pm), order status query (os), stock level query (sl), and delivery (de). TPCC consists of 10 tables to form a warehouse, and each warehouse contains approximately 600,000 records;

[0079] (2) Sysbench, an open-source, modular, cross-platform multi-threaded performance testing tool, which provides a test table, and the number of records in the table can be adjusted by the user. Since the native Sysbench press is implemented in C language and cannot be directly used for Java applications, the applicant implemented a Java version of the press (the code has been open-sourced).

[0080] Comparison Method

[0081] Compare the performance of ShadowDB with two other systems using three metrics: Transactions Per Second (TPS), Average Response Time (ART), and 90th Percentile Response Time (90T).

[0082] (1) Native MySQL (version v5.7.26), which is equivalent to deploying a separate test environment where the test traffic is directly sent to the test database without routing time.

[0083] (2) Takin (version v1.0.1), the only open-source product for full-link stress testing currently available. It can significantly help enterprises reduce the development complexity of the production full-link stress testing platform. Without intruding on business code (connected via probes), it can obtain core production stress testing capabilities such as link governance, data isolation, and performance bottleneck location. It also uses shadow database technology to achieve data isolation. In the applicant's tests, the underlying database systems of ShadowDB and Takin are MySQL.

[0084] Experiment Setup

[0085] To eliminate the performance impact of the application itself, only the performance of SQL requests was tested. In this experiment, three virtual machines on Huawei Cloud were used, each equipped with a CentOS 7.1 64-bit operating system, 32 vCPUs, 64 GB of memory, and 1 TB of mechanical disk. For the ShadowDB system, one virtual machine runs the pressure generator (i.e., the application that submits SQL requests), and the other two run the production database and the shadow database respectively; for MySQL and Takin, the applicant selected one to run the MySQL system or the Takin system, and the other as the pressure generator. By default, the applicant used Sysbench as the test data, and its relevant parameter settings are shown in Table 1:

[0086] Table 1 - Sysbench Parameter Settings

[0087]

[0088] Performance Comparison with Comparative Methods

[0089] Performance Comparison Using Sysbench Dataset

[0090] Figure 3 Shows the performance comparison of different methods in different scenarios of the Sysbench dataset; among them, Figure 3(a) is a comparison graph of the number of transactions per second (TPS) using Sysbench as test data for MySQL, Takin, and the ShadowDB system of the present invention. Figure 3 (b) is a comparison graph of the average response time (ART) using Sysbench as test data for MySQL, Takin, and the ShadowDB system of the present invention. Figure 3 (c) is a comparison graph of the 90th percentile response time (90T) using Sysbench as test data for MySQL, Takin, and the ShadowDB system of the present invention. It can be seen from Figure 3 that: (1) For all scenarios, compared with the MySQL system, the TPS of ShadowDB has a slight decrease, and the ART and 90T have a slight increase. This is because ShadowDB needs to spend a small amount of time parsing and routing SQL statements; (2) The performance of the Takin system is far inferior to that of MySQL and ShadowDB.

[0091] There may be two reasons for this: First, Takin adopts a network forwarding framework. Its upper layer uses Spring Boot

[18] to receive SQL requests and then forwards the SQL requests to the database through the network, which takes more time; Second, the production database and the shadow database at the bottom of Takin are the same database, and data isolation is only achieved through the form of shadow tables. When production requests and test requests are sent to the same database on the same machine, resource conflicts may occur because the total available connection number, memory resources, and computing resources of the same database are limited; (3) The performance of different scenarios is different. Generally, the performance involving write operations (such as wo, rw, ui, etc.) is lower than that of only read operations (such as ps and ro). Since the read-write scenario (rw) is the most common scenario in application programs, in the subsequent Sysbench experiments, the applicant uses it as the default scenario.

[0092] 8.4.2 Performance Comparison Using the TPCC Dataset

[0093] Figure 4 shows the comparison of 90T in different scenarios of the TPCC dataset for the MySQL and ShadowDB systems. Note that Takin is not compared because a large amount of Takin code needs to be modified to be compatible with TPCC (from Figure 3It can be seen that the performance of Takin is very different from that of ShadowDB). The reason why the applicant did not show ART is that the TPCC version used by the applicant does not support the statistics of the average response time. In addition, the request ratio for each scenario in TPCC is different and fixed, and it is impossible to calculate the TPS for each scenario. Therefore, the applicant only gives the total TPS. Under the experimental settings of the applicant, the total TPS of MySQL and ShadowDB are 4982.55 and 4924.87 respectively, Figure 4 It also shows that under different TPCC scenarios, the difference in 90T between MySQL and ShadowDB is negligible, which further proves that using ShadowDB for full-link stress testing can ensure the reliability of the stress test results.

[0094] Performance comparison of different parameters

[0095] 1 Different query concurrency levels

[0096] Figure 5 The figure shows the comparison of the performance changes of different systems with the request concurrency level under the Sysbench dataset; among them, Figure 5(a) is a comparison graph of the number of transactions per second (TPS) of Takin and the ShadowDB system of the present invention using Sysbench as the test data changing with the request concurrency level, Figure 5 (b) is a comparison graph of the average response time (ART) of Takin and the ShadowDB system of the present invention using Sysbench as the test data changing with the request concurrency level, Figure 5 (c) are respectively comparison graphs of the 90th percentile response time (90T) of Takin and the ShadowDB system of the present invention using Sysbench as the test data changing with the request concurrency level; Figure 5 In the figure, since the data lines of MySQL and ShadowDB almost completely overlap, the results of MySQL are not drawn in the figure. From Figure 5It can be observed that: (1) As the access concurrency increases, the TPS of ShadowDB first increases and then levels off, while the TPS of Takin first increases and then gradually decreases; (2) As the concurrency increases, the ART and 90T of both ShadowDB and Takin show an upward trend, but the increase amplitude of Takin is greater; (3) For all concurrency levels, the performance of ShadowDB is far superior to that of Takin. For ShadowDB, since SQL routing is hardly affected by the network, its bottleneck lies in the underlying database system. When the concurrency increases, although resource contention occurs, ShadowDB can still return each request within a short time (within 25 milliseconds). For Takin, its bottleneck lies in the network. When the concurrency increases, the response time of each request reaches more than 2.5 seconds, resulting in a slow decrease in its TPS.

[0097] 2 Different data volumes

[0098] Figure 6 The performance of different methods was compared under the Sysbench dataset as the data volume changed. Among them, Figure 6 (a) is a comparison graph of the number of transactions per second (TPS) of MySQL, Takin, and the ShadowDB system of the present invention using Sysbench as test data changing with the data volume. Figure 6 (b) is a comparison graph of the average response time (ART) of MySQL, Takin, and the ShadowDB system of the present invention using Sysbench as test data changing with the data volume. Figure 6 (c) are respectively comparison graphs of the 90th percentile response time (90T) of MySQL, Takin, and the ShadowDB system of the present invention using Sysbench as test data changing with the data volume. It can be Figure 6 seen that as the data volume increases, the performance of MySQL and ShadowDB remains unchanged first, but when the data volume is greater than 14 million, their performance slowly decreases. Because the database system usually constructs indexes in a tree form, the larger the data volume in the data table, the higher the index level. Even for the same request, the number of disk accesses increases, resulting in an increase in response time and a decrease in TPS. Interestingly, for the Takin system, its performance seems to have nothing to do with the size of the underlying data volume. The reason may be that its bottleneck does not lie in the underlying database but in network forwarding. The slowdown of the underlying database is negligible compared to the time-consuming network forwarding. It can also be seen that the performance of ShadowDB and MySQL is comparable under different data volume conditions, while the performance of Takin is much worse.

[0099] Different shadow algorithms

[0100] To verify the excellent performance of this application, the applicant also compared the performance of different shadow algorithms of ShadowDB, and the results are shown in Table 2. It can be seen from the table that the performance of the hint-based shadow algorithm is slightly weaker than that of the column-based shadow algorithm. There are mainly two reasons. First, in the SQL parsing stage, the hint-based shadow algorithm requires the SQL parsing comments to be enabled, and parsing comments takes more time. Second, in the SQL routing stage, the column-based shadow algorithm can avoid further invalid checks by checking whether the tables involved in the SQL statement are shadow tables, while the hint-based shadow algorithm still needs to parse the key-value pairs in the comments during routing and then judge whether each one is a shadow label one by one, which consumes more time.

[0101] Table 2 Comparison of the performance of different shadow algorithms for Sysbench datasets

[0102]

[0103] The SQL engine is one of the core parts of a database system. Whether it is a traditional relational database system, a new database system, or a proprietary database system, the SQL engine has been implemented to help R & D personnel manage data more conveniently. The SQL engine usually includes four steps: SQL parsing, SQL verification, SQL optimization, and SQL execution.

[0104] In the full-link stress testing platform, it is rare to use the SQL engine for data routing. Usually, existing full-link stress testing platforms achieve data shunting by explicitly adding test marks in the request link or request header. However, this method cannot support the column-based shadow algorithm proposed in this paper. This paper uses the SQL parsing technology of the SQL engine to design and implement an SQL router for full-link stress testing, which can achieve the purpose of efficient data shunting and can also flexibly support multiple shadow algorithms. Experiments prove that this application is superior to the Takin system under three criteria: average response time, percentile response time, and number of transactions processed per second. Specifically, the experimental results show that the TPS loss of this system relative to the native MySQL direct connection query is between 1% and 6%, and the average response time and 90th percentile response time are hardly affected. Compared with the open-source full-link stress testing product Takin, this system has an average performance improvement of two orders of magnitude.

[0105] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention and not to limit the technical solutions. Those of ordinary skill in the art should understand that any modifications or equivalent replacements to the technical solutions of the present invention without departing from the purpose and scope of the present technical solution should be covered by the scope of the claims of the present invention.

Claims

1. A full-link stress test data shunting system based on an SQL engine, characterized in that: It includes a parser, a configuration manager, a router, and an executor; The parser is used to parse the SQL statements in the incoming traffic and convert the SQL statements into an abstract syntax tree; The configuration manager is used to set up a configuration file, and is also used to parse the configuration file to obtain configuration information, and cache the configuration information in memory for use by the router; The router is used to obtain a routing result according to the configuration information and the parsed abstract syntax tree, and the content of the routing result includes routing the SQL statement to the corresponding database at the bottom layer; The executor is used to obtain the routing result of the router, and call the interface of the corresponding database through the database connection technology JDBC, and send the SQL statement to the corresponding database for the corresponding database to execute, and the corresponding database is a production database or a shadow database; the executor is also used to encapsulate the execution result and return it to the requester in the manner of the standard JDBC database protocol.

2. The full-link stress test data shunting system based on the SQL engine according to claim 1, characterized in that: The content of the configuration file includes the attributes of the production database, the attributes of the shadow database, the attributes of the shadow table, and the shadow algorithm used; the shadow algorithm is a column-based shadow routing algorithm or a Hint-based shadow routing algorithm.

3. The full-link stress test data shunting system based on the SQL engine according to claim 2, wherein: When the shadow algorithm is a column-based shadow routing algorithm, the content of the configuration file further includes a routing rule for performing shadow library matching. When the router routes an SQL statement, for the column-based shadow routing algorithm, the router first determines the set of tables T involved in the parsed syntax tree ast ast and the set of shadow tables T in the configuration file config to see if there is an intersection. If there is no intersection, the SQL statement is directly routed to the production library. Otherwise, iterate through each table t in T ast ∩T config If the shadow field value of t in the SQL statement conforms to any routing rule, the SQL statement is routed to the shadow library and the iteration process is terminated in advance.

4. The full-link stress test data shunting system based on the SQL engine according to claim 3, characterized in that: When the shadow algorithm is the Hint-based shadow routing algorithm, the content of the configuration file further includes the hint shadow flag f config ; when the router routes SQL statements, for the Hint-based shadow routing algorithm, the router verifies one by one whether each key-value pair flag in F ast is the same as f config ; if they are the same, it routes to the shadow database; if all the flags are not the same as f config , it routes to the production database; where F ast is the set of all key-value pair flags of the abstract syntax tree ast 5. The full-link stress test data shunting system based on the SQL engine according to claim 4, characterized in that: The parser is also used to parse the hint shadow tag f in the configuration file when the shadow algorithm is the Hint-based shadow algorithm, and generate a separate annotation node to be mounted on the abstract syntax tree. config and generate a separate annotation node to be mounted on the abstract syntax tree.

6. The full-link stress test data shunting system based on the SQL engine according to claim 1, characterized in that: Multiple different database dialects are preset in the parser; the multiple different database dialects include the dialects of MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, and openGauss.

7. A full-link stress test data shunting method based on an SQL engine, characterized in that, Using the full-link stress test data shunting system based on the SQL engine described in any one of claims 1-6, includes the following steps: S1. Set up a configuration file according to requirements and store it on the disk; S2. Parse the SQL statements in the incoming traffic through the parser and convert the SQL statements into an abstract syntax tree; S3. Parse the configuration file through the configuration manager to obtain configuration information, and cache the configuration information in memory for use by the router; S4. According to the configuration information and the parsed abstract syntax tree, route the SQL statement to the corresponding database at the bottom layer through the router; S5. Receive the routing result of the router through the executor, and call the interface of the corresponding database through the database connection technology JDBC, and send the SQL statement to the production database or the shadow database for the underlying database to execute; S6. After the underlying database finishes execution, encapsulate the execution result through the executor and return it to the requester in the manner of the standard JDBC database protocol.

8. The full-link stress test data shunting method based on the SQL engine according to claim 7, wherein: In S1, the content of the configuration file includes the attributes of the production database, the attributes of the shadow database, the attributes of the shadow table, and the shadow algorithm used; wherein, the shadow algorithm is a column-based shadow routing algorithm or a Hint-based shadow routing algorithm.

9. The full-link stress test data shunting method based on the SQL engine according to claim 8, characterized in that: In S4, when the SQL statement is routed to the corresponding underlying database through the router, if the shadow algorithm in the configuration information is the column-based shadow routing algorithm, the router first determines the set of tables T involved in the parsed syntax tree ast ast and the set of shadow tables T in the configuration file config to check if there is an intersection. If there is no intersection, the SQL statement is directly routed to the production database. Otherwise, iterate through each table t in T ast ∩T config If the shadow field value of t in the SQL statement conforms to any routing rule, the SQL statement is routed to the shadow database and the iteration process is terminated prematurely.

10. The full-link stress test data shunting method based on the SQL engine according to claim 9, characterized in that: In S4, when the SQL statement is routed to the corresponding underlying database through the router, if the shadow algorithm in the configuration information is the hint-based shadow routing algorithm, the router verifies one by one whether each key-value pair tag in F ast is the same as f config . If they are the same, it is routed to the shadow database; if all tags are not the same as f config , it is routed to the production database; where F ast is the set of all key-value pair tags of the abstract syntax tree ast; f config is the hint shadow tag in the configuration file.

Images