We are evaluating a migration from influxdb 1.8 to 2.6 and we noticed that some very simple queries became very slow, and trigger very high cpu load.
The queries that are failing are the type queries of that influxdb is actually designed for. Query for data in a certain (small) time window. Such queries should be very fast.
I reproduced the problem on a small database. Its a database collecting p1 measurements of a device (~ 1 insert second, 1 device).
I query for the latest data. (show all data for the last 5 seconds).
Using influxql I get very bad performance.
Please advise.
If this is not the proper location to report performance issues, let me know, and I’ll re-file the problem.
Steps to reproduce
influxql query (5s range)
select * from p1_values where time > '2023-03-15T15:10:25+00:00' and time <= '2023-03-15T15:10:30+00:00';
results
1 queries took 0.124614s (returned 5 rows), (this is a query done on localhost, no network overhead)
Running this periodically, (2 times per second) (over a persistent connection to the database) triggers a significant cpu load on the database server (cpu consumed by influxd process).
Expected behavior
I’d expect such query to finish in the 1-2 ms range, not > 120ms. On influxdb 1.8 similar queries take < 1 ms on a much larger database. (~200GB)
database
small database
# du -sh /var/lib/influxdb/engine/data/
2.2G /var/lib/influxdb/engine/data/
I have a low cardinality dataset.
influxdb.cardinality(bucket: "energy", start: -1y)
gives
Result: _result
Table: keys: []
_value:int
--------------------------
4
show tag keys
┏━━━━━━━┳━━━━━━━━┓
┃ index ┃ tagKey ┃
┣━━━━━━━╋━━━━━━━━┫
┃ 1┃region ┃
┃ 2┃sensor ┃
┣━━━━━━━┻━━━━━━━━┫
┃ 2 Columns, 2┃
┃ Rows, Page 1/1┃
┃ Table 1/1,┃
┃ Statement 1/1┃
┗━━━━━━━━━━━━━━━━┛
show field keys
Name: p1_values
┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━┓
┃ index ┃ fieldKey ┃ fieldType ┃
┣━━━━━━━╋━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━╋━━━━━━━━━━━┫
┃ 1┃CURRENT_ELECTRICITY_DELIVERY ┃float ┃
┃ 2┃CURRENT_ELECTRICITY_USAGE ┃float ┃
┃ 3┃DEVICE_TYPE ┃float ┃
┃ 4┃ELECTRICITY_DELIVERED_TARIFF_1 ┃float ┃
┃ 5┃ELECTRICITY_DELIVERED_TARIFF_2 ┃float ┃
┃ 6┃ELECTRICITY_USED_TARIFF_1 ┃float ┃
┃ 7┃ELECTRICITY_USED_TARIFF_2 ┃float ┃
┃ 8┃HOURLY_GAS_METER_READING ┃float ┃
┃ 9┃INSTANTANEOUS_ACTIVE_POWER_L1_NEGATIVE ┃float ┃
┃ 10┃INSTANTANEOUS_ACTIVE_POWER_L1_POSITIVE ┃float ┃
┃ 11┃INSTANTANEOUS_ACTIVE_POWER_L2_NEGATIVE ┃float ┃
┃ 12┃INSTANTANEOUS_ACTIVE_POWER_L2_POSITIVE ┃float ┃
┃ 13┃INSTANTANEOUS_ACTIVE_POWER_L3_NEGATIVE ┃float ┃
┃ 14┃INSTANTANEOUS_ACTIVE_POWER_L3_POSITIVE ┃float ┃
┃ 15┃INSTANTANEOUS_CURRENT_L1 ┃float ┃
┃ 16┃INSTANTANEOUS_CURRENT_L2 ┃float ┃
┃ 17┃INSTANTANEOUS_CURRENT_L3 ┃float ┃
┃ 18┃INSTANTANEOUS_VOLTAGE_L1 ┃float ┃
┃ 19┃INSTANTANEOUS_VOLTAGE_L2 ┃float ┃
┃ 20┃INSTANTANEOUS_VOLTAGE_L3 ┃float ┃
┃ 21┃LONG_POWER_FAILURE_COUNT ┃float ┃
┃ 22┃SHORT_POWER_FAILURE_COUNT ┃float ┃
┃ 23┃VOLTAGE_SAG_L1_COUNT ┃float ┃
┃ 24┃VOLTAGE_SAG_L2_COUNT ┃float ┃
┃ 25┃VOLTAGE_SAG_L3_COUNT ┃float ┃
┃ 26┃VOLTAGE_SWELL_L1_COUNT ┃float ┃
┃ 27┃VOLTAGE_SWELL_L2_COUNT ┃float ┃
┃ 28┃VOLTAGE_SWELL_L3_COUNT ┃float ┃
┣━━━━━━━┻━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┻━━━━━━━━━━━┫
┃ 3 Columns, 28 Rows, Page 1/1┃
┃ Table 1/1, Statement 1/1┃
build-tsi
I’ve tried rebuilding the indexes using influxd inspect build-tsi
. This did not have any (positive nor negative) effect on the performance.
version
InfluxDB v2.6.1 (git: 9dcf880fe0) build_date: 2022-12-29T15:53:07Z
on Ubuntu 20.04.6 LTS \n \l
Queries executed on localhost using python script using client influxdb==5.3.1
from influxdb import InfluxDBClient
Unfortunately running the query on the CLI (influx v1 shell
) does not show how long the query takes. (It shows the result, but not the duration, so I couldn’t test without a python client)
On influxdb 1.8 our python clients perform just fine. (as mentioned above, <1ms query times)