SQL table as a datasource

Grafana MSSQL
1

Hi,
With different queries running simultaneously in a Grafana dashboard, does it not impact performance having to hit the database repeatedly for each query?
Is there a way I can have a table as a datasource ( like data access object / DAO which remains cached ) and each query is run against the cached object without having to hit the database everytime ?
Thanks

1 Like
2

Grafana Enterprise has a feature for query caching. Here’s the link to the documentation where you can read about how to set it up, but it is basically designed to do exactly the thing your’e asking about. The main tradeoff is that if Grafana caches the query, the memory needed to run Grafana increases, so that’s something to be aware of. Full details in the docs:

3

Thanks @davidallen5 for your prompt reply.
I went through the link and read about the query caching capability of Grafana. Seems query caching can be enabled/configured at datasource level so that all query results in the dashboard can be cached and cache intervals can be set at the panel level.
Is there a way I can write separate queries off the same cached object to populate different panels in the dashboard ? That way, all the queries are being run from the same cached object rather than caching all queries for the datasource.
eg.

  1. select c1, c2, c3 from tbl1 → store as cached object ( obj1 )
  2. write different queries off the above cached object for different panels in the dashboard
    say
    panel 1 → select c1, max(c2) from obj1 group by c1
    panel 2 → select c1, sum(c3) from obj1 group by c1

Thanks

1 Like
4

to the best of my knowledge, no, there isn’t a way to do that. That crosses a certain line where you’d be trying to use Grafana itself as a data source. Effectively, if you were sub-querying your cache, you’d need some kind of query execution engine inside of Grafana that could serve queries off of that cache, and honestly – that’s not normally what I think of Grafana being used for. (This is just my opinion, others may differ)

Now the use case you’re asking for is totally legitimate, but there are many different data stores on the market that do exactly that. You can for example get in-memory SQL databases where you can set up that they ingest on some interval, and then query the in-memory SQL database with Grafana so that you’re always looking at a hot in-memory view of the data. In doing this, you’d effectively get what you want.

In your local RDBMS you may also have other in-memory view capabilities which would do the same thing without an additional database. The more I think about this, the more I think creating a view inside of the SQL database is probably the right place to go. That lets you treat your datastore as a datastore, and lets you use Grafana to do data source queries.

On the Grafana level, the in-memory view is “just another table”, and effectively you’ve moved your whole caching construct from Grafana to the database itself. Serving queries against in-memory views is not a lot of load. And if load on the DB is a real concern, there are other database architecture options which will alleviate that (like read-only clones of the database, or read-only cluster members)

So I think my basic answer is “yes you can do this, but not with the architecture you suggest”

1 Like