Improving Query Latency in a Smart Heating Monitoring and Analysis System

Miaomiao Jia (TSCC)
Miaomiao Jia (TSCC)
/
Share on LinkedIn

TDengine is being used to monitor an extensive heating network and in the analysis system for the consequent energy consumption. Compared with MySQL, the storage has been significantly reduced and query latency has been significantly improved. We are increasing the use of TDengine across our business lines for various applications on time-series data.

Introduction

In our “smart heating” project we had started out using MySQL to store historical data. However, as the amount of data increased, query performance decreased significantly and it became difficult to meet the needs of business users. In order to alleviate the status quo, we started looking into TDengine, a time series database (TSDB). After doing our due diligence we realized that TDengine was really built from the ground up for IoT time-series data and that it supported SQL. We went through a comprehensive testing period before deploying TDengine in our project.

Currently, we are using TDengine in the applications that we discuss in detail below.

Heating Network Monitoring 

The heating network monitoring system currently includes both heat source monitoring and thermal station monitoring. The system monitors the operational status of heat sources and thermal stations in real-time and provides data visualization, so that operations managers can easily get an overview of the status of the entire heating network.

Energy Analysis System

The energy analysis system is used for real-time statistics, calculation, and monitoring of energy consumption, and establishes a graded energy consumption evaluation system. It also helps pinpoint sources of energy waste through an energy consumption ranking. This allows staff to carry out targeted improvement and optimization in order to reduce energy waste and realize real energy savings.

Essentially we have found that both systems have a very high demand for real-time queries and data visualization which allows real-time management of heating sources and real-time display of energy consumption trends among many other things.

For the processing of high-frequency time-series data generated by equipment, TDengine is undoubtedly a very suitable choice. However, due to improvements in the features offered by TDengine and performance improvements, we have accelerated the replacement of other database products with TDengine.

Naturally, our deployment of TDengine was not without its share of issues. For example, the old version of TDengine did not support “group by” for timestamps but this was resolved after upgrading. Another example is that the table structure obtained by different clients during queries is different. This is because the metadata of each client’s cache is inconsistent. Again, this was resolved by using the reset query cache command. We have run into minor issues for  which we have received timely feedback and help from the official technical support at TDengine as well as from the vast TDengine open source community.

TDengine in Production

We deployed TDengine in a three-node, three-copy cluster configuration. Each machine was configured with 16 Core, 32G RAM and a 1 TB hard drive. Data from our equipment is first collected and written to Kafka in real time, and then stored in TDengine using the Python connector library.

Currently we have created more than 5,500 subtables that collectively store about 90 million rows of data. The largest supertable has nearly 73 million rows of data, and a single row is about 180 bytes. Even with three copies, the current disk space is only 10.2G in total, thanks to the compression ratios of TDengine. This coupled with the configurable mechanism of data expiration and deletion, we basically no longer need to worry about the cost of disk storage .

The usage rates of memory and CPU are also maintained at around 1.9% and 0.3% which has led to an extremely stable and stress-free environment.

The SQL below shows some common queries that we run which basically execute and return data in milliseconds.

select sum(Ep) as Ep,sum(HM_HT) as HM_HT .............. interval(1d);
SELECT AVG(heatsourcepg) AS heatsourcepg, AVG(heatsourcetg) AS heatsourcetg, AVG(heatsourcef_mtrg) AS heatsourcef_mtrg .............. FROM iot_device.source_minute WHERE ts >="2022-04-06 12:00:00" AND ts <"2022-04-06 13:00:00.000" GROUP BY groupid, level

Conclusion

In 2019, TSCC began to actively explore new markets for smart energy services. We developed a comprehensive energy management and control system for heating, cooling, power and gas supply. We also developed a smart water supervision platform. A year later, we officially introduced TDengine into our smart platform and TDengine did not disappoint us. In the future, we expect TDengine to continuously contribute to the efficient utilization of urban energy, increasing use of clean energy, and the creation of a low-carbon smart city.

  • Miaomiao Jia (TSCC)

    Miaomiao Jia is an R&D Engineer at TSCC.