Grafana: how it display the data?

Hi,

I was wondering if Grafana brings the entire data from database and then display according to the Time Range selected in the dashboard or panel.
In that case, if the database is huge, say 10-20GB, then it will eat up entire memory of the PS and stall.

Could anyone please tell how it works and what I am thinking is correct or I misunderstood?

Regards,
Deb

Hi @sendaird

None of the core grafana datasources will slurp in an entire database into the browser. Time parameters delimit the query request sent to the db.

Here is the raw query request and response from a graphite query. Note the from=-5min&until=now in the request:

{
  "request": {
    "method": "POST",
    "url": "api/datasources/proxy/1/render",
    "data": "target=aliasByNode(movingAverage(scaleToSeconds(apps.fakesite.*.counters.requests.count%2C%201)%2C%202)%2C%202)&from=-5min&until=now&format=json&maxDataPoints=1555",
    "hideFromInspector": false
  },
  "response": [
    {
      "target": "web_server_01",
      "datapoints": [
        [
          30.650000000000002,
          1631673590000
        ],
        [
          28.05,
          1631673600000
        ],
        [
          26.75,
          1631673610000
        ],
        [
          29.300000000000004,
          1631673620000
        ],
        [
          29.5,
          1631673630000
        ],
        [
          27.8,
          1631673640000
        ],
        [
          30.35,
          1631673650000
        ],
        [
          31.800000000000004,
          1631673660000
        ],
        [
          29.75,
          1631673670000
        ],
        [
          29.3,
          1631673680000
        ],
        [
          30.75,
          1631673690000
        ],
        [
          30.6,
          1631673700000
        ],
        [
          30.3,
          1631673710000
        ],
        [
          31.200000000000003,
          1631673720000
        ],
        [
          31.900000000000002,
          1631673730000
        ],
        [
          31.35,
          1631673740000
        ],
        [
          29.900000000000002,
          1631673750000
        ],
        [
          30.3,
          1631673760000
        ],
        [
          31.1,
          1631673770000
        ],
        [
          28.950000000000003,
          1631673780000
        ],
        [
          26.950000000000003,
          1631673790000
        ],
        [
          28.550000000000004,
          1631673800000
        ],
        [
          28.400000000000002,
          1631673810000
        ],
        [
          29.200000000000003,
          1631673820000
        ],
        [
          30,
          1631673830000
        ],
        [
          28.85,
          1631673840000
        ],
        [
          29.200000000000003,
          1631673850000
        ],
        [
          30.000000000000004,
          1631673860000
        ],
        [
          29.200000000000003,
          1631673870000
        ],
        [
          27.700000000000003,
          1631673880000
        ]
      ],
      "title": "web_server_01"
    },
    {
      "target": "web_server_02",
      "datapoints": [
        [
          28.700000000000003,
          1631673590000
        ],
        [
          28.4,
          1631673600000
        ],
        [
          28.6,
          1631673610000
        ],
        [
          27.700000000000003,
          1631673620000
        ],
        [
          27.25,
          1631673630000
        ],
        [
          26.5,
          1631673640000
        ],
        [
          28.450000000000003,
          1631673650000
        ],
        [
          30.550000000000004,
          1631673660000
        ],
        [
          28.950000000000003,
          1631673670000
        ],
        [
          29.1,
          1631673680000
        ],
        [
          32.7,
          1631673690000
        ],
        [
          33.150000000000006,
          1631673700000
        ],
        [
          29.85,
          1631673710000
        ],
        [
          28.950000000000003,
          1631673720000
        ],
        [
          30.1,
          1631673730000
        ],
        [
          28.450000000000003,
          1631673740000
        ],
        [
          27.85,
          1631673750000
        ],
        [
          28.400000000000002,
          1631673760000
        ],
        [
          27.3,
          1631673770000
        ],
        [
          29.05,
          1631673780000
        ],
        [
          29.55,
          1631673790000
        ],
        [
          29.55,
          1631673800000
        ],
        [
          30.400000000000002,
          1631673810000
        ],
        [
          29.900000000000002,
          1631673820000
        ],
        [
          28.6,
          1631673830000
        ],
        [
          28.25,
          1631673840000
        ],
        [
          28.650000000000002,
          1631673850000
        ],
        [
          29.75,
          1631673860000
        ],
        [
          28.6,
          1631673870000
        ],
        [
          28.5,
          1631673880000
        ]
      ],
      "title": "web_server_02"
    },
    {
      "target": "web_server_03",
      "datapoints": [
        [
          29.200000000000003,
          1631673590000
        ],
        [
          29.550000000000004,
          1631673600000
        ],
        [
          29.550000000000004,
          1631673610000
        ],
        [
          27.35,
          1631673620000
        ],
        [
          27.5,
          1631673630000
        ],
        [
          29.6,
          1631673640000
        ],
        [
          29.800000000000004,
          1631673650000
        ],
        [
          28.550000000000004,
          1631673660000
        ],
        [
          28.700000000000003,
          1631673670000
        ],
        [
          29.900000000000002,
          1631673680000
        ],
        [
          29.450000000000003,
          1631673690000
        ],
        [
          29.200000000000003,
          1631673700000
        ],
        [
          30.400000000000002,
          1631673710000
        ],
        [
          31.150000000000002,
          1631673720000
        ],
        [
          31.3,
          1631673730000
        ],
        [
          29.9,
          1631673740000
        ],
        [
          28.6,
          1631673750000
        ],
        [
          28.900000000000002,
          1631673760000
        ],
        [
          28.6,
          1631673770000
        ],
        [
          26.9,
          1631673780000
        ],
        [
          27.65,
          1631673790000
        ],
        [
          31.3,
          1631673800000
        ],
        [
          31.550000000000004,
          1631673810000
        ],
        [
          29.950000000000003,
          1631673820000
        ],
        [
          30.700000000000003,
          1631673830000
        ],
        [
          30.450000000000003,
          1631673840000
        ],
        [
          31.05,
          1631673850000
        ],
        [
          30.200000000000003,
          1631673860000
        ],
        [
          28.75,
          1631673870000
        ],
        [
          30.200000000000003,
          1631673880000
        ]
      ],
      "title": "web_server_03"
    },
    {
      "target": "web_server_04",
      "datapoints": [
        [
          30.85,
          1631673590000
        ],
        [
          28.9,
          1631673600000
        ],
        [
          28.05,
          1631673610000
        ],
        [
          29.15,
          1631673620000
        ],
        [
          29.15,
          1631673630000
        ],
        [
          26.85,
          1631673640000
        ],
        [
          27.150000000000002,
          1631673650000
        ],
        [
          28.900000000000002,
          1631673660000
        ],
        [
          29.800000000000004,
          1631673670000
        ],
        [
          29.1,
          1631673680000
        ],
        [
          30.1,
          1631673690000
        ],
        [
          31.050000000000004,
          1631673700000
        ],
        [
          29.050000000000004,
          1631673710000
        ],
        [
          30.950000000000003,
          1631673720000
        ],
        [
          32.650000000000006,
          1631673730000
        ],
        [
          30.25,
          1631673740000
        ],
        [
          28.25,
          1631673750000
        ],
        [
          28.900000000000002,
          1631673760000
        ],
        [
          28.800000000000004,
          1631673770000
        ],
        [
          28.200000000000003,
          1631673780000
        ],
        [
          28.700000000000003,
          1631673790000
        ],
        [
          28.6,
          1631673800000
        ],
        [
          28.9,
          1631673810000
        ],
        [
          29.55,
          1631673820000
        ],
        [
          28.6,
          1631673830000
        ],
        [
          27.700000000000003,
          1631673840000
        ],
        [
          28.65,
          1631673850000
        ],
        [
          29.5,
          1631673860000
        ],
        [
          29.9,
          1631673870000
        ],
        [
          29.4,
          1631673880000
        ]
      ],
      "title": "web_server_04"
    }
  ]
}

Hi @mattabrams

Thank you for your reply and the example.

Here is a little detail about my system. I created a db (mariaDB) with 13 years of data, one per-hr, from 2008 to 2021. Altogether total number of rows: 113976.

Now, even if I set the time-range in the Dashboard or Panel, say for two years (dashboard setting - From: now-5y, To:now-3y) OR say in one Panel query, Query options - Relative time: now-2y, it is bringing data for the fields as per Query, but for entire 13 years.

Following is the query is wrote. ‘measurement’ is the table name, ‘temperature’ , ‘batt_id’, ‘cell_id’, and ‘datetime’ are the fields. Set variables for ‘batt_id’ and ‘cell_id’.


SELECT
datetime AS “time”,
temperature
FROM measurement
WHERE
batt_id = $BATT_ID and
cell_id = $CELL_ID
ORDER BY datetime

This is the query response:

SELECT
datetime AS “time”,
voltage
FROM measurement
WHERE
batt_id = 3 and
cell_id = 1
ORDER BY datetime

If I look at the Query inspector (Refresh) then it is as follows. This one I can not edit. The time range part seems quite different from the one you gave.


Object
request:Object
url:“api/ds/query”
method:“POST”
data:Object
queries:Array[1]
range:Object
from:“1568611249936”
to:“1631769649936”
hideFromInspector:false
response:Object
results:Object
A:Object

I may have some basic misunderstanding due to inexperience in both Grafana and mariaDB. So try to give as much detail as I thought may needed. Could you please tell which part I should edit/change to get only the specified time-range, not all the rows?

Thanks and regards,
Deb

Hi @sendaird

I would suggest reading our documentation on global variables, and using the examples as a guide. Global variables include numerous time macros that might help you with your query.

Hi @mattabrams

Thank you.

Deb