CN114817245A - Database log storage method and database log analysis method and device - Google Patents
Database log storage method and database log analysis method and device Download PDFInfo
- Publication number
- CN114817245A CN114817245A CN202210343130.XA CN202210343130A CN114817245A CN 114817245 A CN114817245 A CN 114817245A CN 202210343130 A CN202210343130 A CN 202210343130A CN 114817245 A CN114817245 A CN 114817245A
- Authority
- CN
- China
- Prior art keywords
- log
- database
- write
- information
- analysis
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/242—Query formulation
- G06F16/2433—Query languages
Abstract
The embodiment of the specification provides a storage method of a database log, and an analysis method and device of the database log, wherein the method comprises the following steps: acquiring instant log information of the database; and executing a first writing operation to write the instant log information into a system log table pre-established in the database.
Description
Technical Field
One or more embodiments of the present disclosure relate to the field of databases, and in particular, to a storage method of a database log, and an analysis method and apparatus of the database log.
Background
The database system is an information infrastructure of many industries, such as finance, commerce, transportation and other industries, and the performance of the database system is a guarantee that the industries can effectively provide normal services for users. Database systems typically generate a large number of database logs that are used to record events and operations, for example, that occur during the operation of the database system. Based on these logs, the status of the database operation can be analyzed. Currently, the solution for storing and analyzing the database log usually consumes more computing resources, which may result in the performance degradation of the database system.
Therefore, in order to reduce the resource overhead of storing and analyzing the database log, a new method for storing the database log and a corresponding method for analyzing the database log are needed.
Disclosure of Invention
Embodiments in this specification aim to provide a new database log storage method, a new database log analysis method, and a new database log analysis device, by which a database log generated during database system operation can be stored in a log data table pre-built in a database itself. The database log may then be queried and analyzed by directly accessing the log data table. Therefore, the method can reduce a large amount of external file access consumption and file analysis consumption caused by storing the database logs into the disk storage file, reading, analyzing and loading the database logs into the external log analysis system from the disk file in the process of storing and analyzing the database logs, and solves the defects in the prior art.
According to a first aspect, there is provided a method for storing a database log, including:
acquiring instant log information of the database;
and executing a first writing operation to write the instant log information into a system log table pre-established in the database.
In one possible embodiment, the method further comprises:
and if the first write-in operation fails to be executed, executing a second write-in operation to write the instant log information into a log file in a text form.
In one possible embodiment, the method further comprises:
reading the log text stored in the log file within a preset time range;
and executing a third write operation to analyze and write the log text into the system log table.
In one possible embodiment, the method further comprises: and deleting the log file when the third write operation is successfully executed.
In a possible embodiment, the pre-established system log table is established when the database is initialized for the first time.
In one possible embodiment, the fields of the system log table include: one or more of log generation time, database thread number, TraceID, database module name, log-related code location, log message, and detailed information.
In one possible embodiment, the system log table is a SSTable.
In a second aspect, a method for analyzing a database log is provided, including:
reading log information from the system log table of the first aspect;
and performing log analysis of a preset type according to the log information.
In a possible implementation, the performing, according to the log information, a predetermined type of log analysis includes:
and performing log analysis of a preset type according to the log information through a preset SQL statement set.
In a second aspect, there is provided a storage device for database logs, comprising:
the log information acquisition unit is configured to acquire instant log information of the database;
and the log saving unit is configured to execute a first writing operation and write the immediate log information into a system log table which is pre-established in the database.
In a possible implementation, the log saving unit is further configured to:
and if the first write-in operation fails to be executed, executing a second write-in operation to write the instant log information into a log file in a text form.
In a possible implementation, the log saving unit is further configured to:
reading the log text stored in the log file within a preset time range;
and executing a third write operation to analyze and write the log text into the system log table.
In a possible implementation, the log saving unit is further configured to: and deleting the log file when the third write operation is successfully executed.
In one possible embodiment, the pre-established system log table is established when the database is initialized for the first time.
In one possible embodiment, the fields of the system log table include: one or more of log generation time, database thread number, TraceID, database module name, log-related code location, log message, and detailed information.
In one possible embodiment, the system log table is a SSTable.
In a fourth aspect, an apparatus for analyzing a database log is provided, including:
a log extracting unit configured to read log information from the system log table recited in claim 1;
and the log analysis unit is configured to perform predetermined types of log analysis according to the log information.
In a possible embodiment, the log analysis unit is further configured to,
and performing log analysis of a preset type according to the log information through a preset SQL statement set.
According to a fifth aspect, there is provided a computer readable storage medium having stored thereon a computer program which, when executed in a computer, causes the computer to perform the method of the first or second aspect.
According to a sixth aspect, there is provided a computing device comprising a memory having stored therein executable code and a processor that, when executing the executable code, implements the method of the first or second aspect.
By using one or more of the methods, apparatuses, computing devices, and storage media in the above aspects, consumption of external file access and/or consumption of external file parsing generated in the process of database log saving or analysis may be greatly reduced.
Drawings
In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the description of the embodiments will be briefly introduced below, and it is obvious that the drawings in the following description are only some embodiments of the present invention, and it is obvious for those skilled in the art that other drawings can be obtained according to the drawings without creative efforts.
FIG. 1 is a schematic diagram illustrating a conventional database log storage method;
FIG. 2 is a schematic diagram illustrating a method for storing database logs according to an embodiment of the present disclosure;
FIG. 3 is a flow chart illustrating a method of storing database logs according to an embodiment of the present description;
FIG. 4 illustrates a flow diagram of a method of analyzing database logs, in accordance with an embodiment of the present description;
FIG. 5 illustrates a block diagram of a storage device for database logs, according to an embodiment of the present description;
fig. 6 is a block diagram illustrating an analysis apparatus of a database log according to an embodiment of the present specification.
Detailed Description
The solution provided by the present specification will be described below with reference to the accompanying drawings.
As mentioned above, the database system is currently an information infrastructure for many industries, such as finance, commerce, transportation, etc., and the performance of the database system is a guarantee that these industries can effectively provide normal services to users. Database systems today typically generate a large number of database logs that are used to record events and operations, for example, that occur during the operation of the database system. These database logs are usually stored in log files in a storage medium (e.g., a disk), so that various types of log analysis, such as query statement execution status analysis, database operation error or warning analysis, system operation status analysis, etc., can be performed later on the basis of the database logs.
Fig. 1 is a schematic diagram illustrating a conventional storage method of a database log. As shown in fig. 1, it is an existing technical solution for storing and analyzing a database log. Generally, log information of various types generated by various database components is written into a log file in a text form and stored in a storage medium (e.g., a magnetic disk). In different embodiments, different specific mechanisms may be used to write the log into the log text file. For example, in one embodiment, the log information may be first placed in a buffer in the memory, and then written into the text file when the buffer is filled with the log information. And then, when log analysis is desired, the text files can be read through an external log collection component, the text files are analyzed, log information is acquired and then is imported into an external log analysis system, and then various types of log analysis are performed based on the log analysis system.
The problem with this solution is that: on the one hand, the logs are mainly stored through text files, but the text files are generally large in volume, and particularly when the log data volume is large, the storage cost is high. On the other hand, the log is mainly analyzed by an external log analysis system, log information is read and analyzed from the text file by depending on an external log collection component and is imported into the log analysis system, the log import and analysis overhead exists in the process, and the system maintenance cost is increased by the existence of external dependencies (the external log collection component and the external log analysis system). Furthermore, it should be noted that for practical applications of databases, it is often difficult to perform efficient log analysis based on the text file itself. The main reasons for this are that it is difficult to perform large-scale (the number of database logs is usually large) or complex (for example, perform multidimensional data analysis) data analysis based on text files, the calculation efficiency is poor, and the consumed calculation resources (for example, memory) are also large, which is often difficult to bear in practical applications.
In order to solve the technical problem, an embodiment of the present specification provides a storage method for a database log. Fig. 2 is a schematic diagram illustrating a storage method of a database log according to an embodiment of the present disclosure. As shown in fig. 2, in the storage scheme of this embodiment, various types of log information generated by various database components are written into a log table for storing logs, which is established in advance in the database. The collection and writing of log information to the data table may also have different specific mechanisms in different embodiments. For example, in one embodiment, the log information may be first placed in a buffer in the memory, and then written into the log table when the buffer is filled with the log information. After the log information in the current buffer area is written into the log table, the buffer area can be emptied, so that the buffer area can receive subsequent log information. Thereafter, when it is desired to perform log analysis of different specific types (e.g., analysis of running state of the database, analysis of running alarm or analysis of running error, etc.), log analysis of different specific types may be performed according to log information stored in the log table through data query and data analysis functions (e.g., data query may be performed through a single query statement supported by the database system, and complex data query and complex data analysis may be performed through an SQL program or stored procedure including an SQL statement set) that are provided in the database itself.
The method has the following advantages: on one hand, the log information generated by the database system is directly stored in the data table of the database, so that the dependence on external components (external file access components) is avoided, and the operation and maintenance complexity and the cost of log output and import are reduced. In the second aspect, the log data table can be compressed by utilizing the compression capability of the database, so that the storage cost of the system log is greatly reduced. In the third aspect, the data query and analysis functions of the database can be conveniently utilized to query and analyze the log information in the log data table, an external log analysis system is not required to be relied on, the dependence on an external component (the external log analysis system) is further avoided, and the operation and maintenance complexity and the log output and import overhead are reduced.
The details of the process are further set forth below. Fig. 3 is a flowchart illustrating a method for storing a database log according to an embodiment of the present disclosure. As shown in fig. 3, the method at least comprises the following steps:
First, at step 31, the immediate log information of the database is acquired. In different embodiments, the database may be different specific types of databases, and the present specification does not limit this. In one embodiment, the database may be a relational data system. In another embodiment, the database may be a distributed relational data system.
The instant log information refers to log information generated by the database system in real time, and the log information may include, for example, running state information of the database system at the current time (or time interval), SQL (Structured Query Language) statement execution information, transaction execution information, system ERROR (ERROR) or Warning (WARN) information, and the like. In different embodiments, different types of log information may be generated. Also, there may be different internal components that generate database logs, usually in different types of databases. For example, in one embodiment, the internal components may be one or more of a storage management component, a query and optimization component, a memory and storage synchronization component, a metadata management component, a cluster state detection component, a fault tolerance and recovery component, a data backup and recovery component, a resource isolation component, a rights management component, a data access interface component. In this specification, no limitation is made on what specific type of the obtained instant log information of what specific database is, or what specific database component generates the instant log information.
These generated log information, in different embodiments, may be used for different specific purposes, such as database run state analysis, query statement execution state analysis, transaction execution status analysis, or for data recovery, etc. The specification does not limit what specific type of instant log information is obtained.
In different embodiments, the instant log information may be obtained in different specific ways. For example, in one embodiment, the retrieval may be from a specific database component generated from the instant log information. In one embodiment, a database component that generates live log information places the generated live log information into a buffer in memory. Thus, for example, when the buffer is full, the live log information therein may be retrieved from the buffer.
After obtaining the immediate log information, in step 32, a first write operation is performed to write the immediate log information into a system log table pre-established in the database.
A TABLE (or data TABLE) is an object used to store data in a database, and is a set of structured data. In this step, a pre-established system log table, that is, a pre-established data table for structured storage of instant log information in the database is provided. In different embodiments, the system log table may have different specific table structures. For example, in one embodiment, the fields in the system log table may include: one or more of log generation time, database thread number, TraceID, database module name, log-related code location, log message, and detailed information.
The pre-established system log table can have different establishing modes. In one embodiment, the pre-established system log table may be established when the database is first initialized. Specifically, the system log table may be created by executing a table building statement or a storage procedure, for example, when the database is initialized for the first time.
As described above, in different embodiments, different specific types of databases may be employed, and different types of databases may support different specific types of data tables. Thus, in various embodiments, the particular type of data table for the system log table may be determined based on the particular database in which it is employed. In one embodiment, the system log table may be a SSTable table. In a specific example, the system log table may be further compressed by using a compression mechanism for SSTable, so as to reduce a storage space consumed by the system log table and further reduce a storage cost of the database. Compared with the traditional scheme of storing the database logs in the text file, the method can greatly reduce the storage space consumed by storing the database logs.
In an actual production scenario, there is a possibility that a write to a data table in a database fails to execute due to an exception in the system operation, for example, a data table write failure due to the data table write buffer being currently full or the target partition of the data table being currently unavailable. In order not to lose the immediate log information when writing into the system log table fails, the immediate log information may be saved to a disk file, for example, temporarily in a disk text file. Therefore, in one embodiment, if the first write operation fails to be performed, a second write operation may be performed to write the immediate log information into a log file in text form. Then, after the abnormal operation is recovered, for example, after the write buffer is emptied or the target partition is recovered to be usable, the log information temporarily stored in the disk text file may be written into the system log table, so as to ensure that the system log table can store the full database log. In the case of not determining whether the exception is recovered, the write operation may be performed on the system log table in a timed polling manner until the write is successful. Therefore, in one embodiment, the log text saved in the log file can be read within a predetermined time range; and executing a third write operation to analyze and write the log text into the system log table. In a specific embodiment, the log file may be deleted in case the third write operation is successfully performed.
According to an embodiment of another aspect, a method for analyzing a database log is also provided. Fig. 4 is a flowchart illustrating a method for analyzing a database log according to an embodiment of the present disclosure, where, as shown in fig. 4, the method includes at least the following steps:
in step 41, reading log information from the system log table;
for a detailed description of the system log table and the log information in this step, i.e. the system log table and the immediate log information stored in step 31, refer to the description of step 41, which is not described herein again.
At step 42, a predetermined type of log analysis is performed based on the log information.
In different embodiments, different specific types of log analysis may be performed according to different specific log information, which is not limited in this specification. In one embodiment, for example, one or more of SQL statement execution status analysis, database run error or warning analysis, system run state analysis, and transaction execution status analysis may be performed.
In different embodiments, log analysis can be performed by different specific data query and data analysis functions in the database. In one embodiment, data queries and analysis may be performed through query statements supported by a database system. In another embodiment, complex data queries and complex data analysis may also be performed by SQL programs or stored procedures that include a collection of SQL statements. Therefore, in a specific embodiment, a predetermined type of log analysis can be performed according to the log information through a preset SQL statement set.
In summary, the storage method for database logs and the analysis method for database logs corresponding to the storage method have the following advantages: the log information generated by the database system is directly stored in the data table of the database, so that the high-efficiency storage and data analysis capability supported by the database system can be utilized, the storage cost of the database log is reduced, the dependence of an external component for analyzing the database log is reduced, and the operation and maintenance complexity and the operation overhead of importing data output into the external component are reduced.
According to an embodiment of another aspect, a storage device of the database log is also provided. Fig. 5 is a block diagram illustrating a storage apparatus of a database log according to an embodiment of the present specification, and as shown in fig. 5, the apparatus 500 includes:
a log information obtaining unit 51 configured to obtain immediate log information of the database;
the log saving unit 52 is configured to perform a first write operation, so as to write the immediate log information into a system log table pre-established in the database.
In one embodiment, the log saving unit may be further configured to:
and if the first write-in operation fails to be executed, executing a second write-in operation to write the instant log information into a log file in a text form.
In one embodiment, the log saving unit may be further configured to:
reading the log text stored in the log file within a preset time range;
and executing a third write operation to analyze and write the log text into the system log table.
In one embodiment, the log saving unit may be further configured to: and deleting the log file when the third write operation is successfully executed.
In one embodiment, the pre-established system log table may be established when the database is first initialized.
In one embodiment, the fields of the system log table may include: one or more of log generation time, database thread number, TraceID, database module name, log-related code location, log message, and detailed information.
In one embodiment, the system log table may be a SSTable.
According to an embodiment of another aspect, an analysis device of the database log is also provided. Fig. 6 is a block diagram illustrating an apparatus for analyzing a database log according to an embodiment of the present disclosure, and as shown in fig. 6, the apparatus 600 includes:
a log extracting unit 61 configured to read log information from the system log table described in claim 1;
a log analyzing unit 62 configured to perform a predetermined type of log analysis based on the log information.
In one embodiment, the log analysis unit may be further configured to,
and performing log analysis of a preset type according to the log information through a preset SQL statement set.
Yet another aspect of the present specification provides a computer readable storage medium having a computer program stored thereon, which, when executed in a computer, causes the computer to perform any of the methods described above.
Yet another aspect of the present specification provides a computing device comprising a memory having stored therein executable code, and a processor that, when executing the executable code, implements any of the methods described above.
It is to be understood that the terms "first," "second," and the like, herein are used for descriptive purposes only and not for purposes of limitation, to distinguish between similar concepts.
Those skilled in the art will recognize that, in one or more of the examples described above, the functions described in this invention may be implemented in hardware, software, firmware, or any combination thereof. When implemented in software, the functions may be stored on or transmitted over as one or more instructions or code on a computer-readable medium.
The above-mentioned embodiments, objects, technical solutions and advantages of the present invention are further described in detail, it should be understood that the above-mentioned embodiments are only exemplary embodiments of the present invention, and are not intended to limit the scope of the present invention, and any modifications, equivalent substitutions, improvements and the like made on the basis of the technical solutions of the present invention should be included in the scope of the present invention.
Claims (20)
1. A method of storing a database log, comprising:
acquiring instant log information of the database;
and executing a first writing operation to write the instant log information into a system log table pre-established in the database.
2. The method of claim 1, further comprising:
and if the first write-in operation fails to be executed, executing a second write-in operation to write the instant log information into a log file in a text form.
3. The method of claim 2, further comprising:
reading the log text stored in the log file within a preset time range;
and executing a third write operation to analyze and write the log text into the system log table.
4. The method of claim 3, further comprising: and deleting the log file when the third write operation is successfully executed.
5. The method of claim 1, wherein,
the pre-established system log table is established when the database is initialized for the first time.
6. The method of claim 1, wherein the fields of the system log table comprise: one or more of log generation time, database thread number, TraceID, database module name, log-related code location, log message, and detailed information.
7. The method of claim 1, wherein the system log table is an SSTable table.
8. A method of analyzing database logs, comprising:
reading log information from the system log table as recited in claim 1;
and performing log analysis of a preset type according to the log information.
9. The method of claim 8, wherein said performing a predetermined type of log analysis based on said log information comprises:
and performing log analysis of a preset type according to the log information through a preset SQL statement set.
10. A storage device for database logs, comprising:
the log information acquisition unit is configured to acquire instant log information of the database;
and the log saving unit is configured to execute a first writing operation and write the immediate log information into a system log table which is pre-established in the database.
11. The apparatus of claim 10, wherein the log saving unit is further configured to:
and if the first write-in operation fails to be executed, executing a second write-in operation to write the instant log information into a log file in a text form.
12. The apparatus of claim 11, wherein the log saving unit is further configured to:
reading the log text stored in the log file within a preset time range;
and executing a third write operation to analyze and write the log text into the system log table.
13. The apparatus of claim 12, wherein the log saving unit is further configured to: and deleting the log file when the third write operation is successfully executed.
14. The apparatus of claim 10, wherein,
the pre-established system log table is established when the database is initialized for the first time.
15. The apparatus of claim 10, wherein the fields of the system log table comprise: one or more of log generation time, database thread number, TraceID, database module name, log-related code location, log message, and detailed information.
16. The apparatus of claim 10, wherein the system log table is an SSTable table.
17. An apparatus for analyzing database logs, comprising:
a log extracting unit configured to read log information from the system log table recited in claim 1;
and the log analysis unit is configured to perform predetermined types of log analysis according to the log information.
18. The apparatus of claim 17, wherein the log analysis unit is further configured to,
and performing log analysis of a preset type according to the log information through a preset SQL statement set.
19. A computer-readable storage medium, on which a computer program is stored which, when executed in a computer, causes the computer to carry out the method of any one of claims 1-9.
20. A computing device comprising a memory having executable code stored therein and a processor that, when executing the executable code, implements the method of any of claims 1-9.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210343130.XA CN114817245A (en) | 2022-04-02 | 2022-04-02 | Database log storage method and database log analysis method and device |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202210343130.XA CN114817245A (en) | 2022-04-02 | 2022-04-02 | Database log storage method and database log analysis method and device |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114817245A true CN114817245A (en) | 2022-07-29 |
Family
ID=82532153
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202210343130.XA Pending CN114817245A (en) | 2022-04-02 | 2022-04-02 | Database log storage method and database log analysis method and device |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114817245A (en) |
-
2022
- 2022-04-02 CN CN202210343130.XA patent/CN114817245A/en active Pending
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN110442560B (en) | Log replay method, device, server and storage medium | |
| US8108343B2 (en) | De-duplication and completeness in multi-log based replication | |
| US20120246116A1 (en) | System and method for data replication between heterogeneous databases | |
| CN112286941B (en) | Big data synchronization method and device based on Binlog + HBase + Hive | |
| US20220245132A1 (en) | Transaction monitoring method, apparatus and system for distributed database, and storage medium | |
| US8793288B2 (en) | Online access to database snapshots | |
| CN110727548A (en) | Continuous data protection method and device based on database DML synchronization | |
| US20180032567A1 (en) | Method and device for processing data blocks in a distributed database | |
| CN113448938A (en) | Data processing method and device, electronic equipment and storage medium | |
| CN110737710A (en) | Distributed data automatic structured warehousing method and system | |
| CN114490735A (en) | Method and device for constructing distributed OLAP data analysis based on MPP and full-text index | |
| CN116501700B (en) | APP formatted file offline storage method, device, equipment and storage medium | |
| CN117112522A (en) | Concurrent process log management method, device, equipment and storage medium | |
| CN109947828B (en) | Method and device for processing report data | |
| CN114817245A (en) | Database log storage method and database log analysis method and device | |
| US8630976B2 (en) | Fast search replication synchronization processes | |
| CN114896276A (en) | Data storage method and device, electronic equipment and distributed storage system | |
| CN112612647A (en) | Log parallel replay method, device, equipment and storage medium | |
| CN112711606A (en) | Database access method and device, computer equipment and storage medium | |
| CN112835887A (en) | Database management method, database management device, computing equipment and storage medium | |
| CN117390040B (en) | Service request processing method, device and storage medium based on real-time wide table | |
| CN115544173B (en) | Linearly expandable distributed database | |
| CN114138746A (en) | Database monitoring method, device, equipment and storage medium | |
| JP2017130111A (en) | Dataflow log generation device, relational database, dataflow log generation method, program, and monitoring system | |
| CN116414917A (en) | Data transmission method, device, equipment and storage medium based on Myhouse database |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |