Introduction to Timeseries Databases

A presentation at PHPDay 2021 Digital Edition in June 2021 in by Lorna Jane Mitchell

Slide 1

Slide 1

Time Series Databases Lorna Mitchell, Aiven

Slide 2

Slide 2

Time Series Data • Timestamp information • Point-in-time value • Metadata (such as location, measurement type) • Immutable @lornajane

Slide 3

Slide 3

Time Series Data ===== Time ===== 11:17 11:22 11:27 11:32 ===== =========== Temperature =========== 24.8 24.8 25.0 24.9 =========== ================== Metadata ================== Fish tank Fish tank Fish tank Fish tank ================== @lornajane

Slide 4

Slide 4

How to work with Time Series Data in PHP

Slide 5

Slide 5

How to work with Time Series Data in PHP Use PostgreSQL

Slide 6

Slide 6

Dataset Weather in Manchester 1980-2020 @lornajane

Slide 7

Slide 7

Time Series Queries With this much data, aggregation is very useful Select the average temperature for each week with PostgreSQL: select date_trunc(‘week’, time) as week, avg(temp) from weather group by week; @lornajane

Slide 8

Slide 8

Next-Level: TimescaleDB “TimescaleDB is a category-defining relational database for time-series data.” • https://www.timescale.com/ • A PostgreSQL extension • Hypertable support • Walks and talks like PostgreSQL @lornajane

Slide 9

Slide 9

TimescaleDB Time_Bucket The time_bucket() function allows more flexible queries • Query by second/minute/day/week as before • Also by multiples of those, e.g. 5 minutes or 8 hours select time_bucket(‘4 weeks’, time) as month, avg(temp) from weather group by month; @lornajane

Slide 10

Slide 10

TimescaleDB Aggregation • Use downsampling to store reduced resolution data for longer periods • TimescaleDB has continuous aggregates that define: • the time bucket size • which aggregates (average, maximum) to store • Comparable to a materialised view @lornajane

Slide 11

Slide 11

Downsampling Data

Slide 12

Slide 12

Downsampling Data We don’t need every datapoint, forever. Above: 9 days. Below: 3 years. @lornajane

Slide 13

Slide 13

Reducing Detail Over Time @lornajane

Slide 14

Slide 14

Specialist Time Series Databases

Slide 15

Slide 15

Specialist Databases • Prometheus, open source metrics collector • InfluxDB, open source for single node • M3DB, open source, clustered • VictoriaMetrics, open source, clustered @lornajane

Slide 16

Slide 16

M3DB Architecture @lornajane

Slide 17

Slide 17

PHP and M3DB Influx DB wire protocol is supported: composer require influxdb/influxdb-php Connect to the database: $client = new \InfluxDB\Client( “https://example-service-name.aivencloud.com”, “13041”, “avnadmin”, “supersecretandsecure”, true, true, ); @lornajane

Slide 18

Slide 18

PHP and M3DB Sending data: // construct a data point $point = new \InfluxDB\Point( ‘php_example_metric’, // name of the measurement 0.64, // the measurement value [‘host’ => ‘server1’, ‘location’ => ‘EU-DE-22’], // optional tags [‘cpucount’ => 8], // optional additional fields ); // write to the database using the path $result = $client->write([“url” => “api/v1/influxdb/write?db=default”], $point); Pass as many points as you like! @lornajane

Slide 19

Slide 19

Ready-Made Integrations Try M3 by monitoring a services, view with Grafana @lornajane

Slide 20

Slide 20

Ready-Made Integrations Visualise server metrics with Grafana @lornajane

Slide 21

Slide 21

M3DB Namespaces M3DB namespaces can have different retention and resolution of the data stored • One unaggregated namespace, required • Optional additional aggregated namespaces • resolution (e.g. 4h) • retention period (e.g. 30 days) @lornajane

Slide 22

Slide 22

M3DB Aggregator Makes the aggregation magic work in the distributed system. An optional (but recommended) extra component • samples the incoming unaggregated data • writes the aggregated results to M3DB @lornajane

Slide 23

Slide 23

Time Series Databases Specialist tools for specialist problems

Slide 24

Slide 24

Resources • https://aiven.io (free trial!) • https://www.postgresql.org • https://www.timescale.com • https://www.influxdata.com • https://m3db.io • https://lornajane.net @lornajane