In my project we are storing several data from sensor belonging to buildings. Then we let te user select a set of buildings of his interest and perform a query to get the data.
The data is stored in a simple format with a unique id generated for the building and sensor(tag). and then just the double value as a field. All series in the same messuremenr, as the hash is unique and identifies the whole serie.
we have created different series with the same format for the downsamples/ upsamples of the data to get better performance.
The users can select a set of desired buildings and get the information. For example
The electricity, gas, water and diesel general consumption of 306 of the buildings for the last 2 months aggregated (we have monthly values generated and stored with they respective id for esch building)
Then we perform the query by getting the data on the selected period with the hashes in the list. We use one query for energy source to simplify the grouping. Get the data for the involved hashes - i saw that using contains is way more slow than using s list of r.hash=‘value’ or r.hash=value1…and applying the sum of all data.
Get the electricity, gas, water and diesel general consumption of 306 of the buildings for the last 2 months and aggregate grouped by building to compare
same as before but this tine we group by building and return de sumup for each instead of a single value
We have orther queries of similar behaviour
Esch of the queries is pretty fast, they take less than 1 seccind when running alone, however if we run it together(to power a ui to display the data, they start getting slower and slower, reaching the point where the database returns a timeout and the ui is irresponsive.
It happens very often as we run 1 query for energy source and different queries for each metric, it makes like 10 queries or more to load a single page.
I dont know if it is too much work for the ui or we are doing bad. By using only one messurement. But it seems that the tags are indexed in the same way.
We have influx 2 runing in a kubernetes cluster with 20 Gb of ram and 8 virtual cores.
I would apreciate any advice to achieve some improvement. If you need any more information about the system I can provide it.
Thanks in advance
