Comments (8)
@seybi87 @ryanbooz ......if you guys can share anything :)
from tsbs.
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.
from tsbs.
@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
from tsbs.
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
from tsbs.
@seybi87 , please let me know if you need more information
from tsbs.
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
from tsbs.
@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
from tsbs.
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
from tsbs.
Related Issues (20)
- Tsbs supports OpenTsdb?
- Mismatched interval in description and generated sql for single-groupy-X-X-X series
- Mismatched interval in description and generated sql for cpu-max-all-X series
- Mismatched interval in description and generated sql for double-groupby-X series
- Cannot install timescaoe/tsbs on AWS Linux machine HOT 1
- could not load data to Timestream, could you kindly help me on this HOT 1
- Scalable Analytical DBs Not Present
- Can not load data when use mongodb(6.0): no reachable servers HOT 1
- tsbs_load_questdb write: connection reset by peer
- TimescaleDB'compression policy is not effective after inserting data in iot test HOT 1
- timescale: tsbs_load panics with option `create-metrics-table: false`
- [influx] API Request "Unauthorized" HOT 1
- Can the corresponding query list be generated according to the query sequence of real services?
- Installation description outdated HOT 1
- unknown flag: --auth-token
- tsbs_load_timescale data loss HOT 1
- Will the vector database be added in the future to perform stress testing on similarity searches?
- Unable to benchmark InfluxDB 2.x - Clarify supported versions HOT 1
- tsbs_load load timescaledb error HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from tsbs.