influxdata / influxdata/influxdb

Poor performance on last()

Open
#14,564 3 comments 6 reactions 0 assignees View on GitHub
1.x
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.