Database performance determination method and apparatus, electronic device, and storage medium

Abstract

The application discloses a database performance determination method and device, electronic equipment and a storage medium. The method comprises the following steps: determining the execution time of a preset benchmark structured query statement in each database instance of a database cluster; determining a database to be optimized when the execution time of the benchmark structured query statement exceeds a first time threshold, and determining a slow query corresponding to each database to be optimized; determining a first performance parameter of the slow query according to an index value of a performance evaluation index corresponding to the slow query, wherein the first performance parameter is used to represent the influence degree of the slow query on the database performance; and determining a second performance parameter of the database to be optimized according to the first performance parameters of all slow queries corresponding to the database to be optimized. The application solves the technical problem that the database operation and maintenance tool in the related art cannot locate the worst database in the database cluster.

Description

Technical Field

[0001] This application relates to the field of database operation and maintenance technology, and more specifically, to a database performance determination method, apparatus, electronic device, and storage medium. Background Technology

[0002] Customer Relationship Management (CRM) systems are core systems for telecom operators. Performance issues in the database can significantly impact the efficiency of frontline operations. However, these systems are large, involving numerous subsystems and modules, and data often resides in multiple databases. Existing database maintenance tools are inadequate for performance troubleshooting, failing to quickly pinpoint the worst-performing database among multiple clusters. This results in a cumbersome, complex, and inefficient process for multi-cluster database performance optimization analysis for CRM systems.

[0003] There is currently no effective solution to the above problems. Summary of the Invention

[0004] This application provides a database performance determination method, apparatus, electronic device, and storage medium to at least solve the technical problem in related technologies that database operation and maintenance tools cannot locate the worst-performing database in a database cluster.

[0005] According to one aspect of the embodiments of this application, a database performance determination method is provided, comprising: determining the execution time of a preset baseline structured query statement in each database instance of a database cluster, wherein the database instance is a database system corresponding to each subsystem of a customer relationship management system in the database cluster; identifying database instances whose execution time of the baseline structured query statement exceeds a first time threshold as databases to be optimized, and identifying slow statements corresponding to each database to be optimized, wherein the slow statements are structured query statements in the historical logs of the database to be optimized whose execution time exceeds a second time threshold; determining a first performance parameter of the slow statement based on the index value of the performance evaluation index corresponding to the slow statement, wherein the first performance parameter is used to characterize the degree of impact of the slow statement on the database performance; and determining a second performance parameter of the database to be optimized based on the first performance parameters of all slow statements corresponding to the database to be optimized, wherein the second performance parameter is used to characterize the performance quality of the database to be optimized.

[0006] Optionally, the method further includes: executing each baseline structured query statement in the database instance, and determining the average execution time of each baseline structured query statement as the execution time corresponding to the database instance, wherein the baseline structured query statements corresponding to each database instance are the same; if the execution time is greater than a first time threshold, determining that the performance of the database instance needs to be optimized, and identifying the database instance as a database to be optimized; if the execution time is not greater than the first time threshold, determining that the performance of the database instance does not need to be optimized.

[0007] Optionally, determining the slow statements corresponding to each database to be optimized includes: accessing the database to be optimized based on the access information corresponding to the database to be optimized, and obtaining the historical logs corresponding to the database to be optimized. The access information includes: database network protocol address, port, login account and password. The historical logs include the structured query statements executed by the database to be optimized in the past and the corresponding execution information. The structured query statements in the historical logs whose execution time exceeds the second time threshold are identified as slow statements corresponding to the database to be optimized.

[0008] Optionally, determining the first performance parameter of a slow statement includes: determining the metric values ​​of performance evaluation indicators for each slow statement in the database to be optimized, wherein the performance evaluation indicators include at least one of the following: the number of queries, query duration, waiting time, and retrieved data volume for the slow statement, and the waiting time is the time that the query operation corresponding to the slow statement waits to acquire record locks when retrieving data; determining the first weight coefficients corresponding to each performance evaluation indicator of the slow statement, wherein the first weight coefficients are used to characterize the degree of demand for performance evaluation indicators by the business corresponding to the slow statement; and determining the first performance parameter of the slow statement based on the metric values ​​of each performance evaluation indicator and the first weight coefficients corresponding to each performance evaluation indicator.

[0009] Optionally, determining the second performance parameter of the database to be optimized based on the first performance parameter of all slow statements corresponding to the database to be optimized includes: identifying the subsystems in the customer relationship management system associated with the database to be optimized, and determining the second weight coefficient corresponding to each database to be optimized based on the subsystem's influence on the customer relationship management system, wherein the second weight coefficient is used to characterize the influence of the database to be optimized on the customer relationship management system; and determining the second performance parameter based on the first performance parameter of each slow statement corresponding to the database to be optimized and the second weight coefficient corresponding to the database to be optimized.

[0010] Optionally, after determining the second performance parameter of the database to be optimized, the method further includes: sorting the databases to be optimized in descending order of the second performance parameter, wherein the larger the second performance parameter, the higher the priority of the database to be optimized; and optimizing the slow statements of each database to be optimized in the sorted order according to the statement optimization rules, wherein the statement optimization rules are used to transform the syntax of the slow statements, and the execution performance of the statements after syntax transformation is better than that of the statements before transformation, and the execution result of the statements is consistent with the execution result of the statements before transformation.

[0011] Optionally, optimizing the slow statements of each database to be optimized sequentially includes: sorting the slow statements corresponding to the database to be optimized in descending order of their first performance parameter, wherein the larger the first performance parameter, the higher the priority of optimizing the slow statement; and optimizing each slow statement corresponding to the database to be optimized sequentially according to the sorted order of the slow statements.

[0012] According to another aspect of the embodiments of this application, a database performance determination apparatus is also provided, comprising: an execution time determination module, configured to determine the execution time of executing a preset baseline structured query statement in each database instance of a database cluster, wherein the database instance is a database system in the database cluster corresponding to each subsystem of a customer relationship management system; a slow statement determination module, configured to determine database instances whose execution time of the baseline structured query statement exceeds a first time threshold as databases to be optimized, and to determine slow statements corresponding to each database to be optimized, wherein the slow statements are structured query statements in the historical logs of the database to be optimized whose execution time exceeds a second time threshold; a first performance evaluation module, configured to determine a first performance parameter of the slow statement based on the index value of the performance evaluation index corresponding to the slow statement, wherein the first performance parameter is used to characterize the degree of impact of the slow statement on database performance; and a second performance evaluation module, configured to determine a second performance parameter of the database to be optimized based on the first performance parameters of all slow statements corresponding to the database to be optimized, wherein the second performance parameter is used to characterize the performance quality of the database to be optimized.

[0013] According to another aspect of the embodiments of this application, an electronic device is also provided, including: a memory and a processor, the processor being configured to run a program stored in the memory, wherein the program executes a database performance determination method during runtime.

[0014] According to another aspect of the embodiments of this application, a non-volatile storage medium is also provided, the non-volatile storage medium including a stored computer program, wherein the device where the non-volatile storage medium is located executes a database performance determination method by running the computer program.

[0015] According to another aspect of the embodiments of this application, a computer program product is also provided, including a computer program that, when executed by a processor, implements the steps of a database performance determination method.

[0016] In this embodiment, the execution time of a preset baseline structured query statement is determined in each database instance of the database cluster. The database instance is a database system corresponding to a subsystem of the customer relationship management system within the database cluster. Database instances whose execution time exceeds a first time threshold are identified as databases to be optimized. Slow statements are identified for each database to be optimized, where slow statements are structured query statements in the historical logs of the database to be optimized whose execution time exceeds a second time threshold. A first performance parameter for the slow statement is determined based on the performance evaluation index value corresponding to the slow statement. Performance parameters are used to characterize the impact of slow statements on database performance. Based on the first performance parameter of all slow statements corresponding to the database to be optimized, the second performance parameter of the database to be optimized is determined. The second performance parameter is used to characterize the performance of the database to be optimized. By quantifying the performance of slow statements in a single database and further quantifying the overall performance of the database based on the quantified value of slow statement performance, the goal of quickly locating a single performance bottleneck instance in a clustered database scenario composed of a large number of database instances is achieved. This solves the problem that database operation and maintenance tools in related technologies cannot locate the worst-performing database in a database cluster. Attached Figure Description

[0017] The accompanying drawings, which are included to provide a further understanding of this application and form part of this application, illustrate exemplary embodiments of this application and are used to explain this application, but do not constitute an undue limitation of this application. In the drawings:

[0018] Figure 1 This is a hardware structure block diagram of a computer terminal (or electronic device) for implementing a method for determining database performance, according to an embodiment of this application.

[0019] Figure 2 This is a schematic diagram of a method for determining database performance according to an embodiment of this application;

[0020] Figure 3 This is a schematic diagram of a multi-cluster database performance optimization and analysis framework provided according to an embodiment of this application;

[0021] Figure 4 This is a schematic diagram of a multi-cluster database performance optimization analysis method for CRM systems in a domestic IT innovation environment, provided by an embodiment of this application.

[0022] Figure 5 This is a schematic diagram of a database performance determination device provided according to an embodiment of this application. Detailed Implementation

[0023] To enable those skilled in the art to better understand the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present application, and not all embodiments. Based on the embodiments in the present application, all other embodiments obtained by those of ordinary skill in the art without creative effort should fall within the scope of protection of the present application.

[0024] It should be noted that the terms "first," "second," etc., in the specification, claims, and accompanying drawings of this application are used to distinguish similar objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data can be interchanged where appropriate so that the embodiments of this application described herein can be implemented in orders other than those illustrated or described herein. Furthermore, the terms "comprising" and "having," and any variations thereof, are intended to cover non-exclusive inclusion; for example, a process, method, system, product, or apparatus that comprises a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units not explicitly listed or inherent to such processes, methods, products, or apparatus.

[0025] Customer Relationship Management (CRM) systems are core systems for telecom operators, covering the entire business process from mobile phone card application and cancellation at operator stores, to the launch and upgrade of service packages, bill payment, and customer complaints. Therefore, the system has very high requirements for operating speed. Once the database experiences performance issues, it will affect the efficiency of front-line business operations, trigger customer complaints, and have a negative impact on the company.

[0026] This system is large in scale, involving many subsystems and submodules, and the data often exists in multiple databases. However, traditional database operation and maintenance tools in related technologies are lacking in performance troubleshooting. First, they cannot quickly locate the worst-performing database among multiple databases, and second, they cannot quickly locate the SQL (Structured Query Language) that has the most impact on performance in a single database.

[0027] To address the aforementioned issues, this application provides relevant solutions to guide operations and maintenance personnel in optimizing and analyzing the database to quickly restore system performance. These solutions are described in detail below.

[0028] According to an embodiment of this application, a method embodiment for determining database performance is provided. It should be noted that the steps shown in the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer-executable instructions. Furthermore, although a logical order is shown in the flowchart, in some cases, the steps shown or described may be executed in a different order than that shown here.

[0029] The methods and embodiments provided in this application can be executed on mobile terminals, computer terminals, or similar computing devices. Figure 1 A hardware block diagram of a computer terminal (or electronic device) for implementing a database performance determination method is shown. Figure 1 As shown, the computer terminal 10 (or electronic device) may include one or more processors 102 (shown as 102a, 102b, ..., 102n in the figure) 102 (processor 102 may include, but is not limited to, a microprocessor MCU or a programmable logic device FPGA, etc.), a memory 104 for storing data, and a transmission device 106 for communication functions. In addition, it may also include: a display, an input / output interface (I / O interface), a universal serial bus (USB) port (which may be included as one of the ports of a BUS bus), a network interface, a power supply, and / or a camera. Those skilled in the art will understand that... Figure 1 The structure shown is for illustrative purposes only and does not limit the structure of the aforementioned electronic device. For example, computer terminal 10 may also include... Figure 1 The more or fewer components shown, or having the same Figure 1 The different configurations shown.

[0030] It should be noted that the aforementioned one or more processors 102 and / or other data processing circuits are generally referred to herein as "data processing circuits". These data processing circuits may be embodied, in whole or in part, in software, hardware, firmware, or any other combination thereof. Furthermore, the data processing circuits may be a single, independent processing module, or may be integrated, in whole or in part, into any other element within the computer terminal 10 (or electronic device). As involved in the embodiments of this application, the data processing circuits serve as a processor control mechanism (e.g., selection of a variable resistor termination path connected to an interface).

[0031] The memory 104 can be used to store software programs and modules of application software, such as the program instructions / data storage device corresponding to the database performance determination method in this embodiment. The processor 102 executes various functional applications and data processing by running the software programs and modules stored in the memory 104, thereby realizing the aforementioned database performance determination method. The memory 104 may include high-speed random access memory, and may also include non-volatile memory, such as one or more magnetic storage devices, flash memory, or other non-volatile solid-state memory. In some instances, the memory 104 may further include memory remotely located relative to the processor 102, and these remote memories can be connected to the computer terminal 10 via a network. Examples of such networks include, but are not limited to, the Internet, corporate intranets, local area networks, mobile communication networks, and combinations thereof.

[0032] The transmission device 106 is used to receive or send data via a network. Specific examples of the network described above may include a wireless network provided by the communication provider of the computer terminal 10. In one example, the transmission device 106 includes a Network Interface Controller (NIC), which can connect to other network devices via a base station to communicate with the Internet. In another example, the transmission device 106 may be a Radio Frequency (RF) module, used for wireless communication with the Internet.

[0033] The display may be, for example, a touchscreen liquid crystal display (LCD) that allows the user to interact with the user interface of the computer terminal 10 (or electronic device).

[0034] Under the above operating environment, this application provides a method for determining database performance. Figure 2 This is a schematic diagram of a method for determining database performance according to an embodiment of this application, as shown below. Figure 2 As shown, the method includes the following steps:

[0035] Step S202: Determine the execution time of the preset baseline structured query statement in each database instance of the database cluster, wherein the database instance is the database system in the database cluster corresponding to each subsystem of the customer relationship management system.

[0036] Step S204: Database instances whose execution time of the baseline structured query statement exceeds the first time threshold are identified as databases to be optimized, and slow statements corresponding to each database to be optimized are identified. The slow statements are structured query statements in the historical logs of the databases to be optimized whose execution time exceeds the second time threshold.

[0037] Step S206: Based on the index value of the performance evaluation index corresponding to the slow statement, determine the first performance parameter of the slow statement, wherein the first performance parameter is used to characterize the degree of impact of the slow statement on the database performance.

[0038] Step S208: Based on the first performance parameters of all slow statements corresponding to the database to be optimized, determine the second performance parameters of the database to be optimized, wherein the second performance parameters are used to characterize the performance level of the database to be optimized.

[0039] By quantifying the performance of slow statements in a single database and further quantifying the overall performance of the database based on the quantified value of slow statement performance, the goal of quickly locating a single performance bottleneck instance in a clustered database scenario composed of a large number of database instances is achieved. This solves the problem that database operation and maintenance tools in related technologies cannot locate the worst-performing database in a database cluster.

[0040] The database performance determination method in steps S202 to S208 of the embodiments of this application will be further described below.

[0041] In this application embodiment, a performance analysis platform can be used to perform performance optimization analysis on multi-cluster databases, such as... Figure 3 As shown, the performance analysis platform includes modules for data input, performance evaluation, performance analysis, and performance optimization. The data input module stores the Internet Protocol addresses (IPs), ports, usernames, and passwords of all instances in the database cluster involved in the CRM system. It can also receive performance optimization coefficients (i.e., second weighting coefficients) preset by operations and maintenance personnel for the database instances involved in various subsystem modules within the CRM system. The performance evaluation module performs performance checks on all database instances involved in the CRM system. The performance analysis module calculates the performance optimization index (i.e., second performance parameter) for all database instances and sorts them. The performance optimization module provides optimization suggestions to operations and maintenance personnel based on the database performance optimization index (i.e., second performance parameter) output by the performance analysis module.

[0042] Figure 4 This is a schematic diagram illustrating a multi-cluster database performance optimization analysis method for CRM systems under a domestic IT innovation environment, as provided in the embodiments of this application. Figure 4 As shown, this method can be used Figure 3 Execute within the framework shown below, combined with Figure 3 and Figure 4 The content further describes the database performance determination method in the embodiments of this application.

[0043] First, a set of performance check benchmark statements, namely benchmark structured query statements, can be set through performance evaluation parameters. Performance evaluation is performed by having each database instance execute the preset benchmark structured query statements. The specific steps are as follows.

[0044] In some embodiments of this application, the method further includes the following steps: in a database instance, executing each baseline structured query statement, and determining the average execution time of each baseline structured query statement as the execution time corresponding to the database instance, wherein the baseline structured query statements corresponding to each database instance are the same; if the execution time is greater than a first time threshold, determining that the performance of the database instance needs to be optimized, and identifying the database instance as a database to be optimized; if the execution time is not greater than the first time threshold, determining that the performance of the database instance does not need to be optimized.

[0045] Specifically, a baseline structured query statement is executed in the database instance, and the execution time of each baseline structured query statement is recorded. Finally, the average value is taken as the execution time corresponding to the database instance. Then, the execution time is compared with a pre-set first time threshold. If it is greater than the first time threshold, it is determined that the performance of the database instance needs to be optimized, and subsequent performance analysis begins. If it is less than the first time threshold, it is determined that the performance of the database instance does not need to be optimized, and information such as "system performance is good, no optimization is required" is returned.

[0046] The following section further describes the process of performing performance analysis on databases identified as needing optimization in Jining using the performance analysis module.

[0047] First, it is necessary to identify the slow statements in each database to be optimized. The specific steps are as follows.

[0048] In some embodiments of this application, determining the slow statement corresponding to each database to be optimized includes the following steps: accessing the database to be optimized based on the access information corresponding to the database to be optimized, and obtaining the historical log corresponding to the database to be optimized, wherein the access information includes: database network protocol address, port, login account and password, and the historical log includes structured query statements executed by the database to be optimized in the past and the corresponding execution information; and determining the structured query statements in the historical log whose execution time exceeds a second time threshold as the slow statement corresponding to the database to be optimized.

[0049] Specifically, first, based on the access information, log in to the database to be optimized (the i-th database is database i, and there are n databases to be optimized). The access information can be set by the operation and maintenance personnel through the data input module, including but not limited to: subsystem list, database IP, port, username, password, etc.

[0050] After successful login, you can query and parse slow statements whose execution time exceeds the second time threshold in the logs of the currently logged-in database to be optimized, based on the pre-set second time threshold.

[0051] Once the slow statements are identified, the performance optimization index, i.e. the first performance parameter, can be calculated based on the index value and corresponding weight value of the slow statements with the current parameters to be optimized. The specific steps are as follows.

[0052] In some embodiments of this application, determining the first performance parameter of a slow statement includes the following steps: determining the index values ​​of performance evaluation indicators corresponding to each slow statement in the database to be optimized, wherein the performance evaluation indicators include at least one of the following: the number of queries corresponding to the slow statement, query duration, waiting time, and retrieved data volume, wherein the waiting time is the time that the query operation corresponding to the slow statement waits due to acquiring record locks when retrieving data; determining the first weight coefficients corresponding to each performance evaluation indicator of the slow statement, wherein the first weight coefficients are used to characterize the degree of demand of the business corresponding to the slow statement for the performance evaluation indicators; and determining the first performance parameter of the slow statement based on the index values ​​of each performance evaluation indicator corresponding to the slow statement and the first weight coefficients corresponding to each performance evaluation indicator.

[0053] Specifically, performance evaluation metrics can be selected based on the specific business nature of the slow statement. In this embodiment, the aforementioned performance evaluation metrics include, but are not limited to, query count, query time, time spent waiting to acquire record locks while retrieving data, and the amount of data retrieved (RowsExamined). Furthermore, after determining the performance evaluation metrics, the weighting of each performance evaluation metric can be determined based on the business nature of the slow statement and the degree of emphasis placed on it. For example, if the current slow statement particularly values ​​the accuracy and comprehensiveness of the execution results, the weighting of the query time metric can be appropriately reduced, while the weighting of the query data volume metric can be increased.

[0054] Then, based on the performance evaluation metrics of the slow statement and their corresponding weighting coefficients, the first performance parameter, score, can be calculated for that slow statement. sql =sum(func(q1,q2,q3,...,q) n )*l), where func() represents a function that normalizes the values ​​of various indicators. Additionally, in this embodiment, multiple slow statements in the same database to be optimized can be sorted according to a calculated first performance parameter.

[0055] After obtaining the first performance parameters of each slow statement in the database to be optimized, the overall performance optimization index of the data to be optimized, i.e. the second performance parameter, can be further quantified. The specific steps are as follows.

[0056] In some embodiments of this application, determining the second performance parameter of the database to be optimized based on the first performance parameter of all slow statements corresponding to the database to be optimized includes the following steps: identifying the subsystems in the customer relationship management system associated with the database to be optimized, and determining the second weight coefficient corresponding to each database to be optimized based on the subsystem's influence on the customer relationship management system, wherein the second weight coefficient is used to characterize the degree of influence of the database to be optimized on the customer relationship management system; and determining the second performance parameter based on the first performance parameter of each slow statement corresponding to the database to be optimized and the second weight coefficient corresponding to the database to be optimized.

[0057] Specifically, the second performance parameter (score) of the database to be optimized can be calculated based on the first performance parameter of each slow statement and the second weighting coefficient of the database to be optimized. dbn The specific formula is as follows:

[0058] score dbn =func(score) sql1 score sql2 score sql3 ,...,score sqln )*m

[0059] Among them, score sql1 score sql2 score sql3 ,...,score sqln This represents the first performance parameter corresponding to the n slow statements in the parameter database to be optimized, and m represents the second weighting coefficient mentioned above. In this embodiment, the second weighting coefficient can be determined based on the mapping list between the subsystem and each database instance obtained through the data input module, combined with the degree of influence of each subsystem on the CRM system.

[0060] By repeating the above performance analysis process, the second performance parameters corresponding to all n databases to be optimized can be obtained. Then, the embodiment of this application can also use a performance optimization module to optimize the databases to be optimized. The specific steps are as follows.

[0061] In some embodiments of this application, after determining the second performance parameter of the database to be optimized, the method further includes the following steps: sorting the databases to be optimized in descending order of the second performance parameter, wherein the larger the second performance parameter, the higher the priority of the database to be optimized; and optimizing the slow statements of each database to be optimized in the sorted order according to the statement optimization rules, wherein the statement optimization rules are used to convert the syntax format of the slow statements, and the execution performance of the statements after syntax format conversion is better than that of the statements before conversion, and the execution result of the statements is consistent with the execution result of the statements before conversion.

[0062] In some embodiments of this application, the slow statements of each database to be optimized are optimized sequentially as follows: the slow statements corresponding to each database to be optimized are sorted in descending order of their first performance parameter, wherein the larger the first performance parameter, the higher the priority of the slow statement optimization; during the optimization process of each database to be optimized, the slow statements corresponding to each database to be optimized are optimized sequentially according to the sorted order of the slow statements.

[0063] Specifically, for all databases, the second performance parameter, score. dbn The databases are sorted, and then, based on the statement optimization rule set set in the performance optimization module, optimization suggestions are given to the databases with the highest second performance parameter in the sorted order. Simultaneously, for each database, slow statements are optimized according to the first performance parameter (score). sql Sort the statements and prioritize optimization suggestions for slow statements with the highest first performance parameter according to the sorted order.

[0064] The aforementioned language optimization rule set contains several pre-set rules for grammatical format conversion of slow statements to improve statement execution efficiency. For example, it converts "SELECT *" (which selects all columns in the data table for querying) in a slow statement into selecting the corresponding columns required by the statement's business logic for querying.

[0065] As an optional implementation, before optimizing slow statements in the database to be optimized, optimization suggestions for the slow statements can be sent to the front-end interactive interface for display. The operation and maintenance personnel can then choose whether to optimize according to the suggestions. This supports manual judgment on whether to optimize, which is more in line with the actual scenario and ensures that no failures are caused while improving efficiency.

[0066] This application provides an operational tool for CRM systems under a domestic IT innovation environment, designed to improve database performance optimization efficiency. It quantifies the performance of individual database statements into a performance optimization index, and quantifies the performance of multiple database scenarios into a total performance optimization index. Furthermore, it integrates the experience of operational personnel from both the business and platform sides, making the index more adaptable to the application scenarios of CRM systems. Moreover, this application uses a pre-defined set of rules to provide intelligent optimization suggestions for slow statements in the database, significantly improving performance optimization efficiency. It solves the problem of not being able to quickly locate individual performance bottleneck instances or the SQL statements triggering performance issues in clustered database scenarios composed of a large number of database instances. This improves the efficiency of system operational personnel in optimizing database performance and ensures the stable operation of IT systems.

[0067] According to an embodiment of this application, an embodiment of a database performance determination apparatus is also provided. Figure 5 This is a schematic diagram of a database performance determination device according to an embodiment of this application. Figure 5 As shown, the device includes:

[0068] The execution time determination module 50 is used to determine the execution time of a preset baseline structured query statement in each database instance of the database cluster. The database instance is the database system in the database cluster that corresponds to each subsystem of the customer relationship management system.

[0069] The slow statement determination module 52 is used to identify database instances whose execution time of the baseline structured query statement exceeds the first time threshold as databases to be optimized, and to determine the slow statements corresponding to each database to be optimized. The slow statements are structured query statements in the historical logs of the databases to be optimized whose execution time exceeds the second time threshold.

[0070] The first performance evaluation module 54 is used to determine the first performance parameter of the slow statement based on the index value of the performance evaluation index corresponding to the slow statement. The first performance parameter is used to characterize the degree of impact of the slow statement on the database performance.

[0071] The second performance evaluation module 56 is used to determine the second performance parameters of the database to be optimized based on the first performance parameters of all slow statements corresponding to the database to be optimized. The second performance parameters are used to characterize the performance level of the database to be optimized.

[0072] Optionally, the database performance determination device is further configured to: execute each benchmark structured query statement in the database instance, and determine the average execution time of each benchmark structured query statement as the execution time corresponding to the database instance, wherein the benchmark structured query statements corresponding to each database instance are the same; if the execution time is greater than a first time threshold, determine that the performance of the database instance needs to be optimized and identify the database instance as a database to be optimized; if the execution time is not greater than the first time threshold, determine that the performance of the database instance does not need to be optimized.

[0073] Optionally, determining the slow statements corresponding to each database to be optimized includes: accessing the database to be optimized based on the access information corresponding to the database to be optimized, and obtaining the historical logs corresponding to the database to be optimized. The access information includes: database network protocol address, port, login account and password. The historical logs include the structured query statements executed by the database to be optimized in the past and the corresponding execution information. The structured query statements in the historical logs whose execution time exceeds the second time threshold are identified as slow statements corresponding to the database to be optimized.

[0074] Optionally, determining the first performance parameter of a slow statement includes: determining the metric values ​​of performance evaluation indicators for each slow statement in the database to be optimized, wherein the performance evaluation indicators include at least one of the following: the number of queries, query duration, waiting time, and retrieved data volume for the slow statement, and the waiting time is the time that the query operation corresponding to the slow statement waits to acquire record locks when retrieving data; determining the first weight coefficients corresponding to each performance evaluation indicator of the slow statement, wherein the first weight coefficients are used to characterize the degree of demand for performance evaluation indicators by the business corresponding to the slow statement; and determining the first performance parameter of the slow statement based on the metric values ​​of each performance evaluation indicator and the first weight coefficients corresponding to each performance evaluation indicator.

[0075] Optionally, determining the second performance parameter of the database to be optimized based on the first performance parameter of all slow statements corresponding to the database to be optimized includes: identifying the subsystems in the customer relationship management system associated with the database to be optimized, and determining the second weight coefficient corresponding to each database to be optimized based on the subsystem's influence on the customer relationship management system, wherein the second weight coefficient is used to characterize the influence of the database to be optimized on the customer relationship management system; and determining the second performance parameter based on the first performance parameter of each slow statement corresponding to the database to be optimized and the second weight coefficient corresponding to the database to be optimized.

[0076] Optionally, after determining the second performance parameter of the database to be optimized, the database performance determination device is further configured to: sort the databases to be optimized in descending order of the second performance parameter, wherein the larger the second performance parameter, the higher the priority of the database to be optimized; and optimize the slow statements of each database to be optimized in the sorted order according to the statement optimization rules, wherein the statement optimization rules are used to convert the syntax of the slow statements, and the execution performance of the statements after syntax conversion is better than that of the statements before conversion, and the execution result of the statements is consistent with the execution result of the statements before conversion.

[0077] Optionally, optimizing the slow statements of each database to be optimized sequentially includes: sorting the slow statements corresponding to the database to be optimized in descending order of their first performance parameter, wherein the larger the first performance parameter, the higher the priority of optimizing the slow statement; and optimizing each slow statement corresponding to the database to be optimized sequentially according to the sorted order of the slow statements.

[0078] It should be noted that each module in the above-mentioned database performance determination device can be a program module (e.g., a set of program instructions to implement a certain function) or a hardware module. For the latter, it can be manifested in the following forms, but is not limited to them: each of the above modules is manifested as a processor, or the functions of each of the above modules are implemented by a processor.

[0079] It should be noted that the database performance determination device provided in this embodiment can be used to perform... Figure 2 The database performance determination method shown above is also applicable to the embodiments of this application, and will not be repeated here.

[0080] This application embodiment also provides a non-volatile storage medium, which includes a stored computer program. The device containing the non-volatile storage medium executes the following database performance determination method by running the computer program: determining the execution time of a preset baseline structured query statement in each database instance of a database cluster, wherein the database instance is a database system corresponding to each subsystem of a customer relationship management system in the database cluster; identifying database instances whose execution time of the baseline structured query statement exceeds a first time threshold as databases to be optimized, and identifying slow statements corresponding to each database to be optimized, wherein the slow statements are structured query statements in the historical logs of the database to be optimized whose execution time exceeds a second time threshold; determining a first performance parameter of the slow statement based on the index value of the performance evaluation index corresponding to the slow statement, wherein the first performance parameter characterizes the degree of impact of the slow statement on database performance; and determining a second performance parameter of the database to be optimized based on the first performance parameters of all slow statements corresponding to the database to be optimized, wherein the second performance parameter characterizes the performance level of the database to be optimized.

[0081] This application also provides a computer program product, including a computer program that, when executed by a processor, implements the steps of the database performance determination method described in various embodiments of this application: determining the execution time of a preset baseline structured query statement in each database instance of a database cluster, wherein the database instance is a database system corresponding to each subsystem of a customer relationship management system in the database cluster; identifying database instances whose execution time of the baseline structured query statement exceeds a first time threshold as databases to be optimized, and identifying slow statements corresponding to each database to be optimized, wherein the slow statements are structured query statements in the historical logs of the database to be optimized whose execution time exceeds a second time threshold; determining a first performance parameter of the slow statement based on the index value of the performance evaluation index corresponding to the slow statement, wherein the first performance parameter is used to characterize the degree of impact of the slow statement on database performance; and determining a second performance parameter of the database to be optimized based on the first performance parameter of all slow statements corresponding to the database to be optimized, wherein the second performance parameter is used to characterize the performance quality of the database to be optimized.

[0082] The sequence numbers of the embodiments in this application are for descriptive purposes only and do not represent the superiority or inferiority of the embodiments.

[0083] In the above embodiments of this application, the descriptions of each embodiment have different focuses. For parts not described in detail in a certain embodiment, please refer to the relevant descriptions of other embodiments.

[0084] In the several embodiments provided in this application, it should be understood that the disclosed technical content can be implemented in other ways. The device embodiments described above are merely illustrative; for example, the division of units can be a logical functional division, and in actual implementation, there may be other division methods. For instance, multiple units or components may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the displayed or discussed mutual coupling, direct coupling, or communication connection may be through some interfaces; the indirect coupling or communication connection between units or modules may be electrical or other forms.

[0085] The units described as separate components may or may not be physically separate. The components shown as units may or may not be physical units; that is, they may be located in one place or distributed across multiple units. Some or all of the units can be selected to achieve the purpose of this embodiment according to actual needs.

[0086] Furthermore, the functional units in the various embodiments of this application can be integrated into one processing unit, or each unit can exist physically separately, or two or more units can be integrated into one unit. The integrated unit can be implemented in hardware or as a software functional unit.

[0087] If the integrated unit is implemented as a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of this application, in essence, or the part that contributes to the prior art, or all or part of the technical solution, can be embodied in the form of a software product. This computer software product is stored in a storage medium and includes several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute all or part of the steps of the methods described in the various embodiments of this application. The aforementioned storage medium includes various media capable of storing program code, such as a USB flash drive, read-only memory (ROM), random access memory (RAM), portable hard drive, magnetic disk, or optical disk.

[0088] The above description is only a preferred embodiment of this application. It should be noted that for those skilled in the art, several improvements and modifications can be made without departing from the principle of this application, and these improvements and modifications should also be considered within the scope of protection of this application.

Claims

1. A method for determining database performance, characterized in that, include: Determine the execution time of a preset baseline structured query statement in each database instance of the database cluster, wherein the database instance is the database system in the database cluster corresponding to each subsystem of the customer relationship management system; Database instances whose execution time of the baseline structured query statement exceeds a first time threshold are identified as databases to be optimized, and slow statements corresponding to each database to be optimized are identified, wherein the slow statements are structured query statements in the historical log of the database to be optimized whose execution time exceeds a second time threshold. Based on the index value of the performance evaluation index corresponding to the slow statement, a first performance parameter of the slow statement is determined, wherein the first performance parameter is used to characterize the degree of impact of the slow statement on database performance. Determining a second performance parameter for the database to be optimized based on the first performance parameters of all the slow statements corresponding to the database to be optimized includes: identifying the subsystems associated with the database to be optimized in the customer relationship management system, and determining a second weight coefficient for each database to be optimized based on the influence degree parameter of the subsystem on the customer relationship management system, wherein the second weight coefficient is used to characterize the influence degree of the database to be optimized on the customer relationship management system; determining the second performance parameter based on the first performance parameters of each slow statement corresponding to the database to be optimized and the second weight coefficient corresponding to the database to be optimized; wherein the second performance parameter is used to characterize the performance quality of the database to be optimized.

2. The database performance determination method according to claim 1, characterized in that, The method further includes: In the database instance, each of the baseline structured query statements is executed, and the average execution time of each baseline structured query statement is determined as the execution time corresponding to the database instance, wherein the baseline structured query statements corresponding to each database instance are the same; If the execution time exceeds the first time threshold, it is determined that the performance of the database instance needs to be optimized, and the database instance is identified as the database to be optimized. If the execution time is not greater than the first time threshold, it is determined that the performance of the database instance does not need to be optimized.

3. The database performance determination method according to claim 1, characterized in that, The slow statements corresponding to each of the databases to be optimized include: Based on the access information corresponding to the database to be optimized, the database to be optimized is accessed, and the historical logs corresponding to the database to be optimized are obtained. The access information includes: database network protocol address, port, login account and password, and the historical logs include the structured query statements executed by the database to be optimized in the past and the corresponding execution information. Structured query statements in the historical logs whose execution time exceeds the second duration threshold are identified as slow statements corresponding to the database to be optimized.

4. The database performance determination method according to claim 3, characterized in that, The first performance parameter for determining the slow statement includes: Determine the performance evaluation index values ​​for each slow statement corresponding to the database to be optimized, wherein the performance evaluation index includes at least one of the following: the number of queries, query duration, waiting time, and retrieved data volume corresponding to the slow statement, wherein the waiting time is the time that the query operation corresponding to the slow statement waits to acquire record locks when retrieving data; Determine the first weight coefficient corresponding to each of the performance evaluation indicators of the slow statement, wherein the first weight coefficient is used to characterize the degree of demand of the business corresponding to the slow statement for the performance evaluation indicators; The first performance parameter of the slow statement is determined based on the index values ​​of each performance evaluation index corresponding to the slow statement and the first weight coefficient corresponding to each performance evaluation index.

5. The database performance determination method according to claim 1, characterized in that, After determining the second performance parameter of the database to be optimized, the method further includes: The databases to be optimized are sorted in descending order of the second performance parameter, wherein the larger the second performance parameter is, the higher the priority of the database to be optimized. According to the sorted order and based on the statement optimization rules, the slow statements in each of the databases to be optimized are optimized in turn. The statement optimization rules are used to transform the syntax of the slow statements. The execution performance of the statements after the syntax transformation is better than that of the statements before transformation, and the execution result of the statements is consistent with that of the statements before transformation.

6. The database performance determination method according to claim 5, characterized in that, The optimization process for the slow statements in each of the databases to be optimized includes: The slow statements are sorted in descending order of their first performance parameter, with the higher the first performance parameter, the higher the priority of optimization. During the optimization process for each of the databases to be optimized, the slow statements corresponding to each database to be optimized are optimized sequentially according to the sorted order of the slow statements.

7. A database performance determination device, characterized in that, include: The execution time determination module is used to determine the execution time of a preset baseline structured query statement in each database instance of the database cluster, wherein the database instance is the database system in the database cluster corresponding to each subsystem of the customer relationship management system. The slow statement determination module is used to identify database instances whose execution time exceeds a first time threshold as databases to be optimized, and to identify slow statements corresponding to each database to be optimized, wherein the slow statement is a structured query statement in the historical log of the database to be optimized whose execution time exceeds a second time threshold. The first performance evaluation module is used to determine the first performance parameter of the slow statement based on the index value of the performance evaluation index corresponding to the slow statement, wherein the first performance parameter is used to characterize the degree of impact of the slow statement on database performance. The second performance evaluation module is used to determine a second performance parameter of the database to be optimized based on the first performance parameters of all the slow statements corresponding to the database to be optimized. This includes: identifying the subsystems in the customer relationship management system associated with the database to be optimized, and determining a second weight coefficient corresponding to each database to be optimized based on the influence parameter of the subsystem on the customer relationship management system, wherein the second weight coefficient characterizes the influence of the database to be optimized on the customer relationship management system; and determining the second performance parameter based on the first performance parameters of each slow statement corresponding to the database to be optimized and the second weight coefficient corresponding to the database to be optimized, wherein the second performance parameter characterizes the performance level of the database to be optimized.

8. An electronic device, characterized in that, include: A memory and a processor, the processor being configured to run a program stored in the memory, wherein the program, when running, executes the database performance determination method according to any one of claims 1 to 6.

9. A non-volatile storage medium, characterized in that, The non-volatile storage medium includes a stored computer program, wherein the device containing the non-volatile storage medium executes the database performance determination method according to any one of claims 1 to 6 by running the computer program.

10. A computer program product, comprising a computer program, characterized in that, When the computer program is executed by a processor, it implements the steps of the database performance determination method according to any one of claims 1 to 6.

Images