Transforming a medical asset management IoT platform by migrating from MySQL and OpenTSDB

Any IoT platform consists of hundreds of use cases and complex data models. With changing business requirements and massive increases in data volumes, the original architecture of OpenTSDB and MySQL was reaching its limits. After comprehensive research into and deployment of TDengine, the Medatc IoT platform has been completely transformed. This article shares Medatc’s experience in upgrading and modernizing its system architecture.

The Medatc IoT Platform

With the goal of improving efficiency and reducing consumption, Medatc helps medical institutions achieve efficient management of active equipment, and provides all-round support for intelligent analysis of equipment utilization, scientific equipment configuration, equipment dynamic allocation, and equipment safety assurance. At present, a total of 15 applications and 30 event and anomalous alarm reminders have been launched.

Prior to deploying TDengine, the system was implemented by a combination of OpenTSDB and MySQL. Since OpenTSDB could not meet the requirements of some of our business queries, 80% of the query use-cases had to be implemented using tables in a MySQL database. The problem with this is that because of the massive data volumes, we had to periodically separate hot and cold data in the MySQL database and this took a lot of maintenance and administration work.

Transforming an IoT Platform With TDengine

While OpenTSDB is a large and comprehensive database system, it is quite cumbersome. As our business requirements changed and as data volumes kept growing, its limitations became apparent and a system architecture upgrade became urgent and inevitable.

In 2021, after a thorough study of TDengine, we decided to migrate our IoT platform from OpenTSDB because of the many relevant features of TDengine. Since we completed the migration, about 80% of the models have been put into TDengine, and about 20% of the main data or dimension table data have been stored in the MySQL database.

Essentially the data storage model is the reverse of what it was with OpenTSDB and MySQL where 80% of the data was in MySQL.  Our server resource usage has also decreased, the overall speed of the application is significantly improved, and the data model and data indicators are more flexible and changeable.

The following is the topological road map of Medatc network:

The following is a comparison of the database before and after the transformation:

DatabaseServer ConfigurationNumberIssues
BeforeOpenTSDB8 Cores, 32 GB RAM2Slow queries, few aggregate query functions
AfterTDengine4 Cores, 8 G RAM3Fast queries, many aggregate query functions

The following query is made with 110,000 data tables, and the results are as follows:

Aggregate query – query against 1,155,876 records completed using the GROUP BY aggregate query in less than 0.17 seconds :

In terms of write speed, the following shows Flink writing to the TDengine database. 2.5 million pieces of collected data were written in about 3 hours. This amount of writing is far from reaching a writing bottleneck for TDengine and there is ample room for growth even with the current server configuration.

Feedback on TDengine

Sometimes we need to re-calculate some hourly, daily or monthly indicators. The recalculated data needs to overwrite the previous data or delete and rewrite the previous data. However, TDengine currently does not support data deletion, and we hope that there will be deletion support in the future.

At present, update only supports updating data with the same timestamp. We hope there will be support for update using tag and common field filtering since there are business use cases that require such functionality.

In addition, there are some restrictions on sorting, and it is hoped that tags and common fields can be sorted in ascending and descending order.

Finally, I would like to thank the TDengine team for an excellent open source time-series database and for their support and help to the Medatc R&D team. I wish TDengine greater and greater success.