A real-time data collection method for database

Abstract

The present invention relates to the field of data acquisition technology, and in particular to a real-time acquisition method for a database, comprising: determining a database state based on an abnormal frequency flow coefficient and a compatible influence coefficient, and determining a tuning setting method according to the database state; determining a tuning statement category according to a high-frequency execution coefficient and a sensitive bottleneck threshold, and performing tuning on high-frequency impact statements; determining a data partitioning method for a change log to be analyzed to obtain a plurality of data segments according to a tuning comparison coefficient and a dynamic load intensity; determining a data segment set according to a distribution adaptability, and determining a distribution method for each data segment set according to a segment difference degree and a pre-delay coefficient; and determining an optimization method according to a delay coefficient and an adjustment comparison coefficient. The present invention improves the efficiency of change data acquisition.

Description

Technical Field

[0001] The present invention relates to the technical field of data acquisition, and in particular to a real-time acquisition method for a database. Background Art

[0002] With the increasing complexity of business systems and the exponential growth of data volume, data changes in databases face severe problems such as collection lag, synchronization delay, and lack of real-time performance. As a result, downstream systems are unable to reflect dynamic business changes in a timely and accurate manner. Therefore, how to improve the efficiency of change data collection is a technical problem that technical personnel in this field urgently need to solve.

[0003] Chinese Patent Publication No. CN114547045A discloses a data collection and storage method based on a real-time database, including: S1. Launching antivirus software to scan the database and confirming that there is no malware or virus, then launching the real-time database device data source; S2. Periodically collecting data through a Java project using a RESTful interface; S3. After collecting the interface data, storing it in a real-time data table, an N-hour data table, and a historical data table. This technical solution has the following problems: it cannot accurately tune statements in the database, and it is difficult to overcome database performance bottlenecks when facing complex queries or high-concurrency scenarios, resulting in poor change data collection efficiency. Summary of the Invention

[0004] To this end, the present invention provides a real-time collection method for a database to overcome the problem in the existing technology that it is impossible to accurately tune the statements in the database. When facing complex queries or high concurrency scenarios, the database performance bottleneck is difficult to break through, which leads to poor efficiency in change data collection.

[0005] To achieve the above object, the present invention provides a real-time data collection method for a database, comprising:

[0006] The database status is determined based on the abnormal frequency flow coefficient and the compatibility impact coefficient. The tuning setting method is determined based on the database status, such as triggered tuning based on the trigger impact coefficient or cyclic tuning based on the performance load coefficient.

[0007] Determine the tuning statement category based on the high-frequency execution coefficient and sensitive bottleneck threshold, and perform tuning on high-frequency impact statements;

[0008] Determine the data partitioning method of the change log to be analyzed based on the tuning comparison coefficient and dynamic load intensity to obtain several data segments. The data partitioning method is uniform segmentation based on the evaluation deviation index, or dynamic segmentation is determined based on the attribute fixity and range coordination coefficient.

[0009] The dynamic division method is to perform association division based on attribute similarity and attribute representation value, or to perform multi-dimensional division based on influence similarity and hash similarity;

[0010] Determine a data segment set according to the distribution adaptability, and determine the distribution mode of each data segment set as multi-path parallel distribution or single-path interval distribution according to the segment difference and the pre-delay coefficient;

[0011] The optimization method is determined according to the delay coefficient and the adjustment comparison coefficient to adjust the transmission interval or the priority adjustment method is determined according to the priority pre-adjustment coefficient. The priority adjustment method is partial priority replacement or overall priority replacement.

[0012] Furthermore, if the database status is that the abnormal frequency flow coefficient is greater than or equal to the preset abnormal frequency flow coefficient or the compatible impact coefficient is greater than or equal to the preset compatible impact coefficient, the tuning setting method is to perform cyclic tuning according to the performance load coefficient.

[0013] Furthermore, if the database status is that the abnormal frequency flow coefficient is less than the preset abnormal frequency flow coefficient and the compatible influence coefficient is less than the preset compatible influence coefficient, the tuning setting method is to perform triggered tuning according to the trigger influence coefficient.

[0014] Furthermore, the tuning statement category is determined based on the high-frequency execution coefficient and the sensitive bottleneck threshold. The tuning statement categories include:

[0015] High-frequency impact statements whose high-frequency execution coefficient is greater than or equal to the preset high-frequency execution coefficient or whose sensitive bottleneck threshold is greater than or equal to the preset sensitive bottleneck threshold;

[0016] Low-frequency impact statements whose high-frequency execution coefficient is less than a preset high-frequency execution coefficient and whose sensitive bottleneck threshold is less than a preset sensitive bottleneck threshold.

[0017] Furthermore, the data partitioning method of the change log to be analyzed is determined based on the tuning comparison coefficient and the dynamic load intensity, including:

[0018] If the tuning comparison coefficient is less than the preset tuning comparison coefficient and the dynamic load intensity is less than the preset dynamic load intensity, the data division method is to divide the data into uniform segments according to the evaluation deviation index;

[0019] If the tuning comparison coefficient is greater than or equal to the preset tuning comparison coefficient or the dynamic load intensity is greater than or equal to the preset dynamic load intensity, the data division method is to determine the dynamic division method based on the attribute fixity and the range coordination coefficient.

[0020] Furthermore, if the attribute fixity is greater than or equal to the preset attribute fixity and the range coordination coefficient is greater than or equal to the preset range coordination coefficient, the dynamic division method is to perform association division according to the attribute similarity and the attribute representation value.

[0021] Furthermore, if the attribute fixity is less than the preset attribute fixity or the range coordination coefficient is less than the preset range coordination coefficient, the dynamic division method is to perform multi-dimensional division according to the impact similarity and the hash similarity.

[0022] Furthermore, the distribution mode of each data segment set is determined according to the segment difference and the pre-delay coefficient, including:

[0023] For a single data fragment collection,

[0024] If the segment difference is greater than or equal to the preset segment difference or the pre-delay coefficient is greater than or equal to the preset pre-delay coefficient, the distribution mode is multi-path parallel distribution;

[0025] If the segment difference is less than the preset segment difference and the pre-delay coefficient is less than the preset pre-delay coefficient, the segment distribution mode is single-path interval distribution.

[0026] Furthermore, if the delay coefficient is greater than or equal to the preset delay coefficient or the adjustment comparison coefficient is greater than or equal to the preset adjustment comparison coefficient, the optimization method is to determine the priority adjustment method according to the priority pre-adjustment coefficient;

[0027] If the priority pre-adjustment coefficient is less than the preset priority pre-adjustment coefficient, the priority adjustment method is partial priority replacement;

[0028] If the priority pre-adjustment coefficient is greater than or equal to the preset priority pre-adjustment coefficient, the priority adjustment method is overall priority replacement.

[0029] Furthermore, if the delay coefficient is less than the preset delay coefficient and the adjustment comparison coefficient is less than the preset adjustment comparison coefficient, the optimization method is to reduce the adjustment for the aggregate transmission interval;

[0030] The reduction value of the aggregate transmission interval is negatively correlated with the abnormality assessment coefficient.

[0031] Compared with the prior art, the beneficial effect of the present invention lies in that, in the technical solution of the present invention, the database status is determined based on the abnormal frequency flow coefficient and the compatible influence coefficient, and the abnormal condition of the database is effectively reflected by the abnormal frequency flow coefficient and the compatible influence coefficient, and then different tuning setting methods are adaptively selected according to the database status, so that the selection of the tuning setting method is more in line with the actual application scenario, which helps to accurately locate the problem and improve the tuning efficiency. The importance of the SQL statement is effectively reflected by the high-frequency execution coefficient and the sensitive bottleneck threshold, and then the high-frequency impact statement is tuned, which can reduce redundant or repeated data changes while improving data processing efficiency, thereby improving the efficiency of change data collection.

[0032] Furthermore, the present invention effectively reflects the tuning comparison status and load conditions by tuning the comparison coefficient and dynamic load strength, and then adaptively selects different data partitioning methods according to the tuning comparison coefficient and dynamic load strength. Uniform segmentation according to the evaluation deviation index can ensure the balance of data segments, reduce segmentation overhead, and avoid resource waste. Determining the dynamic partitioning method according to the attribute fixity and range coordination coefficient can reduce data redundancy and improve analysis efficiency.

[0033] Furthermore, the present invention effectively reflects the potential correlation between data in the database through attribute fixity and range coordination coefficient, and then adaptively selects different dynamic division methods according to the attribute fixity and range coordination coefficient, so that the dynamic division method can adapt to data changes and improve the efficiency of change data collection.

[0034] Furthermore, the present invention determines the distribution method of each data segment set based on the segment difference and the pre-delay coefficient. Through multi-path parallel distribution, it can fully utilize network bandwidth resources and significantly improve data transmission rate and system throughput. Through single-path interval distribution, it can avoid path congestion, ensure orderly data transmission, reduce transmission interruptions caused by path failures, and facilitate timely transmission of data to the change analysis node for data change analysis, thereby improving the efficiency of change data collection. BRIEF DESCRIPTION OF THE DRAWINGS

[0035] Figure 1 Schematic diagram of the real-time acquisition method for a database according to the present invention;

[0036] Figure 2 This is a flow chart of the present invention for determining a tuning setting method according to a database status;

[0037] Figure 3 This is a flow chart of the present invention for determining a data partitioning method based on an optimized comparison coefficient and dynamic load intensity;

[0038] Figure 4 This is a flow chart of the present invention for determining a fragment distribution method based on fragment difference and pre-delay coefficient. DETAILED DESCRIPTION

[0039] In order to make the objects and advantages of the present invention more clearly understood, the present invention is further described below in conjunction with embodiments; it should be understood that the specific embodiments described herein are merely used to explain the present invention and are not intended to limit the present invention.

[0040] The preferred embodiments of the present invention are described below with reference to the accompanying drawings. It should be understood by those skilled in the art that these embodiments are only used to explain the technical principles of the present invention and are not intended to limit the scope of protection of the present invention.

[0041] It should be noted that, in the description of the present invention, terms such as "up", "down", "left", "right", "inside", and "outside" indicating directions or positional relationships are based on the directions or positional relationships shown in the accompanying drawings. This is only for the convenience of description and does not indicate or imply that the device or element must have a specific orientation, be constructed and operated in a specific orientation. Therefore, it cannot be understood as a limitation on the present invention.

[0042] Furthermore, it should be noted that, in the description of the present invention, unless otherwise expressly specified or limited, the terms "mounted," "connected," and "connected" should be understood in a broad sense. For example, they may refer to fixed connections, detachable connections, or integral connections; mechanical connections or electrical connections; direct connections or indirect connections through an intermediate medium; and internal communication between two components. Those skilled in the art will understand the specific meanings of the above terms in the present invention based on specific circumstances.

[0043] See also Figures 1 to 4 As shown, the present invention provides a real-time acquisition method for a database, comprising:

[0044] The database status is determined based on the abnormal frequency flow coefficient and the compatibility impact coefficient. The tuning setting method is determined based on the database status, such as triggered tuning based on the trigger impact coefficient or cyclic tuning based on the performance load coefficient.

[0045] Determine the tuning statement category based on the high-frequency execution coefficient and sensitive bottleneck threshold, and perform tuning on high-frequency impact statements;

[0046] Determine the data partitioning method of the change log to be analyzed based on the tuning comparison coefficient and dynamic load intensity to obtain several data segments. The data partitioning method is uniform segmentation based on the evaluation deviation index, or dynamic segmentation is determined based on the attribute fixity and range coordination coefficient.

[0047] The dynamic division method is to perform association division based on attribute similarity and attribute representation value, or to perform multi-dimensional division based on influence similarity and hash similarity;

[0048] Determine a data segment set according to the distribution adaptability, and determine the distribution mode of each data segment set as multi-path parallel distribution or single-path interval distribution according to the segment difference and the pre-delay coefficient;

[0049] The optimization method is determined according to the delay coefficient and the adjustment comparison coefficient to adjust the transmission interval or the priority adjustment method is determined according to the priority pre-adjustment coefficient. The priority adjustment method is partial priority replacement or overall priority replacement.

[0050] The application scenario of the present invention is the collection of database change data. In the present invention, the database contains several SQL statements, and a single SQL statement corresponds to a log. The SQL statement is responsible for defining the read and write operations on the database, and the log records the detailed information of the read and write operations; the log records the execution process and results of the SQL statement. The log includes but is not limited to the execution timestamp of the SQL statement, the specific content of the SQL statement, and the execution result of the SQL statement. The change log is a log of insertion, update, or deletion in the database captured by Debezium. A single change log contains several timestamps, each timestamp corresponds to a log interval, indicating that all log lines in the log interval are inserted, updated, or deleted at that time point. A single log interval contains several log lines, which is easy for those skilled in the art to understand and will not be described in detail.

[0051] The present invention provides several historical records, and any one of the historical records records the sub-abnormal frequency flow value, abnormal frequency flow coefficient, compatible impact coefficient, trigger impact coefficient, high-frequency execution coefficient, and sensitive bottleneck threshold value in the historical process of at least one database change data collection, and each historical record corresponds to a qualified mark, which records whether the process of database change data collection meets user requirements. The qualified mark can be recorded manually. It can be understood that the user can determine whether the process of database change data collection meets the requirements based on self-set indicators. The self-set indicators can be but not limited to the transmission index, which will not be elaborated here. The transmission index is the time length for transmitting the change log to the change analysis node;

[0052] The present invention includes several transmission paths and a change analysis node. Data segments can be transmitted to the change analysis node via the transmission paths. The change analysis node can extract key information from the change log, identify potential risks, and provide data support for subsequent decision-making. The specific extraction method is easy to understand for those skilled in the art and will not be described in detail.

[0053] When tuning frequently impacted statements, methods available to users include, but are not limited to, delaying the writing of non-critical data, using temporary tables or intermediate tables, and optimizing indexes. Users can choose these methods based on their actual needs. These are commonly used techniques by those skilled in the art and will not be detailed here.

[0054] Determining a data segment set based on the distribution fitness includes: performing a combined analysis on each data segment, when performing the combined analysis on a single data segment, recording the data segment as a target data segment, recording data segments other than the target data segment that are not recorded in the data segment set as reference data segments, recording the set of the reference data segments that have a distribution fitness with the target data segment greater than a preset distribution fitness and the target data segment as a data segment set, and continuing to perform the combined analysis on each data segment that is not recorded in the data segment set until all data segments are recorded in the data segment set, then stopping the combined analysis;

[0055] It should be noted that the transmission priority coefficient of a single data segment set is positively correlated with the transmission demand coefficient corresponding to the data segment set. The larger the transmission priority coefficient of the data segment set, the higher the priority of the transmission order.

[0056] The transmission demand coefficient is confirmed by, for a single data segment set, recording the data segment set as the target set, recording other data segment sets outside the target set as reference sets, recording the keywords appearing in each data segment in the target set as reference words, and the transmission demand coefficient corresponding to the target set is the average of the segment reference values corresponding to each reference word. The segment reference value corresponding to a single reference word is the number of reference sets in which the reference word appears.

[0057] The distribution adaptability is confirmed in the following way: for any two data segments, the distribution adaptability corresponding to the two data segments = 1-(the absolute value of the difference between the sub-distribution coefficients corresponding to the two data segments / the larger value of the sub-distribution coefficients corresponding to the two data segments), and the sub-distribution coefficient corresponding to a single data segment = the number of different keywords contained in the data segment / the number of log lines contained in the data segment; the value of the preset distribution adaptability can be determined by the user based on the actual application scenario. The greater the user's demand for data distribution efficiency, the greater the value of the preset distribution adaptability. A value of the preset distribution adaptability is provided, and the preset distribution adaptability is 70%.

[0058] Specifically, if the database status is that the abnormal frequency flow coefficient is greater than or equal to the preset abnormal frequency flow coefficient or the compatible impact coefficient is greater than or equal to the preset compatible impact coefficient, the tuning setting method is to perform cyclic tuning according to the performance load coefficient.

[0059] The present invention provides a continuously circulating tuning setting cycle. At the end of each tuning setting cycle, the database status is determined once. The duration of the tuning setting cycle can be set according to user needs. The greater the user's demand for tuning accuracy, the shorter the duration of the tuning setting cycle. A value of the tuning setting cycle is provided, and the tuning setting cycle is 1d. The tuning setting cycle adjacent to and before the current tuning setting cycle is recorded as the target tuning setting cycle.

[0060] The database state includes a first database state and a second database state. The first database state is that the abnormal frequency flow coefficient is greater than or equal to the preset abnormal frequency flow coefficient or the compatible influence coefficient is greater than or equal to the preset compatible influence coefficient. The second database state is that the abnormal frequency flow coefficient is less than the preset abnormal frequency flow coefficient and the compatible influence coefficient is less than the preset compatible influence coefficient.

[0061] The abnormal frequency flow coefficient is the average value of the sub-abnormal frequency flow values corresponding to each historical record. The sub-abnormal frequency flow value is confirmed by, for a single historical record, recording the historical record as the target historical record, recording other historical records other than the target historical record as reference historical records, and recording the average value of the abnormal interaction degree corresponding to the target historical record and each reference historical record as the sub-abnormal frequency flow value corresponding to the target historical record. The abnormal interaction degree is confirmed by, for any two historical records, detecting the hash value of the data fragment corresponding to each change data in the two historical records respectively. The abnormal interaction degree corresponding to the two historical records = the number of hash values that are the same in one historical record and the other historical record / the larger value of the number of data fragments corresponding to each change data in the two historical records; the hash value corresponding to a single data fragment can be mapped to a string by a hash function. Hash functions include but are not limited to MD5, SHA-1, and SHA-256. Users can choose according to actual needs and there are no specific restrictions.

[0062] Compatibility impact coefficient = (first impact value - second impact value) / first impact value. Record the historical records where the sub-abnormal frequency flow value is greater than or equal to the preset sub-abnormal frequency flow value as the first historical records, and record the historical records where the sub-abnormal frequency flow value is less than the preset sub-abnormal frequency flow value as the second historical records. The first impact value is the average capture time corresponding to each first historical record, and the second impact value is the average capture time corresponding to each second historical record. The capture time corresponding to a single historical record is the time taken by the change analysis node after the data segment in the historical record is transferred to the change analysis node.

[0063] The values of the preset abnormal frequency flow coefficient, the preset compatible impact coefficient and the preset sub-abnormal frequency flow value can be determined by the user according to the actual application scenario. The greater the user's demand for improving the efficiency of change data capture, the larger the values of the preset abnormal frequency flow coefficient and the preset compatible impact coefficient. A value of the preset abnormal frequency flow coefficient and the preset compatible impact coefficient is provided. The historical records of the user's cyclic tuning according to the performance load factor are detected, and the average value of the abnormal frequency flow coefficient corresponding to the historical records that can meet the user's needs is recorded as the preset abnormal frequency flow coefficient, and the average value of the compatible impact coefficient corresponding to the historical records that can meet the user's needs is recorded as the preset compatible impact coefficient. The larger the value of the preset sub-abnormal frequency flow value, the greater the user's demand for determining the historical record as the second historical record. A value of the preset sub-abnormal frequency flow value is provided as 40%;

[0064] Perform cyclic tuning based on the performance load factor, including setting a continuous tuning cycle. At the end of each tuning cycle, tune the frequently affected statements in the database. The duration of a single tuning cycle is negatively correlated with the performance load factor.

[0065] Performance load factor = (abnormal frequency coefficient + compatibility impact coefficient) × number of database requests processed during the target tuning period. The number of database requests processed during the target tuning period is the total number of query, update, insert, delete, and other operations received by the database from applications, users, or other systems during the target tuning period.

[0066] Specifically, if the database status is that the abnormal frequency flow coefficient is less than the preset abnormal frequency flow coefficient and the compatible impact coefficient is less than the preset compatible impact coefficient, the tuning setting method is to perform triggered tuning according to the trigger impact coefficient.

[0067] The trigger-type tuning is performed according to the trigger influence coefficient, including: when the trigger influence coefficient is greater than the preset trigger influence coefficient, tuning the high-frequency impact statements in the database;

[0068] The trigger impact coefficient = the subrequest coefficient corresponding to the current moment × the surge coefficient. The subrequest coefficient corresponding to a single moment is the number of query, update, insert, and delete operations received from applications, users, or other systems at that moment. The surge coefficient = (the subrequest coefficient corresponding to the current moment - the average of the subrequest coefficients corresponding to the reference moments before the current moment) / the average of the subrequest coefficients corresponding to the reference moments before the current moment. Reference moments are set by the user. A method for setting reference moments is provided. Each minute is recorded as a reference moment, following the current tuning cycle from earliest to latest.

[0069] The value of the preset trigger impact coefficient can be determined by the user according to the actual application scenario. The greater the user's demand for improving the efficiency of change data capture, the smaller the value of the preset trigger impact coefficient. A value of the preset trigger impact coefficient is provided, and the historical records of users performing triggered tuning based on the trigger impact coefficient are detected. The average value of the trigger impact coefficients corresponding to the moments of tuning in the historical records that can meet the user's needs is recorded as the preset trigger impact coefficient.

[0070] Specifically, the tuning statement category is determined based on the high-frequency execution coefficient and the sensitive bottleneck threshold. The tuning statement categories include:

[0071] High-frequency impact statements whose high-frequency execution coefficient is greater than or equal to the preset high-frequency execution coefficient or whose sensitive bottleneck threshold is greater than or equal to the preset sensitive bottleneck threshold;

[0072] Low-frequency impact statements whose high-frequency execution coefficient is less than a preset high-frequency execution coefficient and whose sensitive bottleneck threshold is less than a preset sensitive bottleneck threshold.

[0073] The high-frequency execution coefficient corresponding to a single SQL statement = the number of times the SQL statement is executed within the target tuning period / the sum of the number of times each SQL statement is executed within the target tuning period. Executing an SQL statement means interacting with the database to perform operations such as updating, inserting, or deleting data.

[0074] Sensitive bottleneck threshold = execution time × number of scanned rows. The execution time of a single SQL statement is the total time from the start to the completion of the SQL statement. The number of scanned rows of a single SQL statement is the number of row records in the underlying data table or index actually read or checked during the execution of the SQL statement. The number of scanned rows can be viewed through MySQL.

[0075] The values of the preset high-frequency execution coefficient and the preset sensitive bottleneck threshold can be determined by the user according to the actual application scenario. The greater the user's demand for improving the efficiency of change data collection, the smaller the values of the preset high-frequency execution coefficient and the preset sensitive bottleneck threshold. A value of the preset high-frequency execution coefficient and the preset sensitive bottleneck threshold is provided, and the average value of the high-frequency execution coefficient corresponding to the high-frequency impact statements in the historical records that can meet the user's needs is recorded as the preset high-frequency execution coefficient, and the average value of the sensitive bottleneck threshold corresponding to the high-frequency impact statements in the historical records that can meet the user's needs is recorded as the preset sensitive bottleneck threshold.

[0076] Specifically, the data division method of the change log to be analyzed is determined based on the tuning comparison coefficient and dynamic load intensity, including:

[0077] If the tuning comparison coefficient is less than the preset tuning comparison coefficient and the dynamic load intensity is less than the preset dynamic load intensity, the data division method is to divide the data into uniform segments according to the evaluation deviation index;

[0078] If the tuning comparison coefficient is greater than or equal to the preset tuning comparison coefficient or the dynamic load intensity is greater than or equal to the preset dynamic load intensity, the data division method is to determine the dynamic division method based on the attribute fixity and the range coordination coefficient.

[0079] The change log to be analyzed is the change log captured by Debezium at the current moment;

[0080] The tuning comparison coefficient = the number of high-frequency impact statements at the current moment / the number of low-frequency impact statements at the current moment; the dynamic load intensity = log data volume + log append frequency, where the log data volume is the number of change logs to be analyzed at the current moment, and the log append frequency is the number of change logs to be analyzed that have new log records written to the end of the log at the current moment.

[0081] The values of the preset tuning comparison coefficient and the preset dynamic load strength can be determined by the user according to the actual application scenario. The larger the values of the preset tuning comparison coefficient and the preset dynamic load strength, the greater the user's demand for uniform segmentation according to the evaluation deviation index. A value of a preset tuning comparison coefficient and a preset dynamic load strength is provided, and the historical records of the user performing uniform segmentation according to the evaluation deviation index are detected. The average value of the tuning comparison coefficients corresponding to the historical records that can meet the user's needs is recorded as the preset tuning comparison coefficient, and the average value of the dynamic load strength corresponding to the historical records that can meet the user's needs is recorded as the preset dynamic load strength;

[0082] Performing uniform segmentation based on the evaluation deviation index includes: uniformly dividing the change log to be analyzed into n1 data segments, where n1 is negatively correlated with the evaluation deviation index; when performing uniform segmentation of the change log to be analyzed, starting from the initial log line of the change log to be analyzed, the log lines are sequentially allocated to the data segments in a front-to-back order, and the number of log lines contained in a single data segment is a minimum integer less than or equal to k1, where k1 = the number of log lines contained in the change log to be analyzed / n1. It should be noted that if k1 is not an integer, the last data segment will contain the remaining log lines; a single change log to be analyzed contains a number of lines, and each line of the change log to be analyzed is recorded as a log line;

[0083] Evaluation deviation index = tuning comparison coefficient × dynamic load intensity.

[0084] Specifically, if the attribute fixity is greater than or equal to the preset attribute fixity and the range coordination coefficient is greater than or equal to the preset range coordination coefficient, the dynamic division method is to perform association division based on attribute similarity and attribute representation value.

[0085] Among them, the method of confirming the attribute fixity is as follows: for a single change log to be analyzed, the change log to be analyzed is recorded as the target log, the keywords in the target log whose frequency coefficient is greater than the preset frequency coefficient are recorded as frequency words, and the average value of the distribution reference values corresponding to each frequency word is recorded as the attribute fixity corresponding to the target log; for a single frequency word, the frequency word is recorded as the frequency word to be analyzed, the log line containing the frequency word to be analyzed is recorded as the analysis line, and the average value of the sub-distribution coefficient corresponding to each analysis line is recorded as the distribution reference value corresponding to the frequency word to be analyzed; the sub-distribution coefficient corresponding to a single analysis line is the average value of the row intervals from the analysis line to other analysis lines, and the row interval corresponding to any two analysis lines is the number of log lines between the two analysis lines;

[0086] The frequency coefficient corresponding to a single keyword is the number of times the keyword appears in the target log. The value of the preset frequency coefficient can be determined by the user according to the actual application scenario. The smaller the value of the preset frequency coefficient, the greater the user's demand for determining the keyword as a frequency word. A value of the preset frequency coefficient is provided, and the average value of the frequency coefficients corresponding to each frequency word in the historical records that can meet the user's needs is recorded as the preset frequency coefficient;

[0087] The range coordination coefficient is determined as follows: for a single change log to be analyzed, the range coordination coefficient corresponding to the change log to be analyzed is 1-[the standard deviation of the operation time of the log interval corresponding to each timestamp in the change log to be analyzed × (the length of time between the earliest time and the latest time corresponding to each timestamp in the change log to be analyzed / the number of timestamps in the change log to be analyzed)]. The operation time of the log interval corresponding to a single timestamp is the duration of the insert, update, or delete operation in the log interval corresponding to the timestamp.

[0088] The values of the preset attribute fixity and the preset range coordination coefficient can be determined by the user according to the actual application scenario. The smaller the values of the preset attribute fixity and the preset range coordination coefficient, the greater the user's demand for association division based on attribute similarity and attribute representation value. A value of the preset attribute fixity and the preset range coordination coefficient is provided, and the historical records of the user's association division based on attribute similarity and attribute representation value are detected. The average value of the attribute fixity corresponding to the historical records that can meet the user's needs is recorded as the preset attribute fixity, and the average value of the range coordination coefficient corresponding to the historical records that can meet the user's needs is recorded as the preset range coordination coefficient;

[0089] Performing association division based on attribute similarity and attribute representation value, including: for a single change log to be analyzed, evenly dividing the change log to be analyzed into n2 log segments, n2 having a positive correlation with the attribute representation value corresponding to the change log to be analyzed, performing association analysis on each log segment, when performing association analysis on a single log segment, recording the log segment as a target log segment, recording log segments other than the target log segment that are not recorded in the data segment as reference log segments, recording the set of each reference log segment having an attribute similarity with the target log segment greater than a preset attribute similarity and the target log segment as a data segment, and continuing to perform association analysis on the log segments that are not recorded in the data segment until all log segments are recorded in the data segment, then stopping the association analysis;

[0090] When evenly dividing the change log to be analyzed into n2 log sections, starting from the initial log line of the change log to be analyzed, log lines are assigned to log sections in order from the front to the back. The number of log lines contained in a single log section is the smallest integer less than or equal to k2, where k = the number of log lines contained in the change log to be analyzed / n2. Note that if k2 is not an integer, the last log section will contain the remaining log lines.

[0091] Attribute representation value = attribute fixity × range coordination coefficient. Attribute similarity is determined by: for any two log segments, attribute similarity = number of keywords present in both log segments / length of time between the earliest and latest timestamps of the two log segments.

[0092] The value of the preset attribute similarity can be determined by the user according to the actual application scenario. The greater the user's demand for improving the accuracy of data segment division, the greater the value of the preset attribute similarity. A value of the preset attribute similarity is provided, and the historical records that are associated and divided according to attribute similarity and attribute representation value are detected. The average value of the reference attribute similarity corresponding to each data segment in the historical records that can meet the user's needs is recorded as the preset attribute similarity. The reference attribute similarity corresponding to a single data segment is the attribute similarity corresponding to any two log paragraphs in the data segment.

[0093] Specifically, if the attribute fixity is less than the preset attribute fixity or the range coordination coefficient is less than the preset range coordination coefficient, the dynamic division method is to perform multi-dimensional division according to the impact similarity and the hash similarity.

[0094] The multi-dimensional partitioning is performed based on the impact similarity and the hash similarity, including: for a single change log to be analyzed, partitioning and analyzing each log line of the change log to be analyzed; when partitioning and analyzing a single log line, recording the log line as a target log line, recording other log lines other than the target log line that are not recorded in the data fragment as reference log lines, recording a set of each reference log line having an impact similarity with the target log line greater than a preset impact similarity and a hash similarity greater than a preset hash similarity and the target log line as a data fragment, and continuing to partition and analyze the log lines that are not recorded in the data fragment until all log lines are recorded in the data fragment, then stopping the partitioning and analysis;

[0095] The method for confirming impact similarity is to record the keywords corresponding to any two log lines as the first keyword and the second keyword respectively, and record the average co-occurrence coefficients corresponding to the first keywords as the impact similarity corresponding to the two log lines. Then, the historical records that are multi-dimensionally divided according to the impact similarity and hash similarity are tested, and each data segment in which the first keyword appears in the historical records that can meet the user's needs is recorded as a reference segment. The co-occurrence coefficient corresponding to a single first keyword = the total number of second keywords that appear in all reference segments / the number of reference segments;

[0096] Hash similarity is determined by calculating the number of identical characters in the strings corresponding to any two log lines divided by the number of characters in the string corresponding to a single log line. The string corresponding to a single log line is the string mapped to the log line by the user using the MD5 hash function.

[0097] The values of the preset impact similarity and the preset hash similarity can be determined by the user according to the actual application scenario. The greater the user's demand for improving the accuracy of data segment division, the greater the values of the preset impact similarity and the preset hash similarity. A value of the preset impact similarity and the preset hash similarity is provided, and the preset hash similarity is 70%. The historical records of the user's multi-dimensional division based on the impact similarity and the hash similarity are detected, and the average value of the reference impact similarity corresponding to each data segment in the historical records that can meet the user's needs is recorded as the preset impact similarity. The reference impact similarity corresponding to a single data segment is the impact similarity corresponding to any two log lines in the data segment.

[0098] Specifically, the distribution method of each data segment set is determined based on the segment difference and the pre-delay coefficient, including:

[0099] For a single data fragment collection,

[0100] If the segment difference is greater than or equal to the preset segment difference or the pre-delay coefficient is greater than or equal to the preset pre-delay coefficient, the distribution mode is multi-path parallel distribution;

[0101] If the segment difference is less than the preset segment difference and the pre-delay coefficient is less than the preset pre-delay coefficient, the segment distribution mode is single-path interval distribution.

[0102] The fragment difference corresponding to a single data fragment set is the standard deviation of the fragment reference values corresponding to each data fragment included in the data fragment set, and the fragment reference value corresponding to a single data fragment is the number of log lines included in the data fragment;

[0103] The pre-delay coefficient corresponding to a single data segment set = the standard deviation of the available bandwidth corresponding to each transmission path / the storage reference value corresponding to the data segment set. The available bandwidth corresponding to a single transmission path is the currently available bandwidth on the transmission path, in Mbps. The storage reference value corresponding to a single data segment set is the sum of the memory of each data segment in the data segment set, in MB.

[0104] The values of the preset segment difference and the preset pre-delay coefficient can be determined by the user according to the actual application scenario. The smaller the values of the preset segment difference and the preset pre-delay coefficient, the greater the user's demand for multi-path parallel distribution. The values of the preset segment difference and the preset pre-delay coefficient are provided, and the historical records of the user performing multi-path parallel distribution are detected. The average value of the segment difference corresponding to the historical records that can meet the user's needs is recorded as the preset segment difference, and the average value of the pre-delay coefficient corresponding to the historical records that can meet the user's needs is recorded as the preset pre-delay coefficient.

[0105] The multi-path parallel distribution includes: selecting a transmission path having a path validity coefficient greater than a preset path validity coefficient and a transmission tilt coefficient less than a preset transmission tilt coefficient as a selected path, and determining a distribution data volume of each selected path based on available bandwidth;

[0106] The path effectiveness coefficient is determined by, for a single transmission path. It is understood that the present invention collects change data in real time, so on each transmission path, data segments are continuously transmitted to each transmission path. For a single transmission path, the data segments that have been distributed to the transmission path and are being transmitted on the transmission path and have not yet been transmitted to the change analysis node are recorded as distributed segments, and the data segments to be distributed at the current moment are recorded as pending segments. The path effectiveness coefficient = 1 / the number of identical keywords in the distributed segments and the pending segments.

[0107] The transmission tilt coefficient is determined as follows: for a single transmission path, the transmission tilt coefficient = the number of segments distributed in the transmission path at the current moment / the total number of segments distributed in all transmission paths at the current moment;

[0108] The values of the preset path effectiveness coefficient and the preset transmission tilt coefficient can be determined by the user based on the actual application scenario. The greater the user's demand for improving data distribution efficiency, the greater the value of the preset path effectiveness coefficient and the smaller the value of the preset transmission tilt coefficient. Provided are values of the preset path effectiveness coefficient and the preset transmission tilt coefficient. The user's historical records of multi-path parallel distribution are detected, and the average value of the path effectiveness coefficients corresponding to each selected path in the historical records that can meet the user's needs is recorded as the preset path effectiveness coefficient. The average value of the transmission tilt coefficients corresponding to each selected path in the historical records that can meet the user's needs is recorded as the preset transmission tilt coefficient.

[0109] The amount of distributed data corresponding to a single selected path = (the available bandwidth corresponding to the selected path / the sum of the available bandwidths corresponding to all selected paths) × the number of data segments contained in a single data segment set;

[0110] The data segments for a single selected path for distribution can be selected by the user. Only the data segments that can meet the distribution volume corresponding to the selected path can be selected. There is no specific restriction.

[0111] Single-path interval distribution includes: when distributing a single data segment set, selecting the transmission path with the largest path evaluation coefficient as the selected path, and determining the interval distribution method of each data segment in the data segment set according to the channel transmission difficulty value;

[0112] If the channel transmission difficulty value is greater than or equal to the preset channel transmission difficulty value, the interval distribution mode is single-segment interval transmission;

[0113] If the channel transmission difficulty value is less than the preset channel transmission difficulty value, the interval distribution mode is multi-segment interval transmission;

[0114] For a single transmission path, the path evaluation coefficient corresponding to the transmission path = the path effectiveness coefficient corresponding to the transmission path - the transmission tilt coefficient corresponding to the transmission path. The channel transmission difficulty value = the path evaluation coefficient corresponding to the selected path - the average value of the path evaluation coefficients corresponding to other transmission paths other than the selected path.

[0115] The value of the preset channel transmission difficulty value is determined by the user based on the actual application scenario. The larger the value of the preset channel transmission difficulty value, the greater the user's demand for multi-segment interval transmission. A value of the preset channel transmission difficulty value is provided, and the user's historical records of multi-segment interval transmission are detected. The average value of the channel transmission difficulty values corresponding to the historical records that can meet the user's needs is recorded as the preset channel transmission difficulty value;

[0116] Single-segment interval transmission includes: transmitting each data segment in a single data segment set at intervals, wherein the transmission interval is positively correlated with the channel transmission difficulty value, and the transmission interval is the interval time length between the transmissions of two adjacent data segments;

[0117] Multi-fragment interval transmission includes: evenly dividing each data fragment in a single data fragment set into several fragment combinations, a single fragment combination contains several data fragments, the number of data fragments contained in each fragment combination is the same, the number of data fragments contained in a single fragment combination is positively correlated with the path evaluation coefficient, the combined transmission interval is positively correlated with the channel transmission difficulty value, and the combined transmission interval is the interval time length between the transmission of two adjacent fragment combinations.

[0118] Specifically, if the delay coefficient is greater than or equal to the preset delay coefficient or the adjustment comparison coefficient is greater than or equal to the preset adjustment comparison coefficient, the optimization method is to determine the priority adjustment method according to the priority pre-adjustment coefficient;

[0119] If the priority pre-adjustment coefficient is less than the preset priority pre-adjustment coefficient, the priority adjustment method is partial priority replacement;

[0120] If the priority pre-adjustment coefficient is greater than or equal to the preset priority pre-adjustment coefficient, the priority adjustment method is overall priority replacement.

[0121] The time with the shortest interval from the current time and at which all corresponding data segments are transmitted to the change analysis node is recorded as the near reference time. The delay coefficient = the transmission time corresponding to the near reference time - the average transmission time corresponding to each time in the historical records that can meet user needs. The transmission time corresponding to a single time is the time it takes to transmit each data segment at that time to the change analysis node.

[0122] Adjusted comparison coefficient = |standard deviation of the segment differences corresponding to each data segment set at the current moment - standard deviation of the segment differences corresponding to each data segment set at the adjacent reference moment|;

[0123] Priority pre-adjustment coefficient = adjustment comparison coefficient × pre-delay coefficient;

[0124] The values of the preset delay coefficient, the preset adjustment comparison coefficient and the preset priority pre-adjustment coefficient can be determined by the user according to the actual application scenario. The smaller the values of the preset delay coefficient and the preset adjustment comparison coefficient, the greater the user's demand for determining the priority adjustment method according to the priority pre-adjustment coefficient. A method is provided for taking the values of the preset delay coefficient and the preset adjustment comparison coefficient, detecting the historical records of the user determining the priority adjustment method according to the priority pre-adjustment coefficient, recording the average value of the delay coefficients corresponding to the historical records that can meet the user's needs as the preset delay coefficient, and recording the average value of the adjustment comparison coefficients corresponding to the historical records that can meet the user's needs as the preset adjustment comparison coefficient. The larger the value of the preset priority pre-adjustment coefficient, the greater the user's demand for partial priority replacement. A method is provided for taking the value of the preset priority pre-adjustment coefficient, detecting the historical records of the user performing partial priority replacement, and recording the priority pre-adjustment coefficient corresponding to the historical records that can meet the user's needs as the preset priority pre-adjustment coefficient;

[0125] The partial priority replacement includes: detecting an adjustment reference value corresponding to each data segment set, and increasing a transmission priority coefficient corresponding to a data segment set whose adjustment reference value is greater than a preset adjustment reference value, wherein the increase value of the transmission priority coefficient corresponding to a single data segment set is positively correlated with the adjustment reference value corresponding to the data segment set;

[0126] The adjustment reference value is determined by, for a single data segment set, recording the data segment set as the target set, recording each data segment set corresponding to a nearby reference time as the reference set, and recording the average of the sub-transmission durations corresponding to reference sets whose similarity coefficients with the target set are greater than a preset similarity coefficient as the adjustment reference value corresponding to the target set. The sub-transmission duration corresponding to a single reference set is the time required to transmit each data segment in the reference set to the change analysis node. The similarity coefficients corresponding to two data segment sets = 1 - (the absolute value of the difference between the transmission requirement coefficients corresponding to the two data segment sets / the larger value of the transmission requirement coefficients corresponding to the two data segment sets).

[0127] The overall priority replacement includes: determining the transmission priority coefficient corresponding to each data segment set according to the adjustment comparison coefficient, where the adjustment comparison coefficient = transmission demand coefficient / adjustment reference value;

[0128] The values of the preset adjustment reference value and the preset similarity coefficient can be determined by the user according to the actual application scenario. The greater the user's demand for improving the efficiency of changing data transmission, the smaller the value of the preset adjustment reference value and the larger the value of the preset similarity coefficient. A preset adjustment reference value and a preset similarity coefficient are provided to detect the historical records of partial priority replacement performed by the user, and the average value of the adjustment reference values corresponding to the sets of data segments adjusted for the transmission priority coefficient in the historical records that can meet the user's needs is recorded as the preset adjustment reference value. The preset similarity coefficient is 70%.

[0129] Specifically, if the delay coefficient is less than the preset delay coefficient and the adjustment comparison coefficient is less than the preset adjustment comparison coefficient, the optimization method is to reduce the adjustment for the aggregate transmission interval;

[0130] The reduction value of the aggregate transmission interval is negatively correlated with the abnormality assessment coefficient.

[0131] Among them, abnormal assessment coefficient = delay coefficient + adjustment comparison coefficient;

[0132] The set transmission interval is the time interval between the transmissions of two adjacent sets of data segments. It should be noted that the initial transmission interval in the present invention is negatively correlated with the total number of sets of data segments.

[0133] Thus far, the technical solutions of the present invention have been described in conjunction with the preferred embodiments shown in the accompanying drawings. However, it will be readily understood by those skilled in the art that the scope of protection of the present invention is obviously not limited to these specific embodiments. Without departing from the principles of the present invention, those skilled in the art may make equivalent changes or substitutions to the relevant technical features, and the technical solutions after such changes or substitutions will fall within the scope of protection of the present invention.

[0134] The foregoing description is merely a preferred embodiment of the present invention and is not intended to limit the present invention. Those skilled in the art will readily appreciate that the present invention is susceptible to various modifications and variations. Any modifications, equivalent substitutions, or improvements made within the spirit and principles of the present invention are intended to be within the scope of protection of the present invention.

Claims

1. A real-time data collection method for a database, characterized in that: include: The database status is determined based on the abnormal frequency flow coefficient and the compatibility impact coefficient. The tuning setting method is determined based on the database status, such as triggered tuning based on the trigger impact coefficient or cyclic tuning based on the performance load coefficient. The tuning statement category is determined based on the high-frequency execution coefficient and the sensitive bottleneck threshold. The tuning statement categories include: High-frequency impact statements whose high-frequency execution coefficient is greater than or equal to the preset high-frequency execution coefficient or whose sensitive bottleneck threshold is greater than or equal to the preset sensitive bottleneck threshold; Low-frequency impact statements whose high-frequency execution coefficient is less than the preset high-frequency execution coefficient and whose sensitive bottleneck threshold is less than the preset sensitive bottleneck threshold; And optimize for high-frequency impact statements; Determine the data partitioning method of the change log to be analyzed based on the tuning comparison coefficient and dynamic load intensity to obtain several data segments. The data partitioning method is uniform segmentation based on the evaluation deviation index, or dynamic segmentation is determined based on the attribute fixity and range coordination coefficient. The dynamic division method is to perform association division based on attribute similarity and attribute representation value, or to perform multi-dimensional division based on influence similarity and hash similarity; Determine a data segment set according to the distribution adaptability, and determine the distribution mode of each data segment set as multi-path parallel distribution or single-path interval distribution according to the segment difference and the pre-delay coefficient; Determine the optimization mode according to the delay coefficient and the adjustment comparison coefficient, which is to adjust the transmission interval or determine the priority adjustment mode according to the priority pre-adjustment coefficient, wherein the priority adjustment mode is partial priority replacement or overall priority replacement; The abnormal frequency flow coefficient is the average of the sub-abnormal frequency flow values corresponding to each historical record. The sub-abnormal frequency flow value is confirmed by recording a single historical record as the target historical record, recording all historical records other than the target historical record as reference historical records, and recording the average of the abnormal interaction degrees corresponding to the target historical record and each reference historical record as the sub-abnormal frequency flow value corresponding to the target historical record. The abnormal interaction degree is confirmed by detecting the hash values of the data fragments corresponding to each change data in any two historical records. The abnormal interaction degree corresponding to the two historical records = the number of hash values that are the same in one historical record and the other historical record / the number of data fragments corresponding to each change data in the two historical records, whichever is larger. Compatibility impact coefficient = (first impact value - second impact value) / first impact value. Record the historical records where the sub-abnormal frequency flow value is greater than or equal to the preset sub-abnormal frequency flow value as the first historical records, and record the historical records where the sub-abnormal frequency flow value is less than the preset sub-abnormal frequency flow value as the second historical records. The first impact value is the average capture time corresponding to each first historical record, and the second impact value is the average capture time corresponding to each second historical record. The capture time corresponding to a single historical record is the time taken by the change analysis node after the data segment in the historical record is transferred to the change analysis node. Tuning comparison coefficient = number of high-frequency impact statements at the current moment / number of low-frequency impact statements at the current moment.

2. The real-time data collection method for a database according to claim 1, characterized in that: If the database status is that the abnormal frequency flow coefficient is greater than or equal to the preset abnormal frequency flow coefficient or the compatible impact coefficient is greater than or equal to the preset compatible impact coefficient, the tuning setting method is to perform cyclic tuning based on the performance load factor.

3. The real-time data collection method for a database according to claim 2, characterized in that: If the database status is that the abnormal frequency flow coefficient is less than the preset abnormal frequency flow coefficient and the compatible impact coefficient is less than the preset compatible impact coefficient, the tuning setting method is triggered tuning based on the trigger impact coefficient.

4. The real-time data collection method for a database according to claim 1, characterized in that: The data division method of the change log to be analyzed is determined based on the tuning comparison coefficient and dynamic load intensity, including: If the tuning comparison coefficient is less than the preset tuning comparison coefficient and the dynamic load intensity is less than the preset dynamic load intensity, the data division method is to divide the data into uniform segments according to the evaluation deviation index; If the tuning comparison coefficient is greater than or equal to the preset tuning comparison coefficient or the dynamic load intensity is greater than or equal to the preset dynamic load intensity, the data division method is to determine the dynamic division method based on the attribute fixity and the range coordination coefficient.

5. The real-time data collection method for a database according to claim 4, characterized in that: If the attribute fixity is greater than or equal to the preset attribute fixity and the range coordination coefficient is greater than or equal to the preset range coordination coefficient, the dynamic division method is to perform association division based on attribute similarity and attribute representation value.

6. The real-time data collection method for a database according to claim 5, characterized in that: If the attribute fixity is less than the preset attribute fixity or the range coordination coefficient is less than the preset range coordination coefficient, the dynamic division method is to perform multi-dimensional division based on the impact similarity and hash similarity.

7. The real-time data collection method for a database according to claim 6, characterized in that: The distribution method of each data segment set is determined based on the segment difference and the pre-delay coefficient, including: For a single data fragment collection, If the segment difference is greater than or equal to the preset segment difference or the pre-delay coefficient is greater than or equal to the preset pre-delay coefficient, the distribution mode is multi-path parallel distribution; If the segment difference is less than the preset segment difference and the pre-delay coefficient is less than the preset pre-delay coefficient, the segment distribution mode is single-path interval distribution.

8. The real-time data collection method for a database according to claim 7, characterized in that: If the delay coefficient is greater than or equal to the preset delay coefficient or the adjustment comparison coefficient is greater than or equal to the preset adjustment comparison coefficient, the optimization method is to determine the priority adjustment method according to the priority pre-adjustment coefficient; If the priority pre-adjustment coefficient is less than the preset priority pre-adjustment coefficient, the priority adjustment method is partial priority replacement; If the priority pre-adjustment coefficient is greater than or equal to the preset priority pre-adjustment coefficient, the priority adjustment method is overall priority replacement.

9. The real-time data collection method for a database according to claim 8, characterized in that: If the delay coefficient is less than the preset delay coefficient and the adjustment comparison coefficient is less than the preset adjustment comparison coefficient, the optimization method is to reduce the adjustment for the aggregate transmission interval; The reduction value of the aggregate transmission interval is negatively correlated with the abnormality assessment coefficient.

Images