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