Methods for intelligently identifying SQL performance risks before Redis goes live

Abstract

This invention provides a method for intelligently assessing SQL performance risks before Redis deployment. The method includes: acquiring test data packets from an application in a test environment; parsing data packets related to the Redis protocol port in the test data packets to obtain several test data sets, and storing the test data in a local file; upon receiving a user-inputted SQL performance test command, parsing each test data set in the local file into production data generated by the application in a production environment according to a preset mapping relationship to obtain several production data sets; writing the production data into a message queue; consuming the production data from the message queue; determining whether the corresponding request has a risk based on each production data set; and outputting a prompt message when the production data has a risk. This invention effectively avoids the problem of application crashes causing Redis server collapse, ensuring the stability of the application.

Description

Technical Field

[0001] This invention relates to the field of computer technology, and in particular to a method for intelligently identifying SQL performance risks before Redis goes live. Background Technology

[0002] Redis is widely used in various industries, including internet, e-commerce, gaming, and finance. Redis applications are typically high-concurrency, time-sensitive core business scenarios. A single slow Redis query or a large, high-concurrency query can cause systemic crashes, impacting business stability, resulting in financial losses and damage to the company's reputation. Currently, many companies experience serious production failures due to insufficient performance of Redis SQL after deployment, and there are no standardized processes or solutions for handling such issues. Therefore, avoiding financial losses caused by Redis SQL performance problems before business deployment is crucial for the database and business stability of every company. Summary of the Invention

[0003] This invention provides a method for intelligently identifying SQL performance risks before Redis goes live, avoiding serious production failures and huge losses caused by insufficient Redis SQL performance in many companies' production environments after deployment, and further safeguarding the stability of Redis in production.

[0004] In a first aspect, embodiments of the present invention provide a method for intelligently assessing SQL performance risks before Redis goes live, the method for intelligently identifying SQL performance risks before Redis goes live includes:

[0005] Obtain the test data package of the application in the test environment;

[0006] Parsing the data packets related to the Redis protocol port in the test data packet yields several test data sets. Each test data set includes one or more of the following: the first SQL request, the first execution time, the length of the first request packet, and the length of the first return packet.

[0007] The test data is stored in a local file;

[0008] When a user inputs an SQL performance test command, each piece of test data in the local file is parsed into production data generated in the production environment according to a preset mapping relationship to obtain several pieces of production data. Each piece of production data includes one or more of the following: a second SQL request, a second execution time, the length of the second request packet, and the length of the second return packet.

[0009] Write the production data into the message queue;

[0010] The production data described in the consumer message queue;

[0011] Determine whether there is any risk in the corresponding second SQL request based on each piece of production data;

[0012] When there is a risk in the production data, a warning message will be output.

[0013] Optionally, the step of determining whether the corresponding second SQL request is risky based on each piece of production data specifically includes:

[0014] Determine whether the execution time of the second SQL request exceeds the preset execution time;

[0015] Determine whether the length of the second request packet or response packet exceeds a preset length;

[0016] If the length of the second request packet and the length of the second return packet are greater than the preset maximum SQL request execution time, maximum request packet length, and maximum return packet length, a prompt message will be output.

[0017] Optionally, the step of determining whether the corresponding second SQL request has a risk based on each piece of production data further includes:

[0018] Determine the QPS corresponding to the second SQL request;

[0019] If the QPS metric corresponding to the second SQL request exceeds the preset QPS threshold and / or bandwidth of the Redis server, a prompt message will be output.

[0020] Optionally, the step of determining whether the corresponding second SQL request has a risk based on each piece of production data further includes:

[0021] Send the second SQL request to access the Redis server;

[0022] Get the execution time, request packet length, and return packet length of the second SQL request returned by the Redis server;

[0023] The presence of risk is determined based on the execution time, request packet length, and return packet length of the second SQL request returned by the Redis server.

[0024] Optionally, the Redis server is a server for a Redis slave database.

[0025] Optionally, the risks include non-compliance risks and potential risks. When a risk exists, the output prompt information specifically includes:

[0026] The second SQL request for non-compliance risk and potential risk is labeled with different risks; and / or a notification message is sent to the client to disclose the second SQL request for non-compliance risk and potential risk.

[0027] Optionally, when the production data meets the standards, the second SQL request is marked as risk-free.

[0028] Optionally, parsing the data related to the Redis protocol port in the test data packet specifically includes:

[0029] Analyze the data type of the relevant data;

[0030] Based on the relevant data types, the corresponding instructions for extracting data are matched;

[0031] The corresponding test data is obtained from the data extraction instructions.

[0032] In a second aspect, embodiments of the present invention provide a computer device, comprising:

[0033] Memory, used to store computer programs;

[0034] A processor for executing the computer program to implement the method described in the first aspect.

[0035] Thirdly, embodiments of the present invention provide a computer-readable storage medium storing a computer program, which, when executed by a processor, is used to implement the method described in the first aspect above.

[0036] The above approach ensures that the requests generated by the application will not cause catastrophic problems to the Redis server after the application goes live by conducting preliminary performance tests on the requests before the application goes live, and then conducting secondary tests in the production environment. This guarantees the stability of the application. Attached Figure Description

[0037] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on the structures shown in these drawings without creative effort.

[0038] Figure 1 The flowchart illustrates a method for intelligently assessing SQL performance risks before Redis deployment, as provided in the first embodiment of the present invention.

[0039] Figure 2This is a first sub-flowchart of step S107 provided in the first embodiment of the present invention.

[0040] Figure 3 The second sub-flowchart of step S107 provided in the first embodiment of the present invention.

[0041] Figure 4 The third sub-flowchart of step S107 provided in the first embodiment of the present invention.

[0042] Figure 5 A schematic diagram of a computer device structure provided in the first embodiment of the present invention.

[0043] The realization of the objective, functional features and advantages of the present invention will be further explained in conjunction with the embodiments and with reference to the accompanying drawings. Detailed Implementation

[0044] To make the objectives, technical solutions, and advantages of this invention clearer, the invention will be further described in detail below with reference to the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are merely illustrative and not intended to limit the invention. All other embodiments obtained by those skilled in the art based on the embodiments of this invention without inventive effort are within the scope of protection of this invention.

[0045] The terms “first,” “second,” “third,” “fourth,” etc. (if present) in the specification, claims, and accompanying drawings of this application are used to distinguish similar planned objects and are not necessarily used to describe a specific order or sequence. It should be understood that such data are interchangeable where appropriate; in other words, the described embodiments are implemented according to a sequence other than that illustrated or described herein. Furthermore, the terms “comprising” and “having,” and any variations thereof, may also include other content; 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.

[0046] It should be noted that the descriptions involving "first," "second," etc., in this invention are for descriptive purposes only and should not be construed as indicating or implying their relative importance or implicitly specifying the number of technical features indicated. Therefore, features defined with "first" or "second" may explicitly or implicitly include one or more of that feature. Furthermore, the technical solutions of the various embodiments can be combined with each other, but this must be based on the ability of those skilled in the art to implement them. If the combination of technical solutions is contradictory or impossible to implement, it should be considered that such a combination of technical solutions does not exist and is not within the scope of protection claimed by this invention.

[0047] In this embodiment, before the developers submit the test environment to production release, they add enabling test function coverage and Redis SQL performance verification as mandatory verification steps. Only applications that pass the Redis SQL performance verification can be released to the production environment. All Redis SQL performance verifications are monitored and calculated by the test program.

[0048] Please refer to Figure 1 This is a flowchart illustrating a method for intelligently assessing SQL performance risks before Redis deployment, provided in the first embodiment of the present invention. Figure 1 As shown in the figure, the method for intelligently assessing SQL performance risks before Redis goes live, provided by an embodiment of the present invention, specifically includes steps S101-S108.

[0049] Step S101: Obtain the test data package of the application in the test environment.

[0050] In this embodiment, a packet capture script for the Redis protocol is deployed on each application server. This script can be executed using the Python pcapy packet capture tool at the network interface card (NIC) layer of each application server. The resulting test data packets can then be sent to a test program that intelligently assesses SQL performance risks before Redis deployment. In this embodiment, the execution of this test program can identify and address SQL performance risks during the pre-deployment intelligent assessment of Redis deployment.

[0051] Step S102: Parse the data packets related to the Redis protocol port in the test data packet to obtain several test data packets. Each test data packet includes one or more of the following: the first SQL request, the first execution time, the length of the first request packet, and the length of the first return packet.

[0052] In this step, in order to achieve the principle of minimizing the template for analyzing Redis SQL performance and reducing the workload caused by interfering SQL, the developers only parse packets related to the Redis protocol port.

[0053] After the test program obtains the access data of the application software to the Redis server, it performs data analysis and obtains the analysis data. It should be noted that in this embodiment, the Redis server is a slave database in the test environment. Therefore, when accessing the Redis server, any operation on the data will not affect the operation of the Redis server in the production environment.

[0054] Step S103: Store the test data in a local file.

[0055] In this step, the test data parsed in step S102 is stored in a local file, wherein the test data includes one or more of the following: the first SQL request, the first execution time, the length of the first request packet, and the length of the first return packet.

[0056] Step S104: When the SQL performance test command input by the user is received, each piece of test data in the local file is parsed into production data generated in the production environment according to the preset mapping relationship to obtain several pieces of production data. Each piece of production data includes one or more of the following: the second SQL request, the second execution time, the length of the second request packet, and the length of the second return packet.

[0057] Specifically, before conducting data testing, the developers pre-mapped the correspondence between the test environment and the production environment Redis. The purpose of this was to facilitate mutual access and sharing of data between the test environment and the production environment.

[0058] Step S105: Write the production data into the message queue. Understandably, the test data obtained in the test environment, including the second SQL request, the second execution time, the length of the second request packet, and the length of the second return packet, or one of these, is written into the message queue. The message queue can be any of ActiveMQ, RabbitMQ, ZeroMQ, Kafka, or RocketMQ, without limitation.

[0059] Step S106: Consume the production data from the message queue. In this step, the test program will automatically pull messages from the message queue for consumption and send the second SQL request from these messages to the Redis server to access the Redis server.

[0060] Step S107: Determine whether the corresponding second SQL request is risky based on each piece of production data. The risk of risky access is determined by the Redis server's response to the second SQL request sent in step S106. The specific determination method will be described in detail in the following steps and will not be repeated here.

[0061] Step S108: When production data presents a risk, a warning message is output. When the test program detects a risk in the second SQL request, a warning message is output.

[0062] In this embodiment, second SQL requests that do not meet the standards and have potential risks are marked with different risks and / or notification information is sent to the client to reveal the risks of the second SQL requests that do not meet the standards and have potential risks; second SQL requests that meet the standards are marked as risk-free.

[0063] For example, non-compliant data is marked in red, data with potential risks is marked in yellow, and compliant data is marked in green. Furthermore, non-compliant and potentially risky requests are sent to developers and / or DBAs for notification, and a review process by development architects and / or DBAs is added to ensure the stability of Redis SQL deployment.

[0064] Please refer to Figure 2 This is the first sub-flowchart of step S107 provided in the first embodiment of the present invention, which includes steps S10-S12.

[0065] Step S10: Determine whether the execution time of the second SQL request exceeds the preset execution time.

[0066] Step S11: Determine whether the length of the second request packet or return packet exceeds the preset length.

[0067] Step S12: Check if the length of the second request packet and the length of the second return packet are greater than the preset maximum SQL request execution time, the maximum request packet length, and the maximum return packet length. If they are greater, output a prompt message.

[0068] Specifically, if the execution time of the second SQL request is greater than the preset execution time, or the length of the second request packet or the return packet is greater than the preset length, and either of these conditions is not met, it indicates that there is a risk in the data of the second SQL request, and a risk warning is output.

[0069] Please refer to Figure 3 This is a second sub-flowchart of step S107 provided in the first embodiment of the present invention, which includes steps S13-S14.

[0070] Step S13: Determine the QPS corresponding to the second SQL request;

[0071] Step S14: Does the QPS metric corresponding to the second SQL request exceed the preset QPS threshold and / or bandwidth of the Redis server? If it does, a prompt message will be output.

[0072] For example, the second SQL request has a total of 100 requests, uses a Redis server with a total of 4 cores, and has a request time limit of 2 seconds.

[0073] According to the formula QPS = total number of requests / (total number of processes * request time), the QPS for the second SQL request is calculated to be 12.5 times.

[0074] At this point, a single process is successfully requesting the Redis server 12.5 times per second.

[0075] If the default QPS threshold for a single-core Redis server is 20 queries per second, then the second SQL request data is not at risk; and

[0076] Then, according to the formula: Bandwidth (bps) = Total traffic (bits) / Duration of traffic generation (seconds), the bandwidth required for the second SQL request is calculated to be 100 / 2 = 50 bits.

[0077] If the preset bandwidth is 20 bits, the data in the second SQL request is at risk.

[0078] In summary, the QPS or bandwidth corresponding to the second SQL request must be less than the preset QPS threshold or bandwidth to determine that the second SQL request is not risky; otherwise, a risk warning message will be output.

[0079] Please refer to Figure 4 The third sub-flowchart of step S107 provided in the first embodiment of the present invention includes steps S13-S14.

[0080] Step S15: Send the second SQL request to access the Redis server;

[0081] Step S16: Obtain the execution time, request packet length, and return packet length of the second SQL request returned by the Redis server;

[0082] Step S17: Determine whether there is a risk based on the execution time, request packet length, and return packet length of the second SQL request returned by the Redis server.

[0083] Specifically, in the production environment, the test program will automatically pull the second SQL request from the consumption queue to access the Redis server. The test program will monitor the execution time of the Redis server for the second SQL request and calculate the length of the second SQL request packet and the length of the return packet. Then, it will compare the execution time, request packet length and return packet length obtained by monitoring and calculation with the execution time, request packet length and return packet length of the second SQL request tested in the test environment.

[0084] If any of the monitored and calculated execution time, request packet length, and return packet length is greater than the execution time, request packet length, and return packet length obtained in the test environment, the data is deemed to be at risk, and a risk warning will be output.

[0085] Please refer to Figure 5 This is a schematic diagram of a computer device structure provided in the first embodiment of the present invention. Figure 5As shown, the computer device 30 includes a processor 301 and a memory 302 communicatively connected to the processor 301, the memory 302 storing computer execution instructions.

[0086] The processor executes computer execution instructions stored in the memory to implement the solution provided in any of the above method embodiments; the specific functions and technical effects achieved are not elaborated here. The computer device can be the server mentioned above.

[0087] This application also provides a computer-readable storage medium storing computer-executable instructions. When executed by a processor, the computer-executable instructions are used to implement the solution provided in any of the above method embodiments. The specific functions and technical effects to be achieved are not described here.

[0088] In the above embodiments, the application performs preliminary performance testing on the request commands generated by the application before it goes live, and then performs secondary testing in the production environment to ensure that the requests issued by the application after it goes live will not cause a collapse problem to the Redis server, thus ensuring the stability of the application.

[0089] Obviously, those skilled in the art can make various modifications and variations to this invention without departing from its spirit and scope. Therefore, if these modifications and variations fall within the scope of the claims of this invention and their equivalents, this invention also intends to include these modifications and variations.

[0090] The above-listed embodiments are merely preferred embodiments of the present invention and should not be construed as limiting the scope of the present invention. Therefore, any equivalent variations made in accordance with the claims of the present invention are still within the scope of the present invention.

Claims

1. A method for pre-online intelligent assessment of SQL performance risk on Redis, characterized in that, The method for intelligently identifying SQL performance risks before Redis goes online comprises the following steps: Obtaining test data packets of an application in a test environment; Parsing the test data packets related to the Redis protocol port to obtain a plurality of test data, each of which comprises one or more of a first SQL request, a first execution time, a length of a first request packet, and a length of a first return packet; wherein a packet capturing script for capturing the Redis protocol is deployed on each application server to transmit the test data packets to a test program for intelligently evaluating SQL performance risks before Redis goes online; after the test program obtains the access data of the application software to the Redis server, data analysis is performed to obtain a plurality of test data; Storing the test data in a local file; When a SQL performance test instruction input by a user is received, each test data in the local file is parsed into production data generated by the application in a production environment according to a preset mapping relationship to obtain a plurality of production data, each of which comprises one or more of a second SQL request, a second execution time, a length of a second request packet, and a length of a second return packet; Writing the production data into a message queue; Consuming the production data in the message queue; Judging whether the corresponding second SQL request of each production data has a risk or not; When the production data has a risk, outputting a prompt information.

2. The method of pre-onboarding intelligent assessment of SQL performance risks on Redis as claimed in claim 1, wherein, The method for judging whether the corresponding second SQL request of each production data has a risk or not comprises the following steps: Judging whether the execution time of the second SQL request exceeds a preset execution time; Judging whether the length of the second request packet or the return packet exceeds a preset length; Judging whether the length of the second request packet and the length of the second return packet are greater than the preset maximum SQL request execution time, the preset maximum request packet length, and the preset maximum return packet length, and outputting a prompt information if they are greater than the preset maximum values.

3. The method of pre-onboarding intelligent assessment of SQL performance risks on Redis as claimed in claim 1, wherein, The method for judging whether the corresponding second SQL request of each production data has a risk or not further comprises the following steps: Judging the QPS corresponding to the second SQL request; Judging whether the QPS corresponding to the second SQL request exceeds a preset QPS threshold and / or bandwidth size of the Redis server, and outputting a prompt information if it is greater than the preset threshold and / or bandwidth size.

4. The method of pre-onboarding intelligent assessment of SQL performance risks on Redis of claim 1, wherein, The method for judging whether the corresponding second SQL request of each production data has a risk or not further comprises the following steps: Sending the second SQL request to access the Redis server; Obtaining the execution time, the length of the request packet, and the length of the return packet of the second SQL request returned by the Redis server; Judging whether there is a risk according to the execution time, the length of the request packet, and the length of the return packet of the second SQL request returned by the Redis server.

5. The method of pre-onboarding intelligent assessment of SQL performance risks on Redis as claimed in claim 4, wherein, The Redis server is a Redis slave server.

6. The method for identifying SQL performance risks intelligently before going online on Redis of claim 5, wherein, The risks include unqualified risks and potential risks, and the prompt information outputted when the production data has a risk comprises the following steps: Different risk labels are respectively added to the second SQL requests of the unqualified risks and the potential risks; and / or notification information is sent to a client to reveal the second SQL requests of the unqualified risks and the potential risks.

7. The method of identifying SQL performance risks intelligently before going live on Redis of claim 6, wherein, When the production data meets the criteria, the second SQL request is annotated without risk.

8. The method of identifying SQL performance risks intelligently before going live on Redis of claim 1, wherein, The related data in the test data packet is parsed, and the related data specifically includes: Analyzing the data type of the related data; According to the related data type, the corresponding extraction data instruction is matched out; According to the extraction data instruction, the corresponding test data is obtained.

9. A computer device, comprising: Including: Memory for storing computer programs; The processor is used for executing the computer program to realize the method for identifying Redis online intelligent SQL performance risk in any one of claims 1 to 8.

10. A computer-readable storage medium, characterized in that, The computer readable storage medium stores a computer program, and the computer program is executed by the processor to realize the method of any one of claims 1 to 8.

Images