Performance: TDengine vs OpenTSDB

Abstract：In this test, TDengine is compared with OpenTSDB in the terms of writing throughput, query throughput, aggregation query response time and on-disk compression. The results demonstrates that TDengine outperforms OpenTSDB with 25x greater write throughput, 32x larger query throughput, 1000x faster in aggregation query (1000x when grouping by tags and 40x when grouping by time) while using 5x less disk space.

About the hardware

Servers and the testing program are running on the same Dell desktop of the model type ” OptiPlex- 3050″, with  4 cores and 8G memory. Detailed configurations are as follows:

• OS: Ubuntu 16.04 x64
• CPU: Intel(R) Core(TM) i3-7100 CPU @ 3.90GHz
• Memory: 8GB
• Disk: 1TB HDD

About the dataset

Two popular datasets were investigated before this test:

• New York taxi running information: cannot design the data model becuase there is no information for the single car
• faker generation tool: unfit for the IoT scenario because only strings can be generated.

To make this test repeatable, we write a specific data generation program, which simulates the temperature (int) and humidity (float) meausrements from thermohydrometers. Each thermohydrometer has three tags: device ID, device group, and device name. To make the simulation look authentic, the dataset distributes normally, not randomly.

All measurements are sampled every 1 second, containing 10k devices with 10k records from each device. There are 3 tags, 2 numeric values and 1 timestamp in each record.

2.data generatation code

The data generation code is written in Java, which can be downloaded here and executed as follows:

cd tests/comparisonTest/dataGenerator
javac com/taosdata/generator/DataGenerator.java 

3.options for the data generation code

• dataDir : filepath to store output data files
• numOfFiles : number of output data files
• numOfDevices : number of devices
• rowsPerDevice : number of records from each device

4.data generation

Execute the following commands and then 100 data files would be created. Each data file includes measurements from 100 devices. Totally there are 10k devices with 10k records from each device.

mkdir ~/testdata
java com/taosdata/generator/DataGenerator -dataDir ~/testdata -numOfDevices 10000 -numOfFiles 100 -rowsPerDevice 10000

TDengine preparation

TDengine is an  open-source big data platform designed and optimized for Internet of Things ( (IoT), Connected Vehicles, and Industrial IoT. Besides the 10x faster time-series database (TSDB), it provides caching, stream computing, message queuing and other functionalities to reduce the complexity and costs of development  and operations.

1.installation

• Download tdengine-1.6.1.0.tar.gz.
• Unzip and then run “install.sh” to install TDengine
• Start TDengien by executing “sudo systemctl start taosd”
• If the installation succeeds, enter “taos” in the terminal and then the following texts will be displayed:

Welcome to the TDengine shell, server version:1.6.1.0  client version:1.6.1.0
Copyright (c) 2017 by TAOS Data, Inc. All rights reserved.

taos> 

2.data model

For TDengine, a supertable would be created for all devices in the same type and then one table for one device. Thus, for the supertable, the data records include measurement time, temperature and humidity; the static device atributes in tags include device ID, device group, and device name.

SQL syntax for creating a supertable

create table devices(ts timestamp, temperature int, humidity float) tags(devid int, devname binary(16), devgroup int);

SQL syntax for dynamically creating one table using the supertable as template and insert one record

insert into dev1 using devices tags(1,'d1',0) values(1545038786000,1,3.560000);

3.testing code

The TDengine C driver is used to insert and query reocrds. In the future the testing code based on JDBCdriver will also be provided. Currently the testing code can be downloaded here. Enter the following commands in the terminal, an executable file “./tdengineTest” will be created:

cd tdengine
make

4.how to use the testing code

Writing Options

• writeClients : number of client connections to insert data concurrently, default 1
• rowsPerRequest: number of records in one request ranging 1-1000, default 100
• dataDir : data file path, same with the dataDir in the data generation code
• numOfFiles : number of files read from dataDir

For example

./tdengineTest -dataDir ./data -numOfFiles 10 -writeClients 2 -rowsPerRequest 100

Query Options 

• sql: path of the files which store all SQL statements to executed.

For example

./tdengineTest -sql ./sqlCmd.txt

OpenTSDB preparation

OpenTSDB is a scalable time series database built on top of Hadoop and HBase. It simplifies the process of storing and analyzing large amounts of time-series data generated by endpoints like sensors or servers.

1.installation

• Install HBase

Download “hbase-1.4.10-bin.tar.gz” from http://archive.apache.org/dist/hbase/1.4.10/

tar xzvf hbase-1.4.10-bin.tar.gz

cd hbase-1.4.10/bin

./start_hbase.sh

• Download and install OpenTSDB

git clone git://github.com/OpenTSDB/opentsdb.git
cd opentsdb
./build.sh

• Create tables in HBase

If this is the first time to run OpenTSDB, some tables need to be created in HBase beforehead.

env COMPRESSION=NONE HBASE_HOME=${HBASE_HOME}/hbase-version ${OpenTSDB_download_path}/src/create_table.sh

• Start OpenTSDB

sudo service opentsdb start

Open interative web page from http://hostIp:424

• Modify configuartion file

cd /etc/opentsdb
vim opentsdb.conf

tsd.core.auto_create_metrics = true 
tsd.http.request.enable_chunked = true 
tsd.http.request.max_chunk = 30000

If errors like “java.net.NoRouteToHostException: Cannot assign requested address (Address not available)” occur in this test, you can execute the following command:

sudo sysctl -w net.ipv4.tcp_tw_reuse=1

2.data model

For each row of records, create two points separately for temperature and humidity. Each point include a metric name, metric value and its timestamp. Each point is  attached with three tags related with the measurement device: device ID, device group and device name. 

3.testing code

This test uses OpenTSDB http client and the testing code can be downloaded here.

4.how to use testing code

Writing Options

• writeClients : number of client connections to insert data concurrently, default 1
• rowsPerRequest : number of records in one request ranging, default 100
• dataDir : data file path, same with the dataDir in the data generation code
• numOfFiles : number of files read from dataDir

For example

cd opentsdb/opentsdbtest/src/target
java -jar opentsdbtest-1.0-SNAPSHOT-jar-with-dependencies.jar -dataDir ~/testdata -numOfFiles 100 -writeClients 2 -rowsPerRequest 30

Querying Options

• sql: path of the files which store all SQL statements to be executed. 

java -jar opentsdbtest-1.0-SNAPSHOT-jar-with-dependencies.jar -sql sqlchoice

Write performance

One writing request can send one record or multiple records, denoting as “R/R” or “Records/Request”. The writing speed would increase with the “R/R”. Meanwhile, one database server can connect to many clients. The more connection, the larger write throughput. Thus, both one-connection case and multi-connection case would be tested.

1.TDengine

Writing tests are taken in multiple scanarios, including 1R/R, 100R/R, 500R/R, 1000 R/R and 2000R/R with different number of connections. You can change the options in the example and take different tests.

1. clean up the existing dataset
   drop database db;
2. start testing
   The example command to read 100 data files in the ~/testdata and insert 1000 records per request by 5 clients:
./tdengineTest -dataDir ~/testdata -numOfFiles 100 -writeClients 5 -rowsPerRequest 1000

Write throughput is as follows，unit in records/second

R/R	1 client	2 clients	3 clients	4 clients	5 clients	6 clients	7 clients
1	26824	43699	55137	62869	64529	68647	72277
100	415800	734484	895522	976085	1087902	1171074	1192199
500	479846	882612	1083032	1195100	1269196	1364256	1417004
1000	500751	914494	1121914	1239157	1367989	1418104	1476560
2000	512820	1055520	1174164	1306904	1426635	1458434	1477208

2.OpenTSDB

Writing tests are taken in multiple scenarios, including 1R/R, 10R/R, 30R/R, 50R/R and 80R/R with different number of connections. You can change the options in the following example and take different tests.

1. clean up the existing dataset
   1. Execute “./hbase shell” to launch HBase shell client.
      disable 'tsdb'; disable 'tsdb-meta'; disable 'tsdb-tree'; disable 'tsdb-uid';
drop 'tsdb'; drop 'tsdb-meta'; drop 'tsdb-tree'; drop 'tsdb-uid';
quit
   2. then create tables for OpenTSDB env
      COMPRESSION=NONE HBASE_HOME=${HBASE_HOME}/hbase-version ${OpenTSDB_download_path}/src/create_table.sh
2. start testing
   The example command to read 100 data files in the “~/testdata” and insert 30 records per request by 5 clients:
   java -jar opentsdbtest-1.0-SNAPSHOT-jar-with-dependencies.jar -dataDir ~/testdata -numOfFiles 100 -writeClients 5 -rowsPerRequest 30
   

Write throughput is as follows，unit in records/second

R/R	1 client	2 clients	3 clients	4 clients	5 clients	6 clients	7 clients
1	2370	2474	2572	2710	2497	2436	2371
10	18393	22828	22656	22924	22507	23200	23099
30	37463	45649	45735	46342	46795	46675	44908
50	45255	53222	50503	54475	54543	54283	54970
80	48794	56386	54564	56999	57198	57318	57272

3.Best Write Performance TDengine vs OpenTSDB

Compare the best writting performance of TDengine and OpenTSDB. Results are as follows:

R/R	1 client	2 clients	3 clients	4 clients	5 clients	6 clients	7 clients
TDengine	512820	1055520	1174164	1306904	1426635	1458434	1477208
OpenTSDB	48794	56386	54564	56999	57198	57318	57272

Figure 3 demonstrates that the writing speed of TDengine is in the order of 1million records per second while that of OpenTSDB is in the order of 60k records per second. In conclusion, it writes about 25 times faster in the TDengine than in the OpenTSDB.

Read Performance

For reading performance, this test takes a simple tranversing query, that is, reading all the data having been written into the database. this test only ingests 1million records in every query. The testing dataset has been already divided into 100 groups according to devgroup in the preparation and the following test just randomly selects 10 groups in every query.

1. how to use the TDengine testing code
   SQL expresssions are in the “tdengine/q1.txt”, for example, select * from db.devices where devgroup=0;
   Execute the following command:
   /./tdengineTest -sql ./q1.txt
2. how to use the OpenTSDB testing code
   The OpenTSDB query statments are in the json format and already written in the testing code. Change the option “sqlchoice” can choose the query operation.
   1. set sqlchoice to be q1
   2. Execute the following command:
      java -jar opentsdbtest-1.0-SNAPSHOT-jar-with-dependencies.jar -sql q1

Reading speed is as follows，unit in second

Latency	G-0	G-10	G-20	G-30	G-40	G-50	G-60	G-70	G-80	G-90
TDengine	0.235	0.212	0.218	0.209	0.210	0.209	0.209	0.209	0.216	0.208
OpenTSDB	6.69	5.92	6.58	6.65	7.29	7.21	5.98	6.20	7.03	6.57

Figure 4 demonstrates that the stable reading speed of TDengine is about 0.21s, that is, reading 5 million records per second. Meanwhile, the stable reading speed of OpenTSDB is about 6.7s, that is, reading 150k records per second. In conclusion, the data query throughput size in TDengine is 32 times larger than that in OpenTSDB.

Aggregation Performance

This part tests five aggregation function: COUNT, AVERAGE, SUM, MAX and MIN which are shared by TDengine and OpenTSDB. Each aggratation query would be paired with a filter to select 1/10, 2/10, 3/10, …, or all of the 100 devices.

1.TDengine

SQL expresssions are in the “tdengine/q2.txt”, for example,

select count(*) from db.devices where devgroup<10;

Execute the following command:

./tdengineTest -sql ./q2.txt

Query response time is as follows，unit in second

	10%	20%	30%	40%	50%	60%	70%	80%	90%	100%
COUNT	0.018	0.026	0.016	0.018	0.017	0.024	0.024	0.027	0.030	0.033
AVG	0.007	0.014	0.015	0.020	0.024	0.038	0.044	0.050	0.057	0.060
SUM	0.006	0.010	0.019	0.018	0.031	0.036	0.034	0.037	0.043	0.046
MAX	0.007	0.013	0.015	0.020	0.025	0.030	0.035	0.039	0.045	0.049
MIN	0.006	0.010	0.016	0.024	0.032	0.039	0.045	0.041	0.043	0.049
SPREAD	0.007	0.010	0.015	0.019	0.033	0.038	0.046	0.052	0.059	0.066

2.OpenTSDB

The OpenTSDB query statments are in the json format and already written in the testing code. Change the option “sqlchoice” can choose the query operation.

1. set sqlchoice to be q2
2. Execute the following command:
   java -jar opentsdbtest-1.0-SNAPSHOT-jar-with-dependencies.jar -sql q2

Query response time is as follows，unit in second,

	10%	20%	30%	40%	50%	60%	70%	80%	90%	100%
COUNT	67.82	67.3	66.87	67.17	66.67	67.23	67.17	66.88	67.1	66.72
MEAN	66.62	67.3	67.21	67.1	67.07	66.76	67.31	67.00	66.52	66.99
SUM	67.12	66.79	67.68	66.90	67.41	66.59	66.95	67.1	66.74	66.59
MAX	66.55	67.13	66.93	67.12	66.96	67.15	66.91	66.73	67.1	67.29
MIN	66.82	67.03	66.66	66.5	66.82	66.64	67.36	67.04	66.51	66.67

3.Comparison

Compare the query response time between TDengine and OpenTSDB based on the 1E8 records.

	Count	Average	Sum	Max	Min
TDengine	0.033	0.06	0.046	0.049	0.049
OpenTSDB	66.72	66.99	66.59	67.29	66.67

Figure 7 demonstrates that the response time of aggregation query in TDengine is within 100ms while the response time in OpenTSDB is about 66 seconds. In conclusion, the response time of aggregation query in the TDengine is more than 1000 times shorter than that in the OpenTSDB. 

Performance of Aggregation  grouped by tags

This part tests the aggregation performance grouped by tags. Each aggregation query would be paired with a filter to select 1/10, 2/10, 3/10, …, or all of the 100 devices.

1. how to use the TDengine testing code
   SQL expresssions are in the “tdengine/q3.txt”, for example, select count(temperature), sum(temperature), avg(temperature) from db.devices where devgroup<10 group by devgroup;
   Execute the following command:
   ./tdengineTest -sql ./q3.txt
2. how to use the OpenTSDB testing code
   1. set sqlchoice to be q3
   2. Execute the following command:
      java -jar opentsdbtest-1.0.SNAPSHOT-jar-with-dependencies.jar -sql q3

Query response time is as follows，unit in second,

	10%	20%	30%	40%	50%	60%	70%	80%	90%	100%
TDengine	0.030	0.028	0.031	0.041	0.069	0.066	0.077	0.091	0.102	0.123
OpenTSDB	125.91	127.39	126.79	126.42	125.73	126.85	127.77	126.99	127.16	126.41

Testing results show that the response time of aggretaion query grouped by tags is 1000 times shorter in the TDengine than that in the OpenTSDB.

Performance of aggregation grouped by time

This part tests the aggregation performane grouped by time. Each aggratation query would be paired with a filter to select 1/10, 2/10, 3/10, …, or all of the 100 devices.

1. how to use the TDengine testing code
   SQL expresssions are in the “tdengine/q4.txt”, for example, select count(temperature), sum(temperature), avg(temperature) from db.devices where devgroups<10 interval(1m);
   Execute the following command:
   ./tdengineTest -sql ./q4.txt
2. how to use the OpenTSDB testing code
   1. set sqlchoice to be q4
   2. Execute the following command:
      java -jar opentsdbtest-1.0-SNAPSHOT-jar-with-dependencies.jar -sql q4

Query response time is as follows，unit in second,

	10%	20%	30%	40%	50%	60%	70%	80%	90%	100%
TDengine	0.237	0.472	0.653	0.902	1.134	1.422	1.753	1.784	2.085	2.549
OpenTSDB	82.53	83.04	83.93	82.74	82.96	82.75	82.14	82.37	83.29	82.46

Testing results show that the response time of aggretaion query grouped by time is 40 times shorter in the TDengine than that in the OpenTSDB.

On-disk Compression

1.Original dataset size

In this test 100 data files are generated and stored in the folder “~/testdata”, whose size can be checked by command “du”. 

cd ~/testdata
du -h .

Results are shown in Fig. 10.

2.Disk space using by TDengine

In TDengine all data are saved in the directory “/var/lib/taos/data” by default. Stop TDengine server before checking the data size.

sudo systemctl stop taosd

Then check the size of data in the folder “/var/lib/taos/data/” by command “du”.

cd /var/lib/taos/data
du -h .

Results are shown in Fig. 11.

3.Disk space using by OpenTSDB

In OpenTSDB all data are saved in the directory “/var/lib/hbase/data” by default. Stop OpenTSDB server before checking the data size.

sudo service opentsdb stop

Check the data folder size by command “du”.

cd /var/lib/hbase/data/
du -sh 

Results are shown in Fig. 12.

4.Comparison of disk usage

Original test dataset occupies 3941MB in the disk, data in the OpenTSDB 2.3GB and data in the TDengine 459MB. The on-disk compressino ratio in the TDengine is 5 times of that in the OpenTSDB.

In the real scenarios of IoT, the on-disk compression ratio of the TDengine is expected to be larger because of the limited spread of real measurements and the column-based storage of TDengine. 

Feature Comparison

Both TDengine and OpenTSDB can be used to process time-series data and they have some similar features.

Feature	TDengine	OpenTSDB
SQL syntax	✅	❌
Private deployment	✅	✅
Scalability	✅	✅
System connection management	✅	✅
Query task management	✅	✅
Data import	✅	✅
Data export	✅	✅
Web management	✅	✅
Multi-layer storage	✅	✅
Telegraf data collection	✅	✅
Grafana data visualization	✅	✅
RESTful	✅	✅
C/C++	✅	❌
JDBC/ODBC	✅	❌
Go	✅	❌
Python	✅	❌
Database configuration	✅	✅
Replica configuration	✅	✅
Data alive time	✅	✅
Data partition	✅	✅
Streaming	✅	❌
Subscriber	✅	✅
Microsecond precision	✅	✅
Aggregation	✅	✅
Downsampling	✅	✅
Limit/offset	✅	✅
Interpolation	✅	✅
Data updated	❌	✅
Tag updated	✅	✅
Time delete	✅	✅
Data cleanup	✅	✅

Conclusion

In this test, TDengine is compared with OpenTSDB in the terms of writing throughput, query throughput, aggregation query response time and on-disk compression. Test dataset, codes and SQL expressions can be downloaded here. Thus, anyone can repeat this test again.

This test demonstates that TDengine outperforms OpenTSDB with 25x greater writting throughput, 32x larger query throughput, 1000x faster in aggrgation query (1000x when grouping by tags and 40x when grouping by time) while using 5x less disk space.

	TDengine	OpenTSDB
Write throughput	1,477,208 rows/second	57,272 rows/second
Time to ingest 1 million rows	0.21s	6.57s
Response time to average 100 million rows	0.06s	66.99s
Response time to average 100 million rows by tag	0.123s	126.41s
Response time to average 100 million rows by timestamp	2.549s	82.46s
Disk usage for 100 million rows	459 MB	2.3 GB