Influxdb: Slow group by time

[ikovalyov]

Bug report

System info: InfluxDB Admin UI: v1.0.2 Server: v1.0.2

Steps to reproduce:

1. I have nodes.infinity.DPMeanHourly measurement with 6246433 points
2. Do call

SELECT count(response_time) FROM infinity.DPMeanHourly WHERE time >= 1475280000000000000 AND time <= 1476489600000000000

1. Received response 6246433 within 799ms which is absolutely ok
2. Do call

SELECT mean(response_time) FROM infinity.DPMeanHourly WHERE time >= '2016-10-01 12:00:00' AND time <= '2016-10-15 04:00:00' GROUP BY time(1h)

1. Received response within 24s which is definitely not ok .

Expected behavior: group by 1h request should take 1-3 seconds

Actual behavior: It takes 24s

Additional info: gist

[ikovalyov]

The query

SELECT count(response_time) FROM infinity.DPMeanHourly WHERE time >= 1475280000000000000 AND time <= 1476489600000000000 GROUP BY time(1d)
Takes 2.34s to respond

The query

SELECT count(response_time) FROM infinity.DPMeanHourly WHERE time >= 1475280000000000000 AND time <= 1476489600000000000 GROUP BY time(2h)
Takes 12.42s to respond.

It looks like influx response time is proportional to the response points count, not processed data length. Definitely looks like an issue for me.

Thanks.

[jwilder]

Can you run:
curl -O http://localhost:8086/debug/pprof/profile

and then run the slower query and attach the profile file that is recorded when the curl command completes? It will collect a profile for 30s.

[ikovalyov]

profile.txt
Here you are

[jimaek]

@jwilder Any news on this?

[jwilder]

Can you try 1.1rc1?

[ikovalyov]

InfluxDB Admin UI: v1.1 Server: v1.1.0~rc1

SELECT count(response_time) FROM infinity.DPMeanHourly WHERE time >= 1475280000000000000 AND time <= 1476489600000000000

Takes 616ms vs 799ms on 1.0

SELECT mean(response_time) FROM infinity.DPMeanHourly WHERE time >= '2016-10-01 12:00:00' AND time <= '2016-10-15 04:00:00' GROUP BY time(1h)

Takes 19.43 vs 24s

SELECT count(response_time) FROM infinity.DPMeanHourly WHERE time >= 1475280000000000000 AND time <= 1476489600000000000 GROUP BY time(1d)

Takes 1.73 vs 2.34

SELECT count(response_time) FROM infinity.DPMeanHourly WHERE time >= 1475280000000000000 AND time <= 1476489600000000000 GROUP BY time(2h)

Takes 11.33 vs 12.42

Faster but still not as fast as it should be :)

[ikovalyov]

any update?

[ikovalyov]

performance degradation at 1.2

SELECT count(response_time) FROM nodes.infinity.DPMeanHourly WHERE time >= '2017-01-01' AND time <= '2017-01-15'

803 ms

SELECT count(response_time) FROM nodes.infinity.DPMeanHourly WHERE time >= '2017-01-01' AND time <= '2017-01-15' group by time(1h)

25.74 s

SELECT count(response_time) FROM nodes.infinity.DPMeanHourly WHERE time >= '2017-01-01' AND time <= '2017-01-15' group by time(1d)

1.96

SELECT count(response_time) FROM nodes.infinity.DPMeanHourly WHERE time >= '2017-01-01' AND time <= '2017-01-15' group by time(2h)

13.45

[jimaek]

@jwilder Check it out when you have time please

[jimaek]

ping

[briandilley]

ping:

select  mean(open) as open,  mean(close) as close,  max(high) as high,  min(low) as low,  sum(coin_volume) as coin_volume,  sum(trade_count) as trade_count from rp_candles.candles_1m where  (base='USD' and coin='ETH')  and time <= 1536964800000000000  group by coin, base, time(1m)  fill(previous)  order by time desc  limit 50

takes ~1.5s

where:

select  mean(open) as open,  mean(close) as close,  max(high) as high,  min(low) as low,  sum(coin_volume) as coin_volume,  sum(trade_count) as trade_count from rp_candles.candles_1m where  (base='USD' and coin='ETH')  and time <= 1536964800000000000  group by coin, base 
fill(previous)  order by time desc  limit 50

takes milliseconds

[dgnorton]

@briandilley which release are you on?

[russorat]

closing due to lack of response.