How to add Read Queries in IOT use-case

[infokng]

Hi Team

When using TSBS benchmark with IOT use-case , I am able to see only write performance on storage end .
How can I make this workload to do Reads ?

Please guide me and I will do the rest .

[infokng]

@seybi87 @ryanbooz ......if you guys can share anything :)

[seybi87]

Can you please share the commands that you used?

Sounds like you have not yet executed the query execution which will perform the reads, see https://github.com/timescale/tsbs#benchmarking-query-execution-performance

And be aware that the IoT use case is only implemented for a subset of databases.

[infokng]

@seybi87 , thanks for coming back

I have tried both Devops and IOT use-cases , IOT shows no reads , Devops show some reads but its very less as compared to NVMe SSD performance ....its in KBs

Below is the trace from Devops use cases--> double-groupby-all and single-groupby-5-8-1 .

bash-4.4# ./tsbs_generate_queries --use-case="devops" --seed=123 --scale=4000 --timestamp-start="2016-01-01T00:00:00Z" --timestamp-end="2016-01-04T00:00:01Z" --queries=1000 --query-type="single-groupby-5-8-1" --format="timescaledb" | gzip > /mnt/timescaledb-queries-single-groupby-5-8-1.gz
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m: 1000 points
bash-4.4# cat /mnt/timescaledb-queries-single-groupby-5-8-1.gz | gunzip | ./tsbs_run_queries_timescaledb --pass=password --postgres="sslmode=disable port=5432" --db-name=devops1 --hosts=timescaledb --user=postgres --workers=8
After 100 queries with 8 workers:
Interval query rate: 43.51 queries/sec  Overall query rate: 43.51 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   130.64ms, med:   171.73ms, mean:   177.28ms, max:  489.21ms, stddev:    37.25ms, sum:  17.7sec, count: 100
all queries                                                        :
min:   130.64ms, med:   171.73ms, mean:   177.28ms, max:  489.21ms, stddev:    37.25ms, sum:  17.7sec, count: 100

After 200 queries with 8 workers:
Interval query rate: 44.84 queries/sec  Overall query rate: 44.17 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   130.64ms, med:   170.81ms, mean:   177.00ms, max:  538.82ms, stddev:    44.50ms, sum:  35.4sec, count: 200
all queries                                                        :
min:   130.64ms, med:   170.81ms, mean:   177.00ms, max:  538.82ms, stddev:    44.50ms, sum:  35.4sec, count: 200

After 300 queries with 8 workers:
Interval query rate: 48.43 queries/sec  Overall query rate: 45.50 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   130.64ms, med:   169.43ms, mean:   173.94ms, max:  538.82ms, stddev:    40.73ms, sum:  52.2sec, count: 300
all queries                                                        :
min:   130.64ms, med:   169.43ms, mean:   173.94ms, max:  538.82ms, stddev:    40.73ms, sum:  52.2sec, count: 300

After 400 queries with 8 workers:
Interval query rate: 46.25 queries/sec  Overall query rate: 45.69 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   130.64ms, med:   168.37ms, mean:   173.35ms, max:  538.82ms, stddev:    41.06ms, sum:  69.3sec, count: 400
all queries                                                        :
min:   130.64ms, med:   168.37ms, mean:   173.35ms, max:  538.82ms, stddev:    41.06ms, sum:  69.3sec, count: 400

After 500 queries with 8 workers:
Interval query rate: 48.40 queries/sec  Overall query rate: 46.20 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   130.64ms, med:   167.58ms, mean:   172.02ms, max:  538.82ms, stddev:    39.19ms, sum:  86.0sec, count: 500
all queries                                                        :
min:   130.64ms, med:   167.58ms, mean:   172.02ms, max:  538.82ms, stddev:    39.19ms, sum:  86.0sec, count: 500

After 600 queries with 8 workers:
Interval query rate: 46.70 queries/sec  Overall query rate: 46.29 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   130.64ms, med:   166.73ms, mean:   171.85ms, max:  538.82ms, stddev:    40.63ms, sum: 103.1sec, count: 600
all queries                                                        :
min:   130.64ms, med:   166.73ms, mean:   171.85ms, max:  538.82ms, stddev:    40.63ms, sum: 103.1sec, count: 600

After 700 queries with 8 workers:
Interval query rate: 48.30 queries/sec  Overall query rate: 46.56 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   130.64ms, med:   166.29ms, mean:   170.99ms, max:  538.82ms, stddev:    40.25ms, sum: 119.7sec, count: 700
all queries                                                        :
min:   130.64ms, med:   166.29ms, mean:   170.99ms, max:  538.82ms, stddev:    40.25ms, sum: 119.7sec, count: 700

After 800 queries with 8 workers:
Interval query rate: 47.63 queries/sec  Overall query rate: 46.69 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   129.79ms, med:   165.83ms, mean:   170.77ms, max:  538.82ms, stddev:    41.01ms, sum: 136.6sec, count: 800
all queries                                                        :
min:   129.79ms, med:   165.83ms, mean:   170.77ms, max:  538.82ms, stddev:    41.01ms, sum: 136.6sec, count: 800

After 900 queries with 8 workers:
Interval query rate: 47.43 queries/sec  Overall query rate: 46.77 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   129.79ms, med:   165.38ms, mean:   170.54ms, max:  538.82ms, stddev:    40.18ms, sum: 153.5sec, count: 900
all queries                                                        :
min:   129.79ms, med:   165.38ms, mean:   170.54ms, max:  538.82ms, stddev:    40.18ms, sum: 153.5sec, count: 900

After 1000 queries with 8 workers:
Interval query rate: 47.57 queries/sec  Overall query rate: 46.85 queries/sec
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   129.79ms, med:   164.94ms, mean:   170.18ms, max:  538.82ms, stddev:    39.98ms, sum: 170.2sec, count: 1000
all queries                                                        :
min:   129.79ms, med:   164.94ms, mean:   170.18ms, max:  538.82ms, stddev:    39.98ms, sum: 170.2sec, count: 1000

Run complete after 1000 queries with 8 workers (Overall query rate 46.77 queries/sec):
TimescaleDB 5 cpu metric(s), random    8 hosts, random 1h0m0s by 1m:
min:   129.79ms, med:   164.94ms, mean:   170.18ms, max:  538.82ms, stddev:    39.98ms, sum: 170.2sec, count: 1000
all queries                                                        :
min:   129.79ms, med:   164.94ms, mean:   170.18ms, max:  538.82ms, stddev:    39.98ms, sum: 170.2sec, count: 1000
wall clock time: 21.411520sec

bash-4.4# ./tsbs_generate_queries --use-case="devops" --seed=123 --scale=4000 --timestamp-start="2016-01-01T00:00:00Z" --timestamp-end="2016-01-04T00:00:01Z" --queries=1000 --query-type="double-groupby-all" --format="timescaledb" | gzip > /mnt/timescaledb-queries-double-groupby-all.gz
TimescaleDB mean of 10 metrics, all hosts, random 12h0m0s by 1h: 1000 points
bash-4.4# cat /mnt/timescaledb-queries-double-groupby-all.gz | gunzip | ./tsbs_run_queries_timescaledb --pass=password --postgres="sslmode=disable port=5432" --db-name=devops1 --hosts=timescaledb --user=postgres --workers=8



After 100 queries with 8 workers:
Interval query rate: 1.42 queries/sec   Overall query rate: 1.42 queries/sec
TimescaleDB mean of 10 metrics, all hosts, random 12h0m0s by 1h:
min:  4951.04ms, med:  5412.61ms, mean:  5475.64ms, max: 6887.42ms, stddev:   292.25ms, sum: 547.6sec, count: 100
all queries                                                    :
min:  4951.04ms, med:  5412.61ms, mean:  5475.64ms, max: 6887.42ms, stddev:   292.25ms, sum: 547.6sec, count: 100

After 200 queries with 8 workers:
Interval query rate: 1.48 queries/sec   Overall query rate: 1.45 queries/sec
TimescaleDB mean of 10 metrics, all hosts, random 12h0m0s by 1h:
min:  4951.04ms, med:  5372.16ms, mean:  5414.39ms, max: 6887.42ms, stddev:   254.81ms, sum: 1082.9sec, count: 200
all queries                                                    :
min:  4951.04ms, med:  5372.16ms, mean:  5414.39ms, max: 6887.42ms, stddev:   254.81ms, sum: 1082.9sec, count: 200

[infokng]

Iostat only shows writes , nvme2n1

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          34.65    0.00    9.16    0.03    0.00   56.16

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1           0.00         0.00         0.00          0          0
nvme1n1           0.00         0.00         0.00          0          0
nvme2n1         772.00         0.00    315552.00          0     315552
nvme3n1           0.00         0.00         0.00          0          0
sda              16.00         0.00       164.00          0        164
sdc               0.00         0.00         0.00          0          0
sdb               0.00         0.00         0.00          0          0
dm-0              0.00         0.00         0.00          0          0
dm-1              0.00         0.00         0.00          0          0
dm-2              0.00         0.00         0.00          0          0
sdd               0.00         0.00         0.00          0          0

[infokng]

@seybi87 , please let me know if you need more information

[seybi87]

I did a quick check on the provided details but I could not spot any issue but in general I suggest longer a longer runtime (i.e. bigger number of queries) of at least 10 minutes to ensure that the cache is warmed up.

We have also published some performance results with all the applied TSBS and Timescale on AWS EC2 configurations, maybe this helps as well: https://github.com/benchANT/database-ranking/tree/main/DevOps/2022

[infokng]

@seybi87
Now I am trying bigger IOT dataset to overcome caching , hopefully we will get Reads
DRAM --> 256 GB

bash-4.4# cat /mnt/timescaledb-data-48d.gz | gunzip | ./tsbs_load_timescaledb --host="timescaledb" --port=5432 --pass="password" --user="postgres" --workers=8 --in-table-partition-tag=true --chunk-time=8h --write-profile= --field-index-count=1 --do-create-db=true --force-text-format=false --do-abort-on-exist=false --db-name=iot2
time,per. metric/s,metric total,overall metric/s,per. row/s,row total,overall row/s
1677305052,1564565.46,1.564590E+07,1564565.46,312995.09,3.130000E+06,312995.09
1677305062,1810439.48,3.375032E+07,1687501.59,361999.50,6.750000E+06,337497.12
1677305072,1769513.65,5.144557E+07,1714838.90,353997.77,1.029000E+07,342997.33
1677305082,1661065.87,6.805608E+07,1701395.81,332003.01,1.361000E+07,340248.78
1677305092,1665039.39,8.470645E+07,1694124.56,333000.36,1.694000E+07,338799.10
1677305102,1624724.49,1.009537E+08,1682557.88,324999.30,2.019000E+07,336499.14
1677305112,1910342.24,1.200572E+08,1715098.44,381999.89,2.401000E+07,342999.23
1677305122,1805342.38,1.381105E+08,1726378.86,361002.08,2.762000E+07,345249.57
1677305132,1744817.64,1.555588E+08,1728427.62,348997.85,3.111000E+07,345666.05
1677305142,1689386.33,1.724526E+08,1724523.49,337999.75,3.449000E+07,344899.42
1677305152,1655893.45,1.890116E+08,1718284.41,330999.89,3.780000E+07,343635.83
1677305162,1565227.08,2.046637E+08,1705529.74,313002.46,4.093000E+07,341083.07
1677305172,1863931.06,2.233031E+08,1717714.51,372997.89,4.466000E+07,343538.07
1677305182,1810185.47,2.414049E+08,1724319.54,362001.97,4.828000E+07,344856.91
1677305192,1780372.12,2.592087E+08,1728056.40,355997.54,5.184000E+07,345599.62
1677305202,1655105.14,2.757598E+08,1723496.95,331000.15,5.515000E+07,344687.16
1677305212,1645335.74,2.922132E+08,1718899.23,328999.71,5.844000E+07,343764.36
1677305222,1574673.22,3.079599E+08,1710886.69,315000.16,6.159000E+07,342166.36
1677305232,1905259.74,3.270124E+08,1721116.80,381001.63,6.540000E+07,344210.31
1677305242,1800336.82,3.450158E+08,1725077.81,359998.64,6.900000E+07,344999.73
1677305252,1734902.34,3.623648E+08,1725545.65,347001.43,7.247000E+07,345095.05
1677305262,1674861.73,3.791134E+08,1723241.84,335000.43,7.582000E+07,344636.20
1677305272,1640064.53,3.955140E+08,1719625.43,327999.63,7.910000E+07,343912.87
1677305282,1564838.40,4.111624E+08,1713175.97,312999.84,8.223000E+07,342624.83

Summary:
loaded 14936588148 metrics in 8709.899sec with 8 workers (mean rate 1714897.97 metrics/sec)
loaded 2987324484 rows in 8709.899sec with 8 workers (mean rate 342980.38 rows/sec)

bash-4.4# du -sh /mnt/data2/base/*
594.4G  /mnt/data2/base/20491
0       /mnt/data2/base/pgsql_tmp

[infokng]

Below is the insert/write benchmark performance

bash-4.4# cat /mnt/timescaledb-queries-breakdown-frequency-48d.gz | gunzip | ./tsbs_run_queries_timescaledb --pass=password --postgres="sslmode=disable port=5432" --db-name=iot2 --hosts=timescaledb --user=postgres --workers=8
After 100 queries with 8 workers:
Interval query rate: 0.01 queries/sec   Overall query rate: 0.01 queries/sec
TimescaleDB truck breakdown frequency per model:
min: 556269.57ms, med: 587104.26ms, mean: 587106.88ms, max: 611647.49ms, stddev: 11233.08ms, sum: 58710.7sec, count: 100
all queries                                    :
min: 556269.57ms, med: 587104.26ms, mean: 587106.88ms, max: 611647.49ms, stddev: 11233.08ms, sum: 58710.7sec, count: 100

IOSTAT
Reads --> 500 MB/s ~ 2.7 GB/s
Writes --> 1.5 GB/s ~ 3 GB/s
CPU utilization --> 50% ~ 80%

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          56.53    0.00   10.77   15.70    0.00   17.00

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1           0.00         0.00         0.00          0          0
nvme1n1           0.00         0.00         0.00          0          0
nvme2n1        8630.00    663296.00   3483896.00     663296    3483896
nvme3n1           0.00         0.00         0.00          0          0
sda              21.00       756.00        24.00        756         24
sdc               0.00         0.00         0.00          0          0
sdb               0.00         0.00         0.00          0          0
dm-0              0.00         0.00         0.00          0          0
dm-1              0.00         0.00         0.00          0          0
dm-2              0.00         0.00         0.00          0          0
sdd               0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          48.93    0.01    8.65   24.29    0.00   18.12

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1           0.00         0.00         0.00          0          0
nvme1n1           0.00         0.00         0.00          0          0
nvme2n1        7650.00    612352.00   3126992.00     612352    3126992
nvme3n1           0.00         0.00         0.00          0          0
sda               0.00         0.00         0.00          0          0
sdc               0.00         0.00         0.00          0          0
sdb               0.00         0.00         0.00          0          0
dm-0              0.00         0.00         0.00          0          0
dm-1              0.00         0.00         0.00          0          0
dm-2              0.00         0.00         0.00          0          0
sdd               0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          47.87    0.00    8.17   25.60    0.00   18.37

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1           0.00         0.00         0.00          0          0
nvme1n1           0.00         0.00         0.00          0          0
nvme2n1        7704.00    607488.00   3132620.00     607488    3132620
nvme3n1           0.00         0.00         0.00          0          0
sda              17.00       436.00         0.00        436          0
sdc               0.00         0.00         0.00          0          0
sdb               0.00         0.00         0.00          0          0
dm-0              0.00         0.00         0.00          0          0
dm-1              0.00         0.00         0.00          0          0
dm-2              0.00         0.00         0.00          0          0
sdd               0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          25.84    0.00    4.83   51.37    0.00   17.97

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1           0.00         0.00         0.00          0          0
nvme1n1           0.00         0.00         0.00          0          0
nvme2n1       20664.00   3486336.00       256.00    3486336        256
nvme3n1           0.00         0.00         0.00          0          0
sda               0.00         0.00         0.00          0          0
sdc               0.00         0.00         0.00          0          0
sdb               0.00         0.00         0.00          0          0
dm-0              0.00         0.00         0.00          0          0
dm-1              0.00         0.00         0.00          0          0
dm-2              0.00         0.00         0.00          0          0
sdd               0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          23.43    0.02    4.77   54.53    0.00   17.25

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1           0.00         0.00         0.00          0          0
nvme1n1           0.00         0.00         0.00          0          0
nvme2n1       17969.00   2609808.00   1356800.00    2609808    1356800
nvme3n1           0.00         0.00         0.00          0          0
sda               0.00         0.00         0.00          0          0
sdc               0.00         0.00         0.00          0          0
sdb               0.00         0.00         0.00          0          0
dm-0              0.00         0.00         0.00          0          0
dm-1              0.00         0.00         0.00          0          0
dm-2              0.00         0.00         0.00          0          0
sdd               0.00         0.00         0.00          0          0

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          22.36    0.00    4.37   55.76    0.00   17.51

Device             tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
nvme0n1           0.00         0.00         0.00          0          0
nvme1n1           0.00         0.00         0.00          0          0
nvme2n1       16171.00   2015600.00   2255616.00    2015600    2255616
nvme3n1           0.00         0.00         0.00          0          0
sda               0.00         0.00         0.00          0          0
sdc               0.00         0.00         0.00          0          0
sdb               0.00         0.00         0.00          0          0
dm-0              0.00         0.00         0.00          0          0
dm-1              0.00         0.00         0.00          0          0
dm-2              0.00         0.00         0.00          0          0
sdd               0.00         0.00         0.00          0          0