A TSDB queries tour (Sysadmin days - Paris)

@sysadmindays @ : / # A T B qu e
t wi m i r zo

@sysadmindays @ : / # Aurélien Hébert @AurrelH95 Software Engineer
and data lover 2

@sysadmindays @ : / # A connected world 3

@sysadmindays @ : / # Producing more daily data 4

@sysadmindays @ : / # Human data classification ❖ Relational
ID Name Country Job 1 Peter Ireland Bookkeeper 2 Paolo Italy Sales 5

@sysadmindays @ : / # And ❖ Key/Value ❖ Document
❖ Graphs ❖ ... Key Value edge Node Node 6

@sysadmindays @ : / # Server and application data 7

@sysadmindays @ : / # Metrics A series of data
point indexed by time 8

@sysadmindays @ : / # Time series are well known
Stock market Analytics Economic Forecasting 9

@sysadmindays @ : / # Time series Database 10

@sysadmindays @ : / # Many open source out there
❖ Steven Acreman (Outlier) ❖ Top 10 Time Series Databases 11

@sysadmindays @ : / # A monitoring use case 12

@sysadmindays @ : / # Server data CPU disk disk
I/O load kernel memory network I/O temperature system swap ... 13

@sysadmindays @ : / # What can we do ?
14

@sysadmindays @ : / # 1. Human data viz ❖
Raw ❖ Sampling ❖ Grouping 15

@sysadmindays @ : / # 2. Data analysis ❖ Metrics
functions ❖ Operation across metrics 16

@sysadmindays @ : / # 3. More complex analytics 17

@sysadmindays @ : / # An example is worth 1000
words 18

@sysadmindays @ : / # Using a server data subset
Memory available CPU usage Disks I/O 19

@sysadmindays @ : / # Data collection Agent 20

@sysadmindays @ : / # Raw memory data Name Meta
List <time, value> 21

@sysadmindays @ : / # Focus on a TSDB subset
grafana 22

@sysadmindays @ : / # 1. Human data viz 23

@sysadmindays @ : / # OpenTSDB api/query { "start":1535752800000, "end":1535839199999,
"queries": [ { "metric":"mem.available", "aggregator":"none" } ] } 24

@sysadmindays @ : / # PromQL api/v1/query_range? query=mem.available& start=1535797890& end=1535818770&
step=30 25

@sysadmindays @ : / # Graphite /render? target=mem.available& from=1535797842& until=1535818822&
26

@sysadmindays @ : / # CPU’s monitoring 27

@sysadmindays @ : / # Reduce data point per series
❖ Keep only one point every 2 minutes 28

@sysadmindays @ : / # Sampling 29

@sysadmindays @ : / # OpenTSDB "queries": [{ "metric":"cpu.usage_system", "aggregator":"sum",
"downsample":"2m-avg", "tags": { "cpu":"*" } }] 32

@sysadmindays @ : / # OpenTSDB > Main down-samplers are:
avg, count, dev, first, last, percentiles, min, max and sum 33

@sysadmindays @ : / # PromQL api/v1/query_range? query=cpu.usage_system{ cpu=~"cpu[0-7]*"}& start=1535797890&
end=1535818770& step=2m 34

@sysadmindays @ : / # Only last down-sampler Interpolation of
missing values are computed using last too PromQL 35

@sysadmindays @ : / # Graphite At configuration, using aggregation-rules
cpu.usage_system (120) = avg cpu.usage_system Main down-samplers are: sum, avg, min, max, percentiles and count 36

@sysadmindays @ : / # Reduce CPU series 37

@sysadmindays @ : / # Group CPU data ❖ Sampling
synchronised timestamps ➢ Compute max aggregation 38

@sysadmindays @ : / # OpenTSDB "queries": [{ "metric":"cpu.usage_system", "aggregator":"max",
"downsample":"2m-avg", "filters":[{ "type":"regexp", "tagk":"cpu", "filter":"cpu[0-9]+", "groupBy":false }] }] 39

@sysadmindays @ : / # OpenTSDB > Main aggregators are:
avg, count, dev, percentiles, min, max, mimmin, mimmax, sum, none (raw data) and zimsum (Difference between mimmin and min are missing values interpolation, same for mimmax and max and zimsum and sum) 40

@sysadmindays @ : / # PromQL api/v1/query_range? query=max(cpu. usage_system{ cpu=~"cpu[0-7]*"})
start=1535797890& end=1535818770& step=2m 41

@sysadmindays @ : / # PromQL > Grouping operator can
be one of: sum, avg, min, max, stddev, stdvar, count, topk, bottomk and quantile 42

@sysadmindays @ : / # Graphite /render? target=aggregate(cpu. usage_system,'max')& from=1535797842&
until=1535818822& 43

@sysadmindays @ : / # Graphite > Main aggregators are
avg, median, sum, min, max, diff, stddev, count, range, multiply and last 44

@sysadmindays @ : / # Be able to see data
45

@sysadmindays @ : / # Disk I/O’s monitoring 46

@sysadmindays @ : / # 2. Data analysis 47

@sysadmindays @ : / # Compute a rate From bytes
to bytes per seconds 48

@sysadmindays @ : / # OpenTSDB "queries": [{ "metric":"diskio.writes", "aggregator":"sum",
"downsample":"2m-avg", "rateOptions": { "counter":true, "dropResets":true }, "tags": { "name":"*" } }] 49

@sysadmindays @ : / # OpenTSDB functions Only rate operation
50

@sysadmindays @ : / # PromQL api/v1/query_range? query=rate(diskio.wri tes[2m])& start=1535797890&
end=1535818770& step=2m 51

@sysadmindays @ : / # PromQL functions Around 50 functions
Mean_over_time, max_over_time Delta, rate, sqrt Topk, sort 52

@sysadmindays @ : / # Graphite /render? target=divideSeries( derivative(diskio.writes), 60)&
from=1535797842& until=1535818822& 53

@sysadmindays @ : / # Graphite functions TimeSlice, TimeShift Integral,
Interpolate, derivative More than 100 functions Unique, sort LinearRegression, exponential smoothing PieAverage, legendValue MovingMean, MovingMax 54

@sysadmindays @ : / # Disk I/O writes times 55

@sysadmindays @ : / # Series operators Disk I/O time
Disk I/O writes 56

@sysadmindays @ : / # Series operators Prometheus: rate(diskio.write_time[2m]) /
on(name) rate(diskio.writes[2m]) Graphite: divideSeriesList(derivative( diskio.write_time), derivative(diskio.writes)) 57

@sysadmindays @ : / # Graphite and promQL review 58

@sysadmindays @ : / # Common usage ❖ Succinct time
series queries ❖ Same functionality ❖ Analytics 59

@sysadmindays @ : / # Data model structure ❖ Labels
with Key/Value map attached to each metrics with Prometheus ❖ Name with dot separated component for Graphite 60

@sysadmindays @ : / # Languages review PromQL: ❖ Structured
❖ Easier to compute operation on multiple series ❖ Less control Graphite: ❖ More Time series functions ➢ stats ➢ maths ➢ graphs ❖ Less control 61

@sysadmindays @ : / # 3. Complex analytics 62

@sysadmindays @ : / # Warp 10 api/v0/exec: [ "token"
"cpu.average" { "cpu" "~cpu[0-9]+" } 1535818770 10 h ] FETCH [ SWAP bucketizer.mean 0 2 m 0 ] BUCKETIZE [ SWAP [ "host" ] reducer.max ] REDUCE 63

@sysadmindays @ : / # Hello Exo World use case
64

@sysadmindays @ : / # Warp10 - hands on 65

@sysadmindays @ : / # Hello Exo World result 66

@sysadmindays @ : / # Warp 10 review ❖ Dedicated
language ❖ A time series workflow ❖ Queries complexity ❖ Abstraction needed to end user 67

@sysadmindays @ : / # And the Elastic Stack? 68

@sysadmindays @ : / # The Elastic Time Series stack
.es(index=test*, metric=min:mem.available).mvavg(10) 69

@sysadmindays @ : / # Does the job ❖ Mix
of visualization ❖ Multiple series ❖ Lof of functions (functions) ❖ Less control on data ❖ Need a graphical tool (Timelion on Kibana) ❖ Lower query performance 70

@sysadmindays @ : / # M3 TSDB POST /query {
"namespace": "test", "query": { "regexp": { "field": "city", "regexp": ".*" } }, "rangeStart": 0, "rangeEnd":'"$(date +"%s")"' } 71

@sysadmindays @ : / # Nobody’s is perfect 72

@sysadmindays @ : / # Different use cases, differents TSDB...
73

Wait we are missing one, aren't we? 74

Wait we are missing one, aren't we? 75

@sysadmindays @ : / # From InfluxQL SELECT max("usage_system") FROM
"telegraf".."cpu" WHERE "host" = 'ahe-XPS-13-9360' AND time > now() - 12h GROUP BY time(10m) ❖ First iteration ❖ Database queries ❖ Familiar SQL user 76

@sysadmindays @ : / # InfluxQL drawback Time series data
are NOT relational InfluxQL had limitations for advanced use cases 77

@sysadmindays @ : / # To IFQL select(db:"telegraf") .where(exp:{"_measurement"=="cpu" AND
"_field"=="usage_system") .range(start:-12h) .window(every:10m) .max() ❖ Time series API ❖ Functional paradigm ❖ Consistent semantics 78

@sysadmindays @ : / # And flux POST query= from(bucket:"telegraf")
|> filter(fn: (r) => r._measurement == "cpu" AND r._field == "usage_system") |> range(start:-12h) |> group(by: ["host"]) |> window(every: 10m) |> max() ❖ Data language ❖ Lot of native functions ❖ User defined function ❖ A usable language 79

@sysadmindays @ : / # A time series query language
Working on data locally is more powerful 80

@sysadmindays @ : / # What we want? ❖ Quick
access to the data ❖ Times series native features ❖ Back-end agnostic ❖ Simplify user experience 81

@sysadmindays @ : / # Alternative: TSQL spec select("cpu.usage_system") .where("cpu~cpu[0-7]*")
.last(12h) .sampleBy(5m,max) .groupBy(mean) .rate() ❖ Time Series Queries Language ❖ Simplify Time Series computation 82

@sysadmindays @ : / # HEW use case with TSQL
sample = select('sap.flux') .where('KEPLERID=6541920') .from("2009-05-02T00:56:10.000000Z", to="2013-05-11T12:02:06.000000Z") .timesplit(6h,100,"record") .filterByLabels('record~[2-5]') .sampleBy(2h, min, false, "none") trend = sample.window(mean, 5, 5) sub(sample,trend) .on('KEPLERID','record') .lessThan(-20.0) ❖ Support complex use cases 83

@sysadmindays @ : / # Tha s!

A TSDB queries tour (Sysadmin days - Paris)

A TSDB queries tour (Sysadmin days - Paris)

More Decks by Aurélien HÉBERT

Other Decks in Programming

Featured

Transcript