Observation and diagnosis system for database

Abstract

The invention discloses an observation and diagnosis system for a database, and relates to the technical field of databases, the observation and diagnosis system comprises a flow monitoring module, a flow dispersion module, a debugging module and an emergency module, the flow dispersion module is connected with the flow monitoring module through signal transmission, and the debugging module is connected with the flow monitoring module through baseband transmission. The emergency module is connected with the flow monitoring module through wireless transmission, and a monitoring tool, a log analysis tool and a network data packet analysis tool are arranged in the flow monitoring module. By designing the flow monitoring module, the function of monitoring the data volume is achieved, the problems that hardware resource bottlenecks are not found in time, internal resources of a database are unreasonably used and hidden hardware faults are not easy to find are solved, the change of the data volume can be found in time, performance reduction caused by too large data volume is avoided, and the service life of the database is prolonged. The security index of the database is improved, and the resource utilization rate is improved.

Description

Technical Field

[0001] The present invention relates to the technical field of databases, and in particular to an observation and diagnosis system for databases. Background Art

[0002] Database technology originated in the 1960s. With the development of systems such as GE's IDS and IBM's IMS, database technology has gradually matured. The application of databases is extensive and in-depth, mainly reflected in data management, data sharing, data security and efficiency improvement. It can meet the efficient management and application needs of large amounts of data in modern society. By centrally managing data, it ensures data consistency and integrity and improves the efficiency of data query and operation. With the vigorous development of big data, enterprises have built complex data integration, data warehouse and data analysis systems to cope with the growing data demand. This complex system brings challenges to monitoring and ensuring the reliable operation of data pipelines. At the same time, it also needs to ensure that the data used is true and valid. The database diagnostic system is an attack used to monitor, analyze and optimize database performance. It aims to help users quickly discover and solve performance problems in the database. It has functions such as real-time monitoring of database status, performance analysis, fault diagnosis and optimization suggestions. It can go deep into the database kernel and provide accurate diagnostic analysis and optimization strategies. Domestic databases are in a stage of rapid development. Product quality control is still unstable and version upgrades are frequent, which brings risks to the safe operation of production systems. Resource requirements and costs are high, and there are defects in some functions. The monitoring and fault analysis tools of domestic databases are not yet mature, and the fault diagnosis and self-healing functions need to be improved. The database observation system and database diagnosis system have certain defects in security, performance, complexity and cost. The database faces security threats such as hacker attacks and virus infections, which lead to data leakage, damage and loss. Audit products have missed audits. As the amount of data increases, the query and update operations of the database may become slow, affecting the system's response speed and user experience. The design, management and maintenance of the database system require professional knowledge and skills, and the hardware, software and human resource costs are high.

[0003] Patent document CN105359143B discloses a database diagnostic interface system. The above patent implements a system that can be used to define, monitor and deploy database diagnostic tools. The interface system presents a user interface for defining parameters, behaviors and scheduling of the database diagnostic tool to the user. The diagnostic tool is executed on the target database to record database parameters and status information. The interface system can present a graphical user interface for assembling the diagnostic tool at least partially from a set of predefined reusable modules and scripts to the user, but the above patent cannot realize the function of monitoring the size of data.

[0004] Patent document CN102354329B discloses an intelligent diagnosis and management system for infrared database of live equipment. The above patent realizes the ability to log in to a remote WEB browser, view infrared detection reports uploaded by the substation, understand the equipment operation status, view historical data, defect reports and historical temperature trend curves of the equipment, and use intelligent diagnosis and auxiliary decision-making systems to perform secondary diagnosis on equipment defects. The equipment of a certain manufacturer can be used as a condition for query to obtain all infrared thermal images of defective equipment. By horizontally comparing the quality of equipment from different manufacturers, a reference can be provided for future purchases. Substation personnel can handle defects in a timely manner and upload infrared detection reports, generate historical temperature trend curves, receive instructions from superiors, and make emergency responses in a timely manner. However, the above patent cannot realize the function of decentralized data processing.

[0005] Patent document CN113377746B discloses a test report database construction and intelligent diagnosis and analysis system. The above patent realizes the construction of a test report database with unified standards to provide sufficient data for subsequent test report intelligent data diagnosis and analysis and strategy formulation, making the intelligent diagnosis and analysis more accurate, but the above patent cannot realize the function of debugging the database.

[0006] Patent document CN103324656B discloses a database management method and a database management server thereof. The above patent realizes remote connection with a specific database system to provide various diagnostic items and provide quantitative analysis of the corresponding diagnostic results, so that the database system can be managed more deeply and conveniently. However, the above patent cannot realize the function of backing up and replacing damaged data.

[0007] In summary, the above patents cannot realize the function of monitoring the size of data, the function of decentralized data processing, the function of debugging the database, and the function of backing up and replacing damaged data, which leads to the problems of untimely discovery of hardware resource bottlenecks, unreasonable use of internal database resources, difficulty in discovering hidden hardware failures, reduced database response efficiency due to increased data volume, database system failures due to increased data volume, poor user experience, excessive backup data volume, backup failures, and untimely data backup when database failures occur, resulting in data loss and economic losses; To this end, the present application proposes an observation and diagnosis system for a database that can monitor the size of data, perform distributed data processing, debug the database, and back up and replace damaged data. Summary of the invention

[0008] The purpose of the present invention is to provide an observation and diagnosis system for a database to solve the technical problems proposed in the above-mentioned background technology that the function of monitoring the size of data cannot be realized, the function of decentralized data processing cannot be realized, the function of debugging the database cannot be realized, and the function of backing up and replacing damaged data cannot be realized, resulting in untimely discovery of hardware resource bottlenecks, unreasonable use of internal database resources, hidden hardware failures that are difficult to discover, increased data volume reduces database response efficiency, increased data volume causes database system failures, poor user experience, excessive backup data volume when failures occur during database use, backup failures, and untimely data backup when database failures occur, resulting in data loss and economic losses.

[0009] To achieve the above object, the present invention provides the following technical solutions: an observation and diagnosis system for a database, comprising a flow monitoring module, a flow dispersion module, a debugging module and an emergency module, wherein the flow dispersion module is connected to the flow monitoring module via signal transmission, the debugging module is connected to the flow monitoring module via baseband transmission, the emergency module is connected to the flow monitoring module via wireless transmission, and a monitoring tool, a log analysis tool and a network data packet analysis tool are provided inside the flow monitoring module; The monitoring tool is internally provided with a collection unit, a first storage unit, a first analysis unit and an alarm unit; The collection unit collects the network data flow of the database in a bypass form, parses the request statement and returns the information result; The first storage unit is responsible for storing the information collected by the collection unit; The first analysis unit analyzes the query performance, resource usage and transaction processing of the database by retrieving information from the storage unit, calculates the query execution time, network delay and processing time, compares the calculated data with the preset data, and diverts the data through the dispersion module when the data exceeds the preset value; The alarm unit generates alarm information based on the analysis result of the analysis unit, and sends an alarm to the administrator via email, SMS and system notification; The log analysis tool is internally provided with a collection unit, a filtering and cleaning unit, a second storage unit and a second analysis unit; The collection unit is installed in the server where the log is generated, reads and uploads the incremental part of the log file, and compresses and encrypts the log; The filtering and cleaning unit cleans and filters the log files, removes duplicate and irrelevant information, and formats the data in the same format; The second storage unit is responsible for storing the processed log files; The second analysis unit parses and processes the log file through Logstash, filters and converts the log data, and analyzes the extracted log data through kibana; The network data packet analysis tool is internally provided with a capture unit, a parsing unit and a display unit; The capture unit collects raw binary data from the network cable, the parsing unit decodes the raw data according to the network protocol, displays the value of each field in the data packet, standardizes the analysis content, converts the binary data into a readable form, and the display unit displays the content parsed by the parsing unit.

[0010] Preferably, the alarm unit is connected to a signal receiver in a flow dispersion module through signal transmission, and a signal receiver and a dispersion tool are provided inside the flow dispersion module; A data valve is provided inside the dispersion tool. The data valve receives information transmitted by the analysis unit in the flow detection component and compares the information with a preset value. When the value of the information transmitted by the analysis unit is lower than or equal to the preset value, the data valve does not operate. When the value of the information transmitted by the analysis unit is higher than the preset value, the data valve operates to disperse the data.

[0011] Preferably, the acquisition unit is connected to the index unit in the debugging module via baseband transmission, and the debugging module is internally provided with an index unit, a decomposition unit and a test unit; The index unit sorts and stores data through data structures, creates indexes for fields with high query frequency, does not use SELECT *, only queries required fields, uses EXPLAIN statements to analyze query execution plans, and understands index usage and the relationship between tables; A data volume measurement tool is set inside the decomposition unit to measure the volume of data passing through. When the volume of data exceeds a preset value, the appropriate data type and format are selected to decompose the data into smaller tables. The test unit performs performance testing on the database through MySQL Performance Schema, collects performance statistics of query execution time, lock waits, and I / O operations to analyze and evaluate the performance of the database.

[0012] Preferably, the alarm unit is connected to the emergency module via wireless transmission, and the emergency module includes a hardware processing component, a backup component and an isolation component; The hardware processing component is equipped with a power-off tool and a self-check tool. After the alarm unit transmits the information to the emergency module, the self-check tool starts to self-check the hardware facilities of the database and transmits the inspection data to the alarm unit. The power-off tool is controlled by the alarm unit to power off the hardware equipment of the database.

[0013] Preferably, the alarm unit is connected to the temperature sensor via wireless transmission. The temperature sensor is internally provided with a temperature sensitive element and a conversion element. The thermoelectric effect generated by the conductors of two different materials under the temperature difference. When the conductors of two different materials form a closed loop and the temperatures of the two contacts are different, an electromotive force will be generated in the loop. The magnitude of this electromotive force is proportional to the temperature difference between the two contacts. The greater the temperature difference, the greater the electromotive force generated. When the conductors of two different materials are in contact, a stable potential difference will be formed on the contact surface due to the different concentrations of free electrons. After the conversion element processes the potential difference, the data is transmitted to the alarm unit. The data is compared with a preset value. When the measured data is greater than the preset value, the alarm unit issues an alarm to the administrator.

[0014] Preferably, the alarm unit is connected to the humidity sensor via wireless transmission. The humidity sensor is internally provided with a humidity-sensitive capacitor and a conversion circuit. The humidity of the environment is measured based on the change of the humidity-sensitive capacitor. Water vapor enters the dielectric layer through the upper electrode of the humidity-sensitive capacitor. The dielectric coefficient of the dielectric layer changes after absorbing water vapor, causing the capacitance of the capacitor to change. The capacitance of the humidity-sensitive capacitor is proportional to the absorbed water vapor. The conversion circuit converts the change in capacitance into relative humidity and transmits the information to the alarm unit. The measured value is compared with a preset value. When the measured value is greater than the preset value, the alarm unit issues an alarm to the administrator.

[0015] Preferably, the alarm unit is connected to the pressure sensor via wireless transmission. The pressure sensor is internally provided with a single crystal silicon diaphragm, a diffused resistor and a bridge circuit. Based on the piezoresistive effect, when pressure acts on the single crystal silicon diaphragm, the lattice of the silicon crystal is deformed, causing the carrier mobility to change, thereby changing the resistivity of silicon. When the resistor strip diffused on the single crystal silicon diaphragm receives pressure, the resistance value changes. The bridge circuit converts the change in resistance value into a voltage signal output, and transmits the signal to the alarm unit. By comparing it with a preset value, when the measured value is greater than the preset value, the alarm unit issues an alarm to the administrator.

[0016] Preferably, the alarm unit is connected to the vibration sensor via wireless transmission. The vibration sensor is internally provided with a permanent magnet, a magnetic conductive shell, a working coil, a circular damper and an information processing element. Based on the law of electromagnetic induction, the vibrating object drives the working coil to move. When the working coil moves in a constant magnetic field, the working coil will cut the magnetic lines of force. According to Faraday's law of electromagnetic induction, an induced electromotive force will be generated in the coil. The induced electromotive force is proportional to the coil speed in the magnetic field. After being processed by the information processing element, the induced electromotive force is converted into the vibration speed and displacement. The data is transmitted to the alarm unit, and the data is compared with a preset value. When the data is greater than the preset value, the alarm unit issues an alarm to the manager.

[0017] Preferably, the backup component is connected to the alarm unit via wireless transmission, and a data backup tool, a storage tool and an information receiving unit are provided inside the backup component. After the information receiving unit receives the information transmitted by the alarm unit, the data backup tool is controlled to extract the backup data in the storage tool to replace the damaged data, and the damaged data is backed up at the same time.

[0018] Preferably, the isolation component is connected to the alarm unit via wireless transmission, and a log analysis tool is provided inside the isolation component to read the log records, identify modified and damaged data, and lock the damaged data with a lock mechanism to prevent the damaged data from being accessed and modified.

[0019] Compared with the prior art, the present invention has the following beneficial effects: 1. The present invention realizes the function of monitoring the data volume by designing a flow monitoring module, solves the problems of untimely discovery of hardware resource bottlenecks, unreasonable use of database internal resources and difficulty in discovering hidden hardware failures, can timely discover changes in data volume, avoid performance degradation caused by excessive data volume, improve the security index of the database, and improve resource utilization; 2. The present invention realizes the function of decentralized data processing by designing a traffic dispersion module, solves the problem that the increase in data volume reduces the database response efficiency, the increase in data volume causes database system failures and poor user experience, can reduce the impact of changes in data volume on the database system, reduce the burden on the database, improve the performance of the database, enhance the user experience, and reduce the risk of database failures; 3. The present invention realizes the function of debugging the database by designing a debugging module, solves the problems of excessive backup data volume and backup failure during the use of the database, and can avoid the impact of excessive database data volume on the database system, improve the performance and security of the database system, prevent SQL injection, reduce the compilation time of the database system, and improve the readability and maintainability of the program; 4. The present invention realizes the function of backing up and replacing damaged data by designing an emergency module, solves the problem of data loss and economic loss caused by untimely data backup when a database fails, can preserve data integrity to a great extent, reduce the impact of data loss, reduce the difficulty of data maintenance, and reduce economic costs. BRIEF DESCRIPTION OF THE DRAWINGS

[0020] Figure 1 It is a schematic diagram of the observation and diagnosis system of the present invention; Figure 2 It is a schematic diagram of a flow monitoring module of the present invention; Figure 3 It is a schematic diagram of the flow dispersion module of the present invention; Figure 4 It is a schematic diagram of the debugging module of the present invention; Figure 5 It is a schematic diagram of the emergency module of the present invention. DETAILED DESCRIPTION

[0021] The following will be combined with the drawings in the embodiments of the present invention to clearly and completely describe the technical solutions in the embodiments of the present invention. Obviously, the described embodiments are only 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 ordinary technicians in this field without creative work are within the scope of protection of the present invention. Example 1

[0022] See also Figure 1 , Figure 2 and Figure 3 , an observation and diagnosis system for a database, comprising a flow monitoring module, a flow dispersion module, a debugging module and an emergency module, wherein the flow dispersion module is connected to the flow monitoring module via signal transmission, the debugging module is connected to the flow monitoring module via baseband transmission, the emergency module is connected to the flow monitoring module via wireless transmission, and the flow monitoring module is internally provided with a monitoring tool, a log analysis tool and a network data packet analysis tool; The flow detection component is internally provided with a monitoring tool, a log analysis tool and a network data packet analysis tool; The monitoring tool is internally provided with a collection unit, a first storage unit, a first analysis unit and an alarm unit; The collection unit collects the network data flow of the database in a bypass form, parses the request statement and returns the information result; The first storage unit is responsible for storing the information collected by the collection unit; the first analysis unit retrieves the information in the storage unit, analyzes the query performance, resource usage and transaction processing of the database, calculates the query execution time, network delay and processing time, compares the calculated data with the preset data, and diverts the data through the dispersion module when the data exceeds the preset value; The alarm unit generates alarm information based on the analysis result of the analysis unit, and sends an alarm to the administrator via email, SMS and system notification; The log analysis tool is internally provided with a collection unit, a filtering and cleaning unit, a second storage unit and a second analysis unit; The collection unit is installed in the server where the log is generated, reads and uploads the incremental part of the log file, and compresses and encrypts the log; The filtering and cleaning unit cleans and filters the log files, removes duplicate and irrelevant information, and formats the data in the same format; The second storage unit is responsible for storing the processed log files; The second analysis unit parses and processes the log file through Logstash, filters and converts the log data, and analyzes the extracted log data through kibana; The network data packet analysis tool is internally provided with a capture unit, a parsing unit and a display unit; The capture unit collects raw binary data from the network cable, the parsing unit decodes the raw data according to the network protocol, displays the value of each field in the data packet, standardizes the analysis content, converts the binary data into a readable form, and the display unit displays the content parsed by the parsing unit; The alarm unit is connected to a signal receiver in a flow dispersion module through signal transmission, and a signal receiver and a dispersion tool are arranged inside the flow dispersion module; A data valve is provided inside the dispersion tool. The data valve receives information transmitted by the analysis unit in the flow detection component and compares the information with a preset value. When the value of the information transmitted by the analysis unit is lower than or equal to the preset value, the data valve does not operate. When the value of the information transmitted by the analysis unit is higher than the preset value, the data valve operates to disperse the data. Furthermore, when data enters the database system, the flow detection component detects the size of the data volume, the collection unit collects the network data flow in the database in a bypass form, receives the information request statement and returns the result, the first storage unit stores the information collected by the collection unit, the first analysis unit calls the information in the first storage unit, analyzes the query performance, resource usage and transaction processing of the database through SQL statements and statistical analysis, calculates the query execution time, network delay and processing time, and compares the calculated data with the preset value. When the data exceeds the preset value, the dispersion module diverts the data through the data valve. After the signal receiver receives the data processed by the first analysis unit transmitted by the alarm unit, the data valve operates to disperse the data into different groups for subsequent transmission. The flow monitoring module realizes the decentralized processing of the data, avoids the excessive amount of data reducing the response rate of the database, improves the user experience, avoids the large amount of data causing a heavy burden on the database, and extends the service life of the database. Example 2

[0023] See also Figure 1 , Figure 2 and Figure 4, an observation and diagnosis system for a database, comprising a flow monitoring module, a flow dispersion module, a debugging module and an emergency module, wherein the flow dispersion module is connected to the flow monitoring module via signal transmission, the debugging module is connected to the flow monitoring module via baseband transmission, the emergency module is connected to the flow monitoring module via wireless transmission, and the flow monitoring module is internally provided with a monitoring tool, a log analysis tool and a network data packet analysis tool; The flow detection component is internally provided with a monitoring tool, a log analysis tool and a network data packet analysis tool; The monitoring tool is internally provided with a collection unit, a first storage unit, a first analysis unit and an alarm unit; The collection unit collects the network data flow of the database in a bypass form, parses the request statement and returns the information result; The first storage unit is responsible for storing the information collected by the collection unit; the first analysis unit retrieves the information in the storage unit, analyzes the query performance, resource usage and transaction processing of the database, calculates the query execution time, network delay and processing time, compares the calculated data with the preset data, and diverts the data through the dispersion module when the data exceeds the preset value; The alarm unit generates alarm information based on the analysis result of the analysis unit, and sends an alarm to the administrator via email, SMS and system notification; The log analysis tool is internally provided with a collection unit, a filtering and cleaning unit, a second storage unit and a second analysis unit; The collection unit is installed in the server where the log is generated, reads and uploads the incremental part of the log file, and compresses and encrypts the log; The filtering and cleaning unit cleans and filters the log files, removes duplicate and irrelevant information, and formats the data in the same format; The second storage unit is responsible for storing the processed log files; The second analysis unit parses and processes the log file through Logstash, filters and converts the log data, and analyzes the extracted log data through kibana; The network data packet analysis tool is internally provided with a capture unit, a parsing unit and a display unit; The capture unit collects raw binary data from the network cable, the parsing unit decodes the raw data according to the network protocol, displays the value of each field in the data packet, standardizes the analysis content, converts the binary data into a readable form, and the display unit displays the content parsed by the parsing unit; The acquisition unit is connected to the index unit in the debugging module through baseband transmission, and the debugging module is internally provided with an index unit, a decomposition unit and a test unit; The index unit sorts and stores data through data structures, creates indexes for fields with high query frequency, does not use SELECT *, only queries required fields, uses EXPLAIN statements to analyze query execution plans, and understands index usage and the relationship between tables; A data volume measurement tool is set inside the decomposition unit to measure the volume of data passing through. When the volume of data exceeds a preset value, the appropriate data type and format are selected to decompose the data into smaller tables. The test unit performs performance testing on the database through MySQL Performance Schema, collects performance statistics of query execution time, lock waits, and I / O operations to analyze and evaluate database performance; Furthermore, the index unit is connected to the collection unit through signal transmission. The index unit sorts and stores data through data structures, creates indexes for fields with high query frequency, does not SELECT * together, only queries required fields, uses EXPLAIN statements to analyze query execution plans, understands index usage and the relationship between tables, and measures the amount of data passing through the decomposition unit through the internally set data volume measurement tool. The test unit uses MySQLPerformance The Schema performs performance testing on the database, collects performance statistics such as query execution time, lock waiting status and I / O operations, to analyze and evaluate the performance of the database. The log analysis tool reads and uploads the incremental part of the log file through the collection unit, compresses and encrypts the log, and the filtering and cleaning unit cleans and filters the log file to remove duplicate and irrelevant information, stores the data in the second storage unit in the same format, and the second analysis unit retrieves the log file in the second storage unit, parses and processes the log file through Logstash, filters and converts the log data, and analyzes the extracted log data through Kibana. The network data packet analysis tool collects the original binary data from the network cable through the capture unit, and the parsing unit decodes the original data according to the network protocol, displays the value of each field in the data packet, standardizes the analysis content, converts the binary data into a readable form, and displays it through the display unit, realizing the function of decentralized data processing, solving the problem that the increase in data volume reduces the database response efficiency, the increase in data volume causes database system failures and poor user experience, can reduce the impact of changes in data volume on the database system, reduce the burden on the database, improve the performance of the database, improve the user experience, and reduce the risk of database failure. Example 3

[0024] See also Figure 1 , Figure 3 and Figure 4 , an observation and diagnosis system for a database, wherein the alarm unit is connected to a signal receiver in a flow dispersion module through signal transmission, and a signal receiver and a dispersion tool are provided inside the flow dispersion module; A data valve is provided inside the dispersion tool. The data valve receives information transmitted by the analysis unit in the flow detection component and compares the information with a preset value. When the value of the information transmitted by the analysis unit is lower than or equal to the preset value, the data valve does not operate. When the value of the information transmitted by the analysis unit is higher than the preset value, the data valve operates to disperse the data. The acquisition unit is connected to the index unit in the debugging module through baseband transmission, and the debugging module is internally provided with an index unit, a decomposition unit and a test unit; The index unit sorts and stores data through data structures, creates indexes for fields with high query frequency, does not use SELECT *, only queries required fields, uses EXPLAIN statements to analyze query execution plans, and understands index usage and the relationship between tables; A data volume measurement tool is set inside the decomposition unit to measure the volume of data passing through. When the volume of data exceeds a preset value, the appropriate data type and format are selected to decompose the data into smaller tables. The test unit performs performance testing on the database through MySQL Performance Schema, collects performance statistics of query execution time, lock waits, and I / O operations to analyze and evaluate database performance; Furthermore, the debugging module is connected to the signal receiver in the traffic dispersion module through baseband transmission. After the database is debugged by the debugging module, the situations and countermeasures that will occur when the data volume is different are transmitted to the signal receiver. After the signal receiver receives the information transmitted from the alarm unit, it compares it with the stored information in the signal receiver as soon as possible to select the best data dispersion method to avoid wasting resources due to too many data dispersion groups and failing to achieve the data dispersion effect due to too few data dispersion groups, thereby achieving good resource utilization, reducing the occurrence of database congestion and reduced query rate, improving user experience, increasing the usage time of the database, and accelerating the rate at which the database processes data. Example 4

[0025] See also Figure 1 , Figure 2 and Figure 5, an observation and diagnosis system for a database, comprising a flow monitoring module, a flow dispersion module, a debugging module and an emergency module, wherein the flow dispersion module is connected to the flow monitoring module via signal transmission, the debugging module is connected to the flow monitoring module via baseband transmission, the emergency module is connected to the flow monitoring module via wireless transmission, and the flow monitoring module is internally provided with a monitoring tool, a log analysis tool and a network data packet analysis tool; The monitoring tool is internally provided with a collection unit, a first storage unit, a first analysis unit and an alarm unit; The collection unit collects the network data flow of the database in a bypass form, parses the request statement and returns the information result; The first storage unit is responsible for storing the information collected by the collection unit; The first analysis unit analyzes the query performance, resource usage and transaction processing of the database by retrieving information from the storage unit, calculates the query execution time, network delay and processing time, compares the calculated data with the preset data, and diverts the data through the dispersion module when the data exceeds the preset value; The alarm unit generates alarm information based on the analysis result of the analysis unit, and sends an alarm to the administrator via email, SMS and system notification; The log analysis tool is internally provided with a collection unit, a filtering and cleaning unit, a second storage unit and a second analysis unit; The collection unit is installed in the server where the log is generated, reads and uploads the incremental part of the log file, and compresses and encrypts the log; The filtering and cleaning unit cleans and filters the log files, removes duplicate and irrelevant information, and formats the data in the same format; The second storage unit is responsible for storing the processed log files; The second analysis unit parses and processes the log file through Logstash, filters and converts the log data, and analyzes the extracted log data through kibana; The network data packet analysis tool is internally provided with a capture unit, a parsing unit and a display unit; The capture unit collects raw binary data from the network cable, the parsing unit decodes the raw data according to the network protocol, displays the value of each field in the data packet, standardizes the analysis content, converts the binary data into a readable form, and the display unit displays the content parsed by the parsing unit; The alarm unit is connected to the emergency module via wireless transmission, and the emergency module includes a hardware processing component, a backup component and an isolation component; The hardware processing component is internally provided with a power-off tool and a self-checking tool. After the alarm unit transmits the information to the emergency module, the self-checking tool starts to self-check the hardware facilities of the database and transmits the inspection data to the alarm unit. The power-off tool is controlled by the alarm unit to power off the hardware equipment of the database. The backup component is internally provided with a data backup tool, a storage tool and an information receiving unit. After receiving the information transmitted by the alarm unit, the information receiving unit controls the data backup tool to extract the backup data in the storage tool to replace the damaged data, and performs backup processing on the damaged data at the same time; The isolation component is internally provided with a log analysis tool to read the log records, identify the modified and damaged data, and lock the damaged data with a lock mechanism so that the damaged data cannot be accessed and modified; Furthermore, when data corruption occurs in the database, the alarm unit sends an alarm to the administrator, and the alarm unit transmits the information to the emergency module. The hardware processing component powers off the hardware settings of the database, and the isolation component reads the log records through the log analysis tool to identify and store the damaged data, and locks the damaged data through a lock mechanism to prevent other operations from accessing and modifying the damaged data. After receiving the information conveyed by the alarm signal, the backup component backs up the damaged files and replaces the intact files in the original backup, realizing the function of backing up and replacing damaged files, solving the problem of data loss and economic loss caused by untimely data backup when a database fails, and can preserve the integrity of the data to a great extent, reduce the impact of data loss, reduce the difficulty of data maintenance, and reduce economic costs. Example 5

[0026] See also Figure 1 , an observation and diagnosis system for a database, comprising a flow monitoring module, a flow dispersion module, a debugging module and an emergency module, wherein the flow dispersion module is connected to the flow monitoring module via signal transmission, the debugging module is connected to the flow monitoring module via baseband transmission, the emergency module is connected to the flow monitoring module via wireless transmission, and the flow monitoring module is internally provided with a monitoring tool, a log analysis tool and a network data packet analysis tool; The monitoring tool is internally provided with a collection unit, a first storage unit, a first analysis unit and an alarm unit; The collection unit collects the network data flow of the database in a bypass form, parses the request statement and returns the information result; The first storage unit is responsible for storing the information collected by the collection unit; The first analysis unit analyzes the query performance, resource usage and transaction processing of the database by retrieving information from the storage unit, calculates the query execution time, network delay and processing time, compares the calculated data with the preset data, and diverts the data through the dispersion module when the data exceeds the preset value; The alarm unit generates alarm information based on the analysis result of the analysis unit, and sends an alarm to the administrator via email, SMS and system notification; The log analysis tool is internally provided with a collection unit, a filtering and cleaning unit, a second storage unit and a second analysis unit; The collection unit is installed in the server where the log is generated, reads and uploads the incremental part of the log file, and compresses and encrypts the log; The filtering and cleaning unit cleans and filters the log files, removes duplicate and irrelevant information, and formats the data in the same format; The second storage unit is responsible for storing the processed log files; The second analysis unit parses and processes the log file through Logstash, filters and converts the log data, and analyzes the extracted log data through kibana; The network data packet analysis tool is internally provided with a capture unit, a parsing unit and a display unit; The capture unit collects raw binary data from the network cable, the parsing unit decodes the raw data according to the network protocol, displays the value of each field in the data packet, standardizes the analysis content, converts the binary data into a readable form, and the display unit displays the content parsed by the parsing unit; The alarm unit is connected to the temperature sensor via wireless transmission. The temperature sensor is internally provided with a temperature-sensitive element and a conversion element. The thermoelectric effect generated by the conductors of two different materials under the temperature difference. When the conductors of two different materials form a closed loop and the temperatures of the two contacts are different, an electromotive force will be generated in the loop. The magnitude of this electromotive force is proportional to the temperature difference of the two contacts. The greater the temperature difference, the greater the electromotive force generated. When the conductors of two different materials are in contact, a stable potential difference will be formed on the contact surface due to the different free electron concentrations. After the conversion element processes the potential difference, the data is transmitted to the alarm unit. The data is compared with the preset value. When the measured data is greater than the preset value, the alarm unit issues an alarm to the administrator. The alarm unit is connected to the humidity sensor via wireless transmission. A humidity-sensitive capacitor and a conversion circuit are provided inside the humidity sensor. The humidity of the environment is measured based on the change of the humidity-sensitive capacitor. Water vapor enters the dielectric layer through the upper electrode of the humidity-sensitive capacitor. After the dielectric layer absorbs water vapor, the dielectric coefficient changes, causing the capacitance of the capacitor to change. The capacitance of the humidity-sensitive capacitor is proportional to the absorbed water vapor. The conversion circuit converts the change in capacitance into relative humidity and transmits the information to the alarm unit. The measured value is compared with the preset value. When the measured value is greater than the preset value, the alarm unit issues an alarm to the administrator. The alarm unit is connected to the pressure sensor via wireless transmission. The pressure sensor is internally provided with a single crystal silicon diaphragm, a diffused resistor and a bridge circuit. Based on the piezoresistive effect, when pressure acts on the single crystal silicon diaphragm, the lattice of the silicon crystal is deformed, so that the carrier mobility changes, thereby changing the resistivity of silicon. After the diffused resistor strip on the single crystal silicon diaphragm receives pressure, the resistance value changes. The bridge circuit converts the change in resistance value into a voltage signal output, and transmits the signal to the alarm unit. By comparing it with a preset value, when the measured value is greater than the preset value, the alarm unit issues an alarm to the administrator; The alarm unit is connected to the vibration sensor through wireless transmission. The vibration sensor is provided with a permanent magnet, a magnetic shell, a working coil, a circular damper and an information processing element. Based on the law of electromagnetic induction, the vibrating object drives the working coil to move. When the working coil moves in a constant magnetic field, the working coil will cut the magnetic lines of force. According to Faraday's law of electromagnetic induction, an induced electromotive force will be generated in the coil. The induced electromotive force is proportional to the speed of the coil in the magnetic field. After being processed by the information processing element, the induced electromotive force is converted into the speed and displacement of the vibration, and the data is transmitted to the alarm unit. The data is compared with a preset value. When the data is greater than the preset value, the alarm unit issues an alarm to the manager. Furthermore, the temperature sensor generates electromotive force at different temperatures through the temperature sensitive element, and the processing element processes the generated electromotive force. The humidity sensor generates capacitance changes at different humidity through the humidity sensitive capacitor, and the conversion circuit converts the capacitance change into relative humidity change. The pressure sensor changes the resistivity by applying pressure on the single crystal silicon diaphragm, and the bridge circuit converts the resistivity change into the applied pressure. The vibration sensor drives the working coil to cut the magnetic lines of force through the vibrating object to generate an induced electromotive force. The information processing element converts the induced electromotive force into the vibration speed and displacement of the vibrating object after processing, and transmits the detected temperature, humidity, pressure and vibration changes to the alarm unit. After comparison with the preset values ​​of each value, when an abnormal situation occurs, the alarm unit issues an alarm to the administrator, realizing the function of real-time monitoring of the external environmental conditions of the database operation, solving the problem of damage to the database hardware facilities and reduction of database performance caused by external environmental changes, avoiding damage to the database hardware facilities caused by the external environment, reducing the cost of maintaining hardware facilities, extending the service life of the hardware facilities, and improving the economic benefits brought by the database. Example 6

[0027] An observation and diagnosis system for a database comprises a pre-shrinking buffer module, wherein a monitoring tool and a compression tool are arranged inside the pre-shrinking buffer module, wherein a monitoring unit is arranged inside the monitoring tool, wherein a collection unit, a processing unit and a connection unit are arranged inside the monitoring unit, wherein the collection unit receives a command of a concentrator by communicating with the concentrator and transmits recorded data and status to the concentrator, wherein the processing unit processes the data collected by the collection unit in real time, and the connection unit transmits the data to the compression tool; The compression tool is internally provided with data layer compression and application layer compression; The data layer compression compresses data at the database storage layer, models the data stream, and encodes it into a more compact form, thereby reducing data redundancy and storage space; The application layer compression reduces the overall length by finding repeated patterns and redundant parts in the data and replacing them with shorter symbols and strings; Furthermore, the monitoring tool monitors the amount of data that needs to be processed, receives commands from the concentrator through the collection unit, transmits recorded data and status to the concentrator, the processing unit processes the data collected by the collection unit in real time, and the connection unit transmits the data to the compression tool. The data layer compression models the data stream and encodes it into a more compact form to reduce data redundancy and storage space. The application layer compression finds the re-reading pattern in the data and uses the Deflate compression algorithm to find repeated character strings and replace them with shorter symbols to shorten the overall length, thereby realizing the function of changing the data size. It solves the problem of excessive data volume reducing the database response rate and user experience, can reduce the possibility of data loss inside the database, improve the database response rate, improve the user experience when querying, reduce the administrator's attention to the database status, and reduce the administrator's work pressure.

[0028] Working principle: The index unit is connected to the collection unit through signal transmission. The index unit sorts and stores data through data structure, creates indexes for fields with high query frequency, does not use SELECT* together, only queries required fields, uses EXPLAIN statement to analyze query execution plan, understands index usage and the relationship between tables, and the decomposition unit measures the amount of data passing through through the internal data volume measurement tool. The test unit performs performance testing on the database through MySQL Performance Schema, collects performance statistics such as query execution time, lock waiting and I / O operation, to analyze and evaluate the performance of the database; The flow detection component detects the size of the data volume. The collection unit collects the network data flow in the database in a bypass form, receives the information request statement and returns the result. The first storage unit stores the information collected by the collection unit. The first analysis unit calls the information in the first storage unit, analyzes the query performance, resource usage and transaction processing of the database through SQL statements and statistical analysis, calculates the query execution time, network delay and processing time, and compares the calculated data with the preset value. The log analysis tool reads and uploads the incremental part of the log file through the collection unit, compresses and encrypts the log, and the filtering and cleaning unit cleans and filters the log file, removes duplicate and irrelevant information, and stores the data in the same format in the second storage unit. The second analysis unit calls the log file in the second storage unit, parses and processes the log file through Logstash, filters and converts the log data, and analyzes the extracted log data through Kibana. The network data packet analysis tool collects the original binary data from the network cable through the capture unit. The parsing unit decodes the original data according to the network protocol, displays the value of each field in the data packet, standardizes the analysis content, converts the binary data into a readable form, and displays it through the display unit. When the data exceeds the preset value, the dispersion module performs data diversion processing through the data valve. After the signal receiver receives the data processed by the first analysis unit transmitted by the alarm unit, the data valve operates to disperse the data into different groups for subsequent transmission; When data corruption occurs in the database, the alarm unit sends an alarm to the administrator and transmits the information to the emergency module. The hardware processing component powers off the hardware settings of the database. The isolation component reads the log records through the log analysis tool, identifies and stores the corrupted data, and locks the corrupted data through the lock mechanism to prevent other operations from accessing and modifying the corrupted data. After receiving the information conveyed by the alarm signal, the backup component backs up the damaged files and replaces the intact files in the original backup.

[0029] It will be apparent to those skilled in the art that the invention is not limited to the details of the exemplary embodiments described above and that the invention can be implemented in other specific forms without departing from the spirit or essential features of the invention. Therefore, the embodiments should be considered exemplary and non-limiting in all respects, and the scope of the invention is defined by the appended claims rather than the foregoing description, and it is intended that all variations falling within the meaning and scope of the equivalent elements of the claims be included in the invention. Any reference numeral in a claim should not be considered as limiting the claim to which it relates.

Claims

1. An observation and diagnosis system for a database, characterized in that: It includes a flow monitoring module, a flow dispersion module, a debugging module and an emergency module. The flow dispersion module is connected to the flow monitoring module through signal transmission, the debugging module is connected to the flow monitoring module through baseband transmission, and the emergency module is connected to the flow monitoring module through wireless transmission. The flow monitoring module is internally provided with a monitoring tool, a log analysis tool and a network data packet analysis tool; The monitoring tool is internally provided with a collection unit, a first storage unit, a first analysis unit and an alarm unit; The collection unit collects the network data flow of the database in a bypass form, parses the request statement and returns the information result; The first storage unit is responsible for storing the information collected by the collection unit; The first analysis unit analyzes the query performance, resource usage and transaction processing of the database by retrieving information from the storage unit, calculates the query execution time, network delay and processing time, compares the calculated data with the preset data, and diverts the data through the dispersion module when the data exceeds the preset value; The alarm unit generates alarm information based on the analysis result of the analysis unit, and sends an alarm to the administrator via email, SMS and system notification; The log analysis tool is internally provided with a collection unit, a filtering and cleaning unit, a second storage unit and a second analysis unit; The collection unit is installed in the server where the log is generated, reads and uploads the incremental part of the log file, and compresses and encrypts the log; The filtering and cleaning unit cleans and filters the log files, removes duplicate and irrelevant information, and formats the data in the same format; The second storage unit is responsible for storing the processed log files; The second analysis unit parses and processes the log file through Logstash, filters and converts the log data, and analyzes the extracted log data through kibana; The network data packet analysis tool is internally provided with a capture unit, a parsing unit and a display unit; The capture unit collects raw binary data from the network cable, the parsing unit decodes the raw data according to the network protocol, displays the value of each field in the data packet, standardizes the analysis content, converts the binary data into a readable form, and the display unit displays the content parsed by the parsing unit.

2. The observation and diagnosis system for a database according to claim 1, characterized in that: The alarm unit is connected to a signal receiver in a flow dispersion module through signal transmission, and a signal receiver and a dispersion tool are arranged inside the flow dispersion module; A data valve is provided inside the dispersion tool. The data valve receives information transmitted by the analysis unit in the flow detection component and compares the information with a preset value. When the value of the information transmitted by the analysis unit is lower than or equal to the preset value, the data valve does not operate. When the value of the information transmitted by the analysis unit is higher than the preset value, the data valve operates to disperse the data.

3. The observation and diagnosis system for a database according to claim 1, characterized in that: The acquisition unit is connected to the index unit in the debugging module through baseband transmission, and the debugging module is internally provided with an index unit, a decomposition unit and a test unit; The index unit sorts and stores data through data structures, creates indexes for fields with high query frequency, does not use SELECT *, only queries required fields, uses EXPLAIN statements to analyze query execution plans, and understands index usage and the relationship between tables; A data volume measurement tool is set inside the decomposition unit to measure the volume of data passing through. When the volume of data exceeds a preset value, the appropriate data type and format are selected to decompose the data into smaller tables. The test unit performs performance testing on the database through MySQL Performance Schema, collects performance statistics of query execution time, lock waits, and I / O operations to analyze and evaluate the performance of the database.

4. The observation and diagnosis system for a database according to claim 1, characterized in that: The alarm unit is connected to the emergency module via wireless transmission, and the emergency module includes a hardware processing component, a backup component and an isolation component; The hardware processing component is equipped with a power-off tool and a self-check tool. After the alarm unit transmits the information to the emergency module, the self-check tool starts to self-check the hardware facilities of the database and transmits the inspection data to the alarm unit. The power-off tool is controlled by the alarm unit to power off the hardware equipment of the database.

5. The observation and diagnosis system for a database according to claim 1, characterized in that: The alarm unit is connected to the temperature sensor through wireless transmission. The temperature sensor is provided with a temperature sensitive element and a conversion element inside. The thermoelectric effect generated by the conductors of two different materials under the temperature difference. When the conductors of two different materials form a closed loop and the temperatures of the two contacts are different, an electromotive force will be generated in the loop. The magnitude of this electromotive force is proportional to the temperature difference between the two contacts. The greater the temperature difference, the greater the electromotive force generated. When the conductors of two different materials are in contact, a stable potential difference will be formed on the contact surface due to the different free electron concentrations. After the conversion element processes the potential difference, the data is transmitted to the alarm unit. The data is compared with the preset value. When the measured data is greater than the preset value, the alarm unit issues an alarm to the administrator.

6. The observation and diagnosis system for a database according to claim 1, characterized in that: The alarm unit is connected to the humidity sensor via wireless transmission. A humidity-sensitive capacitor and a conversion circuit are provided inside the humidity sensor. The humidity of the environment is measured based on the change of the humidity-sensitive capacitor. Water vapor enters the dielectric layer through the upper electrode of the humidity-sensitive capacitor. The dielectric coefficient of the dielectric layer changes after absorbing the water vapor, causing the capacitance of the capacitor to change. The capacitance of the humidity-sensitive capacitor is proportional to the absorbed water vapor. The conversion circuit converts the change in capacitance into relative humidity and transmits the information to the alarm unit. The measured value is compared with the preset value. When the measured value is greater than the preset value, the alarm unit issues an alarm to the administrator.

7. The observation and diagnosis system for a database according to claim 1, characterized in that: The alarm unit is connected to the pressure sensor via wireless transmission. A single crystal silicon diaphragm, a diffused resistor and a bridge circuit are arranged inside the pressure sensor. Based on the piezoresistive effect, when pressure acts on the single crystal silicon diaphragm, the lattice of the silicon crystal is deformed, causing the carrier mobility to change, thereby changing the resistivity of silicon. When the resistor strip diffused on the single crystal silicon diaphragm receives pressure, the resistance value changes. The bridge circuit converts the change in resistance value into a voltage signal output, and transmits the signal to the alarm unit. By comparing it with a preset value, when the measured value is greater than the preset value, the alarm unit issues an alarm to the administrator.

8. The observation and diagnosis system for a database according to claim 1, characterized in that: The alarm unit is connected to the vibration sensor through wireless transmission. The vibration sensor is provided with a permanent magnet, a magnetic shell, a working coil, a circular damper and an information processing element. Based on the law of electromagnetic induction, the vibrating object drives the working coil to move. When the working coil moves in a constant magnetic field, the working coil will cut the magnetic lines of force. According to Faraday's law of electromagnetic induction, an induced electromotive force will be generated in the coil. The induced electromotive force is proportional to the coil speed in the magnetic field. After being processed by the information processing element, the induced electromotive force is converted into the vibration speed and displacement. The data is transmitted to the alarm unit and compared with the preset value. When the data is greater than the preset value, the alarm unit will issue an alarm to the manager.

9. The observation and diagnosis system for a database according to claim 4, characterized in that: The backup component is connected to the alarm unit via wireless transmission. A data backup tool, a storage tool and an information receiving unit are provided inside the backup component. After the information receiving unit receives the information transmitted by the alarm unit, the data backup tool is controlled to extract the backup data in the storage tool to replace the damaged data, and the damaged data is backed up at the same time.

10. The observation and diagnosis system for database according to claim 4, characterized in that: The isolation component is connected to the alarm unit via wireless transmission. The isolation component is internally provided with a log analysis tool to read the log records, identify modified and damaged data, and lock the damaged data with a lock mechanism to prevent the damaged data from being accessed and modified.

Images