CN111984625B

CN111984625B - Database load characteristic processing method and device, medium and electronic equipment

Info

Publication number: CN111984625B
Application number: CN202010853809.4A
Authority: CN
Inventors: 尹强
Original assignee: Beijing Kingbase Information Technologies Co Ltd
Current assignee: Beijing Kingbase Information Technologies Co Ltd
Priority date: 2020-08-24
Filing date: 2020-08-24
Publication date: 2023-09-15
Anticipated expiration: 2040-08-24
Also published as: CN111984625A

Abstract

The disclosure relates to a database load feature processing method, a database load feature processing device, a database load feature processing medium and electronic equipment. The method comprises the following steps: collecting and recording statement information, transaction identifications and session identifications related to each SQL statement when each SQL statement is executed; determining statement type identification of each SQL statement according to statement information related to each SQL statement; determining a corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier; determining a corresponding session type identifier according to the transaction type identifier belonging to the same session identifier; based on all the determined session type identifiers, transaction type identifiers and statement type identifiers, a structured data relationship model is established; the structured data relationship model is used to store session feature information, transaction feature information, and statement feature information for each session type. The scheme of the present disclosure can accurately and comprehensively characterize the load characteristic of the reflection database.

Description

Database load characteristic processing method and device, medium and electronic equipment

Technical Field

The embodiment of the disclosure relates to the technical field of databases, in particular to a database load characteristic processing method, a database load characteristic processing device, a computer readable storage medium for realizing the database load characteristic processing method and electronic equipment.

Background

Databases are an important component of information systems, the task of which is to store and manage data. The performance of the database will directly impact the expansion capability and user experience of the service. Therefore, users want the database to work in the optimal mode for a long time, and how to evaluate and improve the performance of the database becomes an important subject.

At present, a third-party tool can be adopted in the related technology to monitor and analyze the load performance of the database, the load characteristic data of the database is subjected to sorting analysis, and finally a database performance data report is obtained, so that the database is better configured for a database manager, and a basis and a reference are provided for the database manager to work in an efficient mode.

However, the load of the database tends to be more and more complex over time, such as changes in functions, increases in access volume, and even increases in applications, which may lead to an increase in the complexity of the load of the database, and it is difficult to understand the load characteristics of the database more comprehensively, which may pose a great challenge to management and possible reconfiguration of the database. However, the related art has not focused on this problem.

Disclosure of Invention

To solve or at least partially solve the above technical problems, embodiments of the present disclosure provide a database load feature processing method, a database load feature processing apparatus, and a computer readable storage medium and an electronic device implementing the database load feature processing method.

In a first aspect, an embodiment of the present disclosure provides a method for processing a load feature of a database, including:

collecting and recording statement information, transaction identifications and session identifications related to each SQL statement when each SQL statement is executed; the transaction identifier represents the transaction to which each SQL statement belongs, and the session identifier represents the session to which the transaction to which each SQL statement belongs;

determining statement type identification of each SQL statement according to statement information related to each SQL statement; determining a corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier; determining a corresponding session type identifier according to the transaction type identifier belonging to the same session identifier;

based on all the determined session type identifiers, transaction type identifiers and statement type identifiers, a structured data relationship model is established; the structured data relationship model is used for storing session feature information of each session type, transaction feature information belonging to the session feature information and statement feature information belonging to the transaction feature information.

In some embodiments of the present disclosure, the collecting and recording statement information, transaction identifier and session identifier related to each SQL statement when each SQL statement is executed, includes:

When each SQL sentence is executed, acquiring sentence information, transaction identification and session identification related to each SQL sentence through a log system;

wherein the statement information at least comprises one or more of statement content, statement execution time consumption and table information related to the statement.

In some embodiments of the disclosure, the determining the statement type identifier of each SQL statement according to the statement information related to each SQL statement includes:

parameterizing statement content of each SQL statement, performing hash calculation on the parameterized content, and taking the obtained hash value as statement type identification.

In some embodiments of the present disclosure, the parameterizing the statement content of each SQL statement includes:

and replacing the constant part of the sentence content of each SQL sentence with a preset character.

In some embodiments of the present disclosure, the determining the corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier includes:

traversing the related transaction identifications of each SQL sentence to determine the SQL sentences belonging to the same transaction identification;

acquiring statement type identifiers of SQL statements belonging to the same transaction identifier, performing hash calculation on the acquired statement type identifiers, and taking the obtained hash value as the transaction type identifier.

In some embodiments of the present disclosure, further comprising:

when a plurality of SQL sentences belonging to the same transaction identifier exist, acquiring a plurality of sentence type identifiers corresponding to the plurality of SQL sentences belonging to the same transaction identifier;

removing repeated sentence type identifiers in the sentence type identifiers;

and performing hash calculation based on the removed residual statement type identifier, and taking the obtained hash value as a transaction type identifier.

In some embodiments of the present disclosure, the determining the corresponding session type identifier according to the transaction type identifier belonging to the same session identifier includes:

traversing the session identifications and the transaction identifications related to each SQL statement to obtain M transaction type identifications belonging to the same session identification; m is a natural number greater than or equal to 2;

removing repeated transaction type identifiers in M transaction type identifiers to which the same session identifier belongs to obtain remaining N transaction type identifiers;

and carrying out hash calculation based on the rest N transaction type identifiers, and taking the obtained hash value as a session type identifier.

In some embodiments of the present disclosure, further comprising:

comparing the repetition proportion of the remaining N transaction type identifiers to which the two adjacent session identifiers belong one by one, and determining that the session represented by the two session identifiers is the same session when the repetition proportion is greater than a preset proportion threshold; the preset proportion threshold value is more than 80%;

Removing repeated transaction type identifiers in the remaining 2N transaction type identifiers to which the two session identifiers belong to obtain remaining P transaction type identifiers;

and carrying out hash calculation based on the rest P transaction type identifiers, and taking the obtained hash value as a session type identifier of the same session.

In some embodiments of the present disclosure, further comprising:

removing repeated sentence type identifiers in sentence type identifiers of all SQL sentences to obtain residual sentence type identifiers;

acquiring table information related to the SQL statement based on the residual statement type identifier, and acquiring data characteristic information based on the table information;

wherein the data characteristic information at least comprises one or more of table names, table capacity, attribute number, page number, tuple number and statistical information.

In some embodiments of the present disclosure, the structured data relationship model further comprises data characteristic information attributed to sentence characteristic information; the session feature information comprises one or more of session number, session time consumption, transaction type number and transaction total number; the transaction characteristic information comprises one or more of transaction execution times, transaction execution time consumption and execution statement sequences to which the transaction belongs; the sentence characteristic information includes one or more of a number of sentence executions, a time consuming sentence execution, and sentence contents.

In some embodiments of the disclosure, before the determining the statement type identifier of each SQL statement according to the statement information related to each SQL statement, the method further includes:

writing the obtained statement information, transaction identifier and session identifier related to each SQL statement into a log file according to a preset file format record;

introducing the log file into a database in the form of an external table, and converting the external table into a table of a database self engine;

three attribute columns for updating the session type identifier, the transaction type identifier and the statement type identifier determined by the record are newly added in the converted table.

In some embodiments of the present disclosure, further comprising:

acquiring a session type identifier of a session to be analyzed;

and inquiring and acquiring one or more of session characteristic information, transaction characteristic information, statement characteristic information and data characteristic information of the session to be analyzed based on the session type identification of the session to be analyzed and the structured data relation model.

In a second aspect, an embodiment of the present disclosure provides a database load feature processing apparatus, including:

the data acquisition module is used for acquiring and recording statement information, transaction identifications and session identifications related to each SQL statement when the execution of each SQL statement is completed; the transaction identifier represents the transaction to which each SQL statement belongs, and the session identifier represents the session to which the transaction to which each SQL statement belongs;

The data preprocessing module is used for determining statement type identification of each SQL statement according to statement information related to each SQL statement; determining a corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier; determining a corresponding session type identifier according to the transaction type identifier belonging to the same session identifier;

the model building module is used for building a structured data relation model based on all the determined session type identifiers, transaction type identifiers and statement type identifiers; the structured data relationship model is used for storing session feature information of each session type, transaction feature information belonging to the session feature information and statement feature information belonging to the transaction feature information.

In a third aspect, embodiments of the present disclosure provide a computer readable storage medium having stored thereon a computer program which, when executed by a processor, implements the steps of the database load feature processing method of any of the embodiments described above.

In a fourth aspect, an embodiment of the present disclosure provides an electronic device, including:

a processor; and

a memory for storing executable instructions of the processor;

Wherein the processor is configured to perform the steps of the database load feature processing method of any of the embodiments described above via execution of the executable instructions.

Compared with the prior art, the technical scheme provided by the embodiment of the disclosure has the following advantages:

in the embodiment of the disclosure, statement information, transaction identifiers and session identifiers related to each SQL statement are collected and recorded when each SQL statement is executed, and then statement type identifiers of each SQL statement are determined according to the statement information related to each SQL statement; determining a corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier; determining a corresponding session type identifier according to the transaction type identifier belonging to the same session identifier; finally, based on all the determined session type identifiers, transaction type identifiers and statement type identifiers, a structured data relationship model is established; the structured data relationship model is used for storing session feature information of each session type, transaction feature information belonging to the session feature information and statement feature information belonging to the transaction feature information. In this way, the embodiment focuses on the information such as session type, transaction type, statement type and the like reflecting the operation characteristics of the database, and based on the information, a structured data relation model reflecting the load characteristic information of the database is established, so that the load characteristic condition of the database can be more accurately and comprehensively represented, the subsequent load characteristic data analysis is facilitated, the database is better configured for a database manager, and more accurate and comprehensive reference is provided for the database manager to work in an efficient mode.

Drawings

The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the disclosure and together with the description, serve to explain the principles of the disclosure.

In order to more clearly illustrate the embodiments of the present disclosure or the solutions in the prior art, the drawings that are required for the description of the embodiments or the prior art will be briefly described below, and it will be obvious to those skilled in the art that other drawings can be obtained from these drawings without inventive effort.

FIG. 1 is a flowchart of a database load feature processing method according to an embodiment of the present disclosure;

FIG. 2 is a schematic diagram of a structured data relationship model of database load features in an embodiment of the present disclosure;

FIG. 3 is a flowchart of a database load feature processing method according to an embodiment of the present disclosure;

FIG. 4 is a schematic diagram of a database load feature processing device according to an embodiment of the disclosure;

fig. 5 is a schematic diagram of an electronic device for implementing a database load feature processing method according to an embodiment of the disclosure.

Detailed Description

In order that the above objects, features and advantages of the present disclosure may be more clearly understood, a further description of aspects of the present disclosure will be provided below. It should be noted that, without conflict, the embodiments of the present disclosure and features in the embodiments may be combined with each other.

In the following description, numerous specific details are set forth in order to provide a thorough understanding of the present disclosure, but the present disclosure may be practiced otherwise than as described herein; it will be apparent that the embodiments in the specification are only some, but not all, embodiments of the disclosure.

Fig. 1 is a flowchart of a database load feature processing method according to an embodiment of the disclosure, where the database load feature processing method may include the following steps:

step S101: and collecting and recording statement information, transaction identifications and session identifications related to each SQL statement when the execution of each SQL statement is completed. The transaction identifier represents the transaction to which each SQL statement belongs, and the session identifier represents the session to which the transaction to which each SQL statement belongs.

Step S102: determining statement type identification of each SQL statement according to statement information related to each SQL statement; determining a corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier; and determining the corresponding session type identifier according to the transaction type identifier belonging to the same session identifier.

Step S103: based on all the determined session type identifiers, transaction type identifiers and statement type identifiers, a structured data relationship model is established; the structured data relationship model is used for storing session feature information of each session type, transaction feature information belonging to the session feature information and statement feature information belonging to the transaction feature information.

According to the database load characteristic processing method, the information such as the session type, the transaction type and the statement type reflecting the database operation characteristics is focused, the structural data relation model reflecting the database load characteristic information is established based on the information, and the load characteristic condition reflecting the database can be more accurately and comprehensively described, so that subsequent load characteristic data analysis is facilitated, the database is better configured for a database manager, and more accurate and comprehensive references are provided for the database manager to work in an efficient mode.

In some embodiments of the present disclosure, statement information, transaction identifications, and session identifications associated with each SQL statement are collected and recorded at the completion of execution of each SQL statement in step S101. The transaction identifier represents the transaction to which each SQL statement belongs, and the session identifier represents the session to which the transaction to which each SQL statement belongs.

Illustratively, the statement information may include at least one or more of, but is not limited to, statement content, statement execution time consumption, and table information to which the statement relates. Statement content may be, for example, content that relates to data operations such as querying, deleting, updating, and the like. Statement execution time is the time period from the beginning to the end of an SQL statement. The table information to which the statement relates may be a table name, but is not limited thereto. The table to which the statement relates may be one or more, related to the statement content, as determined by the content of the data operation, such as querying, deleting, updating, etc. In addition, as shown in Table 1 below, typically a session may contain one or more transactions attributed to the session, such as session 1 including transaction 1, transaction 2, and transaction 3. A transaction may in turn include one or more SQL statements attributed to the transaction, such as transaction 1 including SQL statement 1 and SQL statement 2. The transaction identifier may be a transaction number, which indicates that a SQL statement belongs to a transaction, such as SQL statement 1 and SQL statement 2 belong to transaction 1. The session identifier may be a session number, indicating that a transaction to which an SQL statement belongs, such as transaction 1, transaction 2, and transaction 3 belong to session 1, and transaction 4, transaction 5, and transaction 6 belong to session 2.

TABLE 1

In this embodiment, when each SQL statement is executed, the related statement information, transaction number and session number of each SQL statement may be collected and recorded.

Specifically, as an example, at the completion of each SQL statement execution, statement information, transaction identifications, such as transaction numbers, and session identifications, such as session numbers, associated with each SQL statement may be obtained through a log system. The related statement information, the transaction number and the session number of each SQL statement can be directly obtained through the database log system, and the processing efficiency is high.

Optionally, in some embodiments of the present disclosure, determining the statement type identifier of each SQL statement according to the statement information related to each SQL statement in step S102 may specifically include: parameterizing statement content of each SQL statement, performing hash calculation on the parameterized content, and taking the obtained hash value as statement type identification.

For example, the statement type identifier, such as a statement type ID, may represent a type of an SQL statement, such as a query statement type, a delete statement type, or an update statement type, and the like, and the value of the different statement type ID, such as a unique numerical code, may represent a corresponding different statement type, for example, a correspondence relationship between the value of the statement type ID and the SQL statement type may be configured in advance, but is not limited thereto. The hash value calculated by the hash is used as the statement type ID, so that the data acquisition and query efficiency in the subsequent feature extraction and analysis can be improved.

In some embodiments of the present disclosure, optionally, parameterizing the statement content of each SQL statement in step S102 may specifically include: and replacing the constant part of the sentence content of each SQL sentence with a preset character.

Specifically, as an example, parameterizing the sentence content, the constant portion of the sentence content may be replaced with a preset character such as "? ", but the preset character is not limited thereto. For example, replace the statement content "where id >5" with "? And performing HASH calculation on the parameterized content, such as HASH calculation based on the character strings in the content, and marking the HASH value obtained by calculation as a statement type into a statement type ID. It will be appreciated that specific hash calculations may refer to the prior art and are not described in detail herein or below.

Optionally, in some embodiments of the present disclosure, determining the corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier in step S102 may specifically include the following steps:

the transaction identifications associated with each SQL statement are traversed to determine the SQL statements that are attributed to the same transaction identification.

Specifically, the transaction type identifier, such as a transaction type ID, represents the type of a transaction, and the values of different transaction type IDs may represent different types of transactions. When a session includes multiple transactions, the types of the respective transactions may not be identical, for example, the transaction 1 and the transaction 2 included in the session 1 may be identical, and the transaction 3 may be different from the transaction 1 and the transaction 2, which is only illustrated herein, and the embodiment is not limited thereto. With the above-described attribution relationships, such as those shown in table 1, the transaction numbers associated with each SQL statement may be traversed in this embodiment to determine SQL statements that are attributed to the same transaction number. For example, if the transaction numbers of the SQL statement 1 and the SQL statement 2 are both the same as the transaction 1, the SQL statement belonging to the same transaction 1 can be determined. For another example, if the transaction numbers of the SQL statement 4, the SQL statement 5 and the SQL statement 6 are all the same as the transaction 3, the SQL statements belonging to the same transaction 3 can be determined.

Thereafter, the statement type identifier of the SQL statement belonging to the same transaction number may be acquired, for example, statement type ID4, statement type ID5, and statement type ID6 corresponding to SQL statement 4, SQL statement 5, and SQL statement 6 belonging to transaction 3, respectively. Then, hash computation can be performed on the statement type ID4, the statement type ID5 and the statement type ID6, that is, hash computation is performed on the basis of the statement sequence (SQL statement 4, SQL statement 5 and SQL statement 6) ID as a whole, and the obtained hash value is used as a transaction type identifier of the transaction 3, such as a transaction type ID.

Further optionally, on the basis of the foregoing embodiments, in some embodiments of the disclosure, the method may further include the steps of:

and when a plurality of SQL sentences belonging to the same transaction identifier exist, acquiring a plurality of sentence type identifiers corresponding to the plurality of SQL sentences belonging to the same transaction identifier.

And removing the repeated statement type identifications in the statement type identifications.

Specifically, some transactions may repeatedly perform some operations for an indefinite number of times, and the statement type ID included in the transaction may be deduplicated and then calculated. Continuing with the above example, when three SQL statements belonging to the transaction 3 are acquired, such as the SQL statement 4, the SQL statement 5, and the SQL statement 6, the statement type ID4, the statement type ID5, and the statement type ID6 corresponding to the SQL statement 4, the SQL statement 5, and the SQL statement 6 to which the transaction 3 belongs are acquired. In some cases, statement type ID4, statement type ID5, and statement type ID6 may have duplicate statement type identifiers, e.g., statement type ID4, statement type ID5 have the same value, indicating that the corresponding SQL statement 4 and SQL statement 5 are the same type of statement, e.g., both are delete statement types. At this time, any one of the repeated statement type ID4 and statement type ID5 is removed, hash calculation is performed based on the removed statement type ID4 and statement type ID6, for example, and the obtained hash value is used as a transaction type identifier, such as a transaction type ID, of the transaction 3. Therefore, the data volume to be processed can be reduced, and the overall data processing efficiency of the database load characteristic processing is improved.

Optionally, based on the foregoing embodiments, in some embodiments of the present disclosure, determining, in step S102, a corresponding session type identifier according to a transaction type identifier attributed to the same session identifier may specifically include the following steps:

traversing the session identifications and the transaction identifications related to each SQL statement to obtain M transaction type identifications belonging to the same session identification; m is a natural number greater than or equal to 2.

And removing the repeated transaction type identifiers in M transaction type identifiers to which the same session identifier belongs to obtain the rest N transaction type identifiers.

Specifically, referring to table 1 above, the session number and the transaction number associated with each SQL statement are traversed to obtain M transaction type identifiers, such as transaction type IDs, assigned to the same session number. For example, the transaction type ID of each of the three transactions (e.g., transaction 4, transaction 5, transaction 6) belonging to session 2 is obtained. At the same time, the transaction type ID of each of the 3 transactions (such as transaction 1, transaction 2, and transaction 3) to which the session 1 belongs can also be acquired.

Then, the repeated transaction type IDs in M transaction type IDs to which the same session number belongs can be removed to obtain the remaining N transaction type IDs. For example, the transaction type IDs of each of three transactions (e.g., transaction 4, transaction 5, transaction 6) belonging to the same session 2 may have two transaction type IDs, e.g., the transaction type IDs of each of transaction 4 and transaction 5, which are identical, at which time duplicate transaction type IDs may be removed. The hash value obtained may then be used as a session type identifier, such as a session type ID, for session 2, based on, for example, the transaction type IDs corresponding to the remaining transactions 5 and 6, respectively. The determination processing manner of the session type ID of the other session, such as session 1, is the same as that of the other session, and will not be described herein.

comparing the repetition proportion of the remaining N transaction type identifiers to which the two adjacent session identifiers belong one by one, and determining that the session represented by the two session identifiers is the same session when the repetition proportion is greater than a preset proportion threshold; the preset proportion threshold value is more than 80%.

And removing the repeated transaction type identifiers in the remaining 2N transaction type identifiers to which the two session identifiers belong to obtain remaining P transaction type identifiers.

Specifically, in order to reduce the data processing amount, the overall data processing efficiency in the processing of the load characteristics of the database is improved. In this embodiment, when the types related to two sessions are the same, the hash calculation may be performed after the sessions of the same type are merged. Continuing with the above example, comparing the repetition ratio of the remaining N transaction type IDs to which the two adjacent session numbers belong, that is, the transaction type ID similarity, one by one, and determining that the session represented by the two session numbers is the same session when the repetition ratio is greater than 80%. For example, if it is assumed that the session 2 has transaction type IDs corresponding to 10 remaining transactions after deduplication and that the session 1 has transaction type IDs corresponding to 11 remaining transactions after deduplication, and if the comparison determines that 10 transaction type IDs belonging to the session 2 are identical to 9 transaction type IDs among the 11 transaction type IDs belonging to the session 1, that is, more than 90% of the transaction type IDs are identical, the session 1 and the session 2 can be considered to be identical in session type. At this time, 9 repeated transaction type IDs among 21 transaction type IDs to which the two sessions 1 and 2 belong may be removed to obtain remaining 12 transaction type IDs, hash computation is performed based on the 12 transaction type IDs, and the obtained hash value is used as the session type ID of the same session.

Optionally, on the basis of any embodiment, in some embodiments of the disclosure, the method further includes the following steps:

and removing repeated statement type identifiers in statement type identifiers of all SQL statements to obtain residual statement type identifiers.

And acquiring table information related to the SQL statement based on the residual statement type identifier, and acquiring data characteristic information based on the table information.

Wherein, the data characteristic information at least can include, but is not limited to, one or more of table names, table capacity, attribute number, page number, tuple number and statistical (statistical) information.

Specifically, the statement type IDs of all the SQL statements 1 to 10 belonging to the session 1 and the session 2 determined based on the above embodiment may have repeated statement type IDs, that is, the types of some two or more SQL statements are the same, and at this time, the repeated statement type IDs in the statement type IDs of 10 SQL statements may be removed to obtain the remaining statement type IDs. The table information related to the related SQL statement can then be obtained based on the remaining statement type ID, and data characteristic information such as table name, table capacity, attribute number, page number, tuple number, statistical (statistical) information and the like can be obtained based on the table information.

Further optionally, after the data characteristic information is obtained, in some embodiments of the present disclosure, the structured data relationship model may further include data characteristic information attributed to the sentence characteristic information. As shown in fig. 2, the structured data relationship model may be used to store session feature information for each session type (session type 1, session type 2 … session type N), transaction feature information attributed to the session feature information, statement feature information attributed to the transaction feature information, and data feature information attributed to the statement feature information. The feature information of each level in the structured data relationship model is stored with the corresponding session type ID, transaction type ID, and statement type ID as index keys, so that the associated feature information of each session type (such as session type 1, session type 2 … session type N) can be stored. The structured data relation model in the embodiment further comprises information of an increased data feature dimension, and the structured data relation model reflecting the load feature information of the database can be established based on the information, so that the load feature condition of the reflecting database can be more accurately and comprehensively represented, further subsequent load feature data analysis is facilitated, the database is better configured for a database manager, and more accurate and comprehensive reference is provided for the database manager to work in an efficient mode.

In some embodiments of the present disclosure, the session feature information may include, but is not limited to, one or more of a number of sessions, a time consumption of a session, a number of transaction types, and a total number of transactions. The transaction characteristic information may include, but is not limited to, one or more of a number of times a transaction is executed, time spent in executing the transaction, a sequence of execution statements to which the transaction belongs. The sentence characteristic information may include, but is not limited to, one or more of a number of sentence executions, a sentence execution time consumption, and a sentence content. Wherein some information of the upper level can be summarized by the related information of the lower level.

On the basis of any one of the foregoing embodiments, in some embodiments of the present disclosure, in order to facilitate processing load feature data, before determining the statement type identifier of each SQL statement according to statement information related to each SQL statement, the method further includes the following steps:

and writing the obtained statement information, transaction identification and session identification related to each SQL statement into a log file according to a preset file format record.

The log file is introduced into the database in the form of an external table and the external table is converted into a table of the database's own engine.

Specifically, in this embodiment, the minimum collection granularity is at the statement level, so the granularity of collection of the original collection information should not be higher than the statement level, and in this embodiment, one piece of original information such as statement information, transaction number, session number, etc. can be recorded for each SQL statement. Because the space occupied by the data to be collected is relatively large, the original information is recorded by adopting a log file rather than a memory mode. It is popular that the session number, the transaction number, the statement content, the statement execution time consumption and the table information related to the statement are recorded into a log file in a preset file format through a log system. The preset file format may specifically be: each attribute is partitioned by, for example, the character "@ @ @ @" and the table to which the statement relates is partitioned by, for example, the string "@ @ @ > @". Therefore, the subsequent data preprocessing can be facilitated, and the processing efficiency is improved.

In this embodiment, the log file is introduced into the database in the form of an external table, and the external table is converted into a table of the database engine, so that the database can perform subsequent data processing.

And finally, newly adding three attribute columns for updating the session type identifier, the transaction type identifier and the statement type identifier determined by the record in the converted table. The session type ID, transaction type ID, and statement type ID determined based on the above embodiments may then be updated and written in the table, thereby constructing the final structured data relationship model.

On the basis of the above embodiments, in some embodiments of the present disclosure, the method may further include the steps of:

and acquiring a session type identifier of the session to be analyzed.

Specifically, for example, the session type ID of the session to be analyzed is obtained, and based on the session type ID of the session to be analyzed and the structured data relationship model, the session feature information, the transaction feature information, the statement feature information and the data feature information to which the session to be analyzed belongs are queried and obtained. Therefore, the load characteristic information of the database can be extracted more comprehensively and accurately based on the structured data relation model, the subsequent analysis of the load characteristic data is facilitated, and the database is better configured for a database manager, so that the database manager works in an efficient mode to provide more accurate and comprehensive reference.

The technical solution of the embodiment of the present disclosure is described below with reference to a specific embodiment shown in fig. 3. In this embodiment, the method mainly comprises the following three processing flows:

1) And (3) data acquisition flow:

step one: and starting a load characteristic acquisition flow through a function work load_capture_start.

Step two: the upper layer application sends a data request to the database.

Step three: the database responds to the data request, and when each SQL statement is executed, the related session number, transaction number, statement content, statement execution time consumption, statement related table information and the like are recorded into a log file through a log system. Each attribute is characterized by characters "||||" is used to indicate that the current is not equal to the current the division is made so that the number of the divided parts, the sub-attribute of the table information related to the statement is divided by the character string "@ @ @ @" and @ ".

Step four: and ending the acquisition flow through the work load_capture_stop.

2) The pretreatment flow comprises the following steps:

step one: the log file is imported into the database in the form of a sys_log external table.

Step two: the external table is dumped into a table of a database self engine, three attribute columns of a session type ID, a transaction type ID and a statement type ID are newly added into the table, and the table is named as sys_work_qos.

Step three: traversing the record of each SQL statement of the table sys_work_queries, parameterizing the statement content, specifically operating to replace the constant part of the statement content with "? ", e.g.," where id >5 "is replaced with"? And performing HASH calculation on the parameterized content, and updating the obtained value as a statement type ID into a table sys_work load_requests.

Step four: traversing all the transaction numbers in the table sys_workload_requests, performing deduplication operation on statement type IDs belonging to statements in the same transaction number, then merging and processing HASH calculation to obtain HASH values as transaction type IDs, and updating the transaction type IDs into the table sys_workload_requests.

Step five: and traversing all session numbers of the table sys-work load-requests, judging whether the two sessions are the same type of session by comparing the repetition proportion of the de-duplicated transaction type IDs contained in the two sessions one by one to be more than 80%, if so, de-duplicated combining the transaction type IDs of the two sessions, performing HASH calculation, and updating the obtained HASH value as the session type ID into the table.

Step six: the new sys_work load_tables table is built, and the column names comprise table names, table IDs and statement type IDs. The sentence type ID in the sys_work_requests table is duplicated and recorded in the table.

Step seven: after the statement type ID in the table sys_work load_requests is de-duplicated, the statement type ID is recorded in the table sys_work load_tables, if one SQL statement relates to a plurality of tables, the statement is split into a plurality of records, and the table IDs of the plurality of tables are searched through a system table and updated into the sys_work load_tables.

Step eight: and ending the preprocessing flow.

It will be appreciated that after the preprocessing procedure is completed, the structured data relationship model is established.

3) The characteristic extraction flow is as follows:

step one: feature extraction flow is entered through function workload_analysis_report

Step two: and aggregating the number of the sessions, the time consumption of the sessions, the number of the transaction types and the number of the transactions by taking one or more session type IDs as grouping conditions to obtain a session level information result.

Step three: for each session, using the session type ID as a filtering condition, aggregating the execution times, the execution time, the execution statement sequence and the like through the transaction type ID grouping to obtain a transaction level information result.

Step four: and for each session, grouping the execution times and the execution time by using the session type ID as a filtering condition and obtaining a statement level information result through statement type ID grouping.

Step five: for each session, using the session type ID as a filtering condition, obtaining all tables related to the session by connecting statement type IDs in sys_work_queries and sys_work_tables, and obtaining data characteristic information such as table names, table capacity, attribute number, page number, tuple number, statistic information and the like after querying a system table.

Step six: and forming a feature report based on all the extracted load feature information, and ending the feature extraction flow.

In this embodiment, the session type ID is traversed, for each session type ID, transaction level information and statement level information are respectively gathered under the condition of the transaction type ID and the statement type ID, then all tables related to the session are found by using the statement type ID corresponding to the session type ID, and the data characteristic information is obtained by searching the system table.

It should be noted that although the steps of the methods of the present disclosure are illustrated in the accompanying drawings in a particular order, this does not require or imply that the steps must be performed in that particular order or that all of the illustrated steps be performed in order to achieve desirable results. Additionally or alternatively, certain steps may be omitted, multiple steps combined into one step to perform, and/or one step decomposed into multiple steps to perform, etc. In addition, it is also readily understood that these steps may be performed synchronously or asynchronously, for example, in a plurality of modules/processes/threads.

Based on the same concept, the embodiments of the present disclosure provide a database load feature processing apparatus, as shown in fig. 4, the database load feature processing apparatus 40 may include: the data collection module 401 is configured to collect and record statement information, transaction identifier and session identifier related to each SQL statement when execution of each SQL statement is completed; the transaction identifier represents the transaction to which each SQL statement belongs, and the session identifier represents the session to which the transaction to which each SQL statement belongs. A data preprocessing module 402, configured to determine a statement type identifier of each SQL statement according to statement information related to each SQL statement; determining a corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier; and determining the corresponding session type identifier according to the transaction type identifier belonging to the same session identifier. A model building module 403, configured to build a structured data relationship model based on all the determined session type identifiers, transaction type identifiers, and statement type identifiers; the structured data relationship model is used for storing session feature information of each session type, transaction feature information belonging to the session feature information and statement feature information belonging to the transaction feature information.

According to the database load characteristic processing device, the information such as the session type, the transaction type and the statement type reflecting the database operation characteristics is focused, the structural data relation model reflecting the database load characteristic information is established based on the information, and the load characteristic condition reflecting the database can be more accurately and comprehensively described, so that subsequent load characteristic data analysis is facilitated, the database is better configured for a database manager, and more accurate and comprehensive references are provided for the database manager to work in an efficient mode.

In some embodiments of the present disclosure, the data collection module 401 collects and records statement information, transaction identifier and session identifier related to each SQL statement when each SQL statement is executed, which may specifically include: when each SQL sentence is executed, acquiring sentence information, transaction identification and session identification related to each SQL sentence through a log system; wherein the statement information at least comprises one or more of statement content, statement execution time consumption and table information related to the statement.

In some embodiments of the present disclosure, the data preprocessing module 402 determines, according to statement information related to each SQL statement, a statement type identifier of each SQL statement, which may specifically include: parameterizing statement content of each SQL statement, performing hash calculation on the parameterized content, and taking the obtained hash value as statement type identification.

In some embodiments of the present disclosure, the data preprocessing module 402 performs parameterization processing on the statement content of each SQL statement, which may specifically include: and replacing the constant part of the sentence content of each SQL sentence with a preset character.

In some embodiments of the present disclosure, the data preprocessing module 402 determines the corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier, which may specifically include: traversing the related transaction identifications of each SQL sentence to determine the SQL sentences belonging to the same transaction identification; and acquiring statement type identifiers of SQL statements belonging to the same transaction identifier, performing hash calculation on the statement type identifiers, and taking the obtained hash value as the transaction type identifier.

In some embodiments of the present disclosure, the data preprocessing module 402 is further configured to: when a plurality of SQL sentences belonging to the same transaction identifier exist, acquiring a plurality of sentence type identifiers corresponding to the plurality of SQL sentences belonging to the same transaction identifier; removing repeated sentence type identifiers in the sentence type identifiers; and performing hash calculation based on the removed residual statement type identifier, and taking the obtained hash value as a transaction type identifier.

In some embodiments of the present disclosure, the data preprocessing module 402 determines a corresponding session type identifier according to a transaction type identifier belonging to the same session identifier, which may specifically include: traversing the session identifications and the transaction identifications related to each SQL statement to obtain M transaction type identifications belonging to the same session identification; m is a natural number greater than or equal to 2; removing repeated transaction type identifiers in M transaction type identifiers to which the same session identifier belongs to obtain remaining N transaction type identifiers; and carrying out hash calculation based on the rest N transaction type identifiers, and taking the obtained hash value as a session type identifier.

In some embodiments of the present disclosure, the data preprocessing module 402 is further configured to compare the repetition proportion of the remaining N transaction type identifiers to which the two adjacent session identifiers belong one by one, and determine that the session represented by the two session identifiers is the same session when the repetition proportion is greater than a preset proportion threshold; the preset proportion threshold value is more than 80%; removing repeated transaction type identifiers in the remaining 2N transaction type identifiers to which the two session identifiers belong to obtain remaining P transaction type identifiers; and carrying out hash calculation based on the rest P transaction type identifiers, and taking the obtained hash value as a session type identifier of the same session.

In some embodiments of the present disclosure, a data feature acquisition module may be further included to remove repeated sentence type identifiers in sentence type identifiers of all SQL sentences to obtain remaining sentence type identifiers; and acquiring table information related to the SQL statement based on the residual statement type identifier, and acquiring data characteristic information based on the table information. Wherein the data characteristic information at least comprises one or more of table names, table capacity, attribute number, page number, tuple number and statistical information.

Optionally, in some embodiments of the present disclosure, the structured data relationship model may further include data characteristic information attributed to sentence characteristic information. The session feature information may include, but is not limited to, one or more of the number of sessions, session time consumption, number of transaction types, and total number of transactions; the transaction characteristic information can include, but is not limited to, one or more of the number of times of transaction execution, time consumption of transaction execution, and execution statement sequence to which the transaction belongs; the sentence characteristic information may include, but is not limited to, one or more of a number of sentence executions, a sentence execution time consumption, and a sentence content.

In some embodiments of the present disclosure, the system may further include an information conversion processing module, configured to, before determining the statement type identifier of each SQL statement according to the statement information related to each SQL statement, write the obtained statement information, transaction identifier, and session identifier related to each SQL statement into a log file in a preset file format; introducing the log file into a database in the form of an external table, and converting the external table into a table of a database self engine; three attribute columns for updating the session type identifier, the transaction type identifier and the statement type identifier determined by the record are newly added in the converted table.

In some embodiments of the present disclosure, a feature extraction module may be further included to obtain a session type identifier of a session to be analyzed; and inquiring and acquiring one or more of session characteristic information, transaction characteristic information, statement characteristic information and data characteristic information of the session to be analyzed based on the session type identification of the session to be analyzed and the structured data relation model.

The specific manner in which the respective modules perform the operations and the corresponding technical effects thereof have been described in corresponding detail in relation to the embodiments of the method in the above embodiments, and will not be described in detail herein.

It should be noted that although in the above detailed description several modules or units of a device for action execution are mentioned, such a division is not mandatory. Indeed, the features and functionality of two or more modules or units described above may be embodied in one module or unit in accordance with embodiments of the present disclosure. Conversely, the features and functions of one module or unit described above may be further divided into a plurality of modules or units to be embodied. The components shown as modules or units may or may not be physical units, may be located in one place, or may be distributed across multiple network elements. Some or all of the modules can be selected according to actual needs to achieve the purpose of the wood disclosure scheme. Those of ordinary skill in the art will understand and implement the present invention without undue burden.

The disclosed embodiments also provide a computer readable storage medium having stored thereon a computer program which, when executed by a processor, implements the steps of the database load feature processing method of any of the embodiments described above.

By way of example, the readable storage medium can be, for example, but not limited to, an electronic, magnetic, optical, electromagnetic, infrared, or semiconductor system, apparatus, or device, or a combination of any of the foregoing. More specific examples (a non-exhaustive list) of the readable storage medium would include the following: an electrical connection having one or more wires, a portable disk, a hard disk, random Access Memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or flash memory), optical fiber, portable compact disk read-only memory (CD-ROM), an optical storage device, a magnetic storage device, or any suitable combination of the foregoing.

The computer readable storage medium may include a data signal propagated in baseband or as part of a carrier wave, with readable program code embodied therein. Such a propagated data signal may take any of a variety of forms, including, but not limited to, electro-magnetic, optical, or any suitable combination of the foregoing. A readable storage medium may also be any readable medium that can communicate, propagate, or transport a program for use by or in connection with an instruction execution system, apparatus, or device. Program code embodied on a readable storage medium may be transmitted using any appropriate medium, including but not limited to wireless, wireline, optical fiber cable, RF, etc., or any suitable combination of the foregoing.

The embodiment of the disclosure also provides an electronic device such as a database server, which comprises a processor and a memory, wherein the memory is used for storing executable instructions of the processor. Wherein the processor is configured to perform the steps of the database load feature processing method of any of the embodiments described above via execution of the executable instructions.

An electronic device 600 according to this embodiment of the invention is described below with reference to fig. 5. The electronic device 600 shown in fig. 5 is merely an example, and should not be construed as limiting the functionality and scope of use of embodiments of the present invention.

As shown in fig. 5, the electronic device 600 is embodied in the form of a general purpose computing device. Components of electronic device 600 may include, but are not limited to: at least one processing unit 610, at least one memory unit 620, a bus 630 connecting the different system components (including the memory unit 620 and the processing unit 610), a display unit 640, etc.

Wherein the storage unit stores program code that is executable by the processing unit 610 such that the processing unit 610 performs the steps according to various exemplary embodiments of the present invention described in the database load characteristic processing method section above in the present specification. For example, the processing unit 610 may perform the steps of the method as shown in fig. 1.

The memory unit 620 may include readable media in the form of volatile memory units, such as Random Access Memory (RAM) 6201 and/or cache memory unit 6202, and may further include Read Only Memory (ROM) 6203.

The storage unit 620 may also include a program/utility 6204 having a set (at least one) of program modules 6205, such program modules 6205 including, but not limited to: an operating system, one or more application programs, other program modules, and program data, each or some combination of which may include an implementation of a network environment.

Bus 630 may be a local bus representing one or more of several types of bus structures including a memory unit bus or memory unit controller, a peripheral bus, an accelerated graphics port, a processing unit, or using any of a variety of bus architectures.

The electronic device 600 may also communicate with one or more external devices 700 (e.g., keyboard, pointing device, bluetooth device, etc.), one or more devices that enable a user to interact with the electronic device 600, and/or any device (e.g., router, modem, etc.) that enables the electronic device 600 to communicate with one or more other computing devices. Such communication may occur through an input/output (I/O) interface 650. Also, electronic device 600 may communicate with one or more networks such as a Local Area Network (LAN), a Wide Area Network (WAN), and/or a public network, such as the Internet, through network adapter 660. The network adapter 660 may communicate with other modules of the electronic device 600 over the bus 630. It should be appreciated that although not shown, other hardware and/or software modules may be used in connection with electronic device 600, including, but not limited to: microcode, device drivers, redundant processing units, external disk drive arrays, RAID systems, tape drives, data backup storage systems, and the like.

From the above description of embodiments, those skilled in the art will readily appreciate that the example embodiments described herein may be implemented in software, or may be implemented in software in combination with the necessary hardware. Thus, the technical solution according to the embodiments of the present disclosure may be embodied in the form of a software product, which may be stored in a non-volatile storage medium (may be a CD-ROM, a usb disk, a mobile hard disk, etc.) or on a network, and includes several instructions to cause a computing device (may be a personal computer, a server, or a network device, etc.) to perform the above-mentioned database load feature processing method according to the embodiments of the present disclosure.

It should be noted that, in this document, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising one … …" does not exclude the presence of other like elements in a process, method, article, or apparatus that comprises the element.

The foregoing is merely a specific embodiment of the disclosure to enable one skilled in the art to understand or practice the disclosure. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the disclosure. Thus, the present disclosure is not intended to be limited to the embodiments shown and described herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.

Claims

1. A method for processing load characteristics of a database, comprising:

2. The method for processing the load characteristics of the database according to claim 1, wherein collecting and recording statement information, transaction identifications and session identifications related to each SQL statement when each SQL statement is executed, comprises:

3. The method for processing the load characteristics of the database according to claim 2, wherein determining the statement type identifier of each SQL statement according to the statement information related to each SQL statement comprises:

4. The method for processing the load characteristics of the database according to claim 3, wherein parameterizing the statement content of each SQL statement comprises:

5. The method for processing the load characteristics of the database according to claim 1, wherein the determining the corresponding transaction type identifier according to the statement type identifier of the SQL statement belonging to the same transaction identifier comprises:

6. The database load characteristic processing method according to claim 5, further comprising:

removing repeated sentence type identifiers in the sentence type identifiers;

7. The method for processing the load characteristics of the database according to claim 1, wherein the determining the corresponding session type identifier according to the transaction type identifier belonging to the same session identifier includes:

8. The database load characteristic processing method according to claim 7, further comprising:

9. The database load characteristic processing method according to claim 1, further comprising:

10. The method of claim 9, wherein the structured data relationship model further comprises data characteristic information attributed to sentence characteristic information; the session feature information comprises one or more of session number, session time consumption, transaction type number and transaction total number; the transaction characteristic information comprises one or more of transaction execution times, transaction execution time consumption and execution statement sequences to which the transaction belongs; the sentence characteristic information includes one or more of a number of sentence executions, a time consuming sentence execution, and sentence contents.

11. The method according to any one of claims 1 to 10, further comprising, before determining the statement type flag of each SQL statement from the statement information related to each SQL statement:

12. The database load characteristic processing method according to claim 10, further comprising:

acquiring a session type identifier of a session to be analyzed;

13. A database load characteristic processing device is characterized in that,

14. A computer-readable storage medium, on which a computer program is stored, characterized in that the computer program, when being executed by a processor, implements the steps of the database load characteristic processing method according to any one of claims 1 to 12.

15. An electronic device, comprising:

a processor; and

a memory for storing executable instructions of the processor;

wherein the processor is configured to perform the steps of the database load feature processing method of any of claims 1 to 12 via execution of the executable instructions.