Am runnung influxDB v1.8.3 on Ubuntu 18.04
Server is a small hosted virtual Server with 1vCPU, 2GB of RAM and 20GB NVMe SSD
Usage is collection of visualisation of building automation sensor data, one time series every 5min, so really small amount of data collected. Total DB file size arounf 400MB at the moment, influxDB server has allocated 1.3G of RAM, so my assumption is that all the queries are served directly from RAM.
Details of the setup here:
show retention policies
name duration shardGroupDuration replicaN default
autogen 0s 168h0m0s 1 false
bmonrp 0s 4368h0m0s 1 true
show measurements
name: measurements
name
ESEKI_Controller_e
ESEKI_Controller_v
ESEKI_WVT1_v
ESEKI_WVT2_v
ESEKI_WVT3_v
ESEKI_WVT4_v
ESEKI_WVTall_e
ESEKI_WVTprognosall
show tag keys on XXX
…
name: ESEKI_Controller_v
tagKey
ESE_KI_ASB_AMT2_ta1_gradC
ESE_KI_CON_CWA1_status
_seq
…
show shards
name: _internal
id database retention_policy shard_group start_time end_time expiry_time owners
212 _internal monitor 212 2021-02-14T00:00:00Z 2021-02-15T00:00:00Z 2021-02-22T00:00:00Z
213 _internal monitor 213 2021-02-15T00:00:00Z 2021-02-16T00:00:00Z 2021-02-23T00:00:00Z
215 _internal monitor 215 2021-02-16T00:00:00Z 2021-02-17T00:00:00Z 2021-02-24T00:00:00Z
216 _internal monitor 216 2021-02-17T00:00:00Z 2021-02-18T00:00:00Z 2021-02-25T00:00:00Z
217 _internal monitor 217 2021-02-18T00:00:00Z 2021-02-19T00:00:00Z 2021-02-26T00:00:00Z
218 _internal monitor 218 2021-02-19T00:00:00Z 2021-02-20T00:00:00Z 2021-02-27T00:00:00Z
219 _internal monitor 219 2021-02-20T00:00:00Z 2021-02-21T00:00:00Z 2021-02-28T00:00:00Z
220 _internal monitor 220 2021-02-21T00:00:00Z 2021-02-22T00:00:00Z 2021-03-01T00:00:00Z
name: bmonlive
id database retention_policy shard_group start_time end_time expiry_time owners
120 bmonlive bmonrp 120 2019-08-12T00:00:00Z 2020-02-10T00:00:00Z 2020-02-10T00:00:00Z
121 bmonlive bmonrp 121 2020-02-10T00:00:00Z 2020-08-10T00:00:00Z 2020-08-10T00:00:00Z
122 bmonlive bmonrp 122 2020-08-10T00:00:00Z 2021-02-08T00:00:00Z 2021-02-08T00:00:00Z
123 bmonlive bmonrp 123 2021-02-08T00:00:00Z 2021-08-09T00:00:00Z 2021-08-09T00:00:00Z
124 bmonlive bmonrp 124 2021-08-09T00:00:00Z 2022-02-07T00:00:00Z 2022-02-07T00:00:00Z
125 bmonlive bmonrp 125 2022-02-07T00:00:00Z 2022-08-08T00:00:00Z 2022-08-08T00:00:00Z
126 bmonlive bmonrp 126 2022-08-08T00:00:00Z 2023-02-06T00:00:00Z 2023-02-06T00:00:00Z
127 bmonlive bmonrp 127 2023-02-06T00:00:00Z 2023-08-07T00:00:00Z 2023-08-07T00:00:00Z
128 bmonlive bmonrp 128 2023-08-07T00:00:00Z 2024-02-05T00:00:00Z 2024-02-05T00:00:00Z
129 bmonlive bmonrp 129 2024-02-05T00:00:00Z 2024-08-05T00:00:00Z 2024-08-05T00:00:00Z
130 bmonlive bmonrp 130 2024-08-05T00:00:00Z 2025-02-03T00:00:00Z 2025-02-03T00:00:00Z
131 bmonlive bmonrp 131 2025-02-03T00:00:00Z 2025-08-04T00:00:00Z 2025-08-04T00:00:00Z
132 bmonlive bmonrp 132 2025-08-04T00:00:00Z 2026-02-02T00:00:00Z 2026-02-02T00:00:00Z
133 bmonlive bmonrp 133 2026-02-02T00:00:00Z 2026-08-03T00:00:00Z 2026-08-03T00:00:00Z
134 bmonlive bmonrp 134 2026-08-03T00:00:00Z 2027-02-01T00:00:00Z 2027-02-01T00:00:00Z
influx_inspect report -detailed /home/influxdb/var/lib/influxdb/data/bmonlive/ESEKI_Controller_v/122
report: lstat /home/influxdb/var/lib/influxdb/data/bmonlive/ESEKI_Controller_v/122: no such file or directory
root@BM-ubuntu-2gb-nbg1-1:/var/lib/influxdb/data/bmonlive/bmonrp# influx_inspect report -detailed /var/lib/influxdb/data/bmonlive/bmonrp/122
DB RP Shard File Series New (est) Min Time Max Time Load Time
bmonlive bmonrp 122 000000535-000000002.tsm 842015 832227 2020-08-10T00:00:00Z 2021-02-07T23:55:00Z 221.107814ms
Summary:
Files: 1
Time Range: 2020-08-10T00:00:00Z - 2021-02-07T23:55:00Z
Duration: 4367h55m0s
Statistics
Series:
- bmonlive (est): 832227 (100%)
Total (est): 832227
Measurements (est):
- ESEKI_Controller_e: 48 (0%)
- ESEKI_Controller_v: 631056 (75%)
- ESEKI_WVT1_v: 54950 (6%)
- ESEKI_WVT2_v: 54332 (6%)
- ESEKI_WVT3_v: 45009 (5%)
- ESEKI_WVT4_v: 45551 (5%)
- ESEKI_WVTall_e: 3350 (0%)
- ESEKI_WVTprognosall: 1 (0%)
Fields (est):
- ESEKI_Controller_e: 8
- ESEKI_Controller_v: 15
- ESEKI_WVT1_v: 6
- ESEKI_WVT2_v: 6
- ESEKI_WVT3_v: 5
- ESEKI_WVT4_v: 5
- ESEKI_WVTall_e: 3
- ESEKI_WVTprognosall: 1
Tags (est):
- ESE_KI_ASB_AMT2_ta1_gradC: 336
- ESE_KI_CON_CWA1_status: 21
- ESE_KI_WVT_WZN1_FabNrKirche: 1
- ESE_KI_WVT_WZN2_FabNrPfarrbuero: 1
- ESE_KI_WVT_WZN3_FabNrKircheLufthzg: 1
- ESE_KI_WVT_WZN3_statusKircheLufthzg: 1
- ESE_KI_WVT_WZN4_FabNrPfarrhaus: 1
- ESE_KI_WVT_WZN4_statusKirchePfarrhaus: 4
- _seq: 80097
- meldung: 57
Completed in 1.799656252s
Observation: Time to perform a simple query as listed below is 500-1000msec. Amount of data retrieved in this query are about 400 data points. 30 of these queries are needed to buil/referesh a full dashboard, so dashboard refresh in grafana is perceived as slow.
explain SELECT first(“ESE_KI_ASB_AMT1_ta_gradC”) FROM “ESEKI_Controller_v” WHERE time >= 1612134000000ms and time <= 1614553199000ms GROUP BY time(24h) fill(none);
QUERY PLAN
EXPRESSION: first(ESE_KI_ASB_AMT1_ta_gradC::float)
NUMBER OF SHARDS: 2
NUMBER OF SERIES: 245624
CACHED VALUES: 0
NUMBER OF FILES: 5896
NUMBER OF BLOCKS: 5896
SIZE OF BLOCKS: 200464
explain analyze SELECT mean(“ESE_KI_ASB_AMT1_ta_gradC”) FROM “ESEKI_Controller_v” WHERE time >= 1612134000000ms and time <= 1614553199000ms GROUP BY time(1h) fill(none);
EXPLAIN ANALYZE
.
└── select
├── execution_time: 36.105291ms
├── planning_time: 540.694624ms
├── total_time: 576.799915ms
└── build_cursor
├── labels
│ └── statement: SELECT mean(ESE_KI_ASB_AMT1_ta_gradC::float) FROM bmonlive.bmonrp.ESEKI_Controller_v GROUP BY time(1h) fill(none)
└── iterator_scanner
├── labels
│ └── expr: mean(ESE_KI_ASB_AMT1_ta_gradC::float)
├── create_iterator
│ ├── labels
│ │ ├── measurement: ESEKI_Controller_v
│ │ └── shard_id: 122
│ ├── cursors_ref: 42602
│ ├── cursors_aux: 0
│ ├── cursors_cond: 0
│ ├── float_blocks_decoded: 2028
│ ├── float_blocks_size_bytes: 68952
│ ├── integer_blocks_decoded: 0
│ ├── integer_blocks_size_bytes: 0
│ ├── unsigned_blocks_decoded: 0
│ ├── unsigned_blocks_size_bytes: 0
│ ├── string_blocks_decoded: 0
│ ├── string_blocks_size_bytes: 0
│ ├── boolean_blocks_decoded: 0
│ ├── boolean_blocks_size_bytes: 0
│ └── planning_time: 438.134818ms
└── create_iterator
├── labels
│ ├── measurement: ESEKI_Controller_v
│ └── shard_id: 123
├── cursors_ref: 3869
├── cursors_aux: 0
├── cursors_cond: 0
├── float_blocks_decoded: 3869
├── float_blocks_size_bytes: 131546
├── integer_blocks_decoded: 0
├── integer_blocks_size_bytes: 0
├── unsigned_blocks_decoded: 0
├── unsigned_blocks_size_bytes: 0
├── string_blocks_decoded: 0
├── string_blocks_size_bytes: 0
├── boolean_blocks_decoded: 0
├── boolean_blocks_size_bytes: 0
└── planning_time: 100.685923ms
Question: Is performance as listed above normal for InfluxDB? Any idea what is wrong with the setup? Any optimization ideas? Would be happy if you could point me to sources, where I can learn more about optimization. But most important for me is the first question: Is the performance observed normal? Do you have similar experience? Just want to make sure that it makes sense at all to check optimization possibilities.
Thank you so much in advance.
