I don’t know if this type of config is possible, but i still believe there is some solution to be found.
I use Grafana to visualise data that is being written to Postgre database (with TimescaleDB). My goal is to create a live-data monitoring dashboard, which represents values being written to Postgre.
To simulate data flow, i use basic SQL script that pushes 1000000 values to two variables (single query):
do $$
begin
for r in 1..10000000 loop
insert into public.timescale_test(int_param, int2_param, time) values(r,1000000 - r,clock_timestamp());
end loop;
end;
$$;
While fill-up query is running (about 1 min) Grafana remains silent and shows graphs only when filling-up is done.
In final project data will be delivered proper way (1 query - 1 row), but i’m concerned that Grafana will be able to draw real-time graphs. Is it just the long query issue?
I would say so. If I’m not mistaken, the do $$ ... $$; block represents a single SQL transaction, so nothing will show up in a SELECT query until the write is completely done.
Depends on what you mean by “real-time”. Grafana will refresh the chart at whatever frequency you’ve set, and show whatever your queries return at each refresh. So you can probably set a short refresh rate to achieve your objective.
To me, true “real-time” mode would imply that Grafana would be aware when new data is written, and display it immediately - kind of like live streaming. But that’s not possible for SQL data sources.
“Live streaming” is exactly what i had in mind beginning my attempt. Now i think about workaround: sending data to some web-service with no problems letting Grafana to retrieve data and draw a graph, and only then burn data to Postgre.
Can you explain a bit more? I’m not quite clear what you have in mind here.
To expand on my previous answer, at the moment Grafana has quite limited capabilities in terms of true live streaming - though these are expanding, especially in v8. Also very few (almost no) datasources actually support live streaming (i.e. data push).
So the most common workaround is to simply have queries executed - and data refreshed - every few seconds. Would that work for you? Or do you need to have high-frequency refresh?
Data is being received every 250-300ms. Sending it rigth to Postgre via query is fine, takes <200 ms to execute. If i got it right, Grafana won’t keep up on that pace due to necessity of SQL in receiving directly from database. My idea is to run service, which can serve as a datasource for Grafana and feed data more gradually, retrieving it from Postgre. I’m about to try out Graphite, though Postgre will probably have to go then. To my needs, it’s better to have some delay (~2 seconds), but consistent refresh rate.
If I were you, I’d just try it with your existing Postgres/Timescale, and optimize further if you encounter issues there. 250-300ms isn’t actually that fast after all. If you don’t have a lot of network latency between your database, Grafana server, and client, then you can generally run queries at periods around (or below) 100ms - as long as you’re not querying large amounts of data.
Not really sure how that solves the (potential) problem. Graphite might have superior performance in some situations, but you’re still facing the same fundamental problem of needing to potentially run queries very frequently.
