A presentation at PHPDay 2021 Digital Edition by Lorna Jane Mitchell
Time Series Databases Lorna Mitchell, Aiven
Time Series Data • Timestamp information • Point-in-time value • Metadata (such as location, measurement type) • Immutable @lornajane
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
How to work with Time Series Data in PHP
How to work with Time Series Data in PHP Use PostgreSQL
Dataset Weather in Manchester 1980-2020 @lornajane
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
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
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
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
Downsampling Data
Downsampling Data We don’t need every datapoint, forever. Above: 9 days. Below: 3 years. @lornajane
Reducing Detail Over Time @lornajane
Specialist Time Series Databases
Specialist Databases • Prometheus, open source metrics collector • InfluxDB, open source for single node • M3DB, open source, clustered • VictoriaMetrics, open source, clustered @lornajane
M3DB Architecture @lornajane
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
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
Ready-Made Integrations Try M3 by monitoring a services, view with Grafana @lornajane
Ready-Made Integrations Visualise server metrics with Grafana @lornajane
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
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
Time Series Databases Specialist tools for specialist problems
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
As our applications gather more and more data, ever more quickly, both developer and application can become overwhelmed in no time. Recognising when data would be best handled as time series data (spoiler: events, logs and metrics all work pretty well), and knowing how to adapt your application to work this way can really help.
This session introduces time series data concepts, and discusses the situations where the aptly named databases are a useful tool - as well as when they are not! You will learn about handling large data volumes and some of the open source tools available, such as InfluxDB and M3DB. We’ll look at examples of implementing time series databases in real world applications, and give resources that you can try out yourself.
