Very slow Influxql performance (Influxdb 2.6)

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)

Hello @harm,
Yes there are definitely tradeoffs between 2.x and 1.x.
You are trading analytics and sophisticated query ability for some performance when it comes to using Flux vs InfluxQL. Especially with some basic queries.
However you have vastly more capabilities with Flux.
That being said InfluxData is moving towards supporting more of SQL and InfluxQL.
Learn more about InfluxDB Cloud powered by IOx which is the direction that InfluxData is taking here:

Thanks!

Hi, @Anaisdg, I’m not sure, I don’t think this is a trade-off issue, nor intended. I suspect this is a genuine bug/regression.

I suspect (now), that this forum is not the proper location to file such problems. I re-filed the problem at influxdb issue list, (accompanied with some generated profile files, as suggested over there).

I guess the issue can be closed here.

1 Like