tsbs
tsbs copied to clipboard
Published
20 hours ago •
timescale
timescale
How to add Read Queries in IOT use-case
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 .
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.
@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
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
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
@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
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