Selection of Big Data Solution for Shuanghui: From InfluxDB+Redis to TDEngine for millisecond level queries

The energy management and control big data system of several branches of Shuanghui Development mainly adopts two technology stack: InfluxDB/Redis and Kafka/Redis/HBase/Flink. It is very difficult for small and medium R & D teams not only to build the system but also to implement the operation and maintenance. After comparing and testing the function and performance of the two big data platforms, Tdengine is finally adopted as the implementation scheme.

Project background

Based on the demand for energy control shuanghui development, using the cloud platform technology and electrical automation processing means, of shuanghui development level 1, level 2, level 3 energy meter overall renovation, realize the instrument network, further through the edge gateway energy monitoring data collection, and reporting to the cloud platform, establish a unified energy management information system, Realize the real-time monitoring of energy, report statistics, energy flow analysis and prediction, reduce the enterprise unit product energy consumption, improve economic benefits, and finally achieve enterprise energy fine management.

The overall architecture

The energy management and control platform is built based on private cloud, including complete IaaS layer, PaaS layer and SaaS layer. The energy acquisition system, as the most important part of the management and control platform, adopts Tdengine as the core data engine to insert online instrument data through RESTful interface, and realize the efficient and stable storage of large-scale time-series data. At the same time, it also provides real-time data query, historical aggregate statistics, flow calculation, subscription services and other functions for the energy management and control application layer to realize energy map monitoring, energy consumption early warning, energy flow prediction and comprehensive decision-making of energy interconnection. The specific structure is shown in the figure below.

Tdengine key applications

The Connector choice

The most critical link of data collection in this project is to insert the subscribed MQTT data into Tdengine, so it involves the selection of Tdengine connector. We usually use Java in the project, and the performance of JDBC is relatively strong, so theoretically, we should choose JDBC API. In the end, however, I chose the RESTful Connector for the following reasons:

1) Simplicity

Undoubtedly, RESTful is the most universal. TDengine operates the database directly through the SQL statement contained in the HTTP POST request BODY. As a sequential database, TDengine itself does not provide stored procedures or transaction mechanism, and basically executes a single SQL statement at a time. So RESTful is easy to use.

2) Portability

The Java applications of this project are all deployed in Kubernetes, so the Java application that inserts data into Tdengine needs to be containerized. As we have learned before, JDBC needs to rely on the local function library libtaos.so file, so containerized deployment may be more troublesome. RESTful can only be realized by using OKHttp library, which has strong portability.

3) Data scale

The scale of data collection in this project is not large, about 7000 pieces of data per minute. Even if the subsequent data collection function is extended to other branches, RESTful also fully meets the performance requirements.

But generally speaking, JDBC is in insert and query performance has certain advantages, and supports the selection of valid nodes from Firstep and secondEp for connection (similar to Nginx KeepAlive high availability), the current release of Tdengine, JDBC maintenance and promotion is also a priority. Subsequent projects may also be migrated to JDBC.

RESTful code implementation

1) ThreadPoolExecutor thread pool

The code to subscribe to EMQX and RESTful insert Tdengine is written in the same Java service. Every time an MQTT subscription message is received, a thread from the thread pool is started to insert data into Tdengine. The initialization is as follows:

ExecutorService pool = new ThreadPoolExecutor(150, 300, 1000, TimeUnit.MILLISECONDS,        new ArrayBlockingQueue<Runnable>(100), Executors.defaultThreadFactory(),new ThreadPoolExecutor.DiscardOldestPolicy());

Thread pool adopts the array based FIFO queue and the rejection strategy of discarding early tasks. Because the single RESTful insertion in this scene is about 100~200 pieces of data, the execution speed is fast, and the delayed execution is most likely due to the abnormal SQL statement or the abnormal connection of TAOSD service and other reasons, the task should be discarded. The number of core threads is set to 150, which is relatively high, mainly to ensure peak stress resistance.

2) OKHttp thread pool

In each ThreadPoolExecutor thread, a ConnectionPool is used to manage the reuse of HTTP and HTTP/2 connections for RESTful inserts based on the OkHttp library to reduce network latency.

public ConnectionPool pool() { return new ConnectionPool(20, 5, TimeUnit.MINUTES); }

That is, the maximum number of idle connections is 20, and the maximum idle time of each connection is 5 minutes. Each OKHttp insert operation adopts asynchronous invocation mode, and the main code is as follows:

public void excuteTdengineWithSqlAsync(String sql,Callback callback) { try{ okhttp3.MediaType mediaType = okhttp3.MediaType.parse("application/octet-stream"); RequestBody body = RequestBody.create(mediaType, sql); Request request = new Request.Builder() .url(tdengineHost) .post(body) .addHeader("Authorization", "Basic cm9vdDp0YW9zZGF0YQ==") .addHeader("cache-control", "no-cache") .build(); mOkHttpClient.newCall(request).enqueue(callback); } catch (Exception E) {Logger. error(" + EquetMessage ()); }}

3) Java packaging image

Long-term stress tests show that 200 RESTful insert requests are performed per second, with a single request containing 100 pieces of data, each piece of data containing 5 groups of labels, and the Java service memory is stable between 300M and 600M. Moreover, the simulation scale mentioned above is only for a single Java application. In Kubernetes, multiple such PODs can be run to consume different MQTT topics, so the concurrency capability is sufficient. When packaging an image, the maximum heap memory is set to 1024MB.

ENTRYPOINT ["java","-Djava.security.egd=file:/dev/./urandom","-XX:MaxRAM=2000m","-Xms1024m","-jar","/app.jar"]

The performance test

1) RESTful insert performance

The data is inserted according to the RESTful code in Section 3.2. Both the Java program and the Tdengine cluster are running in the private cloud, and there is a 10,000MB optical switch between the virtual machines. The Java program is as shown in Section 3.2. The configuration is all 1TB hard disk, 12 cores, and 12GB (the private cloud has abundant CPU, but limited memory). After about three weeks of running in the production environment, the performance is summarized as follows:

Table 1 RESTful insert performance testing in a production environment

In the production environment, the performance of single insert is extremely high, which fully meets the requirements. Of course, we have also carried out a slightly large-scale insert scenario simulation in the early stage, mainly based on the version after 2.0.4.0. It is noted that the RESTful SQL statement of the TDEngine version before 2.0.4.0 is limited to 64KB. RESTful inserts perform very well in the simulated environment, as shown in the following table.

Table 2 RESTful insert performance test in simulated environment

2) RESTful query performance

The performance of SQL query using RESTful is also very good. At present, in the real production environment, the total amount of data is 8 million, which is relatively thin, so the query performance test is carried out in the simulation environment. Under the data volume of 800 million, the LAST_ROW function can reach the response speed of 10ms. The execution speed of count, interval, group by and other related functions is on the order of 100 milliseconds.

Implementation plan

This project aims at collecting energy data in 6 branches of Shuanghui Development (which will be expanded in the future). There are more than 1200 instruments (which will be expanded in the future). Each instrument includes 3 to 5 acquisition labels, and the acquisition frequency is 1 minute, and the data access scale is small. Six factory have their own independent tenant space, in order to facilitate their temporal database management, as well as convenient GeChang between aggregation query (currently six factory subordinate shuanghui development headquarters), so the factory establish super table respectively, each super table includes four tag, factory number, level meter, respectively belong to process and meter number, Specific super table construction is shown in the figure below.

The main clusters used include Tdengine cluster, EMQX cluster and Redis cluster. In terms of data collection, Redis cluster is only used to cache instrument connection state, which focuses on caching business system data. EMQX cluster is used to support the publication and subscription of MQTT data. Deploying in Kubernetes, EMQX cluster can realize flexible resource expansion. The TDengine cluster is deployed in IaaS virtual machines and supports the storage and query of large-scale time-series data.

Table 3 Cluster configuration information

According to the official advice of TDengine, “one table for one data collection point and one super meter for the same type of data collection point”, I set up super meters for water meters, electricity meters, steam meters and gas meters in different branches respectively. Each meter is set up separately to ensure that the time stamp of each meter is increased strictly. In the practice of TDEngine, the key experience is as follows:

1) Low threshold for cluster construction

TDEngine cluster installation and deployment is very convenient, especially compared with other clusters, only need a simple configuration can achieve production environment level construction, official documents are relatively rich, the community is active, and also greatly reduce the subsequent operation and maintenance costs.

2) High efficiency of insertion and query

The insertion and query performance of TDEngine is extremely high, which is also deeply felt in the actual operation. The last_row function is used to query the latest data of the instrument, which can basically reach the millisecond level. The aggregate query operation can also reach the hundredth millisecond level on the billions of data, which greatly provides the response speed of the system.

3) Full-stack sequential processing engine

Before using Tdengine, we mainly used two technology stack of influxDB /Redis and Kafka/Redis/HBase/Flink. For our small and medium R & D team, it is very difficult for us to build the system and implement the operation and maintenance. But after using Tdengine, everything is simplified. Tdengine integrates database, message queue, cache, streaming and other functions together, in a full stack way, bringing convenience to our big data system. The comparison of the technical scheme is shown in Table 4.

Note: Plan 1 refers to the influxDB /Redis, Plan 2 refers to Kafka/Redis/ Hbase /Flink, and Plan 3 refers to Tdengine

Table 4 Comparison of data collection schemes

As you can see from the comparison solutions in Table 4, TDengine (Solution 3) has a lot of advantages, especially in support of open source EMQX Broker (which relies heavily on RESTful interfaces). Others such as Kafka and InfluxDB can only integrate with Enterprise EMQX. In the aspect of data insertion and query efficiency, the key of the above three schemes lies in the comparison of Tdengine, HBase and InfluxDB. There are very detailed test reports on the official website, and Tdengine has absolute advantages, which will not be discussed here. So the choice of TDEngine is imperative.

Technical expectations

Tdengine has great advantages in terms of temporal database performance, and also integrates message subscription and stream computation capabilities, which can be said to be the deployment of Kafka and Flink in small and medium IoT scenarios. Of course, I understand that Tdengine is not designed to completely replace Kafka and Flink, especially in large cloud service projects, more co-exist.

However, at the edge, TDengine will generate more energy due to its extremely low resource utilization rate and excellent timing processing performance. It is expected to fully integrate edge flow calculation and MQTT Broker functions, expand the support of common industrial protocols such as Modbus and OPC-ua, and connect industrial equipment or Internet of Things facilities downward. Collaborate upwards with the edge Kubernetes ecosystem (such as Kubeedge, K3S, etc.) or directly with the cloud center.

System operation interface

The project focuses on energy consumption statistics, and the data collected online in TDEngine is cumulative amount. Therefore, when calculating energy consumption, queries grouped by tables and sampled by time cycle need to be executed in different super tables, similar to the following syntax:

Select last(min) as min (val) from (min) where (min (val) = 1) and (min (val) = 1) select last(min (val) as min (val) from (min) where (min (val) = 1) and (min (val) = 1) select last(min (val) as min (val) from (min) where (min (val) = 1) and (min (val) = 1 [Instrument number];

Thanks to the excellent performance of TDengine, it can basically guarantee the access delay of no more than 100 milliseconds. Below are some relevant PC and mobile interfaces (we use H5 to make mobile interfaces, in order to run directly on Android and iOS).

Write in the last

In fact, I have been paying attention to Tdengine since 2019, and I have read many speeches of General Tao, which have benefited a lot. Especially in August this year, Tdengine has been open source for cluster version, which is just about to start the energy data collection project. Therefore, Tdengine is adopted as the core timing engine, which is also very effective at present. During the implementation of this project, I would like to express my special thanks to Su Xiaowei, engineer of Taosi Data, who has helped solve the implementation problems related to TDengine for many times. We plan to continue to promote TDengine in other projects in the future, and we are also willing to pay for some commercial version functions, support domestic products and support Taosi.

The authors introduce

Muppet, master of education, associate researcher, mainly engaged in research and development of MES system and intelligent manufacturing related theory and the standard research, main research direction: digital factory enabling technology, manufacturing execution system is the key technology and intelligent manufacturing standards system, etc., to participate in more than 10 national project and enterprise projects, including national key research and development plan as well as the national intelligence manufacturing special, etc.