Effidient!Tiimeiieries T with PostgreSQL iTeveiiimps Tont! FOSDEM - - PowerPoint PPT Presentation

▶
effidient ti imeiieries t
SMART_READER_LITE
LIVE PREVIEW

Effidient!Tiimeiieries T with PostgreSQL iTeveiiimps Tont! FOSDEM - - PowerPoint PPT Presentation

Mar 20, 2023 19 likes •1.05k views

Effidient!Tiimeiieries T with PostgreSQL iTeveiiimps Tont! FOSDEM PGDay 2018 steve@smpsn.net Overview Overview Background Overview Background Complexity Overview Background Complexity Time Series Overview Schema Indexing

slide-1
SLIDE 1

Effidient!Ti喘imeiieries T

with PostgreSQL

iTeveiiimps Tont!

steve@smpsn.net

FOSDEM PGDay 2018

slide-2
SLIDE 2
slide-3
SLIDE 3

Overview

slide-4
SLIDE 4

Overview

Background

slide-5
SLIDE 5

Overview

Background Complexity

slide-6
SLIDE 6

Overview

Background Complexity Time Series

slide-7
SLIDE 7

Overview

Background Schema Indexing Normalisation Summarisation Partitioning Complexity Time Series

slide-8
SLIDE 8

Bafidkgrount!d

slide-9
SLIDE 9

Developers T,iDevelopers T,iDevelopers T

slide-10
SLIDE 10

Expent!s Tivei喘oys T

slide-11
SLIDE 11

Mont!iTorint!g

Vis TibiliTyiint!ToiTheioperaTiont!iofihardwareiant!dis TofTware e.g. web site, database, cluster, disk drive

slide-12
SLIDE 12

Mont!iTorint!g

Vis TibiliTyiint!ToiTheioperaTiont!iofihardwareiant!dis TofTware e.g. web site, database, cluster, disk drive

! Look ! ! Graphs !

slide-13
SLIDE 13

Mont!iTorint!gi–i喘imeiieries T

slide-14
SLIDE 14

ComplexiTy

slide-15
SLIDE 15

“Mifidros Tervifides TiHell”

PostgreSQL Redis Frontend Machine Learning Bit Login / IAM Backend API Cache Configuration Store Elasticsearch Cassandra Search API Service Discovery (consul, etcd) Container Orchestration Engine Docker Swarm / Kubernetes MySQL
slide-16
SLIDE 16

MoreiCowbelli-iOpent!iTafidk

Heat Horizon Nova Keystone Conductor Scheduler Compute Glance Cinder KVM MySQL RabbitMQ ... ... ... ... ... ... ... ... Ceph
slide-17
SLIDE 17

MoreiCowbelli-iOpent!iTafidk

Heat Horizon Nova Keystone Conductor Scheduler Compute Glance Cinder KVM MySQL RabbitMQ ... ... ... ... ... ... ... ... Ceph

AWS

slide-18
SLIDE 18

i i

Mont!iTorint!g

Software Network Storage Servers MeTrifids T Logs T
slide-19
SLIDE 19

i i

Middleware

Mont!iTorint!g

Software Network Storage Log API Kafka Logstash Elastic InfluxDB Metric API Alerting Grafana Kibana MySQL SQLite Servers MeTrifids T Logs T Zookeeper Storm
slide-20
SLIDE 20

i i

Refidap

PostgreSQL Redis Frontend Machine Learning Bit Login / IAM Backend API Cache Configuration Store Elasticsearch Cassandra Search API Service Discovery (consul, etcd) Container Orchestration Engine - Docker Swarm / Kubernetes MySQL
slide-21
SLIDE 21

i i

Refidap

PostgreSQL Redis Frontend Machine Learning Bit Login / IAM Backend API Cache Configuration Store Elasticsearch Cassandra Search API Service Discovery (consul, etcd) Container Orchestration Engine - Docker Swarm / Kubernetes MySQL Heat Horizon Nova Keystone Conductor Scheduler Compute Glance Cinder KVM MySQL RabbitMQ ... ... ... ... ... ... ... ... Ceph
slide-22
SLIDE 22

i i

Refidap

PostgreSQL Redis Frontend Machine Learning Bit Login / IAM Backend API Cache Configuration Store Elasticsearch Cassandra Search API Service Discovery (consul, etcd) Container Orchestration Engine - Docker Swarm / Kubernetes MySQL Heat Horizon Nova Keystone Conductor Scheduler Compute Glance Cinder KVM MySQL RabbitMQ ... ... ... ... ... ... ... ... Ceph Middleware Log API Kafka Logstash Elastic InfluxDB Metric API Alerting Grafana Kibana MySQL SQLite Zookeeper Storm
slide-23
SLIDE 23

i i

Middleware

Mont!iTorint!g

Software Network Storage Log API Kafka Logstash Elastic InfluxDB Metric API Alerting Grafana Kibana MySQL SQLite Servers MeTrifids T Logs T Zookeeper Storm
slide-24
SLIDE 24

i i

Middleware

Mont!iTorint!g

Software Network Storage Log API Kafka Logstash Elastic InfluxDB Metric API Alerting Grafana Kibana MySQL SQLite Servers MeTrifids T Logs T Zookeeper Storm
slide-25
SLIDE 25

i i

Mont!iTorint!g

Commendable “right tool for the job” attitude, but…

ATileas TT,ifidouldis TomeiofiTheipers Tis TTent!fideibeiunt!ifedd Fewerifailureimodes T Feweribafidkupis TTraTegies T Feweriredunt!dant!fidy/replifidaTiont!iproTofidols T Ont!eis TeTiofifidont!s Tis TTent!TidaTais Temant!Tifids T Re-us Teiexis TTint!gioperaTiont!aliknt!owledge

slide-26
SLIDE 26

i i

Ont!eis Timpleiques TTiont!…

Is PostreSQL a Time Series Database?

slide-27
SLIDE 27

喘imeiieries T

slide-28
SLIDE 28

喘imeiieries TiDaTabas Tes Ti–i喘heiChoifide!

  • Ganglia (RRDtool)
  • Graphite (Whisper)
  • OpenTSDB (HBase)
  • KairosDB (Cassandra)
  • InfuxDB
  • Prometheus
  • Gnocchi
  • Atlas
  • Heroic
  • Hawkular (Cassandra)
  • MetricT

ank (Cassandra)

  • Riak TS (Riak)
  • Bluefood (Cassandra)
  • DalmatinerDB
  • Druid
  • BTrDB
  • Warp 10 (Hbase)
  • Tgres (PostgreSQL!)
slide-29
SLIDE 29

喘imeiieries TiDaTabas Tes Ti–i喘heiChoifide!

  • Ganglia [Berkley]
  • Graphite [Orbitz]
  • OpenTSDB [Stubleupon]
  • KairosDB
  • InfuxDB
  • Prometheus [SoundCloud]
  • Gnocchi [OpenStack]
  • Atlas [Netfix]
  • Heroic [Spotify]
  • Hawkular [Redhat]
  • MetricT

ank [Raintank]

  • Riak TS [Basho]
  • Bluefood [Rackspace]
  • DalmatinerDB
  • Druid
  • BTrDB
  • Warp 10
  • Tgres
slide-30
SLIDE 30

喘imeiieries TiDaTabas Tes T

slide-31
SLIDE 31

喘imeiieries TiDaTabas Tes T

2000

slide-32
SLIDE 32

喘imeiieries TiDaTabas Tes T

2000 2010

slide-33
SLIDE 33

喘imeiieries TiDaTabas Tes T

2000 2010 2013 - 2017

slide-34
SLIDE 34

喘imeiieries Ti–iPeriodifid time value

10:01 15% 10:02 100% 10:03 86%

CollefidTor

10:04 60% 10:05 0%

slide-35
SLIDE 35 CollefidTor

喘imeiieries Ti–iPeriodifid

CollefidTor

time value

10:01 15%

MeTrifid MeTa

name dimensions

cpu

{ host:prod1 }

10:01 1% cpu

{ host:prod2 }

10:01 24° temp

{ sensor:rack } CollefidTor

10:02 100% cpu

{ host:prod1 }

10:02 87% cpu

{ host:prod2 }

10:02 26° temp

{ sensor:rack }
slide-36
SLIDE 36 CollefidTor

喘imeiieries Ti–iiporadifid

CollefidTor

time value

10:07 13

MeTrifid MeTa

name dimensions meta

log

{ host:prod1 } Event!T MeTa { msg:… }

11:39 1 log

{ host:prod2 } { msg:… }

11:50 2 alarm

{ host:prod1 } { reason:… } Event!Ts T

14:02 1 alarm

{ host:prod1 } { reason:… }
slide-37
SLIDE 37

"metric": { "timestamp": 1232141412, "value": 42, "name": "cpu.percent", "dimensions": { "hostname": "dev-01" }, "value_meta": { … } } 喘imeiieries Ti–iInt!ges TTiDaTa

  • JiONiEnt!fidoded
  • 喘imes TTampi(Unt!ix/IiO/..)
  • Meas Turement!TiValue
  • MeTrifidiIdent!TiffidaTiont!
– Name – Dimensions (or “T

ags”)

  • OpTiont!aliEvent!TiDaTa
slide-38
SLIDE 38

喘imeiieries Ti–iQueries T

10:01 10:02 10:03 20 21 22 23 24 25 26 27 28 29 30 { sensor:rack } temp °C

time value

10:01 15%

name dimensions

cpu

{ host:prod1 }

10:01 1% cpu

{ host:prod2 }

10:01 24° temp

{ sensor:rack }

10:02 100% cpu

{ host:prod1 }

10:02 87% cpu

{ host:prod2 }

10:02 26° temp

{ sensor:rack }

10:03 100% cpu

{ host:prod1 }

10:03 50% cpu

{ host:prod2 }

10:03 27° temp

{ sensor:rack }
slide-39
SLIDE 39

喘imeiieries Ti–iQueries T

10:01 10:02 10:03 10 20 30 40 50 60 70 80 90 100 { host:prod1 } { host:prod2 } cpu %

time value

10:01 15%

name dimensions

cpu

{ host:prod1 }

10:01 1% cpu

{ host:prod2 }

10:01 24° temp

{ sensor:rack }

10:02 100% cpu

{ host:prod1 }

10:02 87% cpu

{ host:prod2 }

10:02 26° temp

{ sensor:rack }

10:03 100% cpu

{ host:prod1 }

10:03 50% cpu

{ host:prod2 }

10:03 27° temp

{ sensor:rack }
slide-40
SLIDE 40 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 10:00 10:00 10:00

喘imeiieries Ti–iQueries T

10:01 10:02 10:03 10 20 30 40 50 60 70 80 90 100 { host:prod1 } { host:prod2 } cpu %

time value

10:01 15%

name dimensions

cpu { host:prod1 } 10:01 1% cpu { host:prod2 } 10:01 24° temp { sensor:rack } 10:02 100% cpu { host:prod1 } 10:02 87% cpu { host:prod2 } 10:02 26° temp { sensor:rack } 10:03 100% cpu { host:prod1 } 10:03 50% cpu { host:prod2 } 10:03 27° temp { sensor:rack } 10:04 10:04 10:04 10:05 10:05 10:05
slide-41
SLIDE 41

喘imeiieries Ti–iifidalint!g time value name dimensions

  • ReTent!Tiont!i(Volume)
– How long is data kept – Data volume stored
  • MeTrifidiAmount!T
– Number of series names – Additional dimensions
  • QueryiComplexiTy
– Time range size – Number of metrics

10:01 15% cpu

{host:prod1}

10:01 1% cpu

{host:prod2}

10:01 24° temp

{sensor:rack}

10:02 100% cpu

{host:prod1}

10:02 87% cpu

{host:prod2}

10:02 26° temp

{sensor:rack}

10:03 100% cpu

{host:prod1}

10:03 50% cpu

{host:prod2}

10:03 27° temp

{sensor:rack}
slide-42
SLIDE 42

RelaTiont!aliModel

slide-43
SLIDE 43

CREATE TABLE measurements ( timestamp TIMESTAMPTZ, value FLOAT8, name VARCHAR, dimensions JSONB, value_meta JSON ); RelaTiont!aliModeli–iDent!ormalis Tediifidhema

  • iToreiint!is Tint!gleiTable
– Trivial mapping of data – Row is a measurement
  • NaTiveiformaTiTime
– Normalise to UTC timezone – “Just use TIMESTAMPTZ”
  • FloaTint!gipoint!Tivalue
– T

ypical for sensor use cases

– Strict accuracy: use NUMERIC
  • e.g. Money!
slide-44
SLIDE 44

CREATE TABLE measurements ( timestamp TIMESTAMPTZ, value FLOAT8, name VARCHAR, dimensions JSONB, value_meta JSON ); RelaTiont!aliModeli–iDent!ormalis Tediifidhema

  • ArbiTraryilent!gThint!ame
– Fixed length: no real efect
  • Key/valueidiment!s Tiont!s T
– JSONB is binary encoded – Effjcient access to children – Best if querying contents
  • Key/valueimeTadaTa
– JSON just validated text – Best if purely payload
slide-45
SLIDE 45

SELECT DISTINCT name, dimensions FROM measurements WHERE

name = 'cpu.percent' dimensions @> '{"host": "dev-01"}'::JSONB

RelaTiont!aliModeli–iieries TiLis TTint!giQuery

  • ielefidTimeTrifidimeTadaTa
  • ihowieafidhimeTrifidiont!fide
  • OpTiont!allyiflTer
– All metrics with name – All metrics for a host
slide-46
SLIDE 46 SELECT TIME_ROUND(timestamp, 60), AVG(value) FROM measurements WHERE timestamp BETWEEN '2015-01-01Z00:00:00' AND '2015-01-01Z01:00:00' AND name = 'cpu.percent' AND dimensions @> '{"host": "dev-01"}'::JSONB GROUP BY 1

RelaTiont!aliModeli–iiint!gleiieries TiQuery

  • Fint!dimeas Turement!Ts T
– Specify time range – Specify metric name – Specify dimension value
  • AggregaTeidaTaipoint!Ts T
– Round to desired interval – Group by that interval – T

ake average of all data points in that interval

slide-47
SLIDE 47

RelaTiont!aliModeli–iPerformant!fideiAnt!alys Tis T

Query Duration (seconds) Data Volume (M/rows)

喘argeT:i<100ms T

(QueryiDuraTiont!)

Query Duration (seconds) Time Range (seconds)

slide-48
SLIDE 48

RelaTiont!aliModeli–iiint!gleiieries TiQueryi(vs Ti喘imeiRant!ge)

1000 2000 3000 4000 5000 6000 7000 8000 9000 0.00 0.05 0.10 0.15 0.20 0.25 0.30 0.35 0.40 3M Rows 2M Rows 1M Rows Query Time Range (seconds) Query Duration (seconds)
slide-49
SLIDE 49

RelaTiont!aliModeli–iiint!gleiieries TiQueryi(vs TiDaTaiVolume)

1 2 3 4 5 6 7 8 9 10 0.00 0.20 0.40 0.60 0.80 1.00 1.20 1.40 Data Volume (M-rows) Query Duration (seconds)
slide-50
SLIDE 50

RelaTiont!aliModeli-iAnt!alys Tis T

✔ QueryiTimeifxedi regardles Ts TiofiTimei rant!ge ✔ Ont!iTargeTifor <i~1Mirows T ✗ QueryiTimeis Tfidales Ti lint!earlyiwiThidaTai volume ✗ Everyiqueryireads Ti everyirow ✗ Full table scan

slide-51
SLIDE 51

Int!dexint!g

slide-52
SLIDE 52

Int!dexint!g

  • 喘imes TTamps Tiareies Ts Tent!Tiallyiint!Tegers T
  • Pos TTgreiQLihas Timant!yiint!dexiTypes T

B喘REE,iHAiH,iBRIN,iGIN,iGIi喘

  • B喘REEiexfidellent!TiforiEqualiTyiant!diBeTweent!
slide-53
SLIDE 53

Index Table

1 6 3 2

Int!dexint!gi–iB喘REE

3 three 2 two 4 four 6 six 1

  • ne

5 five 8 eight 7 seven

4 5 7 8

slide-54
SLIDE 54

Index Table

1 6 3 2

Int!dexint!gi–iB喘REE

3 three 2 two 4 four 6 six 1

  • ne

5 five 8 eight 7 seven

4 5 7 8

=7

slide-55
SLIDE 55

Index Table

1 6 3 2

Int!dexint!gi–iB喘REE

3 three 2 two 4 four 6 six 1

  • ne

5 five 8 eight 7 seven

4 5 7 8

>=6 <=8

slide-56
SLIDE 56 SELECT TIME_ROUND(timestamp, 60), AVG(value) FROM measurements WHERE timestamp BETWEEN '2015-01-01Z00:00:00' AND '2015-01-01Z01:00:00' AND name = 'cpu.percent' AND dimensions @> '{"host": "dev-01"}'::JSONB GROUP BY 1

Int!dexint!gi–iiint!gleiieries TiQuery

  • BE喘WEENipredifidaTe
  • Elimint!aTes TihugeiporTiont!i
  • fiTableifidont!Tent!Ts T
– High selectivity
  • Exfidellent!Tifidant!didaTeifori

int!dexifidreaTiont!

slide-57
SLIDE 57

Int!dexint!gi-i喘imes TTamp CREATE INDEX ON measurements USING BTREE (timestamp);

  • ipefidifyiTableiToiint!dex
  • ipefidifyiint!dexiType
– Optional: BTREE is default
  • ipefidifyifidolumnt!iToiint!dex
slide-58
SLIDE 58

Int!dexint!gi–iiint!gleiieries TiQueryi(vs TiDaTaiVolume)

1 2 3 4 5 6 7 8 9 10 0.00 0.20 0.40 0.60 0.80 1.00 1.20 1.40 No Index With Index Data Volume (millions/rows) Query Duration (seconds)
slide-59
SLIDE 59

Int!dexint!gi–iiint!gleiieries TiQueryi(vs TiDaTaiVolume)

1 2 3 4 5 6 7 8 9 10 0.000 0.005 0.010 0.015 0.020 0.025 9000 8000 7000 Data Volume (millions/rows) Query Duration (seconds)
slide-60
SLIDE 60

Int!dexint!gi–iiint!gleiieries TiQueryi(vs Ti喘imeiRant!ge,i10MiRows T)

1000 2000 3000 4000 5000 6000 7000 8000 9000 10000 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 0.10 1 Metric 10 Metrics Query Time Range (seconds) Query Duration (seconds)
slide-61
SLIDE 61

Int!dexint!gi–iiint!gleiieries TiQueryi(vs Ti喘imeiRant!ge,i10MiRows T)

1000 2000 3000 4000 5000 6000 7000 8000 9000 10000 0.00 0.05 0.10 0.15 0.20 0.25 1 Metric 10 Metrics 100 Metrics Query Time Range (seconds) Query Duration (seconds)
slide-62
SLIDE 62

Int!dexint!gi-iAnt!alys Tis T

✔ DaTaiVolume ✔ T

  • 10M

✔ 喘imeiRant!ge ✔ T

  • 9000s (10 Metrics)

✔ QueryiTimeis TTableias Ti DaTaiVolumeiint!fidreas Tes T

✗ 喘imeiRant!ge ✗ Over 4000s (100 Metrics) ✗ Nowiapparent!Tiqueryi duraTiont!iint!fidreas Tes Tias Ti 喘imeiRant!geigrows T ✗ Int!fidreas Tint!gint!umberiofi meTrifids Tidras TTifidallyi afefidTs TiqueryiduraTiont! ✗ Data for each uninteresting series must be fltered out

slide-63
SLIDE 63 SELECT TIME_ROUND(timestamp, 60), AVG(value) FROM measurements WHERE timestamp BETWEEN '2015-01-01Z00:00:00' AND '2015-01-01Z01:00:00' AND name = 'cpu.percent' AND dimensions @> '{"host": "dev-01"}'::JSONB GROUP BY 1

Int!dexint!gi–iiint!gleiieries TiQuery

  • Moreiint!dexint!gd
– name – dimensions
slide-64
SLIDE 64

Int!dexint!gi–iAddiTiont!al CREATE INDEX ON measurements USING BTREE (name); CREATE INDEX ON measurements USING GIN (dimensions);

  • CreaTeint!ewiint!dexes Tiont!i

meas Turement!Ts TiTable

  • ipefidifyint!ame
– Equality: Use BTREE
  • ipefidifyidiment!s Tiont!s T
– Containment: Use GIN – Find contents of JSON
slide-65
SLIDE 65

Int!dexint!gi–iieries TiQueryi(vs Ti喘imeiRant!ge,i10MiRows T,i100iMeTrifids T)

1000 2000 3000 4000 5000 6000 7000 8000 9000 10000 0.00 0.05 0.10 0.15 0.20 0.25 Time & Metric Time Index Query Time Range (seconds) Query Duration (seconds)
slide-66
SLIDE 66

Int!dexint!g

✗ Ohidear ✗ 喘ooimufidhiint!dexint!gifidant!ibeiharmful

slide-67
SLIDE 67

Normalis TaTiont!

slide-68
SLIDE 68

CREATE TABLE measurements ( timestamp TIMESTAMPTZ, value FLOAT8, name VARCHAR, dimensions JSONB, value_meta JSON ); Normalis TaTiont! CREATE TABLE values ( timestamp TIMESTAMPTZ, value FLOAT8, metric_id INT, value_meta JSON ); CREATE TABLE metrics ( id SERIAL, name VARCHAR, dimensions JSONB, UNIQUE (name, dimensions) );

slide-69
SLIDE 69

Normalis TaTiont!

  • Values Tis TToredibyiint!Tegeriid
– References entry in metric table – The name/dimensions for each

metric are only stored once

– Eliminates repeated bulky data

in measurements table

  • MeTrifidiTableidefnt!es Tiid
– SERIAL produces incrementing

integers to allot id values

– UNIQUE constraint is useful

during normalisation

  • Implicitly creates suitable index

CREATE TABLE values ( timestamp TIMESTAMPTZ, value FLOAT8, metric_id INT, value_meta JSON ); CREATE TABLE metrics ( id SERIAL, name VARCHAR, dimensions JSONB, UNIQUE (name, dimensions) );

slide-70
SLIDE 70

Normalis TaTiont!i–iView CREATE VIEW measurements AS SELECT timestamp, value, name, dimensions, value_meta FROM values INNER JOIN metrics ON (metric_id = id);

  • Mimifidimeas Turement!Ts T
– Views can be queried in

the same way as tables

  • Defnt!ediwiThiiELEC喘
– Query to run which

produces contents of view

  • Joint!int!ormalis TediTables T
  • Cant!ire-us Teis Tamei

queries Tias Tibefore

slide-71
SLIDE 71 CREATE RULE measurements_insert AS ON INSERT TO measurements DO INSTEAD INSERT INTO values ( timestamp, value, metric_id, value_meta ) VALUES ( NEW.timestamp, NEW.value, create_metric ( NEW.name, NEW.dimensions), NEW.value_meta );

Normalis TaTiont!i–iViewiInt!s TerT

  • Cant!’Tiint!s TerTidaTaiint!Toi

views TibyidefaulT

  • Cant!is Tpefidifyiant!iafidTiont!i

Toiperformiont!iINiER喘

  • Int!s TerTiint!Toivalues T
  • HelperiprofidedureiToi

allofidaTeimeTrifid_id

  • Normalis TaTiont!iis Ti

Trant!s Tparent!Tiforius Ter

slide-72
SLIDE 72 CREATE FUNCTION create_metric ( in_name VARCHAR, in_dims JSONB ) RETURNS INT LANGUAGE plpgsql AS $_$ DECLARE
  • ut_id INT;
BEGIN SELECT id INTO out_id FROM metrics AS m WHERE m.name = in_name AND m.dimensions = in_dims; IF NOT FOUND THEN INSERT INTO metrics ("name", "dimensions") VALUES (in_name, in_dims) RETURNING id INTO out_id; END IF; RETURN out_id; END; $_$;

Normalis TaTiont!i–iMeTrifidiLookup

  • iTorediprofidedure
– T

ake name/dimensions

– Returns metric_id
  • Fint!diexis TTint!gimeTrifid
– Return existing id
  • Ifint!ew,iThent!iINiER喘
– Allocates new id – Return the new id
slide-73
SLIDE 73

CREATE INDEX ON values USING BTREE (timestamp); CREATE INDEX ON values USING BTREE (metric_id); Normalis TaTiont!i-iInt!dexint!g

  • 喘imes TTampiint!dex
– Same as before
  • Newiint!dexiont!imeTrifid_id
– Allow effjcient fltering of

metrics during JOIN

– Serves similar purpose to

existing metric indexing

slide-74
SLIDE 74

Normalis TaTiont!i–iieries TiQueryi(vs Ti喘ime,i10MiRows T,i100iMeTrifids T)

1000 2000 3000 4000 5000 6000 7000 8000 9000 0.00 0.05 0.10 0.15 0.20 0.25 Normalised Denormalised (Time Index) Denormalised (Extra Index) Query Time Range (seconds) Query Duration (seconds)
slide-75
SLIDE 75

Normalis TaTiont!

✔ Normalis TaTiont!i elimint!aTedioverheadiofi addiTiont!alimeTrifidi int!dexint!g ✗ 喘heimeTrifidiint!dexint!gi s TTillidoes Tnt!’Tihaveiai pos TiTiveiefefidT

slide-76
SLIDE 76

time value metric

10:01 . 1 10:01 . 2 10:02 . 1 10:02 . 2 10:03 . 1 10:03 . 2 10:04 . 1 10:04 . 2 A B C D E F G H

Normalis TaTiont!i–iBiTmapiInt!dexiifidant!

time index metric index C E F B D F H D D F

2 :02 :03

slide-77
SLIDE 77

time value metric

10:01 . 1 10:01 . 2 10:02 . 1 10:02 . 2 10:03 . 1 10:03 . 2 10:04 . 1 10:04 . 2 A B C D E F G H

Normalis TaTiont!i–iMulTi-Columnt!iInt!dexint!g

time metric index D F

2 :02 :03

slide-78
SLIDE 78

CREATE INDEX ON values USING BTREE (timestamp); CREATE INDEX ON values USING BTREE (metric_id); Normalis TaTiont!i–iMulTi-Columnt!iInt!dexint!g CREATE INDEX ON values USING BTREE (timestamp, metric_id); CREATE INDEX ON values USING BTREE (metric_id, timestamp);

slide-79
SLIDE 79

Normalis TaTiont!i–iieries TiQueryi(vs TiRant!ge,i10MiRows T,i100iMeTrifids T)

1000 2000 3000 4000 5000 6000 7000 8000 9000 0.00 0.05 0.10 0.15 0.20 0.25 Normalised (Single Index) Normalised Denormalised (Time Index) Denormalised (Extra Index) Query Time Range (seconds) Query Duration (seconds)
slide-80
SLIDE 80

Normalis TaTiont!

  • Int!fidreas Teivolumei10MiToi100M
– @ 1Hz / 100 Metrics: 1M seconds
  • Before: ~1.15 days
  • Now: ~11.5 days
  • Int!fidreas TeimaxiTimeirant!ges Tifromi9000s TiToi90,000s T
– Before: 2.5 hours – Now: 1.04 days
slide-81
SLIDE 81

Normalis TaTiont!i–iieries TiQueryi(vs TiVolume,i10iMeTrifids T)

10 20 30 40 50 60 70 80 90 100 0.02 0.04 0.06 0.08 0.1 0.12 10000 20000 30000 Data Volume (M-rows) Query Duration (seconds)
slide-82
SLIDE 82

Normalis TaTiont!i–iieries TiQueryi(vs TiRant!ge,i100MiRows T)

10000 20000 30000 40000 50000 60000 70000 80000 90000 0.00 0.02 0.04 0.06 0.08 0.10 0.12 0.14 0.16 1 Metric 10 Metrics Query Time Range (seconds) Query Duration (seconds)
slide-83
SLIDE 83

Normalis TaTiont!i–iieries TiQueryi(vs TiRant!ge,i100MiRows T)

10000 20000 30000 40000 50000 60000 70000 80000 90000 0.00 0.10 0.20 0.30 0.40 0.50 0.60 0.70 0.80 0.90 1 Metric 10 Metrics 100 Metrics Query Time Range (seconds) Query Duration (seconds)
slide-84
SLIDE 84

Normalis TaTiont!i–iieries TiQueryi(vs TiRant!ge,i100MiRows T)i(+Cont!fg)

10000 20000 30000 40000 50000 60000 70000 80000 90000 0.00 0.10 0.20 0.30 0.40 0.50 0.60 0.70 0.80 0.90 1 Metric 10 Metrics 100 Metrics Query Time Range (seconds) Query Duration (seconds)
slide-85
SLIDE 85

Normalis TaTiont!i-iAnt!alys Tis T

✔ DaTaiVolume ✔ T

  • 100M

✔ 喘imeiRant!ge ✔ T

  • 90,000s (10 Metrics)

✗ 喘imeiRant!ge ✗ Over 30,000s (100 Metrics) ✗ Over 90,000s ✗ NeediaibeTTeris TTraTegyi foris Tervifidint!gilargeri Timeirant!ges T

slide-86
SLIDE 86

iummaris Tint!g

slide-87
SLIDE 87

iummaris Tint!gi-iProblem

  • Fori100imeTrifids T,is Tomeiqueries Timis Ts TiTargeTi100ms T
– Over ~40Ks (~11 days)
  • QueryimighTibeireTurnt!int!giupiToi40Kipoint!Ts T
  • Is TiThis TiafidTuallyint!efides Ts Taryd
– Especially if data is simply used for visualisation – An average 1080p monitor only has ~2000 pixels
  • LeTs Tis Tayi4000ipoint!Ts Tiareient!ough,iorievent!i400
slide-88
SLIDE 88

values_2 iummaris Tint!gi-iExample time sum metric

10:00 10 1 10:00 2 2 10:02 5 1 10:02 4 2

values time value metric

10:00 10 1 10:00 2 2 10:01 20 1 10:01 6 2 10:02 5 1 10:02 4 2 10:03 15 1 10:03 1 2 30 8 20 5 30 8 20 5

✔ iummaryiTableijus TTiai frafidTiont!iofiTheis Tize

slide-89
SLIDE 89

CREATE TABLE values_10 ( timestamp TIMESTAMPTZ, metric_id INT, sum FLOAT8, count FLOAT8, min FLOAT8, max FLOAT8, UNIQUE (metric_id, timestamp) ); iummaris Tint!gi

  • CreaTeivalues TiTable
– Use for 10:1 summary
  • Ont!eient!Try/Timeiperiod
– Per metric – UNIQUE provide indexing
  • MulTipleiaggregaTes T
– SUM – COUNT – MIN – MAX
slide-90
SLIDE 90

CREATE VIEW summary_10 AS SELECT * FROM values_10 INNER JOIN metrics ON (metric_id = id); iummaris Tint!g

  • CreaTeiaiviewias Tibefore
– Only storing metric_id
  • iimplifes Tiqueries T
  • Joint!s TimeTrifididefnt!iTiont!s T
slide-91
SLIDE 91

CREATE FUNCTION summarise_10 () RETURNS TRIGGER LANGUAGE plpgsql AS $_$ BEGIN : END; $_$; CREATE TRIGGER summarise_10_t AFTER INSERT ON values FOR EACH ROW EXECUTE PROCEDURE summarise_10 ();

iummaris Tint!gi–i喘riggeriDefnt!iTiont!

  • BoilerplaTe
  • Defnt!eiTriggerifunt!fidTiont!
– Stored procedure – Contents omitted
  • 喘riggeriToiexefiduTe…
– On INSERT – T
  • values table
– Data passed to procedure
slide-92
SLIDE 92 INSERT INTO values_10 VALUES ( TIME_ROUND(NEW.timestamp, 10), NEW.metric_id, NEW.value, 1, NEW.value, NEW.value ) ON CONFLICT (metric_id, timestamp) DO UPDATE SET sum = sum + EXCLUDED.sum, count = count + EXCLUDED.count, min = LEAST (min,EXCLUDED.min), max = GREATEST(max,EXCLUDED.max) ;

iummaris Tint!gi–i喘riggeriAfidTiont!

  • Int!s TerTiint!Tois Tummary
–

NEW is inserted data

  • Rount!diTimeiToiperiod
–

10 seconds

  • Int!iTialiaggregaTeivalues T
  • Ifient!Tryiexis TTs Tialready
  • UpdaTeiint!s TTead
–

EXCLUDED is current row

–

Combine new value with existing aggregate value

slide-93
SLIDE 93 SELECT TIME_ROUND(timestamp, 60), (SUM(sum) / SUM(count)) AS avg FROM summary_10 WHERE timestamp BETWEEN '2015-01-01Z00:00:00' AND '2015-01-01Z01:00:00' AND name = 'cpu.percent' AND dimensions @> '{"host": "dev-01"}'::JSONB GROUP BY 1

iummaris Tint!gi–iiint!gleiieries TiQuery

  • Mos TTlyiunt!fidhant!ged
  • Queryis TummaryiTable,i

nt!oTirawimeas Turement!Ts T

  • HaveiToiaggregaTeiThei

parTialiaggregaTiont!s T

–

MIN: MIN(min)

–

MAX: MAX(max)

–

SUM: SUM(sum)

–

COUNT: SUM(count)

–

AVG: SUM(sum)/SUM(count)

slide-94
SLIDE 94

iummaris Tint!gi–iieries TiQueryi(vs TiRant!ge,i100MiRows T)

10000 20000 30000 40000 50000 60000 70000 80000 90000 0.00 0.02 0.04 0.06 0.08 0.10 0.12 1 Metric 10 Metrics 100 Metrics Query Time Range (seconds) Query Duration (seconds)
slide-95
SLIDE 95

iummaris Tint!gi–iieries TiQueryi(vs TiRant!ge,i100MiRows T)

10000 20000 30000 40000 50000 60000 70000 80000 90000 0.00 0.02 0.04 0.06 0.08 0.10 0.12 1 Metric 10 Metrics 100 Metrics 1 Metric 10 Metrics 100 Metrics Query Time Range (seconds) Query Duration (seconds)
slide-96
SLIDE 96

iummaris Tint!g

  • Int!fidreas TeivolumeiToi1BN
– @ 1Hz / 100 Metrics: 10M seconds – Before: 11.5 days – Now: ~115 days : 16½ weeks
  • Int!fidreas TeimaxiTimeirant!ges Tifromi90Ks TiToi900Ks T
– Before: ~1.04 days – Now: ~10.4 days
slide-97
SLIDE 97

iummaris Tint!gi–iieries TiQueryi(vs TiVolume;i100M-1BN)

100 200 300 400 500 600 700 800 900 1000 0.000 0.020 0.040 0.060 0.080 0.100 0.120 100000 200000 300000 Data Volume (M-rows) Query Duration (seconds)
slide-98
SLIDE 98

iummaris Tint!gi–iieries TiQueryi(vs TiRant!ge,i1BNiRows T)

100000 200000 300000 400000 500000 600000 700000 800000 900000 0.02 0.04 0.06 0.08 0.1 0.12 Query Time Range (seconds) Query Duration (seconds)
slide-99
SLIDE 99

✔ DaTaiVolume ✔ T

  • 1BN – ~16 weeks

✔ 喘imeiRant!ge ✔ T

  • ~10 days

✔ 喘ois TfidaleifurTherdi喘ryi100:1is Tummary

iummaris Tint!g

slide-100
SLIDE 100

Clos Tint!giNoTes T

slide-101
SLIDE 101

WhaTiNexTd

  • ParTiTiont!int!g
– By timestamp and metric_id – Retention window (i.e. keep last 6 months)
  • Int!ges TTiopTimis TaTiont!
– Compute aggregates in batches (e.g. every 5min) – Non trigger-based summarisation – Possibility to abuse logical replication
slide-102
SLIDE 102

Is TiITiWorThiITd

  • Yes T
– Reduce complexity in terms of number of technologies – Reuse existing infrastructure and operational knowledge – No loss in consistency – ACID all the way
  • Nod
– Increase complexity in terms of database design – Is an out-of-the box tool already available?
slide-103
SLIDE 103

i i 103

喘hant!ks T s TTeve@s Tmps Tnt!.nt!eT