I just started using InfluxDB and found some strange behaviour in query performance. Basically I’m writing a batch of 100 points constantly in the database and then measuring the querying time to check how the querying time increases as the database grows.
In this case I wrote 36000 times so the database grows by 100 in each step so in step=1 we would have 100 points in the db and in the last step 3600000 points. I measured the time it takes to process the query and this is the behavior I found.
This is my query “SELECT LAST(latitude),longitude,speed,acc,heading,id from vehicles WHERE time > now() - 1s GROUP BY id” that always outputs 100 objects (the expected behavior).
What I don’t understand is that “saw” effect in the graph, someone with more experience could help me?
The behavior might differ based on the index type (TMS/TSI), but generally here is what happens when you write data:
- Received points are persisted using the WAL (Write Ahead Log) and in a cache that is used to make those data queriable
- When a threshold (cache size) is reached, the cache WAL content is flushed to an index file
- The DB also caches query results to answer quickly frequent queries
I can’t tell for sure, but since you just get the last second of data you are (almost) always targeting the cache. Therefore I expect the performance to be related to the cache itself.
Looking at your chart, I could say that as the cache grows the query time degrades, once the cache is flushed then the performance improves.
Since you are writing a fixed amount of data I expect the peaks to happen with a fixed frequency (which seems the case), meaning that every ~2500 steps the cache threshold is reached and a flush happens
This is 100% my guess on it.
If you are interested in the internals you can have a look here: Part-1, Part-2