Database memory analysis report generation method and system

Abstract

The invention relates to the technical field of database management, and discloses a database memory analysis report generation method and system.The method comprises the steps that the real-time memory usage amount of a target database is monitored, and when the real-time memory usage amount is larger than a preset memory usage threshold value, the target database is generated; collecting a real-time global shared memory, a real-time private session memory, a real-time database concurrent connection number and real-time user connection thread information of the target database, generating one or more log information, and storing the log information to a local log of the target database; and performing data aggregation on each item of log information stored according to a preset log format in the local log to generate a memory analysis report of the target database. According to the method, the collection mechanism is triggered through the preset threshold value, the comprehensive memory indexes are collected and integrated with the user thread data to obtain the fused monitoring data, the fused monitoring data are stored as the log according to the preset format to generate the memory analysis report, the overall memory use condition is monitored, the specific connection thread can be monitored, and therefore the stability of database service is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a method and system for generating a database memory analysis report. Background Art

[0002] When processing a large number of user requests, the database management system needs to properly manage and monitor database memory usage.

[0003] The memory architecture of the database mainly consists of two parts: global shared memory and session-level private memory. Among them, global shared memory is allocated when the instance is created and shared by all connections; while session-level private memory is an independent cache space allocated when the user thread establishes a connection with the database server. In actual applications, abnormal requests or databases to be optimized may cause a single thread to allocate cache multiple times, causing excessive growth of private memory and affecting the stability of database services.

[0004] The main problem with the existing database memory monitoring technology is that the monitoring system can only obtain the overall memory usage of the database instance, and cannot quickly and accurately locate which specific threads are consuming the most memory resources. Summary of the invention

[0005] In view of this, the present invention provides a method and system for generating a database memory analysis report to solve the problem of how to more accurately locate the consumption of memory resources of each user connection thread, thereby improving the stability of database services.

[0006] In a first aspect, the present invention provides a method for generating a database memory analysis report, the method comprising:

[0007] Monitor the real-time memory usage of the target database. When the real-time memory usage is greater than a preset memory usage threshold, collect the real-time global shared memory, real-time private session memory, number of concurrent connections of the real-time database, and real-time user connection thread information of the target database, wherein the preset memory threshold represents the preset occupied memory threshold when the user establishes a connection thread with the data in the database;

[0008] Generate one or more log information according to the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database, and the real-time user connection thread information and save them to the local log of the target database in a preset log format;

[0009] Data aggregation is performed on each item of log information saved in the local log according to a preset log format to generate a memory analysis report of the target database.

[0010] The embodiment of the present invention obtains the overall memory status of the database through real-time monitoring. When the memory usage exceeds the preset threshold, detailed database memory and connection information is collected. When the system memory consumption exceeds the control range, data collection is automatically triggered, including global shared memory, private session memory and other information. The collected memory usage, connection information, etc. are recorded in the local log file of the database according to the standardized log format. The collected data is used to generate log information according to the preset log format and saved in the local log of the target database. More accurate data support is provided for subsequent analysis, and the monitoring scope is refined from the overall memory usage to more specific memory allocation details and memory consumption of each user connection thread, avoiding the problem in the prior art that only the overall memory usage can be monitored and the specific thread cannot be accurately located. The embodiment of the present invention accurately locates the consumption of memory resources of each user connection thread, thereby improving the stability of database services.

[0011] In an optional implementation, the memory threshold preset memory threshold is calculated by a threshold analysis model based on preset global memory, preset private session memory, preset database concurrent connection number and preset threshold percentage coefficient, and the threshold analysis model is represented by the following formula:

[0012]

[0013] Among them, MEMORY T Indicates the preset memory usage threshold, MEMORY g Indicates the preset global memory, MEMORY s Indicates preset private session memory, CONNECTIONS max Indicates the preset number of concurrent database connections. Indicates the preset threshold percentage coefficient.

[0014] The embodiment of the present invention, by combining a threshold analysis model, directly models the calculation process of the preset memory usage threshold, and dynamically adjusts according to the preset threshold percentage coefficient based on key parameters such as the preset global memory, the preset private session memory, and the preset number of concurrent database connections, thereby avoiding inaccuracies set by human experience. At the same time, the calculation results of the model can be dynamically adjusted as the actual operating parameters of the database change, thereby improving the flexibility of threshold setting and providing data support that is more in line with dynamic memory changes for determining whether memory analysis needs to be triggered.

[0015] In an optional implementation, before monitoring the real-time memory usage of the target database, the method further includes:

[0016] Configuring the status of each monitoring unit of the monitoring module in the target database, wherein each monitoring unit includes at least a memory monitoring unit and a user connection thread monitoring unit, and the status represents an on state or an off state of the monitoring unit;

[0017] After the status configuration of each monitoring unit of the monitoring module in the target database is completed, the target database is restarted to perform real-time monitoring of the target database.

[0018] The embodiment of the present invention divides the monitoring function into different modules such as a memory monitoring unit and a user connection thread monitoring unit, allows independent configuration of the on or off state of each monitoring unit, and restarts the database after the configuration is completed, thereby ensuring that the monitoring configuration can be fully effective, avoiding missed reports and false alarms in the monitoring link, and improving the accuracy of subsequent memory analysis.

[0019] In an optional implementation manner, the real-time global shared memory, real-time private session memory, number of concurrent connections of the real-time database, and real-time user connection thread information of the acquisition target database specifically include:

[0020] Query the memory performance monitoring table according to the first query statement to obtain the real-time global shared memory, real-time private session memory, and number of concurrent connections of the real-time database of the target database;

[0021] Query the database thread table according to the second query statement to obtain one or more target threads that are currently connected to the target database;

[0022] According to the third query statement and one or more target threads, the thread event table is queried to obtain real-time user connection thread information corresponding to the one or more target threads;

[0023] After each data query is completed, the timestamp corresponding to the query time is saved.

[0024] The embodiment of the present invention defines three different query statements. The first query statement is used to obtain the overall memory usage of the database, the second query statement is used to locate the currently active database connection thread, and the third query statement is used to obtain specific thread information. The timestamp is saved after each query, which provides time series support for subsequent data analysis. It can not only ensure the integrity, continuity and accuracy of the collected data, but also record the timestamp after each query, which provides a unified time reference for subsequent data association analysis.

[0025] In an optional implementation, generating one or more log information according to the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database, and the real-time user connection thread information according to a preset log format and saving them to a local log of the target database specifically includes:

[0026] Based on the timestamps corresponding to the real-time global shared memory, real-time private session memory, real-time database concurrent connections, and real-time user connection thread information, data timing is aligned along the same timeline;

[0027] Traverse the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information after data timing alignment, and map the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information corresponding to the same time into a log data according to the preset log data structure and save it to the local log. Stop traversal until all real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information are mapped into log information and saved in the local log.

[0028] The embodiment of the present invention performs data timing alignment based on timestamps to ensure the time consistency of data of different dimensions. By traversing the aligned data, the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information corresponding to the same time are mapped into a log data according to a preset log data structure and saved in a local log. It not only aligns the multi-dimensional information into a log format of a unified timeline, but also ensures that all monitoring data are completely converted into log information, avoiding data loss, and providing data support with clear correlation and explainability for subsequent memory analysis report generation, thereby effectively improving the accuracy of memory analysis.

[0029] In an optional implementation, when the real-time memory usage is greater than the preset memory usage threshold, a timestamp corresponding to the current moment is recorded, and according to the timestamp corresponding to the current moment, a monitoring screenshot generated by the monitoring module within a preset monitoring time interval before the current moment and / or within a preset monitoring time interval after the current moment is obtained;

[0030] The step of aggregating each log information item saved in a preset log format in the local log to generate a memory analysis report of the target database specifically includes:

[0031] Sorting each log information saved in a preset log format in the local log according to a preset sorting rule to obtain a sorting result;

[0032] According to the preset memory report template, the sorting results and the monitoring screenshots are filled into the sub-template in the preset memory report template to generate a memory analysis report of the target database.

[0033] The embodiment of the present invention provides intuitive visual evidence for problem analysis by recording the timestamp of the time when the threshold is exceeded and obtaining monitoring screenshots within a preset time interval before and after. The log information is arranged according to a preset sorting rule, and the sorting results and monitoring screenshots are standardized and integrated using a preset memory report template, so that the memory analysis results are easier to understand, and a complete monitoring record before and after the real-time memory usage is greater than the preset memory usage threshold is provided, thereby intuitively displaying the database memory and user thread status, and providing a complete and explainable report analysis for quickly locating abnormal threads, thereby improving the stability of database services.

[0034] In a second aspect, the present invention provides a database memory analysis report generation system, the system comprising:

[0035] A memory monitoring module is used to monitor the real-time memory usage of the target database. When the real-time memory usage is greater than a preset memory usage threshold, the real-time global shared memory, real-time private session memory, number of concurrent database connections, and real-time user connection thread information of the target database are collected, wherein the preset memory threshold represents a preset occupied memory threshold when a user establishes a connection thread with data in the database;

[0036] A log generation module, used to generate one or more log information and save them to a local log of a target database according to a preset log format based on the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database, and the real-time user connection thread information;

[0037] The data aggregation module is used to aggregate each log information saved in the local log according to the preset log format to generate a memory analysis report of the target database.

[0038] In a third aspect, the present invention provides a computer device, comprising: a memory and a processor, the memory and the processor being communicatively connected to each other, the memory storing computer instructions, and the processor executing the database memory analysis report generation method of the first aspect or any corresponding embodiment thereof by executing the computer instructions.

[0039] In a fourth aspect, the present invention provides a computer-readable storage medium having computer instructions stored thereon, the computer instructions being used to enable a computer to execute the database memory analysis report generation method of the above-mentioned first aspect or any corresponding embodiment thereof.

[0040] In a fifth aspect, the present invention provides a computer program product, comprising computer instructions, wherein the computer instructions are used to enable a computer to execute the method for generating a database memory analysis report according to the first aspect or any corresponding embodiment thereof. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the specific implementation methods of the present invention or the technical solutions in the prior art, the drawings required for use in the specific implementation methods or the description of the prior art will be briefly introduced below. Obviously, the drawings described below are some implementation methods of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying creative work.

[0042] Figure 1 is a flowchart of a method for generating a database memory analysis report according to an embodiment of the present invention;

[0043] Figure 2 is a flowchart of another method for generating a database memory analysis report according to an embodiment of the present invention;

[0044] Figure 3 is a flowchart of another method for generating a database memory analysis report according to an embodiment of the present invention;

[0045] Figure 4 is a flowchart of another method for generating a database memory analysis report according to an embodiment of the present invention;

[0046] Figure 5 is a structural block diagram of a database memory analysis report generation system according to an embodiment of the present invention;

[0047] Figure 6 It is a schematic diagram of the hardware structure of a computer device according to an embodiment of the present invention. DETAILED DESCRIPTION

[0048] In order to make the purpose, technical solution and advantages of the embodiments of the present invention clearer, the technical solution in the embodiments of the present invention will be clearly and completely described below in conjunction with the drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative work are within the scope of protection of the present invention.

[0049] When a database management system processes a large number of user requests, it is necessary to effectively manage and monitor the database memory usage. Taking MySQL as an example, its memory architecture is mainly divided into two parts: global shared memory and session-level private memory. Global shared memory is allocated when the instance is created and shared by all connections, while private memory is the memory space allocated for each connection when the user thread connects to the MySQL server.

[0050] When an abnormal request or a SQL statement to be optimized occurs, a single connection thread may apply for allocation of private memory space multiple times, resulting in a sharp increase in memory usage. This may trigger the OOM (memory management mechanism) mechanism, and the operating system will forcibly terminate the process with excessive memory usage to release memory resources, which may eventually lead to consequences such as MySQL process crash, database service interruption, data loss or corruption, etc.

[0051] The existing monitoring solution can only display the overall memory usage of the MySQL instance, but cannot accurately locate the specific abnormal problem thread. When the memory usage surges, it is impossible to quickly identify which connection threads are consuming the most memory resources. It lacks the ability to monitor the memory usage at the level of a single connection thread in real time, making it difficult to detect potential memory leaks in a timely manner. The main technical problem is that it is impossible to accurately monitor the memory usage of each connection thread in the database, which affects the stability of the database.

[0052] The embodiment of the present invention provides a method for generating a database memory analysis report, which is suitable for environments where real-time monitoring of database memory usage is required and scenarios where troubleshooting occurs when a database has a memory usage failure. By triggering a collection mechanism through a preset threshold, collecting comprehensive memory indicators and integrating user thread data to obtain fused monitoring data, the fused monitoring data is saved as a log in a preset format, and a memory analysis report is generated, which not only monitors the overall memory usage, but also monitors specific connection threads, thereby improving the stability of database services.

[0053] According to an embodiment of the present invention, an embodiment of a method for generating a database memory analysis report is provided. It should be noted that the steps shown in the flowchart of 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.

[0054] In this embodiment, a method for generating a database memory analysis report is provided, which can be used in the above-mentioned computer. Figure 1 is a flowchart of a method for generating a database memory analysis report according to an embodiment of the present invention. Figure 1 As shown, the process includes the following steps:

[0055] Step S101, monitor the real-time memory usage of the target database. When the real-time memory usage is greater than a preset memory usage threshold, collect the real-time global shared memory, real-time private session memory, real-time database concurrent connection number and real-time user connection thread information of the target database, wherein the preset memory threshold represents the preset occupied memory threshold when the user establishes a connection thread with the data in the database.

[0056] It should be noted that real-time memory usage refers to the total amount of memory being used by the database instance at the current moment. The preset memory usage threshold refers to a pre-set memory usage value. When the real-time memory usage exceeds this threshold, the data collection operation is triggered. Real-time global shared memory refers to the global shared memory usage of the database instance at the current moment. Real-time private session memory refers to the sum of private memory usage of all active sessions at the current moment. The number of concurrent connections to the real-time database refers to the number of clients connected to the database at the current moment. Real-time user connection thread information refers to the detailed information of each active connection thread at the current moment, such as thread ID, SQL statement being executed, connection IP, user name, etc.

[0057] Specifically, the preset memory threshold is calculated through a threshold analysis model based on the preset global memory, the preset private session memory, the preset number of concurrent database connections, and the preset threshold percentage coefficient. The threshold analysis model is represented by the following formula:

[0058]

[0059] Among them, MEMORY T Indicates the preset memory usage threshold, MEMORY g Indicates the preset global memory, MEMORY s Indicates preset private session memory, CONNECTIONS max Indicates the preset number of concurrent database connections. Indicates the preset threshold percentage coefficient.

[0060] Understandably, MEMORY g +MEMORY s ×CONNECTIONS max Indicates the theoretical maximum memory usage, and the preset threshold percentage coefficient is a ratio value set by the administrator to determine the threshold for user thread collection and reporting (MEMORY T ) relative to the theoretical maximum memory size, in this formula, A percentage between 0 and 100%.

[0061] For example, if If the memory usage is 80%, then the memory usage threshold is set to 80% of the theoretical maximum memory usage. When the MySQL memory usage is greater than 80% of the theoretical maximum memory usage, the next step of monitoring data collection is required.

[0062] The embodiment of the present invention, by combining a threshold analysis model, directly models the calculation process of the preset memory usage threshold, and dynamically adjusts according to the preset threshold percentage coefficient based on key parameters such as the preset global memory, the preset private session memory, and the preset number of concurrent database connections, thereby avoiding inaccuracies set by human experience. At the same time, the calculation results of the model can be dynamically adjusted as the actual operating parameters of the database change, thereby improving the flexibility of threshold setting and providing data support that is more in line with dynamic memory changes for determining whether memory analysis needs to be triggered.

[0063] Step S102, generating one or more log information according to the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database and the real-time user connection thread information according to the preset log format and saving them to the local log of the target database.

[0064] It should be noted that the preset log format refers to a predefined format of log information, such as JSON format. The local log refers to a log file stored in the local file system of the database server.

[0065] Specifically, use a programming language to write a script, connect to the database, query the corresponding monitoring data source, format the data and write it to the local log file, so as to customize the log output according to flexible needs; or use professional log collection and processing tools, configure different input plug-ins to read various data sources, and use output plug-ins to write data to different destinations (local logs).

[0066] Step S103: Aggregate data of each log information saved in a preset log format in the local log to generate a memory analysis report of the target database.

[0067] It should be noted that the memory analysis report refers to a document or report containing the analysis results of database memory usage. The purpose of generating a memory analysis report in the embodiment of the present invention is to organize the collected data and present it to the operation and maintenance personnel in an easy-to-understand manner, so that the operation and maintenance personnel can quickly locate the specific user thread with memory problems.

[0068] Specifically, the collected data is read from the local log file, parsed, extracted, aggregated and analyzed, and then a memory analysis report is generated. You can use a programming language to write a script to parse the log file and generate a report, or use a report generation tool to directly extract data from the log to generate a report.

[0069] The embodiment of the present invention obtains the overall memory status of the database through real-time monitoring. When the memory usage exceeds the preset threshold, detailed database memory and connection information is collected. When the system memory consumption exceeds the control range, data collection is automatically triggered, including global shared memory, private session memory and other information. The collected memory usage, connection information, etc. are recorded in the local log file of the database according to the standardized log format. The collected data is used to generate log information according to the preset log format and saved in the local log of the target database. More accurate data support is provided for subsequent analysis, and the monitoring scope is refined from the overall memory usage to more specific memory allocation details and memory consumption of each user connection thread, avoiding the problem in the prior art that only the overall memory usage can be monitored and the specific thread cannot be accurately located. The embodiment of the present invention accurately locates the consumption of memory resources of each user connection thread, thereby improving the stability of database services.

[0070] In this embodiment, a method for generating a database memory analysis report is provided, which can be used in the above-mentioned computer. Figure 2 is a flowchart of a method for generating a database memory analysis report according to an embodiment of the present invention. Figure 2 As shown, the process includes the following steps:

[0071] Step S201, configuring the status of each monitoring unit of the monitoring module in the target database, wherein each monitoring unit includes at least a memory monitoring unit and a user connection thread monitoring unit, and the status represents the on state or off state of the monitoring unit.

[0072] It should be noted that the monitoring module refers to a component that monitors various indicators in the database, which may be Performance Schema (i.e., a performance monitoring and analysis tool for MySQL database) in the present invention. The embodiment of the present invention may choose to enable or disable a specific monitoring unit according to actual needs to avoid unnecessary resource consumption.

[0073] Taking Performance Schema as an example, Performance Schema implements monitoring functions by recording various events and resource usage in the database. By configuring the relevant parameters of Performance Schema, you can control which events need to be recorded and the granularity of the records. Specifically, you can modify the performance_schema related parameters in the MySQL configuration file. For example, to enable the collection of memory events, you need to set the fields of the memory-related collector configuration items in the performance_schema.setup_instruments table to YES, and also set the fields of the corresponding consumer configuration items in the performance_schema.setup_consumers table to YES.

[0074] Step S202: after the status configuration of each monitoring unit of the monitoring module in the target database is completed, the target database is restarted to perform real-time monitoring of the target database.

[0075] It should be noted that restarting the database is to make the configuration changes in S201 take effect. For example, some MySQL configuration parameters need to be restarted before the database can take effect, including the configuration of Performance Schema. Specifically, use the operating system command to restart the MySQL service, or use the MySQL client tool to connect to the database server and execute the SHUTDOWN command, and then start the MySQL service.

[0076] In addition, Influxdb, Telegraf, and Grafana can also be installed to monitor and visualize memory, and database instance memory monitoring data can be collected by configuring influxdb and telegraf. InfluxDB represents an open source time series database that is specifically used to store and query time-related data. Telegraf represents an open source agent program that is used to collect, process, and send indicator data to InfluxDB or other data storage systems. It supports a variety of data sources and output plug-ins, and can flexibly adapt to different monitoring needs. Grafana represents an open source data visualization and monitoring platform that can be connected to various data sources, including InfluxDB, etc., to obtain data and create interactive dashboards and charts for real-time monitoring and analysis of data. In the embodiment of the present invention, by dividing the monitoring function into different modules such as a memory monitoring unit and a user connection thread monitoring unit, by allowing the on or off state of each monitoring unit to be independently configured, the monitoring configuration is ensured to be fully effective by restarting the database after the configuration is completed, avoiding the situation of missed reports and false reports in the monitoring link, and improving the accuracy of subsequent memory analysis.

[0077] Step S203, monitor the real-time memory usage of the target database. When the real-time memory usage is greater than a preset memory usage threshold, collect the real-time global shared memory, real-time private session memory, real-time database concurrent connection number and real-time user connection thread information of the target database, wherein the preset memory threshold represents the preset occupied memory threshold when the user establishes a connection thread with the data in the database.

[0078] Specifically, the above step S203 collects the real-time global shared memory, real-time private session memory, number of concurrent connections of the real-time database, and real-time user connection thread information of the target database, including:

[0079] Step S2031, querying the memory performance monitoring table according to the first query statement to obtain the real-time global shared memory, real-time private session memory, and number of concurrent connections of the real-time database of the target database;

[0080] Step S2032, querying the database thread table according to the second query statement to obtain one or more target threads currently connected to the target database;

[0081] Step S2033, querying the thread event table according to the third query statement and one or more target threads, and obtaining real-time user connection thread information corresponding to the one or more target threads; wherein, after each query data is completed, saving the timestamp corresponding to the query time.

[0082] Exemplarily, performance_schema includes multiple MySQL system tables, among which performance_schema.setup_instruments (memory performance monitoring table) configures the performance indicators and events to be monitored. performance_schema.setup_consumers (memory performance monitoring table) configures the components to receive and consume performance data. performance_schema.threads (database thread table) stores information about database threads.

[0083] performance_schema.memory_summary_by_thread_by_event_name (thread event table) stores thread memory usage by thread and event name. Specifically, use

[0084] The performance_schema.setup_instruments table and the performance_schema.setup_consumer table get global memory usage information. Use the threads table to get one or more target threads, thread connection information, and the SQL statement being executed. Use

[0085] The performance_schema.memory_summary_by_thread_by_event_name table obtains thread-level memory usage information and thread connection information. Use a scheduled task or daemon to regularly query the relevant tables of PerformanceSchema and store the collected data in InfluxDB. Configure a dashboard in Grafana to display memory usage trends and set alarm rules. When memory usage exceeds the preset threshold, an alarm is triggered. When memory usage exceeds the threshold, data is collected and saved in a local log file.

[0086] In the embodiment of the present invention, the various parameter indicators and meanings of the collected real-time global shared memory, real-time private session memory, and real-time database concurrent connection number can be referred to the following table:

[0087]

[0088] In the embodiment of the present invention, the corresponding system tables, fields and remarks of the collected real-time user connection thread information can be referred to the following table:

[0089]

[0090] Among them, “ / ” means that the corresponding information does not need to be obtained from the system table, but is collected based on other technical means. For details, please refer to the corresponding notes. Similarly, since it does not need to be obtained from the system table, no field of the system table needs to be used, and the corresponding position is also “ / ”.

[0091] The embodiment of the present invention defines three different query statements. The first query statement is used to obtain the overall memory usage of the database, the second query statement is used to locate the currently active database connection thread, and the third query statement is used to obtain specific thread information. The timestamp is saved after each query, which provides time series support for subsequent data analysis. It can not only ensure the integrity, continuity and accuracy of the collected data, but also record the timestamp after each query, which provides a unified time reference for subsequent data association analysis.

[0092] Step S204, generating one or more log information according to the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database and the real-time user connection thread information according to the preset log format and saving them to the local log of the target database.

[0093] Specifically, step S204 includes:

[0094] Step S2041, based on the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database, and the timestamps corresponding to the real-time user connection thread information, the data timing is aligned according to the same timeline.

[0095] Exemplarily, a uniform time granularity is determined, such as one data point per second, all data are traversed, and the data are grouped into the nearest whole second time point according to the timestamp, and finally a data set aligned by second is obtained, and each time point contains the values ​​of all monitoring indicators.

[0096] Step S2042, traverse the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information after the data timing is aligned, and map the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information corresponding to the same time into a log data according to a preset log data structure and save it to the local log until all the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information are mapped into log information and saved in the local log, and then stop traversing.

[0097] It should be noted that converting the aligned data into a unified log format can facilitate subsequent data processing, and the unified log format ensures that the data is easy to read, easy to parse, and explain. The principle of the steps in this embodiment is to traverse the time-series aligned data and combine all monitoring indicators at each time point into a complete log entry, which is actually a process of compressing multi-dimensional data (different monitoring indicators) into one dimension (a single log).

[0098] Specifically, it includes (1) traversing the time-series aligned data set and processing all indicators at one time point at a time. (2) creating a dictionary or object containing all monitoring indicator values ​​at that time point. (3) converting the dictionary or object into a preset log format. (4) writing the formatted log to a local file. Repeat steps (1) to (4) until all data at all time points are stored.

[0099] Step S205: aggregate each log information saved in the local log in a preset log format to generate a memory analysis report of the target database. Figure 1 Step S103 of the illustrated embodiment will not be described in detail here.

[0100] The embodiment of the present invention performs data timing alignment based on timestamps to ensure the time consistency of data of different dimensions. By traversing the aligned data, the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information corresponding to the same time are mapped into a log data according to a preset log data structure and saved in a local log. It not only aligns the multi-dimensional information into a log format of a unified timeline, but also ensures that all monitoring data are completely converted into log information, avoiding data loss, and providing data support with clear correlation and explainability for subsequent memory analysis report generation, thereby effectively improving the accuracy of memory analysis.

[0101] In this embodiment, a method for generating a database memory analysis report is provided, which can be used in the above-mentioned computer. Figure 3 is a flowchart of a method for generating a database memory analysis report according to an embodiment of the present invention. Figure 3 As shown, the process includes the following steps:

[0102] Step S301, monitor the real-time memory usage of the target database. When the real-time memory usage is greater than the preset memory usage threshold, collect the real-time global shared memory, real-time private session memory, real-time database concurrent connection number and real-time user connection thread information of the target database, wherein the preset memory threshold represents the preset occupied memory threshold when the user establishes a connection thread with the data in the database; record the timestamp corresponding to the current moment, and obtain the monitoring screenshot generated by the monitoring module within the preset monitoring time interval before the current moment and / or the preset monitoring time interval after the current moment according to the timestamp corresponding to the current moment.

[0103] Step S302: Generate one or more log information and save them to the local log of the target database according to the preset log format based on the real-time global shared memory, real-time private session memory, the number of concurrent connections of the real-time database, and the real-time user connection thread information. Figure 1 Step S102 of the illustrated embodiment will not be described in detail here.

[0104] Step S303: Aggregate data of each log information saved in the local log according to the preset log format to generate a memory analysis report of the target database.

[0105] Specifically, step S303 includes:

[0106] Step S3031, sorting each log information saved in a preset log format in the local log according to a preset sorting rule to obtain a sorting result.

[0107] It should be noted that the preset sorting rules refer to predefined sorting methods, such as sorting by timestamp in ascending or descending order, sorting by memory usage from high to low, sorting by thread ID, etc. Sorting log information is to facilitate subsequent report analysis. For example, sorting by timestamp can clearly show the trend of memory usage; sorting by memory usage can quickly identify the thread that consumes the most memory; sorting by thread ID can easily track the memory usage of a specific thread. The sorted data is more organized.

[0108] Specifically, read the local log file, parse the log data therein, convert each log information into a sortable data structure, select a sorting algorithm according to a preset sorting rule, sort the log information based on the data structure, and save the sorted results into a new list or file.

[0109] Step S3032: according to the preset memory report template, fill the sorting results and monitoring screenshots into the sub-template in the preset memory report template to generate a memory analysis report of the target database.

[0110] It should be noted that the preset memory report template refers to a predefined report format, which includes corresponding placeholders. The log information placeholder is used to fill in the sorted log information, and the screenshot information placeholder is used to fill in the monitoring screenshot. Filling the sorted log information and monitoring screenshots into the template can automatically generate a complete memory analysis report, saving the time of manual report writing.

[0111] In actual operation, you can use automatic scripts to read local log files and parse log data in JSON format. Aggregate and analyze the data, including calculating the ranking of memory usage and counting the number of SQL statement executions. Integrate the analysis results and Grafana monitoring charts using the template engine to generate a memory analysis report in PDF format and provide a web download link.

[0112] The embodiment of the present invention provides intuitive visual evidence for problem analysis by recording the timestamp of the time when the threshold is exceeded and obtaining monitoring screenshots within a preset time interval before and after. The log information is arranged according to a preset sorting rule, and the sorting results and monitoring screenshots are standardized and integrated using a preset memory report template, so that the memory analysis results are easier to understand, and a complete monitoring record before and after the real-time memory usage is greater than the preset memory usage threshold is provided, thereby intuitively displaying the database memory and user thread status, and providing a complete and explainable report analysis for quickly locating abnormal threads, thereby improving the stability of database services.

[0113] In actual application, a method for generating a database memory analysis report provided in this embodiment can be referred to Figure 4 Taking MySQL database as an example, the process includes: (1) Continuously monitor the memory usage of MySQL. (2) Compare the current memory usage with the preset threshold. If the memory usage exceeds the threshold, record the current time point, record the relevant MySQL configuration information that causes high memory usage to the log, obtain the current active thread information, including thread ID, connection information, executed SQL statements, etc., and record them to the log / sort the threads by memory usage, and record the sorting results to the log. You can also capture the monitoring chart screenshots before and after the current time point. (3) If the memory usage does not exceed the threshold, return to (1) to continue monitoring. (4) Parse and extract the recorded log data to prepare the data required to generate the report. (5) Generate a memory analysis report based on the extracted data and monitoring chart screenshots. (6) Determine whether it is necessary to terminate memory collection. If terminated, the entire process ends; if not terminated, return to (1) to continue monitoring. Therefore, in actual applications, the most intuitive application scenario is the scenario where the MySQL database instance on the public cloud analyzes the database memory. Through the memory analysis report, operation and maintenance personnel can obtain key configuration information and thread operation information when the memory usage exceeds a specific threshold, and promptly discover abnormal threads that affect memory usage, which is helpful for troubleshooting online operation and maintenance problems, and is also of great help in tuning online database performance parameters.

[0114] In this embodiment, a database memory analysis report generation system is also provided, which is used to implement the above-mentioned embodiments and preferred implementation modes, and will not be repeated hereafter. As used below, the term "module" can implement a combination of software and / or hardware of a predetermined function. Although the system described in the following embodiments is preferably implemented in software, the implementation of hardware, or a combination of software and hardware, is also possible and conceivable.

[0115] This embodiment provides a database memory analysis report generation system, such as Figure 5 As shown, including:

[0116] The memory monitoring module 501 is used to monitor the real-time memory usage of the target database. When the real-time memory usage is greater than a preset memory usage threshold, the real-time global shared memory, real-time private session memory, number of concurrent connections of the real-time database, and real-time user connection thread information of the target database are collected, wherein the preset memory threshold represents the preset occupied memory threshold when the user establishes a connection thread with the data in the database;

[0117] The log generation module 502 is used to generate one or more log information according to the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database and the real-time user connection thread information according to the preset log format and save them to the local log of the target database;

[0118] The data aggregation module 503 is used to aggregate each log information saved in a preset log format in the local log to generate a memory analysis report of the target database.

[0119] In some optional implementations, the preset memory threshold is calculated by a threshold analysis model based on the preset global memory, the preset private session memory, the preset number of concurrent database connections, and the preset threshold percentage coefficient. The threshold analysis model is represented by the following formula:

[0120]

[0121] Among them, MEMORY T Indicates the preset memory usage threshold, MEMORY g Indicates the preset global memory, MEMORY s Indicates preset private session memory, CONNECTIONS max Indicates the preset number of concurrent database connections. Indicates the preset threshold percentage coefficient.

[0122] In some optional embodiments, the above system further includes:

[0123] A monitoring configuration module, used to configure the status of each monitoring unit of the monitoring module in the target database, wherein each monitoring unit includes at least a memory monitoring unit and a user connection thread monitoring unit, and the status represents the on state or off state of the monitoring unit;

[0124] The system restart module is used to restart the target database after the status configuration of each monitoring unit of the monitoring module in the target database is completed, so as to perform real-time monitoring of the target database.

[0125] In some optional implementations, the memory monitoring module 501 includes:

[0126] A memory and connection query unit, used to query the memory performance monitoring table according to the first query statement to obtain the real-time global shared memory, real-time private session memory, and real-time database concurrent connection number of the target database;

[0127] A thread query unit, configured to query a database thread table according to a second query statement, and obtain one or more target threads connected to a target database at a current moment;

[0128] The thread event query unit is used to query the thread event table according to the third query statement and one or more target threads to obtain the real-time user connection thread information corresponding to the one or more target threads; wherein, after each query data is completed, the timestamp corresponding to the query time is saved.

[0129] In some optional implementations, the log generation module 502 includes:

[0130] The data timing alignment unit is used to align the data timing according to the same timeline based on the timestamps corresponding to the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database, and the real-time user connection thread information;

[0131] The log mapping unit is used to traverse the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information after data timing alignment, and map the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information corresponding to the same time into a log data according to a preset log data structure and save it to the local log until all the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information are mapped into log information and saved in the local log, and then stop traversal.

[0132] In some optional implementations, when the real-time memory usage is greater than the preset memory usage threshold, a timestamp corresponding to the current moment is recorded, and according to the timestamp corresponding to the current moment, a monitoring screenshot generated by the monitoring module within a preset monitoring time interval before the current moment and / or within a preset monitoring time interval after the current moment is obtained; the data aggregation module 503 includes:

[0133] The log sorting unit is used to sort each log information saved in a preset log format in the local log according to a preset sorting rule to obtain a sorting result;

[0134] The report generation unit is used to fill the sorting results and monitoring screenshots into the sub-template in the preset memory report template according to the preset memory report template, so as to generate a memory analysis report of the target database.

[0135] The further functional description of each of the above modules and units is the same as that of the above corresponding embodiments and will not be repeated here.

[0136] The database memory analysis report generation system in this embodiment is presented in the form of a functional unit, where the unit refers to an ASIC (Application Specific Integrated Circuit) circuit, a processor and memory that executes one or more software or fixed programs, and / or other devices that can provide the above functions.

[0137] The embodiment of the present invention also provides a computer device having the above Figure 5 The database memory analysis report generation system shown.

[0138] See also Figure 6 , Figure 6 is a schematic diagram of the structure of a computer device provided by an optional embodiment of the present invention, such as Figure 6 As shown, the computer device includes: one or more processors 10, a memory 20, and interfaces for connecting various components, including high-speed interfaces and low-speed interfaces. Various components are connected to each other using different buses for communication, and can be installed on a common mainboard or installed in other ways as needed. The processor can process the instructions executed in the computer device, including instructions stored in or on the memory to display the graphical information of the GUI on an external input / output device (such as, a display device coupled to the interface). In some optional embodiments, if necessary, multiple processors and / or multiple buses can be used together with multiple memories and multiple memories. Similarly, multiple computer devices can be connected, and each device provides some necessary operations (for example, as a server array, a group of blade servers, or a multi-processor system). Figure 6 A processor 10 is taken as an example.

[0139] The processor 10 may be a central processing unit, a network processor or a combination thereof. The processor 10 may further include a hardware chip. The hardware chip may be a dedicated integrated circuit, a programmable logic device or a combination thereof. The programmable logic device may be a complex programmable logic device, a field programmable gate array, a general purpose array logic or any combination thereof.

[0140] The memory 20 stores instructions executable by at least one processor 10, so that at least one processor 10 executes the method shown in the above embodiment.

[0141] The memory 20 may include a program storage area and a data storage area, wherein the program storage area may store an operating system, an application required for at least one function; the data storage area may store data created according to the use of the computer device, etc. In addition, the memory 20 may include a high-speed random access memory, and may also include a non-transient memory, such as at least one disk storage device, a flash memory device, or other non-transient solid-state storage device. In some optional embodiments, the memory 20 may optionally include a memory remotely arranged relative to the processor 10, and these remote memories may be connected to the computer device via a network. Examples of the above-mentioned network include, but are not limited to, the Internet, an intranet, a local area network, a mobile communication network, and combinations thereof.

[0142] The memory 20 may include a volatile memory, such as a random access memory; the memory may also include a non-volatile memory, such as a flash memory, a hard disk or a solid state drive; the memory 20 may also include a combination of the above types of memory.

[0143] The computer device further comprises a communication interface 30 for the computer device to communicate with other devices or a communication network.

[0144] The embodiment of the present invention also provides a computer-readable storage medium. The method according to the embodiment of the present invention can be implemented in hardware, firmware, or can be implemented as a computer code that can be recorded in a storage medium, or can be implemented as a computer code that is originally stored in a remote storage medium or a non-temporary machine-readable storage medium and will be stored in a local storage medium through a network download, so that the method described herein can be stored in such software processing on a storage medium using a general-purpose computer, a dedicated processor, or programmable or dedicated hardware. Among them, the storage medium can be a magnetic disk, an optical disk, a read-only storage memory, a random access memory, a flash memory, a hard disk or a solid-state hard disk, etc.; further, the storage medium can also include a combination of the above types of memories. It can be understood that a computer, a processor, a microprocessor controller, or programmable hardware includes a storage component that can store or receive software or computer code. When the software or computer code is accessed and executed by a computer, a processor, or hardware, the method shown in the above embodiment is implemented.

[0145] A part of the present invention may be applied as a computer program product, such as a computer program instruction, which, when executed by a computer, can call or provide the method and / or technical solution according to the present invention through the operation of the computer. Those skilled in the art should understand that the existence of the computer program instruction in a computer-readable medium includes, but is not limited to, a source file, an executable file, an installation package file, etc., and accordingly, the way in which the computer program instruction is executed by the computer includes, but is not limited to: the computer directly executes the instruction, or the computer compiles the instruction and then executes the corresponding compiled program, or the computer reads and executes the instruction, or the computer reads and installs the instruction and then executes the corresponding installed program. Here, the computer-readable medium may be any available computer-readable storage medium or communication medium accessible to the computer.

[0146] Although the embodiments of the present invention have been described in conjunction with the accompanying drawings, those skilled in the art may make various modifications and variations without departing from the spirit and scope of the present invention, and such modifications and variations are all within the defined scope.

Claims

1. A method for generating a database memory analysis report, characterized in that: The method comprises: Monitor the real-time memory usage of the target database. When the real-time memory usage is greater than a preset memory usage threshold, collect the real-time global shared memory, real-time private session memory, number of concurrent connections of the real-time database, and real-time user connection thread information of the target database, wherein the preset memory threshold represents the preset occupied memory threshold when the user establishes a connection thread with the data in the database; Generate one or more log information according to the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database, and the real-time user connection thread information and save them to the local log of the target database in a preset log format; Data aggregation is performed on each item of log information saved in the local log according to a preset log format to generate a memory analysis report of the target database.

2. The method according to claim 1, characterized in that The preset memory threshold is calculated by a threshold analysis model based on the preset global memory, the preset private session memory, the preset number of concurrent database connections and the preset threshold percentage coefficient. The threshold analysis model is represented by the following formula: Among them, MEMORY T Indicates the preset memory usage threshold, MEMORY g Indicates the preset global memory, MEMORY s Indicates preset private session memory, CONNECTIONS max Indicates the preset number of concurrent database connections. Indicates the preset threshold percentage coefficient.

3. The method according to claim 2, characterized in that Before monitoring the real-time memory usage of the target database, the method further includes: Configuring the status of each monitoring unit of the monitoring module in the target database, wherein each monitoring unit includes at least a memory monitoring unit and a user connection thread monitoring unit, and the status represents an on state or an off state of the monitoring unit; After the status configuration of each monitoring unit of the monitoring module in the target database is completed, the target database is restarted to perform real-time monitoring of the target database.

4. The method according to claim 2 or 3, characterized in that: The real-time global shared memory, real-time private session memory, number of concurrent connections of the real-time database, and real-time user connection thread information of the target database are collected, specifically including: Query the memory performance monitoring table according to the first query statement to obtain the real-time global shared memory, real-time private session memory, and number of concurrent connections of the real-time database of the target database; Query the database thread table according to the second query statement to obtain one or more target threads that are currently connected to the target database; According to the third query statement and one or more target threads, the thread event table is queried to obtain real-time user connection thread information corresponding to the one or more target threads; After each data query is completed, the timestamp corresponding to the query time is saved.

5. The method according to claim 4, characterized in that The method generates one or more log information according to the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database, and the real-time user connection thread information and saves them to the local log of the target database in accordance with the preset log format, specifically including: Based on the timestamps corresponding to the real-time global shared memory, real-time private session memory, real-time database concurrent connections, and real-time user connection thread information, data timing is aligned along the same timeline; Traverse the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information after data timing alignment, and map the real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information corresponding to the same time into a log data according to the preset log data structure and save it to the local log. Stop traversal until all real-time global shared memory, real-time private session memory, real-time database concurrent connection number, and real-time user connection thread information are mapped into log information and saved in the local log.

6. The method according to any one of claims 1 to 3, characterized in that: When the real-time memory usage is greater than the preset memory usage threshold, a timestamp corresponding to the current moment is recorded, and according to the timestamp corresponding to the current moment, a monitoring screenshot generated by the monitoring module within a preset monitoring time interval before the current moment and / or within a preset monitoring time interval after the current moment is obtained; The step of aggregating each log information item saved in a preset log format in the local log to generate a memory analysis report of the target database specifically includes: Sorting each log information saved in a preset log format in the local log according to a preset sorting rule to obtain a sorting result; According to the preset memory report template, the sorting results and the monitoring screenshots are filled into the sub-template in the preset memory report template to generate a memory analysis report of the target database.

7. A database memory analysis report generation system, characterized in that: The system comprises: A memory monitoring module is used to monitor the real-time memory usage of the target database. When the real-time memory usage is greater than a preset memory usage threshold, the real-time global shared memory, real-time private session memory, number of concurrent database connections, and real-time user connection thread information of the target database are collected, wherein the preset memory threshold represents a preset occupied memory threshold when a user establishes a connection thread with data in the database; A log generation module, used to generate one or more log information and save them to a local log of a target database according to a preset log format based on the real-time global shared memory, the real-time private session memory, the number of concurrent connections of the real-time database, and the real-time user connection thread information; The data aggregation module is used to aggregate each log information saved in the local log according to the preset log format to generate a memory analysis report of the target database.

8. A computer device, characterized in that: include: A memory and a processor, wherein the memory and the processor are communicatively connected to each other, the memory stores computer instructions, and the processor executes the database memory analysis report generation method according to any one of claims 1 to 6 by executing the computer instructions.

9. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and the computer instructions are used to enable a computer to execute the database memory analysis report generation method according to any one of claims 1 to 6.

10. A computer program product, characterized in that It includes computer instructions, and the computer instructions are used to enable a computer to execute the database memory analysis report generation method according to any one of claims 1 to 6.

Images