Distributed database time series data statistical information collection method

Abstract

The invention discloses a method for collecting time series data statistical information of a distributed database, and belongs to the technical field of distributed databases in computer science, the method is implemented by comprising the following steps: a database running cluster is provided, and the database cluster of one application comprises a plurality of database running nodes; the database operation node operates data change processing, data change monitoring, statistical information refreshing monitoring and optimizer service; the data change processing module is responsible for processing data operation and synchronizing changes to the database storage engine; the data change monitoring module is responsible for recording the increasing and decreasing quantity of data records of each table; the statistical information refreshing monitoring module is responsible for regularly monitoring the data change condition and determining whether to trigger the updating of the statistical information; a query plan optimization module; a database execution module; a database stores an engine. According to the method, the collection performance of the time sequence data statistical information under the large data volume can be greatly improved, and meanwhile, the service query performance of the application is ensured.

Description

Technical Field

[0001] The present invention relates to the technical field of distributed databases in computer science, and more particularly to a method for collecting statistical information of time-series data in a distributed database. Background Art

[0002] The characteristics of modern database applications are diverse load types, rapid update and iteration, and often require query analysis based on a large amount of data. When users still need to perform continuous data operations through a certain node in a distributed system, when a large amount of data is written, the traditional statistical information collector will be continuously triggered to perform statistical information collection work in the background. For some large-scale services, the amount of data involved may reach billions or tens of billions. When the statistical information collection performed in the background involves such a large amount of data, it may affect the performance of the main business query application, and in severe cases, it may even cause the main task and the background task to affect each other, resulting in a significant reduction in system performance.

[0003] In addition, for different data modalities, such as the relational data engine that emphasizes strong consistency and the time-series data engine that emphasizes write performance and eventual consistency, most database systems adopt the same statistical information collection strategy in order to simplify the process. However, the same collection method may not be well adapted to all modalities. For example, for the time-series engine, in many cases, the sampled values of the data may be continuously changing and there are no null values. Therefore, it may not be meaningful to spend a large amount of computing power to count distinct values and null values.

[0004] When the amount of data reaches billions, it becomes very expensive to calculate statistical information through query statements to drive operators such as distinct and count. At the same time, the main business of the application may also be performing corresponding aggregation analysis queries, which makes the system load increase and the computing resources very tense. The delayed response or interruption of the service will cause certain losses to users. Therefore, it is required that the database is always highly available and has good query performance, and the statistical information collection in the background should not seriously affect the normal operation of the application. Therefore, to optimize the collection of statistical information in the time-series data scenario, we should first focus on the actual scenario. The existing solutions on the market usually further optimize the operators or increase computing power. However, for some users with limited server performance or hardware budget, these solutions cannot fundamentally solve the problem that the statistical information collection is too expensive. Optimizing the operators can indeed improve the performance of query calculation, but on the premise that the hardware performance has an upper limit, the optimized effect may still not be sufficient to support the business load; increasing computing power involves issues such as the user's budget and system hardware upgrade, and the optimization cost is borne by the user, which is not an effective optimization that the database can provide; in addition, the business scenarios supported by different time-series data engines vary widely. Without clarifying the business of a certain time-series database engine, it is not suitable to blindly borrow the optimization solutions of other products.

[0005] In view of the above technical status quo, it is necessary to establish a statistical information collection solution that can collect statistical information of time-series data in the background more efficiently and almost imperceptibly for users, without interrupting services, without excessive time and space overheads, and without seriously affecting the business query services of applications. Summary of the Invention

[0006] The technical task of the present invention is to provide a method for collecting statistical information of time-series data in a distributed database in view of the above deficiencies, which can greatly improve the collection performance of statistical information of time-series data under a large amount of data, and at the same time ensure the business query performance of the application.

[0007] The technical solution adopted by the present invention to solve its technical problems is as follows:

[0008] A method for collecting statistical information of time-series data in a distributed database, the implementation of this method includes:

[0009] A database operation cluster, and the database cluster of an application includes multiple database operation nodes;

[0010] A database operation node, which is the operation basis of the database, and its operations include various services such as data change processing, data change monitoring, statistical information refresh monitoring, and optimizer;

[0011] A data change processing module, which is responsible for processing data operations, including operations such as adding, deleting, modifying, and querying data, and synchronizing the changes to the database storage engine;

[0012] A data change monitoring module, which is responsible for recording the increase and decrease in the number of data records in each table;

[0013] A statistical information refresh monitoring module, which is responsible for periodically listening to data change situations, deciding whether to trigger the update of statistical information, processing the update of different types of data statistical information, and being responsible for updating the statistical information in the system table;

[0014] A query plan optimization module, that is, an optimizer, which selects the optimal query plan for a query statement according to rules and calculation costs;

[0015] A database execution module, which is responsible for executing data definition statements and data operation statements;

[0016] A database storage engine, which stores application load data objects.

[0017] This method analyzes the characteristics of the business scenarios corresponding to time-series data, designs a statistical information collection solution dedicated to supporting time-series services, and greatly improves the collection performance of statistical information of time-series data under a large amount of data by optimizing the counting algorithm and avoiding the calculation of expensive statistical entries that are of little significance, while ensuring the business query performance of the application.

[0018] Further, the database operation node

[0019] Stably runs various basic services of the database, including automatic collection of statistical information of various modal data, including relational data, time-series data, etc.;

[0020] Connects to the monitored cluster where the application is located and obtains data objects referred to by all SQL statements contained in its application load.

[0021] Further, the data change monitoring module records the change in the number of data records in each data table within a specified time period according to the DML statements of the insert and delete types executed by the system.

[0022] Further, the statistical information refresh monitoring module establishes a time-series data statistical information collection trigger rule and a collection rule:

[0023] When the automatic statistical information collection switch is turned on, the statistical information refresh monitoring module is started;

[0024] When the following conditions are met: 1. There is no statistical information of the time-series table with current data volume changes in the system table; 2. The most recent update time of the statistical information of the time-series table has exceeded the set threshold; 3. The change in the number of data records of the time-series table has exceeded the set threshold;

[0025] For the situation where there is a change in data volume but the update is not triggered, the current change amount of the table in the data change monitoring module is accumulated into the cumulative record change amount of each table maintained by the statistical information refresh monitoring module since the last statistical information update;

[0026] When the statistical information update of the time-series table is triggered, different statistical information collection processes are triggered according to different types of columns on the time-series table: 1. For the primary key column, use the CREATE STATISTIC statement and the COUNT operator to count the data of this column, and synchronize the count into the unique count, and write 0 into the non-null count; 2. For the attribute column, use the CREATE STATISTIC statement and the COUNT operator, DISTINCT COUNT operator, and NULL COUNT operator to count the data of this column; 3. For the measurement value column, use a query statement to query the latest COUNT count of this time-series table in the system table, and add the cumulative record change amount of the current table maintained by the statistical information refresh monitoring module since the last statistical information update as the COUNT and DISTINCT COUNT of the measurement value, and set the NULL COUNT to 0; at the same time, execute the SELECT FIRST(Timestamp) query to obtain the start timestamp of the measurement value of the current table.

[0027] Based on the statistical information obtained from the above three time series data columns, the statistical information is updated in the system table in units of columns, and redundant statistical information is deleted.

[0028] Furthermore, the statistical information refresh monitoring module implements an update mechanism for various data modality statistical information, including:

[0029] Monitor data updates of different modes: When there are write and delete DML queries on the cluster nodes, the data change monitoring module will record the changes in the number of records on the corresponding data table; at the same time, the statistical information refresh monitoring module runs at a fixed frequency every specified time period. When data changes are detected during the runtime, the statistical information refresh calculation is triggered. Otherwise, no processing is performed and the module waits to enter the next round of monitoring.

[0030] Distinguish the statistical information updates of different types of data columns of time series data: When the statistical information refresh monitoring module detects that the number of records in a table in the system has changed, it first accesses the data volume change records of each table maintained by the change monitoring module, checks the mode of the table, and triggers the statistical information inspection and collection mechanism corresponding to the relational data for relational data; for time series data, it triggers the statistical information inspection and collection mechanism corresponding to the optimized time series data;

[0031] Verify whether the statistical information refresh function is triggered: Even if the number of records in each table changes, it is necessary to reach a certain threshold before triggering the statistical information refresh; currently, there are three conditions for triggering statistical information refresh: 1) There is no data in the statistical information system table of the table with the number of data records changed; 2) The latest timestamp of the statistical information update of a table exceeds the time interval threshold of the scheduled refresh, such as 24 hours; 3) The number of records in a table changes by more than the predetermined change rate, which is usually determined by a mathematical formula; When all three of the above conditions are not met, skip this round of update, and only count the data change amount of the current table into a data structure of the cumulative data change amount of each table since the last update maintained by the statistical information refresh monitoring module, so as to avoid the data change quantity records that have been generated being cleared and overwritten by the scheduled refresh mechanism of the data change monitoring module;

[0032] Calculation of statistical information update for different types of data columns in time-series data: When it is detected that the table currently requiring triggering of statistical information refresh is of the time-series type, since statistical information is calculated by column, the columns of a time-series table usually include a primary key column, an attribute column, and a numeric column. According to the characteristics of the primary key column, attribute column, and numeric column, different statistical information calculation mechanisms are triggered for each column respectively; for the primary key column and the attribute column, these two columns define the attributes and uniqueness of the entity, and the amount of recorded data is much lower than that of the numeric column. Therefore, complex queries can be reused to update the statistical information with the calculated values obtained by each aggregation operator. However, the difference between the two is that for the primary key column, since it is unique and guaranteed to be non-null, there is no need to calculate DISTINCT COUNT and NULL COUNT additionally; while for the numeric column, since the amount of data may be extremely large, instead of pushing the complex query with aggregation calculation down to the time-series engine for calculation, only the corresponding statistical information system table of the table is accessed to obtain the most recent statistical information of this column of this table, and the cumulative data change amount of each table since the last update maintained is added to the existing statistical information, including DISTINCT COUNT and NULL COUNT, and the current timestamp is obtained.

[0033] Updating statistical information in the system table: Fill the statistical information corresponding to each column of the table currently triggering the update into the SQLDML insert statement and insert it into the system table responsible for maintaining statistical information; for the statistical information table of time-series data, a new start timestamp column is introduced to estimate the data volume; when the database system uses statistical information, usually only the most recent statistical information is used for calculation; when there are more than five pieces of statistical information on a certain column of a table, the earlier information can be regarded as redundant information; therefore, when updating, the number of statistical information on the current column of the current table will also be detected simultaneously, and only the most recent five pieces of statistical information change records will be retained, and the earlier historical records will be cleared.

[0034] Furthermore, the query plan optimization module establishes a data volume estimation rule based on the measured value column of time-series data, including:

[0035] For the calculation of the data volume of the measured value column of the time-series table, first read the start timestamp of the statistical information recorded by the table in the system table, then read the counts of each type, estimate the data volume written per time unit from the start time to the current time, and multiply it by the specified time range of the query to obtain the data volume involved in the query;

[0036] For other types of columns in time-series data, these columns are device attributes and are independent of time, and the various counts of this column in the system table are directly used for calculation.

[0037] When the optimizer processes a query plan based on time-series data, it needs to read statistical information. After using the optimized statistical information collection scheme, the measured value column of time-series data no longer obtains expensive statistical items such as null value counting and unique value technology through aggregation operators, nor does it generate expensive histograms. Therefore, the optimizer uses the estimated null value count and unique value count, and estimates the data volume involved in the query based on the time range of the query and the start and end times of the corresponding time-series table; while other types of columns and other types of data tables still follow the original logic;

[0038] For the estimation of the data volume of the measured value column of time-series data, first read the start time, end time, and data volume count of the column values in the system table to obtain the data volume per time unit, and then multiply the time range involved in the query by the data volume per time unit to obtain the data volume of the time-series measurement values involved in this query. This method utilizes the characteristic of uniform writing of time-series data measurement values to adapt to the change of statistical information items, supports the optimizer to obtain a more accurate estimated data volume, and supports the optimizer to continue to select a suitable query plan.

[0039] Furthermore, the database execution module obtains accurate statistical information in the form of driving various operators; supports the collection of statistical information of the measured value column of time-series data triggered by the CREATE STATISTIC statement, and performs aggregation calculations through operators including COUNT operator, DISTINCT COUNT operator, and NULL COUNT operator to obtain accurate statistical information and update the system table.

[0040] Furthermore, the database storage engine is used to collect and store the running data in the database;

[0041] Receives and stores the data objects referred to by all SQL statements contained in the application load and other associated data objects, and at the same time collects and stores other data used for subsequent statistical information collection and analysis. The running data includes application load information, monitoring metrics, database logs, etc., providing support for statistical information collection.

[0042] The present invention also claims to protect a device for collecting statistical information of time-series data in a distributed database, including: at least one memory and at least one processor;

[0043] The at least one memory is used to store machine-readable programs;

[0044] The at least one processor is used to call the machine-readable program to implement the above method.

[0045] The present invention also claims to protect a computer-readable medium, on which computer instructions are stored, and when the computer instructions are executed by a processor, the above method can be implemented.

[0046] Compared with the prior art, a method for collecting statistical information of time-series data in a distributed database according to the present invention has the following beneficial effects:

[0047] The method for collecting statistical information of time-series data in the distributed database according to the present invention supports real-time updating of the statistical information of time-series data in a more lightweight manner with less impact on the overall application load. Each node of the distributed database first determines whether to enable the statistical information refresh monitoring module according to the automatic statistical information collection configuration of the cluster. When the refresh monitoring module is started, it will regularly check the change of the record number of various types of data tables. When the change of the time-series data table meets the requirement of updating the statistical information, different statistical information calculation methods will be triggered according to different columns. The advantage of this approach is that it significantly reduces the consumption of computing resources while still ensuring the correctness of the statistical information, helping the optimizer select an appropriate query plan.

[0048] Different from common statistical information collection methods, the present invention utilizes the writing characteristics of time-series data and the different characteristics of various types of data columns, and efficiently ensures the performance and correctness of the automatic collection of statistical information of time-series data during the application operation process by methods such as minimizing aggregation calculations and replacing them with equivalent low-cost calculations. When the number of numerical information records in a certain time-series table reaches billions or even tens of billions, the aggregation calculation based on a huge amount of data will become very expensive. The present invention separates the primary key value, attribute value, and measurement value of the time-series data. For the primary key value and attribute value with a small amount of data, accurate aggregation operations are still used to calculate each statistical item. And since the primary key value is guaranteed to be unique and non-null, the non-null count and unique count operations are omitted, further reducing the calculation amount; while for the measurement value with a huge amount of data, expensive aggregation calculations are avoided, and the uniform characteristic of the device writing rate is utilized, and the statistical information is updated to an estimated value by a method based on the data change monitoring module with extremely little calculation amount; at the same time, the user-initiated statistical information update is retained, and an accurate calculation result based on the aggregation operator can still be obtained. During the idle time of the application, the accurate statistical information can be obtained by actively triggering the update, supervising and ensuring the correctness of the statistical information of the time-series data.

[0049] When the optimizer extracts the data record count of the measurement value column of the time-series data, the adjusted algorithm is also used to continue to support the optimizer to obtain a relatively accurate estimated data volume and select an appropriate query plan. Before optimization, for a time-series table with a huge amount of data, its statistical information may not be generated for a long time, resulting in a query plan with poor performance by the optimizer in the absence of statistical information, further affecting the cluster application load and generating a vicious cycle. The solution of the present invention focuses on solving this problem. In addition, these optimizations are invisible to users, only improving the response speed and stability of the application, making the user experience more fluent when using. Description of the Drawings

[0050] Figure 1 It is a system structure diagram of a method for collecting statistical information of time-series data in a distributed database provided by an embodiment of the present invention. Detailed Implementation Manner

[0051] The present invention will be further described below in conjunction with specific embodiments.

[0052] An embodiment of the present invention provides a method for collecting statistical information of time-series data in a distributed database. The implementation of this method includes:

[0053] A database operation cluster. The database cluster of an application includes multiple database operation nodes;

[0054] A database operation node, which is the operation foundation of the database. The operations include various services such as data change processing, data change monitoring, statistical information refresh monitoring, and optimizer.

[0055] A data change processing module, which is responsible for processing operations such as data addition, deletion, modification, and query, and synchronizing the changes to the database storage engine;

[0056] A data change monitoring module, which is responsible for recording the number of increases and decreases in data records of each table;

[0057] A statistical information refresh monitoring module, which is responsible for periodically listening to data change situations, deciding whether to trigger the update of statistical information, processing the update of different types of data statistical information, and being responsible for updating the statistical information in the system table;

[0058] A query plan optimization module, that is, an optimizer, selects the optimal query plan for a query statement according to rules and calculation costs, calculates the cost of the query plan according to rules and statistical information, enumerates query plans, and selects the query plan with the lowest total cost;

[0059] A database execution module, which is responsible for executing data definition statements and data operation statements;

[0060] A database storage engine, which stores application load data objects.

[0061] This method analyzes the characteristics of the business scenarios corresponding to time-series data, designs a statistical information collection scheme dedicated to supporting time-series services, and greatly improves the collection performance of time-series data statistical information under a large amount of data by optimizing the counting algorithm and avoiding the calculation of expensive statistical items with little significance, while ensuring the business query performance of the application.

[0062] Among them, the database operation node:

[0063] Stably run various basic services of the database, including automatic collection of statistical information for various modal data, such as relational data and time-series data, etc.;

[0064] Connect to the monitored cluster where the application is located, and obtain data objects referred to by all SQL statements contained in its application load, etc.

[0065] The data change monitoring module: According to the DML statements of the insert and delete types executed by the system, record the change in the number of data records in each data table within a specified time period.

[0066] The statistical information refresh monitoring module:

[0067] When the automatic statistical information collection switch of the cluster is turned on, the statistical information refresh monitoring module runs at a certain period, for example, triggering a refresh check every 50 seconds, and checking whether the data change has reached the threshold conditions for triggering the collection of statistical information, including the amount of data change, refresh interval, etc.; when the automatic statistical information collection switch of the cluster is turned off, the statistical information refresh monitoring module is in a dormant mode and will not be triggered;

[0068] For time-series data, when the threshold conditions for automatically collecting statistical information are triggered, different internally preset SQL query statements are triggered according to the types of different columns (such as the attribute columns and numerical columns of time-series data) to access the data table and system table, and calculate the statistical information;

[0069] After the statistical information is collected, update the statistical information system table and delete redundant statistical information records.

[0070] The statistical information refresh monitoring module establishes the triggering rules and collection rules for collecting statistical information of time-series data:

[0071] When the automatic statistical information collection switch is turned on, start the statistical information refresh monitoring module;

[0072] When the following conditions are met: 1. There is no statistical information of the time-series table with current data volume change in the system table; 2. The most recent update time of the statistical information of the time-series table has exceeded the set threshold; 3. The amount of change in the data records of the time-series table has exceeded the set threshold;

[0073] For the situation where there is a data volume change but no update is triggered, accumulate the current change amount of this table in the data change monitoring module to the cumulative record change amount of each table maintained by the statistical information refresh monitoring module since the last statistical information update;

[0074] When the update of the statistical information of the time series table is triggered, different statistical information collection processes are triggered according to different types of columns on the time series table: 1. For the primary key column, through the CREATE STATISTIC statement, use the COUNT operator to count the data of this column, and synchronize the count into the unique count, and write 0 into the non-null count; 2. For the attribute column, through the CREATE STATISTIC statement, use the COUNT operator, DISTINCT COUNT operator, and NULL COUNT operator to count the various counts of the data of this column; 3. For the measurement value column, use the query statement to query the latest COUNT count of this time series table in the system table, and add the cumulative record change amount of the current table since the last statistical information update maintained by the statistical information refresh monitoring module as the COUNT and DISTINCT COUNT of the measurement value, and set the NULL COUNT to 0; at the same time, execute the SELECT FIRST(Timestamp) query to obtain the start timestamp of the measurement value of the current table.

[0075] Based on the statistical information obtained from the above three types of time series data columns, update the statistical information in the system table column by column, and delete redundant statistical information.

[0076] The statistical information refresh monitoring module implements an update mechanism for statistical information of various data modalities:

[0077] 1. Monitor data updates of different modalities. When there are DML queries of write and delete types on the cluster nodes, the data change monitoring module will record the change in the number of records on the corresponding data table; at the same time, the statistical information refresh monitoring module runs at a fixed frequency every specified time period. When it detects a change in data changes during operation, the statistical information refresh calculation is triggered, otherwise no processing is performed and it waits to enter the next round of listening.

[0078] 2. Distinguish the update of statistical information for different types of data columns in time series data. When the statistical information refresh monitoring module detects a change in the number of records in a table in the system, it first accesses the record change records of the data volume of each table maintained by the change monitoring module, checks the modality of the table, and triggers the corresponding statistical information check and collection mechanism for relational data; for time series data, it triggers the optimized statistical information check and collection mechanism for time series data.

[0079] 3. Verify whether the statistical information refresh function is triggered. Even if the number of records in each table changes, it is necessary to reach a certain threshold before triggering the refresh of statistical information; currently, there are three conditions for triggering the refresh of statistical information: 1) There is no data in the statistical information system table of the table with the number of data records changed; 2) The latest timestamp of the statistical information update of a table exceeds the time interval threshold of the scheduled refresh, such as 24 hours; 3) The number of records in a table changes by more than the predetermined change rate, which is usually determined by a mathematical formula; When the above three conditions are not met, skip this round of update, and only count the data changes of the current table into a data structure of the cumulative data changes of each table since the last update maintained by the statistical information refresh monitoring module, so as to avoid the data change quantity records that have been generated being cleared and overwritten by the scheduled refresh mechanism of the data change monitoring module.

[0080] 4. Calculate the statistical information updates of different types of data columns of time series data. When it is detected that the table that needs to trigger the statistical information refresh is of the time series type, since the statistical information is calculated by column, the columns of the time series table usually include primary key columns, attribute columns and value columns. According to the characteristics of the primary key columns, attribute columns and value columns, different statistical information calculation mechanisms are triggered for each column; for the primary key column and attribute column, these two columns define the attributes and uniqueness of the entity, and their record data volume is much lower than that of the value column, so complex queries can be used to update the statistical information with the calculated values ​​obtained by each aggregation operator, but the difference between the two is that for the primary key column, since it is unique and guaranteed to be non-empty, there is no need to calculate DISTINCT COUNT and NULLCOUNT additionally; for the value column, since the data volume may be very large, the complex query with aggregation calculation is not pushed down to the time series engine calculation, but only the statistical information system table corresponding to the table is accessed to obtain the latest statistical information of the column of the table, and the accumulated data changes of each table since the last update are accumulated to the existing statistical information, including DISTINCTCOUNT and NULL COUNT, and the current timestamp is obtained. It should be noted that this solution only optimizes the calculation process of automatic time series data statistical information collection. For the statistical information creation command actively triggered by the user, accurate results based on various aggregate calculations can still be obtained.

[0081] 5. Update the statistical information of the system tables. Fill the statistical information corresponding to each column of the table currently triggering the update into the SQLDML insert statement and insert it into the system table responsible for maintaining the statistical information. For the statistical information table of time-series data, a new start timestamp column is introduced to estimate the data volume. When the database system uses the statistical information, usually only the most recent piece of statistical information is used for calculation. When there are more than five pieces of statistical information for a certain column of a table, the earlier information can be regarded as redundant information. Therefore, when updating, the number of statistical information for the current column of the current table is also detected, and only the most recent five pieces of statistical information change records are retained, and the earlier historical records are cleared.

[0082] The query plan optimization module establishes a data volume estimation rule based on the measured value column of time-series data, including:

[0083] For the calculation of the data volume of the measured value column of the time-series table, first read the start timestamp of the statistical information recorded in the system table for this table, and then read the counts of each type. Estimate the data volume written per time unit from the start time to the current time, and multiply it by the specified time range of the query to obtain the data volume involved in the query.

[0084] For other types of columns of time-series data, these columns are device attributes and are independent of time. Directly use the counts of each type of this column in the system table for calculation.

[0085] When the optimizer processes the query plan based on time-series data, it needs to read the statistical information. After using the optimized statistical information collection scheme, the measured value column of time-series data no longer obtains expensive statistical items such as null value counts and unique value techniques through aggregation operators, nor does it generate expensive histograms. Therefore, the optimizer uses the estimated null value count, unique value count, and estimates the data volume involved in the query based on the time range of the query and the start and end times of the corresponding time-series table; while other types of columns and other types of data tables still follow the original logic;

[0086] For the estimation of the data volume of the measured value column of time-series data, first read the start time, end time, and data volume count of the column values in the system table, and the data volume per time unit can be obtained. Then multiply the time range involved in the query by the data volume per time unit to obtain the data volume of the time-series measured value involved in this query. This method utilizes the characteristic of uniform writing of the measured value of time-series data to adapt to the change of statistical information items, supports the optimizer to obtain a relatively accurate estimated data volume, and supports the optimizer to continue to select an appropriate query plan.

[0087] The database execution module, different from the time series data measurement value column statistic information collection scheme in the above statistical information refresh monitoring module, still obtains accurate statistical information in the form of driving various operators. It supports the collection of time series data measurement value column statistic information triggered by the CREATE STATISTIC statement, and through operators including COUNT operator, DISTINCT COUNT operator, and NULL COUNT operator, etc., performs aggregation calculations to obtain accurate statistical information and updates the system table.

[0088] The database storage engine is used to collect and store the running data in the database;

[0089] Receives and stores the data objects referenced by all SQL statements contained in the application workload and other associated data objects, and at the same time collects and stores other data for subsequent statistical information collection and analysis. The running data includes application workload information, monitoring metrics, database logs, etc., providing support for statistical information collection.

[0090] The business object of the time series database is usually a data acquisition device, and its acquisition characteristic is that it almost writes at a constant speed. Therefore, there is no need to trigger expensive counting operators. Instead, after triggering a statistics once, simply record the start and end times during the data writing process and quickly calculate the data volume, and ensure the correctness of the statistical information by triggering count updates during idle periods with low system load. In addition, items such as null value count, distinct count, and histogram that are usually calculated by databases in the market scenarios are less important for the time series data mode mainly used to record device acquisition data than relational databases that are more frequently involved in complex queries, and there are mostly no null values and a large number of duplicate values in the corresponding business scenarios. Therefore, from the perspective of balancing calculations, it can be simply replaced with count information. This optimization will greatly reduce the computational cost of time series data statistical information collection. These optimizations can first ensure that the obtained statistical information can still normally support the optimizer to select appropriate plans for time series related queries, and reduce the amount of calculation through some replacement and optimization methods, making the user's perception of the background time series statistical information collection task almost non-existent, and still being able to update the statistical information in real time. At the same time, for some non-uniform business scenarios, there are still idle or timed statistical information collection tasks triggered by counting operators to further ensure the correctness of the statistical information.

[0091] This method supports a more efficient background statistical information collection method for time series data tables in a distributed database, which has less impact on applications, and updates the statistical information of the time series table. By deeply understanding and utilizing the characteristics of the time series database when inserting data, the collection method of necessary statistical information is optimized, and statistical information collection items with excessive computational workload are avoided. The advantage of this method is that it significantly reduces the system workload caused by statistical information collection, and the statistical information can still well support the database optimizer to select an appropriate execution plan.

[0092] An embodiment of the present invention also provides a device for collecting statistical information of time series data in a distributed database, including: at least one memory and at least one processor;

[0093] The at least one memory is used to store machine-readable programs;

[0094] The at least one processor is used to call the machine-readable program to implement the method for collecting statistical information of time series data in a distributed database described in the above embodiment.

[0095] An embodiment of the present invention also provides a computer-readable medium, on which computer instructions are stored, and when the computer instructions are executed by a processor, the method for collecting statistical information of time series data in a distributed database described in the above embodiment is implemented. Specifically, a system or device equipped with a storage medium can be provided, on which software program code for implementing the functions of any one of the above embodiments is stored, and the computer (or CPU or MPU) of the system or device reads and executes the program code stored in the storage medium.

[0096] In this case, the program code read from the storage medium itself can implement the functions of any one of the above embodiments, so the program code and the storage medium storing the program code constitute a part of the present invention.

[0097] Embodiments of the storage medium for providing program code include floppy disks, hard disks, magneto-optical disks, optical disks (such as CD-ROM, CD-R, CD-RW, DVD-ROM, DVD-RAM, DVD-RW, DVD+RW), magnetic tapes, non-volatile memory cards, and ROMs. Optionally, the program code can be downloaded from a server computer via a communication network.

[0098] In addition, it should be clear that not only can the functions of any one of the above embodiments be implemented by executing the program code read by the computer, but also by instructions based on the program code, the operating system operating on the computer, etc. to complete part or all of the actual operations.

[0099] In addition, it can be understood that the program code read from the storage medium is written into the memory provided in the expansion board inserted into the computer or into the memory provided in the expansion unit connected to the computer, and then based on the instructions of the program code, the CPU or the like installed on the expansion board or the expansion unit is made to execute part or all of the actual operations, thereby realizing the functions of any one of the above embodiments.

[0100] The present invention has been shown and described in detail above with reference to the accompanying drawings and preferred embodiments. However, the present invention is not limited to these disclosed embodiments. Based on the above-mentioned multiple embodiments, those skilled in the art can know that more embodiments of the present invention can be obtained by combining the code review means in the above different embodiments, and these embodiments are also within the protection scope of the present invention.

Claims

1. A method for collecting statistical information of time series data in a distributed database, characterized in that: The implementation of this method includes: Database operation cluster: an application database cluster includes multiple database operation nodes; Database operation node, including data change processing, data change monitoring, statistics refresh monitoring, and optimizer services; The data change processing module is responsible for processing data operations, including adding, deleting, modifying, and querying data, and synchronizing the changes to the database storage engine; The data change monitoring module is responsible for recording the number of increases and decreases in data records in each table; The statistics refresh monitoring module is responsible for regularly monitoring data changes, deciding whether to trigger the update of statistics, processing the update of statistics of different types of data, and updating the statistics in the system table; The query plan optimization module, i.e. the optimizer, selects the optimal query plan for the query statement based on the rules and computational costs; The database execution module is responsible for the execution of data definition statements and data operation statements; Database storage engine, which stores application load data objects.

2. A distributed database time series data statistical information collection method according to claim 1, characterized in that: The database operation node, Stable operation of various basic database services, including automatic collection of statistical information of various modal data, including relational data and time series data; Connect to the monitored cluster where the application is located and obtain the data objects referenced by all SQL statements contained in its application load.

3. A distributed database time series data statistical information collection method according to claim 1, characterized in that: The data change monitoring module records the change in the number of data records in each data table within a specified time period according to the DML statements of the add and delete types executed by the system.

4. A distributed database time series data statistical information collection method according to claim 1, characterized in that: The statistical information refresh monitoring module establishes the triggering rules and collection rules for the statistical information collection of time series data: When the automatic statistics collection switch is turned on, the statistics refresh monitoring module is started; When the following conditions are met: there is no statistical information of the time series table with the current data volume change in the system table; the latest update time of the statistical information of the time series table has exceeded the set threshold; the data record change of the time series table has exceeded the set threshold; In the case where there is a change in data volume but no update is triggered, the current change in the table in the data change monitoring module is accumulated to the cumulative record change of each table from the last statistical information update maintained by the statistical information refresh monitoring module; When the statistical information update of the time series table is triggered, different statistical information collection processes are triggered according to different types of columns in the time series table: for the primary key column, the COUNT operator is used to count the data in the column through the CREATE STATISTIC statement, and the count is synchronously filled into the unique count, and 0 is written to the non-null count; For the attribute column, use the CREATE STATISTIC statement to count the various counts of the data in this column using the COUNT operator, DISTINCT COUNT operator, and NULL COUNT operator. For the measurement value column, use the query statement to query the latest COUNT count of the time series table in the system table, and add the cumulative record change of the current table maintained by the statistical information refresh monitoring module from the last statistical information update as the COUNT and DISTINCTCOUNT of the measurement value, and set the NULL COUNT to 0; at the same time, execute the SELECT FIRST query to obtain the starting timestamp of the current measurement value of the table; Based on the statistical information obtained from the above three time series data columns, the statistical information is updated in the system table in units of columns, and redundant statistical information is deleted.

5. A distributed database time series data statistical information collection method according to claim 1 or 4, characterized in that: The statistical information refresh monitoring module implements an update mechanism for various data modal statistical information, including: Monitor data updates of different modes: When there are write and delete DML queries on the cluster nodes, the data change monitoring module will record the changes in the number of records on the corresponding data table; at the same time, the statistical information refresh monitoring module runs at a fixed frequency every specified time period. When data changes are detected during the runtime, the statistical information refresh calculation is triggered. Otherwise, no processing is performed and the module waits to enter the next round of monitoring. Distinguish the statistical information updates of different types of data columns of time series data: When the statistical information refresh monitoring module detects that the number of records in a table in the system has changed, it first accesses the data volume change records of each table maintained by the change monitoring module, checks the mode of the table, and triggers the statistical information inspection and collection mechanism corresponding to the relational data for relational data; for time series data, it triggers the statistical information inspection and collection mechanism corresponding to the optimized time series data; Verify whether the statistics refresh function is triggered: the statistics refresh is triggered only when the number of records in each table changes and reaches the set threshold; otherwise, skip this round of update and only count the data change of the current table into the data structure of the cumulative data change of each table since the last update maintained by the statistics refresh monitoring module; Calculate and update statistics of different types of columns of time series data: When it is detected that the table that needs to trigger statistics refresh is of time series type, different statistics calculation mechanisms are triggered for each column according to the characteristics of primary key columns, attribute columns, and value columns; Update the statistics of the system table: fill the statistics corresponding to each column of the table that currently triggers the update into the SQL DML insert statement and insert it into the system table responsible for maintaining the statistics; for the statistics table of time series data, introduce a new start timestamp column to estimate the data volume; when there are more than five statistics on a column of a table, the earlier information can be regarded as redundant information; therefore, when updating, the number of statistics on the current column of the current table is detected at the same time, and only the five most recent statistics change records are retained, and the earlier historical records are cleared.

6. A distributed database time series data statistical information collection method according to claim 1, characterized in that: The query plan optimization module establishes a data volume estimation rule based on the time series data measurement value column, including: To calculate the data volume of the measurement value column of the time series table, first read the start timestamp of the statistical information recorded in the system table, then read the counts of each type, estimate the amount of written data per time unit from the start time to the current time, and multiply it by the time range specified by the query to get the amount of data involved in the query; For other types of columns of time series data, directly use the various count calculations of the column in the system table.

7. A distributed database time series data statistical information collection method according to claim 1, characterized in that: The database execution module drives various operators to obtain accurate statistical information; Supports the collection of statistical information of time series data measurement value columns triggered by the CREATESTATISTIC statement. It performs aggregate calculations including the COUNT operator, DISTINCTCOUNT operator, and NULL COUNT operator to obtain accurate statistical information and update system tables.

8. A method for collecting statistical information of time series data in a distributed database according to claim 1, characterized in that: The database storage engine is used to collect and store the operation data in the database; Receive and store the data objects referenced by all SQL statements contained in the application load and other data objects associated with them. At the same time, collect and store other data used for subsequent statistical information collection and analysis. The operating data includes application load information, monitoring indicators, and database logs to provide support for statistical information collection.

9. A distributed database time series data statistical information collection device, characterized in that: include: at least one memory and at least one processor; The at least one memory is used to store a machine-readable program; The at least one processor is used to call the machine-readable program to implement the method described in any one of claims 1 to 8.

10. A computer-readable medium, characterized in that The computer readable medium stores computer instructions, which, when executed by a processor, can implement the method according to any one of claims 1 to 8.

Images