influxdata / influxdata/influxdb
Poor performance on last()
- Dominant language
- Rust
- Stars
- 31.7k
- Forks
- 3.7k
- Avg merge
- 13h 37m
- Merged PRs (30d)
- 8
Description
__Steps to reproduce:__
List the minimal actions needed to reproduce the behavior.
1. Fill a database with a lot of data
2. Query it using last()
3. Wait a long time
__Expected behavior:__
Describe what you expected to happen.
Querying the last value of a given field should be a constant-time lookup.
__Actual behavior:__
Describe What actually happened.
Instead, influx takes increasingly longer to find the last value with more points.
A database containing just 3 million points, the query `select last("my_field") as "last_field" from "my_measurement"` has in some cases taken up to 6 hours to complete, but on average takes about 3 minutes.
While this query executes, it uses 100% of all four cores and makes InfluxDB completely unresponsive until the query has finished executing.
Judging roughly based on the quadratic curve of the queryDurationNs field in the queryExecutor measure, it appears to be ~O(n^2) to calculate this last point.
The poor performance of this function makes it difficult to use on medium-sized datasets.
__My Use Case__
I'm trying to determine the last timestamp a measurement was written to in order to differentially load data into InfluxDB from another database. After searching for a way to do this in a more traditional way, like selecting max(time), I determined that the `last()` InfluxQL function is the only way to do this. I don't actually need the last value of a field, but this function does the job.
__Environment info:__
* System info: Run `uname -srm` and copy the output here
`Linux 3.10.0-957.12.2.el7.x86_64 x86_64`
* InfluxDB version: Run `influxd version` and copy the output here
`InfluxDB v1.7.7 (git: 1.7 f8fdf652f348fc9980997fe1c972e2b79ddd13b0)`
* Other relevant environment details: Container runtime, disk info, etc
Running on RHEL 7 VM: four cores, 64 GB of RAM, 128 GB storage.
__Config:__
Copy any non-default config values here or attach the full config as a gist or file.
max series per database and max series per tag are both set to 0.
__Logs:__
Include snippet of errors in log.
__Performance:__
Generate profiles with the following commands for bugs related to performance, locking, out of memory (OOM), etc.
```sh
# Commands should be run when the bug is actively.
# Note: This command will run for at least 30 seconds.
curl -o profiles.tar.gz "http://localhost:8086/debug/pprof/all?cpu=true"
curl -o vars.txt "http://localhost:8086/debug/vars"
iostat -xd 1 30 > iostat.txt
# Attach the `profiles.tar.gz`, `vars.txt`, and `iostat.txt` output files.
```
Contributor guide
Research direction
Start by reproducing the `last()` query against a large dataset and collect the profiling outputs requested in the issue: `profiles.tar.gz`, `vars.txt`, and `iostat.txt`. Trace where the query spends time and compare execution time as the point count grows; done means the query no longer becomes prohibitively slow or makes the database unresponsive.
Written by the indexing model from the issue text.
Assessment
- Domain
- databases, performance
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 35/100