Time Series Schemas @Percona Live 2017 1 Who Am I? Chris Larsen - - PowerPoint PPT Presentation

▶
time series schemas percona live 2017
SMART_READER_LITE
LIVE PREVIEW

Time Series Schemas @Percona Live 2017 1 Who Am I? Chris Larsen - - PowerPoint PPT Presentation

Dec 03, 2023 158 likes •562 views

Time Series Schemas @Percona Live 2017 1 Who Am I? Chris Larsen Maintainer and author for OpenTSDB since 2013 Software Engineer @ Yahoo Central Monitoring Team Who Im not: A marketer A sales person 2 What are

slide-1
SLIDE 1

1

Time Series Schemas @Percona Live 2017

slide-2
SLIDE 2

2

Who Am I? Chris Larsen

  • Maintainer and author for OpenTSDB since 2013
  • Software Engineer @ Yahoo
  • Central Monitoring Team

Who I’m not:

  • A marketer
  • A sales person
slide-3
SLIDE 3

3

What are Time Series?

Time Series: A sequence of discrete data points (values) ordered and indexed by time associated with an identity. E.g.: web01.sys.cpu.busy.pct 45% 1/1/207 12:01:00 web01.sys.cpu.busy.pct 52% 1/1/207 12:02:00 web01.sys.cpu.busy.pct 35% 1/1/207 12:03:00 ^ Identity ^ Value ^ Timestamp

slide-4
SLIDE 4

4

What are Time Series?

slide-5
SLIDE 5

5

What are Time Series?

Data Point: Metric + Tags + Value: 42 + Timestamp: 1234567890 sys.cpu.user 1234567890 42 host=web01 cpu=0

  • Payload could also be a string, a blob, a histogram,

etc.

^ a data point ^

slide-6
SLIDE 6

6

Chose your own Adventure!

  • You’re developing a new app

and want to see how long it takes to call that backend service.

  • A web server is super slow and you want to track

connections and latencies without parsing logs.

  • You’re running a lab experiment and want to count

cell divisions per second.

slide-7
SLIDE 7

7

In the Beginning… Flat Files

  • Slap in some code to append to a file.
  • Import CSVs to Excel and graph it!
  • PLUS:
  • Easy to share
  • Easy to parse with code
slide-8
SLIDE 8

8

Chose your own Adventure!

Co-workers: “I like your instrumentation and graphs! We have more (apps | servers | experiments) for you to instrument. Can you do it? And give us a UI and CLI and (etc, etc, etc)” You: “... Sure!”

slide-9
SLIDE 9

9

In the Beginning… Flat Files

Now you see some problems:

  • Many series == many files.
  • How do you query lots of files?
  • What if you grow to the point you’re thrashing the disk

IO?

  • Roll your own query and join code between files.
  • Roll your own graphing server, CLI etc.
slide-10
SLIDE 10

10

RDBMS to the rescue!

Pros:

  • Industry standard APIs and tools.
  • Standard query language with transforms, filtering, etc.
  • Replication, backups, high availability.
  • Lots of vendors (OSS and
  • paid) to choose from.
  • Just have to create a UI.
slide-11
SLIDE 11

11

First Schema:

  • Index on metric and timestamp.
  • Easy to query for time ranges

and specific metrics.

SELECT max(value) FROM timeseriesTable WHERE metric = 'web01.sys.cpu.busy.pct' AND timestamp BETWEEN '2011-05-07' AND '2011-05-07 23:59:59:999'

slide-12
SLIDE 12

12

Chose your own Adventure!

Co-workers: “I SQL so much! Thank you! By the way, we’re going to push 1000 new metrics per second in an hour. Have a great lunch break.” You: “...”

slide-13
SLIDE 13

13

First Schema:

Cons:

  • More metrics and/or more frequent data means:
  • Bigger and bigger indices
  • Slower queries as the data set grows
  • Deleting data to cleanup huge tables takes longer
slide-14
SLIDE 14

14

Second Schema:

  • Shard tables by month (later on by day, then hour…).
  • Join across tables in the DB or in app.
  • Delete old data by dropping a table.
  • Room to grow.

SELECT max(value) FROM timeseriesTable_2011_05_07 WHERE metric = 'web01.sys.cpu.busy.pct' AND timestamp BETWEEN '2011-05-07' AND '2011-05-07 23:59:59:999'

slide-15
SLIDE 15

15

Chose your own Adventure!

Co-workers: “Thanks for bringing the DB back up but it’s down

  • again. I think it could be because

the __?__ group started pushing 100,000 metrics per second and are now sending metrics like host.system.cpu.core.busy.pct. You: “... oh.”

slide-16
SLIDE 16

16

Second Schema:

Cons:

  • While it helps buy some time, with continued growth

you still have the problems of V1.

  • One abuser can easily take down your system.
slide-17
SLIDE 17

17

Third Schema:

  • Shard tables by time

and group. (even by server)

  • Reduce storage by

using UID tables.

SELECT max(ts.value), m.metric, h.host, dc.dataCenter FROM groupA_2011_05_07 ts JOIN dataCenters dc ON ts.dataCenterId = dc.dataCenterId JOIN metrics m ON ts.metricId = m.metricId JOIN hosts h ON ts.hostId = h.hostId WHERE m.metric = 'web01.sys.cpu.busy.pct' AND h.host REGEXP 'web.*' AND dc.dataCenter IN ('lga', 'phx') AND ts.timestamp BETWEEN '2011-05-07' AND '2011-05-07 23:59:59:999'

slide-18
SLIDE 18

18

Chose your own Adventure!

Co-workers: “Great work on the schema! Those queries are so much faster. Now we need more dimensions like X, Y, Z, Z’, etc. Can we also store JSON events, Git commits, strings, histograms and get some alerting?” You: sigh “Your wish is my command.”

slide-19
SLIDE 19

19

Third Schema:

Cons:

  • Doesn’t allow for unbounded dimensions (tags).
  • Requires complex shard routing code.
  • Different columns or tables per data type or stored

procedures to encode/decode blobs.

slide-20
SLIDE 20

20

Explore Dedicated Time Series Systems!

slide-21
SLIDE 21

21

Problems to Solve:

  • Handle unbounded metrics and dimensions.
  • Handle high cardinality dimensions.
  • E.g. userId=? where unique(userId) >= 1M
  • Query wide time ranges at lower resolution.
  • E.g. use time rollups for 1 year queries.
  • Aggregate multiple time series into single views.
  • E.g. sum(sys.if.traffic_in) where datacenter = phx.
  • Perform transformations and extract useful analytics.
  • E.g. Top 10 highest traffic hosts. 99th percentile query latency.
  • Replication, High Availability, Write and Read throughput.
slide-22
SLIDE 22

22

1990’s - MRTG and RRDTool

slide-23
SLIDE 23

23

1990’s - MRTG and RRDTool

Schema:

  • Circular buffer, fixed time interval and numeric data.

Pros:

  • Fixed file sizes with lower resolution storage.
  • Built in graphing and simple methods.
  • Portable, backup-able.

Cons:

  • Many series == many files == IO thrashing.
  • No replication/HA.
  • Manual aggregation, no dimensions.
slide-24
SLIDE 24

24

1990’s - KDB+, Informix

Schema:

  • ? Proprietary.

Pros:

  • Designed for time series.
  • Complex analysis.
  • Commercial support.

Cons:

  • Commercial fees.
  • Little integration with open-source

projects.

slide-25
SLIDE 25

25

2000’s - Graphite

Schema:

  • Circular buffer, fixed time interval and numeric data.

Pros:

  • Aggregations and rollups available.
  • Transform functions and

dashboarding.

  • Working on distributed stores.

Cons:

  • Lack of replication/HA.
  • Same as RRDTool.
slide-26
SLIDE 26

26

2010 - OpenTSDB

  • Open Source Time Series Database

based on Google’s in-house time series DB.

  • Store trillions of data points at

millions of writes per second.

  • Keeps raw data at the original

timestamp and precise value.

  • Keep it forever or TTL it out.
  • Scales using HBase or Bigtable.
  • Provides multi-series analysis.
slide-27
SLIDE 27

27

What are HBase and Bigtable?

  • HBase is an OSS distributed LSM backed hash table

based on Google’s Bigtable.

  • Key value, row based column store.
  • Sorted by row, columns and cell versions.
  • Supports:
  • Scans across rows with filters.
  • Get specific row and/or columns.
  • Atomic operations.
  • CP from CAP theorem.
slide-28
SLIDE 28

28

OpenTSDB Schema

  • Row key is a concatenation of UIDs and time:
  • salt + metric + timestamp + tagk1 + tagv1… + tagkN + tagvN
  • sys.cpu.user 1234567890 42 host=web01 cpu=0

\x01\x00\x00\x01\x49\x95\xFB\x70\x00\x00\x01\x00\x00\x01\x00\x00\x02\x00\x00\x02

  • Timestamp normalized on hour or daily boundaries.
  • All data points for an hour or day are stored in one row.
  • Data: VLE 64 bit signed integers or single/double precision

signed floats, Strings and raw histograms.

  • Saves storage space but requires UID conversion.
slide-29
SLIDE 29

29

OpenTSDB Schema

Row Key Columns (qualifier/value) m t1 tagk1 tagv1

  • 1/v1 o2/v2 o3/v3

m t1 tagk1 tagv2

  • 1/v1 o2/v2

m t1 tagk1 tagv1 tagk2 tagv3

  • 1/v1 o2/v2 o3/v3

m t1 tagk1 tagv2 tagk2 tagv4

  • 1/v1 o3/v3

m t1 tagk3 tagv5

  • 1/v1 o2/v2 o3/v3

m t1 tagk3 tagv6

  • 2/v2

m t2 tagk1 tagv1

  • 1/v1 o3/v3

m t2 tagk1 tagv2

  • 1/v1 o2/v2
slide-30
SLIDE 30

30

OpenTSDB Use Cases

  • Backing store for Argus: Open

source monitoring and alerting system.

  • 50M writes per minute.
  • ~4M writes per TSD per minute.
  • 23k queries per minute.
  • https://github.com/salesforce/Argus
slide-31
SLIDE 31

31

OpenTSDB Use Cases

  • Monitoring system, network and

application performance and statistics.

  • Single cluster: 10M to 18M writes/s ~ 3PB.
  • Multi-tenant and Kerberos secure HBase.
  • ~200k writes per second per TSD.
  • Central monitoring for all Yahoo properties.
  • Over 1 billion active time series served.
  • Leading committer to OpenTSDB.
slide-32
SLIDE 32

32

Other Users

slide-33
SLIDE 33

33

OpenTSDB

Pros:

  • Scalable with HBase/HDFS or hosted Google Bigtable

including replication.

  • Annotation and distributed histograms (digests).
  • Rollup, pre-aggregate support.
  • Built-in graphing and analytics or use OSS tools (Grafana).

Cons:

  • Distributed HBase is complex. (Hosted Bigtable easy).
  • UID resolution and current lack of metadata.
  • High cardinality or dense time series still an issue.
slide-34
SLIDE 34

34

OpenTSDB

For version 3.0:

  • New query engine with:
  • Distributed queries.
  • Time based caching.
  • Write-through caching using Facebook Beringei.
  • Pluggable storage engines.
  • Anomaly detection via machine learning.
slide-35
SLIDE 35

35

2010’s - Druid

Schema:

  • Time-sharded columnular segments with bitmapped indexes to

dictionary strings.

  • In memory and on-disk stores with distributed queries.

Pros:

  • Scalable with HDFS or S3, including replication.
  • Analytics and mutations with OLAP slicing and dicing.
  • Time-based rollups and pre-aggregates.

Cons:

  • Complex infrastructure.
  • Similar cardinality issue as TSDB.
  • Complex JSON query syntax (OSS projects to abstract).
slide-36
SLIDE 36

36

2010’s - InfluxDB

Schema:

  • Custom Time structured Log Structured Merge engine.

Pros:

  • Flexible SQLish query language.
  • Time-based rollups available.
  • Nanosecond precision.

Cons:

  • Embryonic clustering support (no longer open sourced).
  • Similar cardinality issues as other stores.
  • Still working on scaling.
slide-37
SLIDE 37

37

Today - Many more

DalmatinerDB

https://misfra.me/2016/04/09/tsdb-list/

slide-38
SLIDE 38

38

Back to RDBMS?

Still possible:

  • Separate meta data (names, dimensions) from values.
  • Shard across servers using abstraction layers, coordinators.
  • Custom SQL plugins.
slide-39
SLIDE 39

39

More Info and Credits

  • Thanks to the Monitoring and HBase teams at Yahoo, Pythian for Bigtable

support and our OSS contributors!

  • Contribute at github.com/OpenTSDB/opentsdb
  • Website: opentsdb.net
  • Mailing List: groups.google.com/group/opentsdb

Images

  • https://commons.wikimedia.org/wiki/File:Programmer_writing_code_with_Unit_Tests.jpg
  • http://www.doncio.navy.mil/CHIPS/ArticleDetails.aspx?ID=8098
  • https://www.google.com/url?sa=i&rct=j&q=&esrc=s&source=images&cd=&cad=rja&uact=8&ved=0ahUKEwiA9ruW9L3

TAhWr0FQKHTacC4kQjRwIBw&url=https%3A%2F%2Fpixabay.com%2Fen%2Fphotos%2Fthumbs%2520up%2F&psi g=AFQjCNGw50t6XHh7NO6swxMd57qYziG6Cg&ust=1493151014332670

  • https://commons.wikimedia.org/wiki/File:Twemoji_1f626.svg
  • https://xkcd.com/1425/
  • https://commons.wikimedia.org/wiki/Emoji#/media/File:Twemoji_1f623.svg
  • https://c1.staticflickr.com/1/508/32307332875_40e73bf750_b.jpg
  • http://code.flickr.net/2008/10/27/counting-timing/