Flux Query too long query time

[indi59]

I got a flux query on which it take long time:

the array variable can have 1000 values ~.

ldevname = from(bucket: v.bucket)
 |> range(start: -365d)
  |> filter(fn: (r) => r._measurement == "LDEV_CNF" )
  |> filter(fn: (r) => r.poolid == "${pool}")
  |> keep(columns: ["ldevID_cnf"])
  |> group()
  |> distinct(column: "ldevID_cnf")
  |> findColumn(fn: (key) => true, column: "_value") 

from(bucket: v.bucket)
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "LDEV" and r._field == "LDEV_IOPS" )
  |> filter(fn: (r) => contains(value: r.ldevID, set: ldevname))

Is-it possible to optimize this one?

Many thanks,

[yosiasz]

The rule with influxdb is as follows:
measurement and tags are indexed whereas fields are not.
So you may want to configure items you are filtering as tags

InfluxDB schema design and data layout | InfluxDB OSS v1 Documentation

[indi59]

thanks for your reply. but the filter is made on tag, numeric value is well set on field.

[yosiasz]

@indi59

contains(value

Then contains is the culprit and notorious for performance issues

Dashboard times out when more than about 24 hours of data is queried

nope. is nodename a field or a tag? create a bucket + token in influxdb2 enable metric export in Proxmox VE. <--- this section is a concern how much faster is your query now with the regex change

[indi59]

I understand, many thanks for links and explanations.
But i already tried with a regex filter but without sucess.

ldevname = from(bucket: v.bucket)
|> range(start: -365d)
|> filter(fn: (r) => r._measurement == “LDEV_CNF” )
|> filter(fn: (r) => r.poolid == “${pool}”)
|> keep(columns: [“ldevID_cnf”])
|> group()
|> distinct(column: “ldevID_cnf”)
|> findColumn(fn: (key) => true, column: “_value”)

from(bucket: v.bucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r._measurement == “LDEV” and r._field == “LDEV_IOPS” )
// |> filter(fn: (r) => contains(value: r.ldevID, set: ldevname))
filter(fn: (r) => r[“ldevID”] =~ /${ldevname:regex}/)

→ invalid: compilation failed: error @15:35-15:54: regex parse error: ${ldevname:regex} error: repetition quantifier expects a valid decimal

value of ldevname is composed as follow: xx:xx:xx

[yosiasz]

=~ /^$hostname$/)
Maybe like this style?

[indi59]

An other error:

filter(fn: (r) => r[“ldevID”] =~ /^$ldevname$/)

or

filter(fn: (r) => r[“ldevID”] =~ /\b${ldevname:regex}\b/)

invalid: error @21:1-21:48: missing required argument tables

the search “ldevname” has following values:

i need to convert in decimal maybe?