Graphing the sum of several counters

InfluxDB 2 Dashboards
1

Hi, I’m doing a dashboard in Grafana to monitor the internals of InfluxDB.

I want a graph showing the total number of series.
I use Telegraf to obtain internal metrics from InfluxDB, so I have measurement “influxdb_database” with “numSeries” value and a tag for “databases”.
Each 10s it sends one insert for each database with the number of series for that particular one.

First I made a “Singlestat”, showing just the sum of all databases for the last data:

SELECT sum(numSeries) FROM "influxdb_database" WHERE time > now() - 15s GROUP BY time(5s) fill(none)

As I know metrics are coming each 10s, looking in 15s windows I will obtain just one “set” of metrics (just once for each database).
Then I made groups each 5s, sum all of them and discard null values (with fill none).

This works but I think is a little bit ugly, and, maybe, if the inserts are not received in the same “instant” sometimes the query return erroneous values.

To graph this my only idea is to group by periods of 10s, but it returns too many points for a timespan of 30d (259k points):
SELECT sum("numSeries") AS "numSeries" FROM "influxdb_database" AND time > now() - 30d GROUP BY time(10s) fill(null)

And, like before, if the inserts don’t come in the same “instant” the value could be miscalculated.

Generalizing the question, if we have 10 databases and we receive the metrics dispersed in 10s slots, how could be done to sum just the last of each one?

If we take a 10s window, you could be missing one metric or taking it two times (supposing they could have some jitter).

Thanks!

2

Hi adrianlzt,

Did you try using nested queries? You would need InfluxDB 1.2 for them. With nested (or sub) queries you could do something like: ```SELECT SUM(s) FROM (SELECT LAST(numSeries) as s FROM influxdb_database GROUP BY “database”)`

3

Thanks, that works for numSeries.

Now I’m trying to get a table of disk consumption of each database.
Should be something like:
SELECT "database",t FROM (SELECT SUM(s) as t FROM (SELECT "database",LAST("diskBytes") as s from "influxdb_tsm1_filestore" where time > now() - 15s AND "database" =~ /A.*/ GROUP BY "database","path") GROUP BY "database")
But that query is breaking influx. I’m in 1.2.0, probably fixed in 1.2.1.

4

What you should do I think is sum the diskBytes of the shards on every host (for all databases or the one you are interested in only) something like select sum(bytes) as totalBytes from (select last(diskBytes) as bytes from "influxdb_tsm1_filestore" where "database" = '<DB_NAME>' and hostname = '<HOSTNAME>' group by id). Skip the database and hostname conditions if you need all databases and you have a single host.

Alos why do you move the data from the _internal database?

5

And yes get the latest InfluxDB as I believe there was a bug using nested queries and LAST.

6

I’m using the database generated by Telegraf.

I have only one node but I want the sum per each database (my databases can be created dynamically)