$30 off During Our Annual Pro Sale. View Details »

Time Series Data Models and the future of Influ...

Paul Dix
April 20, 2017

Time Series Data Models and the future of InfluxDB's Query Language

A talk I gave at the Austin Monitoring Meetup about different open source projects' time series data models and some ideas for a new InfluxDB query language.

Paul Dix

April 20, 2017
Tweet

More Decks by Paul Dix

Other Decks in Technology

Transcript

  1. Time Series Data Models & the Query Languages That Love

    Them Paul Dix paul@influxdb.com @pauldix
  2. Regular time series t0 t1 t2 t3 t4 t6 t7

    Samples at regular intervals
  3. Irregular time series t0 t1 t2 t3 t4 t6 t7

    Events whenever they come in
  4. Differences organization data types precision Graphite hierarchical float64 seconds OpenTSDB

    metric, tags float64 milliseconds Prometheus metric, tags float64 milliseconds InfluxDB 1.x metric, tags, fields float64, int64, bool, string nanoseconds
  5. Lookup tag/label values OpenTSDB /api/search/lookup?query={host=*} (all series with host?) Prometheus

    {host=~“.+”} (all series with host?) InfluxDB SHOW TAG VALUES WITH KEY = “host” SHOW TAG VAVUES FROM “cpu” with KEY = “host”
  6. Drill Down OpenTSDB /api/search/lookup?query={host=*,service=mysql} (all metrics on the hosts) Prometheus

    {host=~“.+”,service=mysql} (all metrics on the hosts) InfluxDB SHOW TAG VALUES WITH KEY = “host” WHERE “service” = ‘mysql’
  7. Facets __name__ host service region group … go_goroutines go_memstats_alloc_bytes go_memstats_alloc_bytes_total

    go_memstats_gc_sys_bytes go_memstats_other_sys_bytes … host service region group …
  8. Facets __name__ host service region group … go_goroutines go_memstats_alloc_bytes go_memstats_alloc_bytes_total

    go_memstats_gc_sys_bytes go_memstats_other_sys_bytes … host service region group … dynamic hierarchy! (name already selected)
  9. Query Languages Query Language Example Graphite functional target, from, until

    sumSeries(summarize(water.level.h2o.feet.*, '1hour', 'max')) OpenTSDB http params startTime, endTime, metric, aggregationFunction, filter, functions, expressions Prometheus functional-ish increase(http_requests_total{job=“prometheus”}[5m]) InfluxDB 1.x SQL-ish select mean(system) from cpu where time > now() - 6h group by time(10m)
  10. Requirements • Support InfluxDB 1.x Data Model • Support InfluxDB

    1.x QL • Support Prometheus Data Model • Functional Query Language • Rich Query Builder UI • Query Completion CLI • PromQL?
  11. InfluxDB 2.0 Data Model • Tags • non-string values? •

    Value • int64 • uint64 • float64 • bool • string • bytes • Timestamp (nanosecond)
  12. D3 d3.select("body") .selectAll("p") .data([4, 8, 15, 16, 23, 42]) .enter().append("p")

    .text(function(d) { return "I’m number " + d + "!"; });
  13. Series { "id": 24, "meta": { "dataType": "float64", "metricType": "gauge"

    }, "tagset": { "host": "A", "region": "B" }, "vector": [ {"value":23.2, "epoch":1491499253}, {"value":78.1, "epoch":1491499263, "tagset":{"host":"B"}} ] }
  14. Matrix [ { "tagset": { "host": "A", }, "vector": [{"value":23.1,

    "epoch":1491499253}, {"value":56.2, "epoch":1491499263}] }, { "tagset": { "host": "B" }, "vector": [{"value":23.1, "epoch":1491499253}, {"value":56.2, "epoch":1491499263}] } ]
  15. Example Queries database(name:"testdb") .select(criteria:`"host" = 'A' and "system" = 'cpu'`)

    .range(startOffset:"-1h") Wrap strings in back ticks to avoid pesky escaping
  16. Database returns Matrix database(name:"testdb") [ { "tagset": { "host": "A",

    }, "vector": [ {"value":23.1, “epoch":1491499253}, {"value":56.2, "epoch":1491499263}] }, { "tagset": { "host": "B" }, "vector": [ {"value":23.1, “epoch":1491499253}, {"value":56.2, "epoch":1491499263}] } ]
  17. Criteria Operators • = • != • =~ • !~

    • < • > • startsWith • in • notIn
  18. // get the cpu load of hosts that have mysql

    running var db = database(name:"testdb") db.select( criteria:`"system" = 'cpu' and "metric" = 'load' and "host" in #{ db.select(`"service" = 'mysql'`).values(key:"host") }`) .range(startOffset:"-4h")
  19. // get the cpu load of hosts that have mysql

    running var db = database(name:"testdb") db.select( criteria:`"system" = 'cpu' and "metric" = 'load' and "host" in #{ db.select(`"service" = 'mysql'`).values(key:"host") }`) .range(startOffset:"-4h") Variables
  20. // get the cpu load of hosts that have mysql

    running var db = database(name:"testdb") db.select( criteria:`"system" = 'cpu' and "metric" = 'load' and "host" in #{ db.select(`"service" = 'mysql'`).values(key:"host") }`) .range(startOffset:"-4h") string interpolation
  21. // get the count in 10m periods in the last

    24h from an event stream // and filter that to only include those periods that were 2 sigma above the average var m = database(name:”testdb”).select(criteria:"\"event\" = 'pageview'") .range(startOffset:"-24h") .merge() .window(func:count(),duration:"10m")
  22. // get the count in 10m periods in the last

    24h from an event stream // and filter that to only include those periods that were 2 sigma above the average var m = database(name:”testdb”).select(criteria:”\”event\” = 'pageview'") .range(startOffset:"-24h") .merge() .window(func:count(),duration:"10m") // this is shorthand for m.stddev.join(op:"*", right:2) var sigma = m.stddev() * 2
  23. // get the count in 10m periods in the last

    24h from an event stream // and filter that to only include those periods that were 2 sigma above the average var m = database(name:”testdb”).select(criteria:”\”event\” = 'pageview'") .range(startOffset:"-24h") .merge() .window(func:count(),duration:"10m") // this is shorthand for m.stddev.join(op:"*", right:2) var sigma = m.stddev() * 2 // return only the counts 1 sigma above m.filter(exp:"$ > #{sigma}")
  24. // return the last hour of time series of the

    top 10 host cpu utilizations by // their average load over last 10 minutes var topTen = db.select(criteria:”\”metric\” = 'load' and system = 'cpu'") .range(startOffset:"-10m") .mean() .sort(func:first()) .slice(end:10) .values(key:"host") db.select(criteria:”\"metric\" = 'load' and system = 'cpu' and host in #{topTen}") .range(startOffset:"-1h")
  25. Functions • interpolate • join • merge • timeShift •

    window • rate • first, last, min, max, mean, percentile, etc.