Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion

Time:2021-9-23

Small t guide: tdengine is used in the eighth five year plan information new energy power Internet of things platform to store, query and analyze the real-time data of Internet of things equipment and the telemetry data of photovoltaic equipment sensors. It is required to support real-time data access, processing and storage of at least 50000 devices and a total of 4 million measuring points, with an estimated daily increment of more than 4 billion. Previously, using timescaledb encountered bottlenecks in both read-write performance and hardware resources, and there was no clustering function. Then switched to tdengine, the read-write performance was improved by 10 times, and the storage cost was reduced to about 1 / 5 of the original.

Usage scenario introduction

Current business scenario

Firstly, we use tdengine on the new energy power Internet of things platform, which is mainly used for the storage, query and analysis of telemetry real-time data of photovoltaic equipment.

The new energy Internet of things production and operation platform integrates the existing power station data through the Internet of things and big data technology to realize the unified operation monitoring and centralized data management of equipment in the park. Provide comprehensive, convenient and differentiated data and services to different personnel.

Provide equipment status information, alarm information, remote fault diagnosis information, real-time data and other data application services to operation and maintenance personnel; Provide application services such as summary and change trend of various test data to managers and operators. The overall architecture of the platform system is as follows:
Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion

According to the design concept of hierarchical implementation, the overall architecture of the system realizes the function separation of each software system in the underlying design to ensure the efficient operation of each part.

Data scale, query pressure and other requirements

The planned and designed data storage scale is about 16t. At present, the daily increment of data is more than 100 million. After all measuring points are connected, the daily increment is expected to be more than 4 billion; The system shall support real-time data access, processing and storage of at least 50000 devices and a total of 4 million measuring points (semaphores and analog quantities).

The routine query of the application system is about 50qps, and the high concurrency is about 100qps. The maximum span of a historical data query and analysis is one year, and supports multi-point and multi-mode analysis. The time series data analysis interface is as follows:
Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion

Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion

Real time display interface of working condition:
Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion

Current total data volume and daily increment:
Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion
Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion

Number of super tables and sub tables currently created:
Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion

Introduction to data model

At present, different super tables are established according to different types of measuring points. Different sub tables are created according to different test point IDs and test point numbers as tags. In this way, we can directly conduct single table analysis for the measuring points, with high processing performance and fast speed. It can also analyze multiple test points and directly operate the super table, which makes the business implementation simple and takes into account the query performance.

Query requirements

·Simple query

Historical data query analysis and time interval aggregation analysis for a single measuring point. It is mainly to check the abnormal data and alarm data of a single measuring point. Tdengine classifies the data of similar devices into a super table, but each device will establish its own sub table according to the structure of the super table. In fact, only one sub table is queried for querying the data of a single test point, which greatly reduces the amount of data traversed, and the query analysis is basically millisecond response. Simple query SQL legend:
Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion

·Aggregate query

Comparative analysis of time migration of different aggregation types with the same time dimension at multiple measurement points. Focus on comparison to facilitate users to more effectively confirm the abnormalities and differences at different measuring points.
Instead of timescaledb, tdengine takes over the photovoltaic power system with an increasing data volume of 4 billion

·Group aggregation query of mass measuring points

Group and time period aggregation query of hundreds of measuring points; In a certain period of time, the query of a large number of measuring points can still respond quickly.

Query pressure

The peak concurrency is 100qps and the average concurrency is 20qps. In the actual scenario, the pressure of concurrent query is small and does not reach the query bottleneck of tdengine.

Benefits from adopting tdengine

system function

The read-write performance is about 10 times higher than that of the original timescaledb database. In the data access layer, there is no need to worry about the write performance bottleneck of the database; The application layer of data analysis and query is also greatly improved compared with the original system, especially in aggregate analysis with large time span, which almost responds instantaneously.

Through the out of order insertion function, the problem of out of order writing caused by untimely data transmission on the edge side due to network problems is solved, and the integrity of data is guaranteed.

Compared with timescaledb, the cluster function has great advantages. Timescaledb does not have the cluster function but supports the primary and standby databases in the flow replication mode; The tdengine cluster is easy to build and has no distinction between master and slave nodes. It is friendly to application transformation and support, and the reading and writing performance of the cluster version is greatly improved.

The data storage increases the multi copy function of the cluster, improves the safety and reliability of the system through data redundancy, and reduces the operation and maintenance cost of the system.

Hardware and software resources

The system saves a lot of computing resources and storage resources, and reduces the storage cost by about 4 times.

Compared with the timescaledb time series library before tdengine was used, after compression was started, the disk occupied by 7 billion data was 165G, and the historical data of one month could not be queried within one minute; After using tdengine, the disk space is about 40g, and the aggregation query for one month’s historical data is returned in milliseconds. Relevant queries are as follows:

Problems and solutions in the application of tdengine

The original time series library bulk imports tdengine with a large amount of data. It is very troublesome to batch import in version 1.6. Only about 200 pieces of data can be inserted at a time. Later, 1m data can be inserted at a time after upgrading to version 2.0 or above, which greatly improves the batch import performance between different database and different table structures.

Considerations for future use of tdengine

After a period of online operation, tdengine has excellent performance. We decided to gradually update all our Internet of things projects to tdengine in the future.

Expectations and suggestions on tdengine functions
Analytical function enhancement, hoping to add practical common analysis functions in the time series library;

Enhance the continuous aggregation query function. At present, the continuous aggregation function is not practical;

Product ecology, integration with spark, Flink and other open source analysis tools.

Author: Li Liangzheng, information development engineer of the Eighth Five Year Plan