Influxdb service crashes down

schema
influxql
influxdb
#1

I have a database and a measurement and lets call its as “sensors” and i see very high CPU usage and Influxdb crashes down when 3 different influx queries being executed. Here is the schema for this measurement

fieldKey fieldType

ActiveCount "integer"
MaxTime "integer"
MinTime "integer"
TotalCount "integer"
TotalTime “integer”

tag Key

Server -> 8 unique values
Path -> 9821 unique values with the max length to 156
Version -> 3 unique values with min length 36 and max lengh is 52
RunId -> with 8 unique values with the max length of 43
Type -> Two values with max length of 10

Here are the Influx queries that are leading very high CPU usage and these are being execured from Grafana and executing all three queries brings down the Influxdb service. My VM has 8 cores and 20GB memory

SELECT “TotalCount” AS “TotalCount”, “TotalTime” AS “TotalTime” FROM “sensors” WHERE “Path” =~ /^$Path$/ AND “Server” =~ /^$Server$/ AND $timeFilter GROUP BY “Path”, “Server”

Sensors Count

SELECT sum(“TotalCount”) FROM “sensors” WHERE “Path” =~ /^$Path$/ AND “Server” =~ /^$Server$/ AND $timeFilter GROUP BY time($interval), “Path” fill(null)

Sensors Time
SELECT sum(“TotalTime”) FROM “sensors” WHERE “Path” =~ /^$Path$/ AND “Server” =~ /^$Server$/ AND $timeFilter GROUP BY time($interval), “Path” fill(null)

The number of series in this database is 156821

Question is how can i optimize the database or queries so the service won’t crash down everytime these 3 queries getting executed.

Appreciate any help from experts

#2

@mohanbabud Each of these queries make use of the =~ regexp matcher. That is a non performant operator. Refactoring the queries to remove those should help with performance.

#3

@jackzampolin : thank you. Do you see any issue with the number of distinct values for the tags and the length of the values?