InfluxDB

Introduction

InfluxDB is an open-source time series database developed by InfluxData.

It is written in Go and optimized for fast, high-availability storage and retrieval of time series data in fields such as operations monitoring, application metrics, Internet of Things sensor data, and real-time analytics.

It is often used with Telegraf or Collectd to gather system information.

https://www.influxdata.com

Download

https://portal.influxdata.com/downloads

Installation

https://docs.influxdata.com/influxdb

https://docs.influxdata.com/influxdb/v1.3/introduction/installation/

https://hostpresto.com/community/tutorials/how-to-install-influxdb-on-ubuntu-14-04/

Limit RAM Memory Usage

sudo nano /etc/systemd/system/influxd.service

[Service]
...
MemoryMax=256M
StandardOutput=null
StandardError=null

https://github.com/influxdata/influxdb/issues/38

https://github.com/influxdata/influxdb/issues/8319

Documentation

https://docs.influxdata.com/influxdb/v1.3/introduction/getting_started/

Administration

http://localhost:8083/

...or...

influx

Databases

Top right > Drop down menu > Telegraf

...or...

show databases

use <database>

show fields keys

Queries

show measurements
select * from mem order by time desc limit 10

Select Time Range

> select * from emby where time >= '2020-12-01 21:44:00' and time < '2020-12-01 21:45:00'
name: emby
time                item                               user
----                ----                               ----
1606859041995492133 Hattie                             Tracey
1606859042069982423 Batman v Superman: Dawn of Justice Amy
1606859042146453803 Dune                               Paul
1606859042222398214 Ratatouille                        Tom
>

Delete Data

The DELETE query deletes all points from a series in a database. Unlike DROP SERIES, it does not drop the series from the index and it supports time intervals in the WHERE clause.

The query takes the following form where you must include either the FROM clause or the WHERE clause, or both.

> DELETE FROM <measurement_name> WHERE [<tag_key>='<tag_value>'] | []

Delete all data associated with the measurement h2o_feet:

> DELETE FROM "h2o_feet"

Delete all data associated with the measurement h2o_quality and where the tag randtag equals 3:

> DELETE FROM "h2o_quality" WHERE "randtag" = '3'

We will delete series data which is older than 1000s:

> DELETE FROM mysql_value WHERE time > 1000s
> SELECT * FROM mysql_value LIMIT 10

Delete all data in the database that occur before January 01, 2016:

> DELETE WHERE time < '2016-01-01'

A successful DELETE query returns an empty result.

Delete Series

Drop Data

> drop series from <measurement>;
> drop measurement <measurement>;

Drop Series

Automated Data Deletion

This sets up how long to keep the data, which is like a self cleaning process.

create retention policy two_years on telegraf duration 64w replication 1 default

Retention Policy Management

Backup and Restore

influxd backup -portable path-to-backup

influxd restore -portable path-to-backup

https://docs.influxdata.com/influxdb/v1.7/administration/backup_and_restore/

PPP Disconnects

echo "pppd,host=erx result=0" | nc 192.168.0.252 8094

> select * from pppd order by time desc limit 10
name: pppd
time                host result
----                ---- ------
1554459314680084531 erx  0

Plex Transcodes

echo "plex_info,host=nas transcodes=`wget -q -O - "http://localhost:32400/status/sessions?X-Plex-Token=1234567890" |grep -c 'User id'` `date +'%s%N'`"

Ubiquiti ER-X Speedtest

ER-X --> Speedtest --> Telegraf --> InfluxDB

• Speedtest.net is a web service that provides free analysis of Internet access performance metrics.
• Telegraf is a plugin-driven server agent for collecting & reporting metrics.
• InfluxDB is a time series database built from the ground up to handle high write and query loads.

/root/erx_speedtest.sh

/usr/local/bin/speedtest-cli --no-pre-allocate --server 14553 --simple >/tmp/speedtest-cli_result.txt

/root/erx_speedtest_to_influx.sh

echo "speedtest,host=erx download=`grep 'Download' /tmp/speedtest-cli_result.txt | awk '{ print $2 }'`,upload=`grep 'Upload' /tmp/speedtest-cli_result.txt | awk '{ print $2 }'`" | nc 192.168.0.252 8094

Grafana

http://docs.grafana.org/features/datasources/influxdb/

https://blog.laputa.io/try-influxdb-and-grafana-by-docker-6b4d50c6a446

Network Usage

https://www.dev-eth0.de/blog/2016/12/06/grafana_snmp.html

SELECT non_negative_derivative(mean(bytes_recv),1s)*8 as "in" FROM "net" WHERE host =~ /$server/ AND interface =~ /(vlan|eth|bond|vmbr|ens).*/ AND interface !~ /veth/ AND $timeFilter GROUP BY time($interval), * fill(none)

SELECT non_negative_derivative(mean(bytes_sent),1s)*8 as "out" FROM "net" WHERE host =~ /$server/ AND interface =~ /(vlan|eth|bond|vmbr|ens).*/ AND interface !~ /veth/ AND $timeFilter GROUP BY time($interval), * fill(none)

InfluxDB2 Setup

In Docker ...

docker exec -it influxdb2 influx setup
> Welcome to InfluxDB 2.0!
? Please type your primary username traefikuser
? Please type your password ****************************
? Please type your password again ****************************
? Please type your primary organization name traefik
? Please type your primary bucket name traefik
? Please type your retention period in hours, or 0 for infinite 0
? Setup with these parameters?
  Username:          traefikuser
  Organization:      traefik
  Bucket:            traefik
  Retention Period:  infinite
 Yes
User            Organization    Bucket
traefikuser     traefik         traefik