[0.9.5-rc3] (regression) Derivative does not work with MIN/MAX/LAST #4849

cnelissen · 2015-11-19T22:16:58Z

Hey guys,
Looks like something has changed since 0.9.4. Derivative no longer works with MIN/MAX/LAST aggregate functions... Here is some sample data and queries:

SELECT * FROM test WHERE time > now() - 1h

time                    value
2015-11-19T21:00:54Z    942562514911
2015-11-19T21:05:53Z    942858150171
2015-11-19T21:11:00Z    943171809495
2015-11-19T21:15:55Z    943444939393
2015-11-19T21:20:58Z    943706044968
2015-11-19T21:26:00Z    943940460815
2015-11-19T21:30:58Z    944141789469
2015-11-19T21:35:57Z    944382269230
2015-11-19T21:40:57Z    944603490366
2015-11-19T21:45:58Z    944773315039
2015-11-19T21:50:55Z    944985627521
2015-11-19T21:56:00Z    946093669442

SELECT DERIVATIVE(SUM(value), 1s) FROM "test" WHERE time > now() - 1h GROUP BY time(5m)

time                    value
2015-11-19T21:00:00Z    657522.08
2015-11-19T21:05:00Z    985450.8666666667
2015-11-19T21:10:00Z    1045531.08
2015-11-19T21:15:00Z    910432.9933333333
2015-11-19T21:20:00Z    870351.9166666666
2015-11-19T21:25:00Z    781386.1566666666
2015-11-19T21:30:00Z    671095.5133333333
2015-11-19T21:35:00Z    801599.2033333334
2015-11-19T21:40:00Z    737403.7866666666
2015-11-19T21:45:00Z    566082.2433333333
2015-11-19T21:50:00Z    707708.2733333333
2015-11-19T21:55:00Z    3693473.07

SELECT DERIVATIVE(LAST(value), 1s) FROM "test" WHERE time > now() - 1h GROUP BY time(5m)

time                    value
2015-11-19T21:00:00Z
2015-11-19T21:05:00Z    
2015-11-19T21:10:00Z    
2015-11-19T21:15:00Z    
2015-11-19T21:20:00Z    
2015-11-19T21:25:00Z    
2015-11-19T21:30:00Z    
2015-11-19T21:35:00Z    
2015-11-19T21:40:00Z    
2015-11-19T21:45:00Z    
2015-11-19T21:50:00Z    
2015-11-19T21:55:00Z

This is obviously an issue because we don't always want to average the values, sometimes we only want the last value reported for that bucket, as is the case with this data. This data is coming from SNMP counter values, which means they are always increasing sequentially. If for some reason we stored the data twice inside of the same 5 minute interval (maybe a lag in the collection agent, etc), we only really care about the last value that was inserted. If we were to use SUM/MEAN or any other aggregate function, it will distort the data.

This was working on previous versions of 0.9.x...

The text was updated successfully, but these errors were encountered:

beckettsean · 2015-11-19T22:40:12Z

@cnelissen are you running a standalone instance or a cluster?

cnelissen · 2015-11-19T22:41:20Z

Single server, no clustering

pasancario · 2015-11-24T10:50:14Z

Hi all,

Same issue here with latest 0.9.5. I have the same use case as @cnelissen and it doesn't work fine for me. Anyone have the same issue?

I'm working also in single server, without clustering

gillarda · 2015-11-24T11:59:23Z

Hi everybody,

Same issue for us, as derivative with some aggregates functions (last, first, min, max) do not return any values.
Single instance, latest 0.9.5.

gucki · 2015-11-26T07:41:03Z

Single instance, latest 0.9.5. This is a complete show stopper - sorry, but aren't there any tests for such obvious bugs? How to use influxdb in a prod environment with such regressions? 👎

otoolep · 2015-11-26T12:27:52Z

We need more details, can you please follow the bug reporting guidelines
here?

https://github.com/influxdb/influxdb/blob/master/CONTRIBUTING.md

On Thursday, November 26, 2015, Corin Langosch notifications@github.com
wrote:

Single instance, latest 0.9.5. This is a complete show stopper - sorry,
but aren't there any tests for such obvious bugs? How to use influxdb in a
prod environment with such regressions? [image: 👎]

—
Reply to this email directly or view it on GitHub
#4849 (comment).

pasancario · 2015-11-26T12:59:26Z

Debian 7.0
Influxdb 0.9.5 rpm installed

Version:
InfluxDB starting, version 0.9.5, branch 0.9.5, commit 770b0ec, built 2015-11-20T15:30:37+0000

Test Example (from influxdb cli execution)

> use telegraf
Using database telegraf
> SELECT derivative(mean(value)) AS value FROM net_packets_recv WHERE host='kyle-influxdb-01' AND interface ='eth0' AND time > now() - 10m group by time(1m)
name: net_packets_recv
----------------------
time            value
1448541780000000000 1549
1448541840000000000 2734
1448541900000000000 2740.5
1448541960000000000 3274.5
1448542020000000000 1924
1448542080000000000 4037.5
1448542140000000000 2263.5
1448542200000000000 3571
1448542260000000000 2988
1448542320000000000 2712

> SELECT derivative(max(value),1m) AS value FROM net_packets_recv WHERE host='kyle-influxdb-01' AND interface ='eth0' AND time > now() - 10m group by time(1m)
name: net_packets_recv
----------------------
time            value
1448541780000000000 
1448541840000000000 
1448541900000000000 
1448541960000000000 
1448542020000000000 
1448542080000000000 
1448542140000000000 
1448542200000000000 
1448542260000000000 
1448542320000000000 

> SELECT derivative(last(value),1m) AS value FROM net_packets_recv WHERE host='kyle-influxdb-01' AND interface ='eth0' AND time > now() - 10m group by time(1m)
name: net_packets_recv
----------------------
time            value
1448541960000000000 
1448542020000000000 
1448542080000000000 
1448542140000000000 
1448542200000000000 
1448542260000000000 
1448542320000000000 
1448542380000000000 
1448542440000000000 
1448542500000000000 

> SELECT derivative(min(value),1m) AS value FROM net_packets_recv WHERE host='kyle-influxdb-01' AND interface ='eth0' AND time > now() - 10m group by time(1m)
name: net_packets_recv
----------------------
time            value
1448541960000000000 
1448542020000000000 
1448542080000000000 
1448542140000000000 
1448542200000000000 
1448542260000000000 
1448542320000000000 
1448542380000000000 
1448542440000000000 
1448542500000000000

As it can be seen, there is no value with MAX,MIN,LAST.

Do you need something more?

gucki · 2015-11-26T13:09:51Z

Fresh install, /var/lib/influx purged, config from package.

Description:  Ubuntu 14.04.2 LTS

ii  influxdb                        0.9.5                         amd64        Distributed time-series database


# create database
curl -G http://localhost:8086/query --data-urlencode "q=CREATE DATABASE mydb"

# write data
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=idle value=59055887.15 1448542309"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=busy value=10375909.05 1448542309"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=iowait value=172619.63 1448542309"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=idle value=59056246.12 1448542343"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=busy value=10375956.57 1448542343"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=iowait value=172620.08 1448542343"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=idle value=59056351.35 1448542354"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=busy value=10375974.77 1448542354"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=iowait value=172620.23 1448542354"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=idle value=59056497.73 1448542368"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=busy value=10375999.31 1448542368"
curl -X POST "http://localhost:8086/write?db=mydb&precision=s" --data-binary "metal.cpu.time,metal=r-ch106,metric=iowait value=172620.55 1448542368"

# query using cli

> USE mydb
Using database mydb
> SELECT MAX(value) FROM "metal.cpu.time" WHERE metric='idle' AND time > 1448542300s AND time < 1448542370s GROUP BY time(5s)
name: metal.cpu.time
--------------------
time      max
1448542300000000000
1448542305000000000 5.905588715e+07
1448542310000000000
1448542315000000000
1448542320000000000
1448542325000000000
1448542330000000000
1448542335000000000
1448542340000000000 5.905624612e+07
1448542345000000000
1448542350000000000 5.905635135e+07
1448542355000000000
1448542360000000000
1448542365000000000 5.905649773e+07

> SELECT DERIVATIVE(MAX(value)) FROM "metal.cpu.time" WHERE metric='idle' AND time > 1448542300s AND time < 1448542370s GROUP BY time(5s)
name: metal.cpu.time
--------------------
time      derivative
1448542305000000000
1448542310000000000
1448542315000000000
1448542320000000000
1448542325000000000
1448542330000000000
1448542335000000000
1448542340000000000
1448542345000000000
1448542350000000000
1448542355000000000
1448542360000000000
1448542365000000000

> SELECT DERIVATIVE(MAX(value), 5s) FROM "metal.cpu.time" WHERE metric='idle' AND time > 1448542300s AND time < 1448542370s GROUP BY time(5s)
name: metal.cpu.time
--------------------
time      derivative
1448542305000000000
1448542310000000000
1448542315000000000
1448542320000000000
1448542325000000000
1448542330000000000
1448542335000000000
1448542340000000000
1448542345000000000
1448542350000000000
1448542355000000000
1448542360000000000
1448542365000000000

otoolep · 2015-11-26T13:16:39Z

Thanks @gucki -- including actual data to insert is very helpful, and makes it much easier to see what is going on.

gucki · 2015-11-27T08:59:44Z

@otoolep Have you been able to reproduce the bug? Is a hotfix planned? If not, is it safe to downgrade to 0.9.4?

otoolep · 2015-11-28T01:02:40Z

@gucki -- yes I have reproduced this issue. Once we make a fix, it'll be available immediately in a nightly.

Downgrading to 0.9.3 should work, but you need to be sure you current configuration file works with 0.9.3. We do not extensively test downgrades, and should back up the directories under your system if you have any concerns.

otoolep · 2015-11-30T16:37:55Z

@jwilder -- I think this issue was introduced by 06c143c

Can you please comment?

otoolep · 2015-11-30T16:38:22Z

Sending to @jwilder to take a look.

jwilder · 2015-11-30T16:43:07Z

The points have gaps w/ nil in between them. Try adding a fill to the query.

otoolep · 2015-11-30T16:47:05Z

Adding fill causes the server to panic.

[query] 2015/11/30 08:46:36 SELECT derivative(max(value), 5s) FROM mydb."default"."metal.cpu.time" WHERE metric = 'idle' AND time > 1448542300s AND time < 1448542370s GROUP BY time(5s) fill(0)
panic: expected either int64 or float64, got {1448542309000000000 5.905588715e+07 map[:5.905588715e+07] map[metal:r-ch106 metric:idle]}

goroutine 86 [running]:
github.com/influxdb/influxdb/tsdb.int64toFloat64(0xbc6020, 0xc82057aba0, 0x0)
        /home/philip/repos/influxdb/src/github.com/influxdb/influxdb/tsdb/raw.go:773 +0x1f5
github.com/influxdb/influxdb/tsdb.ProcessAggregateDerivative(0xc820577600, 0xe, 0xe, 0x0, 0x12a05f200, 0x0, 0x0, 0x0)
        /home/philip/repos/influxdb/src/github.com/influxdb/influxdb/tsdb/raw.go:717 +0x741
github.com/influxdb/influxdb/tsdb.(*AggregateExecutor).processDerivative(0xc82057e160, 0xc820577600, 0xe, 0xe, 0x0, 0x0, 0x0)
        /home/philip/repos/influxdb/src/github.com/influxdb/influxdb/tsdb/aggregate.go:375 +0x18f
github.com/influxdb/influxdb/tsdb.(*AggregateExecutor).execute(0xc82057e160, 0xc820019ec0, 0xc8201e9320)
        /home/philip/repos/influxdb/src/github.com/influxdb/influxdb/tsdb/aggregate.go:162 +0x14a3
created by github.com/influxdb/influxdb/tsdb.(*AggregateExecutor).Execute
        /home/philip/repos/influxdb/src/github.com/influxdb/influxdb/tsdb/aggregate.go:48 +0x67

beckettsean added the area/functions label Nov 19, 2015

beckettsean changed the title ~~[0.9.5-rc3] Derivative does not work with MIN/MAX/LAST~~ [0.9.5-rc3] (regression) Derivative does not work with MIN/MAX/LAST Nov 25, 2015

beckettsean added the support label Nov 25, 2015

otoolep assigned jwilder Nov 30, 2015

jwilder removed their assignment Nov 30, 2015

otoolep mentioned this issue Nov 30, 2015

unexpected derivative behavior #4931

Closed

nathanielc self-assigned this Dec 4, 2015

nathanielc added the 2 - Working label Dec 4, 2015

nathanielc mentioned this issue Dec 5, 2015

Update Derivative for PositionPoint objects #5000

Merged

nathanielc closed this as completed in #5000 Dec 7, 2015

nathanielc removed the 2 - Working label Dec 7, 2015

discoduck2x mentioned this issue Dec 11, 2015

[0.9.6] Derivative (max) & math of max #5093

Closed

jessetan mentioned this issue Feb 8, 2016

Unable to perform math on derivative #5571

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[0.9.5-rc3] (regression) Derivative does not work with MIN/MAX/LAST #4849

[0.9.5-rc3] (regression) Derivative does not work with MIN/MAX/LAST #4849

cnelissen commented Nov 19, 2015

beckettsean commented Nov 19, 2015

cnelissen commented Nov 19, 2015

pasancario commented Nov 24, 2015

gillarda commented Nov 24, 2015

gucki commented Nov 26, 2015

otoolep commented Nov 26, 2015

pasancario commented Nov 26, 2015

gucki commented Nov 26, 2015

otoolep commented Nov 26, 2015

gucki commented Nov 27, 2015

otoolep commented Nov 28, 2015

otoolep commented Nov 30, 2015

otoolep commented Nov 30, 2015

jwilder commented Nov 30, 2015

otoolep commented Nov 30, 2015

[0.9.5-rc3] (regression) Derivative does not work with MIN/MAX/LAST #4849

[0.9.5-rc3] (regression) Derivative does not work with MIN/MAX/LAST #4849

Comments

cnelissen commented Nov 19, 2015

beckettsean commented Nov 19, 2015

cnelissen commented Nov 19, 2015

pasancario commented Nov 24, 2015

gillarda commented Nov 24, 2015

gucki commented Nov 26, 2015

otoolep commented Nov 26, 2015

pasancario commented Nov 26, 2015

gucki commented Nov 26, 2015

otoolep commented Nov 26, 2015

gucki commented Nov 27, 2015

otoolep commented Nov 28, 2015

otoolep commented Nov 30, 2015

otoolep commented Nov 30, 2015

jwilder commented Nov 30, 2015

otoolep commented Nov 30, 2015