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.
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.
Drop Data
> drop series from <measurement>; > drop measurement <measurement>;
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
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