Materialized view and grafana metrics not matching up

1

I created a materialized view which I get from telegraf data.

CREATE MATERIALIZED VIEW public.net_bytes_5min
WITH (timescaledb.continuous) AS 
SELECT time_bucket('5 mins',t."time") as time,
tt.host, tt.interface, avg(bytes_recv) as avg_bytes_recv,avg(bytes_sent) as avg_bytes_sent FROM telegraf.net t telegraf.net_tag tt WHERE (t.tag_id = tt.tag_id) group by 1,2,3;

I have a query (bytes_recv) which plots the delta,

WITH delta as (
  select time,host,interface,(avg_bytes_recv-lag(avg_bytes_recv,1) OVER (partition by interface order by time)) as bytes_recv from net_bytes_5min where host in ('${server}') AND $__timeFilter("time") group by time,host,net_bytes_5min.avg_bytes_recv,interface order by time)

SELECT time,interface,bytes_recv from delta host in ('${server}') AND $__timeFilter("time")  group by delta.time,delta.host,delta.interface

I am not sure why I am getting very large values when I do the charting. Does by materalized view look fine?

2

What does anyone think?

3

Hi @Rita_Morgan , delta lag looks right, have you checked any anomaly in your data? Have you tried an extra last order by in the outer sql query?

...
SELECT time,interface,bytes_recv from delta host in ('${server}') AND $__timeFilter("time")  group by delta.time,delta.host,delta.interface 
ORDER BY time

Can you isolate a small POC and show how the data processing is failing?

4

Thanks for your reply. I don’t see any anomaly. I have a 10G link but I see on grafana that its hitting close to 100GiB/s. Which isn’t theoretically possible.

Take a slice, 2:40 to 2:50. There was a lot of traffic at that time. When I look at the 10sec interface it looks right.

However, it seems when I do the avg its summing up a lot more. Maybe its combining multiple interfaces? I am wondering if avg is what I should do. Instead maybe get max?

5

If bytes_recv is accumulative getting the max would work.

Probably yes.

6

max seems a bit better. But the value is still high. I am not sure why thats the case. Avg makes the most sense but values in grafana are way off.