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, 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 super table would be created for all devices in the same type and then one table for one device. Thus, for the super table, 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 super table

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 super table 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

Figure 0 OpenTSDB interative web page
  • 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 the existing database in the TDengine shell client "taos"
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>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/R1 client2 clients3 clients4 clients5 clients6 clients7 clients
126824436995513762869645296864772277
100415800734484895522976085108790211710741192199
50047984688261210830321195100126919613642561417004
100050075191449411219141239157136798914181041476560
2000512820105552011741641306904142663514584341477208
Table 1 TDengine write throughput
Figure 1 TDengine write throughput

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
Execute "./hbase shell" to launch HBase shell client. The following text will be displayed:
HBase Shell
Use "help" to get list of supported commands.
Use "exit" to quit this interactive shell.
Version 1.4.10, r76ab087819fe82ccf6f531096e18ad1bed079651, Wed Jun  5 16:48:11 PDT 2019
hbase(main):001:0> disable 'tsdb'; disable 'tsdb-meta'; disable 'tsdb-tree'; disable 'tsdb-uid';
hbase(main):002:0> drop 'tsdb'; drop 'tsdb-meta'; drop 'tsdb-tree'; drop 'tsdb-uid';
hbase(main):003:0> quit

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/R1 client2 clients3 clients4 clients5 clients6 clients7 clients
12370247425722710249724362371
1018393228282265622924225072320023099
3037463456494573546342467954667544908
5045255532225050354475545435428354970
8048794563865456456999571985731857272
Table 2 OpenTSDB write throughput
Figure 2 OpenTSDB write throughput

3.Best Write Performance TDengine vs OpenTSDB

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

R/R1 client2 clients3 clients4 clients5 clients6 clients7 clients
TDengine512820105552011741641306904142663514584341477208
OpenTSDB48794563865456456999571985731857272
Table 3 TDengine vs OpenTSDB largest write throughput
Figure 3 TDengine vs OpenTSDB largest write throughput

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.

set sqlchoice to be q1

Execute the following command:
java -jar opentsdbtest-1.0-SNAPSHOT-jar-with-dependencies.jar -sql q1

Reading speed is as follows,unit in second

LatencyG-0G-10G-20G-30G-40G-50G-60G-70G-80G-90
TDengine0.2350.2120.2080.2180.2090.2100.2090.2090.2160.208
OpenTSDB6.695.926.586.657.297.515.986.207.036.57
Table 4 Read Performance TDengine vs OpenTSDB
Figure 4 Read Performance TDengine vs OpenTSDB

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


Latency10%20%30%40%50%60%70%80%90%100%
count0.0180.0260.0160.0180.0170.0240.0240.0270.0300.033
avg0.0070.0140.0150.0200.0240.0380.0440.0500.0570.060
sum0.0060.0100.0190.0180.0310.0360.0340.0370.0430.046
max0.0070.0130.0150.0200.0250.0300.0350.0390.0450.049
min0.0060.0100.0160.0240.0320.0390.0450.0410.0430.049
Table 5 TDengine Query response
Figure 5 TDengine Query response

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.

set sqlchoice to be q2

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,


Latency10%20%30%40%50%60%70%80%90%100%
count67.8267.366.8767.1766.6767.2367.1766.8867.166.72
mean66.6267.367.2167.167.0766.7667.3167.0066.5266.99
sum67.1266.7967.6866.9067.4166.5966.9567.166.7466.59
max66.5567.1366.9367.1266.9667.1566.9166.7367.167.29
min66.8267.0366.6666.566.8266.6467.3667.0466.5166.67
Table 6 OpenTSDB Aggretation Response
Figure 6 OpenTSDB Aggretation Response

3.Comparison

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

>
Latencycountaveragesummaxmin
TDengine0.0330.060.0460.0490.049
OpenTSDB66.7266.9966.5967.2966.67
Table 7 Aggregation Response TDengine vs OpenTSDB
Figure 7 aggregation Response TDengine vs OpenTSDB

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

set sqlchoice to be q3


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,

Latency10%20%30%40%50%60%70%80%90%100%
TDengine0.0300.0280.0310.0410.0690.0660.0770.0910.1020.123
OpenTSDB125.91127.39126.79126.42125.73126.85127.77126.99127.16126.41
Table 8 Query Response grouped by tags TDengine vs OpenTSDB
Figure 8 Query Response grouped by tags TDengine vs OpenTSDB

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 devgroup<10 interval(1m);

Execute the following command:

./tdengineTest -sql ./q4.txt

2. how to use the OpenTSDB testing code

set sqlchoice to be q4

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,

Latency10%20%30%40%50%60%70%80%90%100%
TDengine0.2370.4720.6530.9021.1341.4221.7531.7842.0852.549
OpenTSDB82.5383.0483.9382.7482.9682.7582.1482.3783.2982.46
Table 9 Query Response grouped by time TDengine vs OpenTSDB
Figure 9 Query Response grouped by time TDengine vs OpenTSDB

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.

 Figure 10 Original dataset size 

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.

Figure 11 Disk space usage in the TDengine

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.

Figure 12 Disk space usage in the OpenTSDB

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 supportTDengineOpenTSDB
SQL syntaxYesNo
private deploymentYesYes
scalabilityYesYes
system connection managementYesYes
query task managementYesYes
data importYesYes
data exportYesYes
Web managementYesYes
multi-layer storageYesYes
Telegraf data collectionYesYes
Grafana data visualizationYesYes
RESTFulYesYes
C/C++YesNo
JDBC/ODBCYesNo
GOYesNo
PythonYesNo
database configurationYesYes
replica configuationYesYes
data alive timeYesYes
data partitionYesYes
streamingYesNo
subscriberYesYes
aggregationYesYes
downsamplingYesYes
limit/offsetYesYes
interpolationYesYes
data updatedNoYes
tag updatedYesYes
time deleteYesYes
data cleanupYesYes
Table 10 Features TDengine vs OpenTSDB

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.

TDengineOpenTSDB
write throughput1477208 rows/second57272 rows/second
time to ingest 1million rows0.21s6.57s
response time to average 1E8 rows0.06s66.99s
response time to average 1E8 rows according to tags0.123s126.41s
response time to average 1E8 rows according to timestamp2.549s82.46s
disk usage by 1E8 rows of records459MB2.3GB
Table 11 TDengine vs OpenTSDB