Slow query log management method and system and computer equipment

Abstract

The invention relates to a slow query log management method and system and computer equipment, and the slow query log management method comprises the following steps: a scheduling step: configuring an acquisition period, a database and a scheduling rule of a task scheduling module; an acquisition step: acquiring a slow log file in various databases and / or acquiring slow query data in the databases and storing the slow log file based on the task scheduling module; a parsing step: compiling a parser, using the parser to extract slow log data in the slow log file, and storing the slow log data in a preset structure format for data standardization, the preset structure format comprising timestamps, query statements, execution time, the number of returned documents and the number of scanned documents; and an analysis step of extracting the standardized slow log data and performing data analysis based on a preset analysis dimension, the preset analysis dimension including an execution plan, an execution frequency and an execution time. A large amount of time and labor cost are saved in the process of data analysis and potential problem analysis.

Description

Technical Field

[0001] The present application relates to the technical field of database performance management, and in particular to a slow query log management method and system, and a computer device. Background Art

[0002] As enterprise database systems continue to increase in number and scale, managing and monitoring the performance of these database systems becomes increasingly important.

[0003] A quality check method for a distributed database is disclosed in the prior art, comprising: creating a check task corresponding to the target database according to basic information and check rules corresponding to the target database, and collection actions corresponding to the check rules; wherein the basic information includes connection configuration information, application management information and permission information; the target database is a distributed database that needs to be quality checked; calling the check task according to the scheduling strategy corresponding to the check rules to obtain relevant data of the target database; analyzing the relevant data of the target database to obtain analysis results of the target database; wherein the relevant data includes at least one of a database log, an execution trace and an application version package; and displaying the analysis results of the target database.

[0004] There are many types of distributed databases. When querying and managing information in multiple databases, it is necessary to constantly enter new databases. Due to the diversity and complexity of database systems, existing technologies consume a lot of time and energy in parsing data and analyzing potential problems. Summary of the invention

[0005] The embodiments of the present application provide a slow query log management method and system, and a computer device to at least solve the problem in the related art that a lot of time and energy are consumed in the process of parsing data and analyzing potential problems.

[0006] In a first aspect, an embodiment of the present application provides a slow query log management method, including:

[0007] Scheduling step, configuring the collection cycle, database and scheduling rules of a task scheduling module;

[0008] A collection step, based on the task scheduling module, obtains slow log files in multiple databases and / or obtains slow query data in the database and stores it as a slow log file;

[0009] A parsing step, writing a parser, using the parser to extract the slow log data in the slow log file and store it in a preset structure format for data standardization, wherein the preset structure format includes a timestamp, a query statement, an execution time, a number of returned documents, and a number of scanned documents;

[0010] The analysis step extracts the standardized slow log data and performs data analysis based on preset analysis dimensions, where the preset analysis dimensions include: execution plan, execution frequency, and execution time.

[0011] According to the collection cycle and scheduling rules, multiple databases are collected and slow log data is standardized. Unifying data in different formats into the same format helps to simplify the steps of repeatedly querying data in different databases and save time. Identify and parse slow log data, extract key information, and facilitate the analysis of slow log data. Perform data analysis from multiple dimensions such as execution plan, execution frequency, and execution time, so that the analysis data is more comprehensive and the problems analyzed are more accurate.

[0012] In some embodiments, the analyzing step further comprises:

[0013] In the execution plan analysis step, a data table whose execution plan is a full table scan is extracted to analyze whether its filtering condition is configured with a valid index, and if not, a first warning message is output;

[0014] An execution frequency analysis step is performed to monitor the execution frequency of the statement within a preset time, and to determine whether the execution frequency of the statement exceeds a frequency threshold. If so, a second warning message is output;

[0015] The execution time analysis step calls the execution time of the statement and determines whether the execution time of the statement exceeds a time threshold. If so, outputs the third warning information.

[0016] Analyze slow log data from multiple dimensions and issue early warning information in a timely manner when problems occur. This can provide important reference for database performance optimization.

[0017] In some embodiments, the frequency threshold includes a first frequency threshold and a second frequency threshold, and the step of performing frequency analysis further includes:

[0018] Determine whether the execution frequency of the statement is greater than or equal to the first frequency threshold and less than the second frequency threshold, and if so, the warning severity level of the second warning information output is a mild warning, or;

[0019] It is determined whether the execution frequency of the statement is greater than or equal to the second frequency threshold. If so, the warning severity level of the second warning information output is a serious warning.

[0020] Setting multiple thresholds to determine the execution frequency range can more accurately correspond to different warning levels. Different warning levels can more directly and clearly indicate the severity of the problem the user is currently facing.

[0021] In some of the embodiments, the scheduling rules include time rules, task priority rules, and resource load rules.

[0022] According to different database types and designated databases, time rules, task priority rules, and resource load rules can be flexibly set to realize automatic calling of programs, thereby reducing the tediousness of manual operations and improving database management efficiency.

[0023] In some embodiments, the task priority rule includes high priority and low priority, the task scheduling module preferentially executes tasks configured as high priority, and the collection period of the high priority task is shorter than the collection period of the low priority task.

[0024] Due to different business needs, the priority of tasks may vary. Set the task priority rules in the scheduling rules, and execute high-priority tasks first according to business needs. In this way, you can be more flexible when scheduling tasks and better meet actual needs.

[0025] In some embodiments, the resource load rule includes:

[0026] Configure a time modification parameter, and postpone or advance the execution time of the task according to the time modification parameter;

[0027] Set the maximum number of concurrent tasks to limit the number of tasks executed in the scheduling step to be less than or equal to the maximum number of concurrent tasks.

[0028] Taking into account the impact of resource usage on database operation, resource load rules are set to avoid data collection during business peak hours and when resource load is high. The maximum number of concurrent tasks is also set to control the maximum number of running tasks, thereby reducing the occurrence of resource overload.

[0029] In a second aspect, an embodiment of the present application provides a slow query log management system, including:

[0030] Scheduling module, configures the collection cycle, database and scheduling rules of a task scheduling module;

[0031] A collection module is configured to obtain slow log files from multiple databases and / or obtain slow query data in the database and store it as a slow log file based on the task scheduling module;

[0032] A parsing module is configured with a parser, and uses the parser to extract the slow log data in the slow log file and store it in a preset structure format for data standardization, wherein the preset structure format includes a timestamp, a query statement, an execution time, a number of returned documents, and a number of scanned documents;

[0033] The analysis module is configured to extract the standardized slow log data and perform data analysis based on preset analysis dimensions, where the preset analysis dimensions include: execution plan, execution frequency, and execution time.

[0034] The scheduling module adjusts the collection cycle, and the collection module collects data from multiple databases. The logs of different types of databases are parsed to obtain the required information, extracted into information of the same dimension, and the slow log data is standardized. Unifying data in different formats into the same format helps to simplify the steps of repeatedly querying data in different databases and save time. The parsing module identifies and parses slow log data, extracts key information, and facilitates the analysis module to analyze slow log data. The analysis module analyzes data from multiple dimensions such as execution plan, execution frequency, and execution time, so that the analysis data is more comprehensive and the problems analyzed are more accurate.

[0035] In some embodiments, the analysis module further comprises:

[0036] An execution plan analysis unit is configured to extract a data table whose execution plan is a full table scan and analyze whether its filtering condition is configured with a valid index, and if not, output a first warning message;

[0037] an execution frequency analysis unit, configured to set a frequency threshold, record the execution frequency of the statement within a preset time, determine whether the execution frequency of the statement exceeds the frequency threshold, and if so, output a second warning message;

[0038] The execution time analysis unit is configured to set a time threshold, call the execution time of the statement, and determine whether the execution time of the statement exceeds the time threshold. If so, output a third warning message.

[0039] Analyze slow log data from multiple dimensions and issue early warning information in a timely manner when problems occur. This can provide important reference for database performance optimization.

[0040] In some embodiments, performing the frequency analysis unit includes:

[0041] A frequency threshold setting subunit, configured to set a first frequency threshold and a second frequency threshold;

[0042] The first analysis subunit is configured to determine whether the execution frequency of the statement is greater than or equal to a first frequency threshold and less than a second frequency threshold, and if so, the warning severity level of the second warning information output is a mild warning;

[0043] The second analysis subunit is configured to determine whether the execution frequency of the statement is greater than or equal to a second frequency threshold. If so, the warning severity level of the second warning information output is a severe warning.

[0044] Setting multiple thresholds to determine the execution frequency range can more accurately correspond to different warning levels. Different warning levels can more directly and clearly indicate the severity of the problem the user is currently facing.

[0045] In a third aspect, an embodiment of the present application provides a computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the slow query log management method as described in the first aspect above is implemented.

[0046] Compared with the related art, the slow query log management method, system and computer device provided by the embodiments of the present application process multiple databases and analyze the data in the databases, thereby solving the problem of consuming a lot of time and energy in repeatedly consulting different databases in the process of parsing data and analyzing potential problems, and supports multiple database types, improves database management efficiency and improves data processing accuracy.

[0047] Details of one or more embodiments of the present application are set forth in the following drawings and description to make other features, objects, and advantages of the present application more readily apparent. BRIEF DESCRIPTION OF THE DRAWINGS

[0048] The drawings described herein are used to provide a further understanding of the present application and constitute a part of the present application. The illustrative embodiments of the present application and their descriptions are used to explain the present application and do not constitute an improper limitation on the present application. In the drawings:

[0049] Figure 1 is a flowchart of a slow query log management method according to an embodiment of the present application;

[0050] Figure 2 is a structural block diagram of a slow query log management system according to an embodiment of the present application;

[0051] Figure 3 is another structural block diagram of a slow query log management system according to an embodiment of the present application;

[0052] Figure 4 It is a schematic diagram of the hardware structure of a computer device according to an embodiment of the present application. DETAILED DESCRIPTION

[0053] In order to make the purpose, technical solutions and advantages of the present application clearer, the present application is described and illustrated below in conjunction with the accompanying drawings and embodiments. It should be understood that the specific embodiments described herein are only used to explain the present application and are not intended to limit the present application. Based on the embodiments provided in the present application, all other embodiments obtained by ordinary technicians in the field without making creative work are within the scope of protection of the present application.

[0054] Obviously, the drawings described below are only some examples or embodiments of the present application. For ordinary technicians in this field, the present application can also be applied to other similar scenarios based on these drawings without creative work. In addition, it can also be understood that although the efforts made in this development process may be complicated and lengthy, for ordinary technicians in this field related to the content disclosed in this application, some changes in design, manufacturing or production based on the technical content disclosed in this application are just conventional technical means, and should not be understood as insufficient content disclosed in this application.

[0055] Reference to "embodiments" in this application means that a particular feature, structure, or characteristic described in conjunction with the embodiments may be included in at least one embodiment of the present application. The appearance of the phrase in various locations in the specification does not necessarily refer to the same embodiment, nor is it an independent or alternative embodiment that is mutually exclusive with other embodiments. It is explicitly and implicitly understood by those of ordinary skill in the art that the embodiments described in this application may be combined with other embodiments without conflict.

[0056] Unless otherwise defined, the technical terms or scientific terms involved in this application should be understood by people with ordinary skills in the technical field to which this application belongs. The words "one", "a", "a", "the" and the like involved in this application do not indicate a quantitative limitation, and may represent the singular or plural. The terms "include", "comprise", "have" and any of their variations involved in this application are intended to cover non-exclusive inclusions; for example, a process, method, system, product or device that includes a series of steps or modules (units) is not limited to the listed steps or units, but may also include steps or units that are not listed, or may also include other steps or units inherent to these processes, methods, products or devices. The words "connect", "connected", "coupled" and the like involved in this application are not limited to physical or mechanical connections, but may include electrical connections, whether direct or indirect. The "multiple" involved in this application refers to two or more. "And / or" describes the association relationship of associated objects, indicating that there may be three relationships, for example, "A and / or B" can represent: A exists alone, A and B exist at the same time, and B exists alone. The character " / " generally indicates that the objects before and after are in an "or" relationship. The terms "first", "second", "third", etc. involved in this application are only used to distinguish similar objects and do not represent a specific ordering of the objects.

[0057] This embodiment provides a slow query log management method. Figure 1 is a flowchart of a slow query log management method according to an embodiment of the present application. Figure 1 As shown, the process includes the following steps:

[0058] Scheduling step S101, configuring a collection cycle, database and scheduling rules of a task scheduling module.

[0059] In the acquisition step S102, based on the task scheduling module, slow log files are acquired from various databases and / or slow query data in the database is acquired and stored as slow log files.

[0060] In the parsing step S103, a parser is written, and the slow log data in the slow log file is extracted by the parser and stored in a preset structure format for data standardization. The preset structure format includes a timestamp, a query statement, an execution time, a number of returned documents, and a number of scanned documents.

[0061] In the analysis step S104, the standardized slow log data is extracted and data analysis is performed based on preset analysis dimensions, where the preset analysis dimensions include: execution plan, execution frequency, and execution time.

[0062] Through the above steps, data is collected from multiple databases according to the collection cycle and scheduling rules, and the slow log data is standardized. Unifying data in different formats into the same format helps to simplify the steps of repeatedly querying data in different databases and save time. Identify and parse slow log data, extract key information, and facilitate the analysis of slow log data. Perform data analysis from multiple dimensions such as execution plan, execution frequency, and execution time, so that the analysis data is more comprehensive and the problems analyzed are more accurate.

[0063] Various databases include but are not limited to: Oracle, MySQL, OceanBase, TiDB, Mongo.

[0064] Before collecting slow log files in the database, you need to establish a connection and authentication with the database.

[0065] Database connection authentication includes creating a connection using user information and directly connecting to the database. User information includes user name, password, IP, and port.

[0066] Operating system authentication includes using system user and password to connect to the operating system and directly collect the original slow log files of the database.

[0067] During the data collection process, if the database has slow logs, the slow log files are obtained from the database according to the collection cycle based on V$SQL, DBA_HIST_SQLSTAT and SQL statements. If the database does not have slow logs, the database is sampled and queried during the collection cycle, and the sampled data is converted into slow log files.

[0068] For example: In Oracle database, you can extract slow log files by executing SQL queries; in MySQL database, you can obtain files or tables by directly reading slow log files from files or tables; in OceanBase database, you can extract slow log files from tables or views by executing SQL queries; in TiDB database, you can extract slow log files from the SLOW_QUERY table by executing SQL queries; in MongoDB data, you can use MongoDB query statements to extract slow query logs from the system.profile collection.

[0069] This application can obtain slow query log information from multiple databases, supporting Oracle, MySQL, OceanBase, TiDB, Mongo and other databases, meeting the needs of different database environments. Users do not need to use multiple different tools to manage different types of databases, which greatly simplifies the management process.

[0070] In the parsing step S103, a parser is written to process the log formats of various databases and extract key information. After the slow log file is normalized, the slow log data in the slow log file can be indexed to facilitate subsequent fast query and analysis. The key information includes but is not limited to: query statement, SQL identifier, SQL text, execution time, execution count, timestamp, query statement, execution time, number of returned documents, number of scanned documents, and index usage.

[0071] For example:

[0072]

[0073] The above steps use the parser to identify and parse the slow query log data of various databases, extract key information, and provide reliable data support for subsequent performance analysis and optimization. It also enables users to understand the database performance more accurately, find problems in time, and take corresponding measures.

[0074] In some embodiments, the analyzing step S104 further includes:

[0075] In the execution plan analysis step, a data table whose execution plan is a full table scan is extracted to analyze whether its filtering condition is configured with a valid index. If not, a first warning message is output.

[0076] The execution frequency analysis step monitors the execution frequency of the statement within a preset time, determines whether the execution frequency of the statement exceeds a frequency threshold, and if so, outputs a second warning message.

[0077] The execution time analysis step calls the execution time of the statement and determines whether the execution time of the statement exceeds a time threshold. If so, outputs the third warning information.

[0078] Analyze slow log data from multiple dimensions and issue early warning information in a timely manner when problems occur. This can provide important reference for database performance optimization.

[0079] During the SQL statement execution, the reason why the full table scan is displayed in the analysis execution plan includes that the filter condition column in the where condition has no index or the index exists but is invalid. Based on this situation, first check whether the filter column of the where condition has an index. If there is no index, it will make suggestions and suggest that the user create an index. If there is an index but it is not used, check for possible invalid situations. For example: a function operation is performed on the filter column, and the index itself is invalid. In this case, the possible situations are listed and fed back to the user, giving the first warning information.

[0080] SQL statements are executed multiple times within a certain period of time. The number of executions within a certain period of time is counted to form the execution frequency. When the execution frequency is greater than the frequency threshold, a second warning is issued. The user is prompted to check whether there is any application logic that causes such statements with high execution frequency. The user can determine whether there is a problem with this statement based on the actual situation.

[0081] The time threshold can be set by the user according to the actual situation. When the execution time exceeds the time threshold, a third warning message is issued.

[0082] In some embodiments, the frequency threshold includes a first frequency threshold and a second frequency threshold, and the step of performing the frequency analysis further includes:

[0083] Determine whether the execution frequency of the statement is greater than or equal to the first frequency threshold and less than the second frequency threshold. If so, the warning severity level of the second warning information output is a mild warning. Or,

[0084] It is determined whether the execution frequency of the statement is greater than or equal to the second frequency threshold. If so, the warning severity level of the second warning information output is a serious warning.

[0085] Setting multiple thresholds to determine the execution frequency range can more accurately correspond to different warning levels. Different warning levels can more directly and clearly indicate the severity of the problem the user is currently facing.

[0086] In-depth analysis of slow log data helps users improve database performance. Users can take timely measures to optimize database performance and improve system operation efficiency.

[0087] In some of the embodiments, the scheduling rules include time rules, task priority rules, and resource load rules, and the time rules include timing rules and periodic rules.

[0088] Among them, the timing rules include: executing tasks at the hour or every certain number of minutes every hour, executing tasks at a certain time every day, such as 2 a.m. every day, and executing tasks at a certain date and time of the day every week, such as 8 a.m. every Monday. Periodic rules include: executing tasks every certain number of minutes, such as executing tasks every 15 minutes; executing tasks every certain number of days, such as executing tasks every 3 days. For example: set to automatically extract data from database A at 2 a.m. every day to execute the first task, and then automatically extract data from database B at 3 a.m. to execute the second task. Set tasks according to database dimensions, time dimensions, and single dimensions, set database A to automatically schedule tasks at 2 a.m. every day, and database B to automatically schedule tasks at 10 p.m. on Friday night.

[0089] Among them, the task priority rules include high priority and low priority. The task scheduling module preferentially executes tasks configured as high priority, and the collection period of high priority tasks is shorter than that of low priority tasks.

[0090] Due to different business needs, the priority of tasks may vary. Set the task priority rules in the scheduling rules, and execute high-priority tasks first according to business needs. In this way, you can be more flexible when scheduling tasks and better meet actual needs.

[0091] For high-priority tasks, set critical business tasks to high priority to ensure that they are executed first.

[0092] Low-priority tasks: Set non-business-critical tasks to low priority and execute them during idle time.

[0093] Key and non-key businesses are flexibly adjusted according to business needs.

[0094] The resource load rule includes: configuring a time modification parameter, and delaying or advancing the execution time of the task according to the time modification parameter. The time modification parameter is flexibly configured based on whether the business is at a peak period and the resource load level.

[0095] Set the maximum number of concurrent tasks to limit the number of tasks executed in the scheduling step to be less than or equal to the maximum number of concurrent tasks.

[0096] Based on the above configuration, the embodiment of the present application takes into account the impact of resource usage on database operation, and sets resource load rules to avoid data collection during business peak hours and when resource load is high. It also sets the maximum number of concurrent tasks to control the maximum number of running tasks, thereby reducing the occurrence of resource overload.

[0097] In summary, the embodiments of the present application flexibly set time rules, task priority rules, and resource load rules according to different database types and designated databases to achieve automatic calling of programs, thereby reducing the tediousness of manual operations and improving database management efficiency.

[0098] In another embodiment, the scheduling step also includes failure handling and retry rules, monitoring the task execution status, automatically retrying failed tasks or triggering alarms when failure occurs, so as to handle the problem in a timely manner.

[0099] When a task fails to execute, it will automatically retry a certain number of times and set the retry interval.

[0100] When processing a task timeout, set the maximum duration of task execution. If the timeout occurs, the task will be interrupted and the slow query file will be collected.

[0101] For example:

[0102] {

[0103] "schedule":{

[0104] "type":"daily",

[0105] "time":"02:00"

[0106] },

[0107] "priority":"high",

[0108] "conditions":{

[0109] "cpu_load_below":"50%",

[0110] "memory_usage_below":"70%"

[0111] },

[0112] "retry_policy":{

[0113] "max_retries":3,

[0114] "retry_interval":"5m"

[0115] }

[0116] }

[0117] The above configuration is a high-priority task that is executed at 2 a.m. every day. It also limits execution to when the CPU load is less than 50% and the memory load is less than 70%. It also sets a maximum of 3 failed retries with a retry interval of 5 minutes. This flexible scheduling can improve the operating efficiency and resource utilization of this application.

[0118] It should be noted that the steps shown in the above process or the flowchart in the accompanying drawings can be executed in a computer system such as a set of computer executable instructions, and although a logical order is shown in the flowchart, in some cases, the steps shown or described can be executed in an order different from that shown here.

[0119] This embodiment also provides a slow query log management system. As used below, the terms "module", "unit", "subunit", etc. can be a combination of software and / or hardware that implements a predetermined function. Although the apparatus described in the following embodiments is preferably implemented in software, implementation in hardware, or a combination of software and hardware, is also possible and contemplated.

[0120] Figure 2 is a structural block diagram of a slow query log management system according to an embodiment of the present application. Figure 2 As shown, the system includes:

[0121] The scheduling module configures the collection cycle, database and scheduling rules of a task scheduling module.

[0122] The acquisition module is configured to obtain slow log files from multiple databases based on the task scheduling module and / or obtain slow query data in the database and store it as a slow log file.

[0123] The parsing module is configured with a parser, and the slow log data in the slow log file is extracted by the parser and stored in a preset structure format for data standardization. The preset structure format includes timestamp, query statement, execution time, number of returned documents and number of scanned documents.

[0124] The analysis module is configured to extract the standardized slow log data and perform data analysis based on preset analysis dimensions, which include: execution plan, execution frequency, and execution time.

[0125] The scheduling module adjusts the collection cycle, and the collection module collects data from multiple databases. The logs of different types of databases are parsed to obtain the required information, extracted into information of the same dimension, and the slow log data is standardized. Unifying data in different formats into the same format helps to simplify the steps of repeatedly querying data in different databases and save time. The parsing module identifies and parses slow log data, extracts key information, and facilitates the analysis module to analyze slow log data. The analysis module analyzes data from multiple dimensions such as execution plan, execution frequency, and execution time, so that the analysis data is more comprehensive and the problems analyzed are more accurate.

[0126] The collection module can support multiple databases and dynamic expansion. It can obtain slow query log information from multiple databases such as Oracle, MySQL, OceanBase, TiDB, Mongo, etc., and can dynamically expand and manage data sources according to actual needs, thereby achieving flexible adaptation to different database environments.

[0127] In some embodiments, the analysis module further comprises:

[0128] The execution plan analysis unit is configured to extract a data table whose execution plan is a full table scan and analyze whether its filtering condition is configured with a valid index. If not, output a first warning message.

[0129] The execution frequency analysis unit is configured to set a frequency threshold, record the execution frequency of the statement within a preset time, determine whether the execution frequency of the statement exceeds the frequency threshold, and if so, output a second warning information.

[0130] The execution time analysis unit is configured to set a time threshold, call the execution time of the statement, and determine whether the execution time of the statement exceeds the time threshold. If so, output a third warning message.

[0131] Analyze slow log data from multiple dimensions and issue early warning information in a timely manner when problems occur. This can provide important reference for database performance optimization.

[0132] In some embodiments, performing the frequency analysis unit includes:

[0133] The frequency threshold setting subunit is configured to set a first frequency threshold and a second frequency threshold.

[0134] The first analysis subunit is configured to determine whether the execution frequency of the statement is greater than or equal to a first frequency threshold and less than a second frequency threshold. If so, the warning severity level of the second warning information output is a mild warning.

[0135] The second analysis subunit is configured to determine whether the execution frequency of the statement is greater than or equal to a second frequency threshold. If so, the warning severity level of the second warning information output is a severe warning.

[0136] Setting multiple thresholds to determine the execution frequency range can more accurately correspond to different warning levels. Different warning levels can more directly and clearly indicate the severity of the problem the user is currently facing.

[0137] Figure 3 is another structural block diagram of a slow query log management system according to an embodiment of the present application. Figure 3 As shown, a slow query log management system provided in this embodiment also includes:

[0138] Data storage module: used to store the slow log data that is parsed and standardized in the parsing module, and build an efficient and stable data warehouse. This module can ensure the integrity and security of the data, provide a reliable data foundation for the slow query log management system, and support fast retrieval and query of data.

[0139] Data aggregation module: The key link to integrate and summarize the slow log data analyzed in the analysis module is to facilitate subsequent display. Through flexible aggregation and organization of data, comprehensive and accurate data statistics and summary information can be generated, providing users with a more intuitive and comprehensive data view.

[0140] Data visualization module: Displays the data in the data summary module in the form of charts, tables, etc., and supports classification and sorting in multiple dimensions. Displays content according to actual needs, including sorting by database type, time, etc., to more clearly understand the database performance and trends. For example: sorting and displaying by top time, top database, top execution times, etc. It can also display column information and filter information according to actual conditions to meet the personalized needs of users.

[0141] Report module: including report generator and report sender. The report module is used to generate regular reports or specific event reports and send the reports to relevant personnel in a timely manner. The generated report content includes a variety of database performance analysis and optimization suggestions. It can show the report to the user in detail and clearly, and can also customize the report content according to the user's needs. For example, only send information of a certain database type or a specific database to a certain user, which can accurately provide users with comprehensive database performance management information. The report module can generate regular or specific event reports with detailed and clear content, and supports customized report content, providing users with comprehensive database performance management information.

[0142] Early warning module: used to define and manage early warning rules. When the early warning conditions are met, early warning notifications are sent to relevant personnel. Through the early warning mechanism, the system can promptly detect potential database performance problems and take corresponding preventive and response measures to ensure the stable operation of the system and data security. Users can promptly understand the abnormal situation of the database and take measures to deal with it, avoiding the risk of system failure or data loss.

[0143] The early warning mechanism is usually set up according to specific business needs and system operation conditions.

[0144] Early warning mechanisms include:

[0145] 1. Definition of Notifier:

[0146] Contains the user name, project group, email address and other information of the person who notified you.

[0147] Alarm notifications can be set to notify a specific person, or to notify everyone in a group.

[0148] For example:

[0149]

[0150] 2. Warning condition setting

[0151] Warning conditions can be flexibly set according to specific business needs, including: thresholds and severity levels.

[0152] Thresholds include time thresholds and frequency thresholds. For example, if you set a time threshold, the execution time of a slow query SQL statement exceeds 2 seconds. For example, if you set a frequency threshold of 2 seconds, the statement is triggered more than 10 times in a row within 10 minutes.

[0153] Severity levels include warning, error, critical, etc.

[0154] For example, the use of early warning conditions: the slow query threshold is set to 2s. When it is triggered once, a warning alarm is issued. When the SQL exceeds the threshold 10 times within ten minutes, the alarm is upgraded to error level.

[0155] For example:

[0156]

[0157] 3. Early warning notification

[0158] The notification methods after triggering the warning include: email, SMS, and instant messaging tools. Instant messaging tools include DingTalk and WeChat for Enterprise.

[0159] When the warning conditions are met, the system will immediately send a warning notification to relevant personnel. The notification content includes: warning type, specific parameters, timestamp and recommended measures.

[0160] The warning type is used to describe the type and cause of the warning, for example, "SQL execution exceeds 2 seconds."

[0161] Specific parameters are used to provide specific parameter values ​​that trigger alerts, for example, "SQL specific text".

[0162] The timestamp is used to record the specific time when the alert is triggered.

[0163] Suggested actions are used to provide suggested actions to respond to warnings. For example, "It is recommended to check whether the SQL being run hits the index." For example:

[0164]

[0165] When the warning conditions are met, the system will send a notification. For example:

[0166]

[0167]

[0168] It should be noted that the above modules can be functional modules or program modules, and can be implemented by software or hardware. For modules implemented by hardware, the above modules can be located in the same processor; or the above modules can be located in different processors in any combination.

[0169] In addition, combined Figure 1 The slow query log management method described in the embodiment of the present application can be implemented by a computer device. Figure 4 Schematic diagram of the hardware structure of a computer device according to an embodiment of the present application.

[0170] The computer device may include a processor 81 and a memory 82 storing computer program instructions.

[0171] Specifically, the processor 81 may include a central processing unit (CPU), or an application specific integrated circuit (ASIC), or may be configured to implement one or more integrated circuits of the embodiments of the present application.

[0172] Among them, the memory 82 may include a large-capacity memory for data or instructions. By way of example and not limitation, the memory 82 may include a hard disk drive (HDD), a floppy disk drive, a solid-state drive (SSD), a flash memory, an optical disk, a magneto-optical disk, a magnetic tape, or a universal serial bus (USB) drive, or a combination of two or more of these. Where appropriate, the memory 82 may include a removable or non-removable (or fixed) medium. Where appropriate, the memory 82 may be inside or outside the data processing device. In a specific embodiment, the memory 82 is a non-volatile memory. In a specific embodiment, the memory 82 includes a read-only memory (ROM) and a random access memory (RAM). Where appropriate, the ROM may be a mask-programmed ROM, a programmable ROM (Programmable Read-Only Memory, PROM for short), an erasable PROM (Erasable ProgrammableRead-Only Memory, EPROM for short), an electrically erasable PROM (Electrically Erasable ProgrammableRead-Only Memory, EEPROM for short), an electrically alterable ROM (Electrically Alterable Read-Only Memory, EAROM for short) or a flash memory (FLASH) or a combination of two or more of these. Under appropriate circumstances, the RAM can be a static random access memory (SRAM) or a dynamic random access memory (DRAM), wherein the DRAM can be a fast page mode dynamic random access memory (FPMDRAM), an extended data output dynamic random access memory (EDODRAM), a synchronous dynamic random access memory (SDRAM), etc.

[0173] The memory 82 may be used to store or cache various data files required for processing and / or communication, as well as possible computer program instructions executed by the processor 81 .

[0174] The processor 81 implements any one of the slow query log management methods in the above embodiments by reading and executing computer program instructions stored in the memory 82 .

[0175] In some of these embodiments, the computer device may further include a communication interface 83 and a bus 80. Figure 4 As shown, the processor 81, the memory 82, and the communication interface 83 are connected via a bus 80 and communicate with each other.

[0176] The communication interface 83 is used to implement communication between the modules, devices, units and / or equipment in the embodiment of the present application. The communication interface 83 can also implement data communication with other components such as: external devices, image / data acquisition equipment, databases, external storage, and image / data processing workstations.

[0177] The bus 80 includes hardware, software or both, and couples the components of the slow query log management device to each other. The bus 80 includes but is not limited to at least one of the following: a data bus, an address bus, a control bus, an expansion bus, and a local bus. By way of example and not limitation, bus 80 may include an Accelerated Graphics Port (AGP) or other graphics bus, an Extended Industry Standard Architecture (EISA) bus, a Front Side Bus (FSB), a Hyper Transport (HT) interconnect, an Industry Standard Architecture (ISA) bus, an InfiniBand interconnect, a Low Pin Count (LPC) bus, a memory bus, a Micro Channel Architecture (MCA) bus, a Peripheral Component Interconnect (PCI) bus, a PCI-Express (PCI-X) bus, a Serial Advanced Technology Attachment (SATA) bus, a Video Electronics Standards Association Local Bus (VLB) bus, or other suitable buses or a combination of two or more of these. Where appropriate, bus 80 may include one or more buses. Although embodiments of the present application describe and illustrate a particular bus, the present application contemplates any suitable bus or interconnect.

[0178] The computer device can execute the slow query log management method in the embodiment of the present application based on the slow log data in the various databases obtained, thereby realizing the combination of Figure 1 Describes the slow query log management method.

[0179] In addition, in combination with the slow query log management method in the above embodiment, the embodiment of the present application can provide a computer-readable storage medium for implementation. The computer-readable storage medium stores computer program instructions; when the computer program instructions are executed by a processor, any one of the slow query log management methods in the above embodiment is implemented.

[0180] The technical features of the above-described embodiments may be arbitrarily combined. To make the description concise, not all possible combinations of the technical features in the above-described embodiments are described. However, as long as there is no contradiction in the combination of these technical features, they should be considered to be within the scope of this specification.

[0181] The above-mentioned embodiments only express several implementation methods of the present application, and the descriptions thereof are relatively specific and detailed, but they cannot be understood as limiting the scope of the invention patent. It should be pointed out that, for a person of ordinary skill in the art, several variations and improvements can be made without departing from the concept of the present application, and these all belong to the protection scope of the present application. Therefore, the protection scope of the patent of the present application shall be subject to the attached claims.

Claims

1. A slow query log management method, It is characterized in that include: Scheduling step, configuring the collection cycle, database and scheduling rules of a task scheduling module; A collection step, based on the task scheduling module, obtains slow log files in multiple databases and / or obtains slow query data in the database and stores it as a slow log file; A parsing step, writing a parser, using the parser to extract the slow log data in the slow log file and store it in a preset structure format for data standardization, wherein the preset structure format includes a timestamp, a query statement, an execution time, a number of returned documents, and a number of scanned documents; The analysis step extracts the standardized slow log data and performs data analysis based on preset analysis dimensions, where the preset analysis dimensions include: execution plan, execution frequency, and execution time.

2. A slow query log management method according to claim 1, It is characterized in that The analyzing step further comprises: In the execution plan analysis step, a data table whose execution plan is a full table scan is extracted to analyze whether its filtering condition is configured with a valid index, and if not, a first warning message is output; An execution frequency analysis step is performed to monitor the execution frequency of the statement within a preset time, and to determine whether the execution frequency of the statement exceeds a frequency threshold. If so, a second warning message is output; The execution time analysis step calls the execution time of the statement and determines whether the execution time of the statement exceeds a time threshold. If so, outputs the third warning information.

3. A slow query log management method according to claim 2, It is characterized in that The frequency threshold includes a first frequency threshold and a second frequency threshold, and the step of performing frequency analysis further includes: Determine whether the execution frequency of the statement is greater than or equal to the first frequency threshold and less than the second frequency threshold, and if so, the warning severity level of the second warning information output is a mild warning, or; It is determined whether the execution frequency of the statement is greater than or equal to the second frequency threshold. If so, the warning severity level of the second warning information output is a serious warning.

4. A slow query log management method according to claim 1, It is characterized in that The scheduling rules include time rules, task priority rules, and resource load rules.

5. A slow query log management method according to claim 4, It is characterized in that The task priority rule includes high priority and low priority. The task scheduling module preferentially executes tasks configured as high priority. The collection period of the high priority task is shorter than that of the low priority task.

6. A slow query log management method according to claim 4, It is characterized in that The resource load rule includes: Configure a time modification parameter, and postpone or advance the execution time of the task according to the time modification parameter; Set the maximum number of concurrent tasks to limit the number of tasks executed in the scheduling step to be less than or equal to the maximum number of concurrent tasks.

7. A slow query log management system, used to execute a slow query log management method according to any one of claims 1 to 6, It is characterized in that include: Scheduling module, configures the collection cycle, database and scheduling rules of a task scheduling module; A collection module is configured to obtain slow log files from multiple databases and / or obtain slow query data in the database and store it as a slow log file based on the task scheduling module; A parsing module is configured with a parser, and uses the parser to extract the slow log data in the slow log file and store it in a preset structure format for data standardization, wherein the preset structure format includes a timestamp, a query statement, an execution time, a number of returned documents, and a number of scanned documents; The analysis module is configured to extract the standardized slow log data and perform data analysis based on preset analysis dimensions, where the preset analysis dimensions include: execution plan, execution frequency, and execution time.

8. A slow query log management system according to claim 7, It is characterized in that The analysis module further comprises: An execution plan analysis unit is configured to extract a data table whose execution plan is a full table scan and analyze whether its filtering condition is configured with a valid index, and if not, output a first warning message; an execution frequency analysis unit, configured to set a frequency threshold, record the execution frequency of the statement within a preset time, determine whether the execution frequency of the statement exceeds the frequency threshold, and if so, output a second warning message; The execution time analysis unit is configured to set a time threshold, call the execution time of the statement, and determine whether the execution time of the statement exceeds the time threshold. If so, output a third warning message.

9. A slow query log management system according to claim 7, It is characterized in that The frequency analysis unit includes: A frequency threshold setting subunit, configured to set a first frequency threshold and a second frequency threshold; The first analysis subunit is configured to determine whether the execution frequency of the statement is greater than or equal to a first frequency threshold and less than a second frequency threshold, and if so, the warning severity level of the second warning information output is a mild warning; The second analysis subunit is configured to determine whether the execution frequency of the statement is greater than or equal to a second frequency threshold. If so, the warning severity level of the second warning information output is a severe warning.

10. A computer device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, It is characterized in that When the processor executes the computer program, a slow query log management method according to any one of claims 1 to 6 is implemented.

Images