Marcel, would you mind sharing the SQL queries for these sensors, and are they for MariaDB or SQLite?
They are for MariaDB, here you go:
Attributes:
SELECT table_schema "database", "attributes" as "part", Round(Sum(data_length + index_length) / 1048576, 3) "value" FROM information_schema.tables
WHERE table_schema="homeassistant" and table_name in ("state_attributes")
GROUP BY table_schema;
Events:
SELECT table_schema "database", "events" as "part", Round(Sum(data_length + index_length) / 1048576, 3) "value" FROM information_schema.tables
WHERE table_schema="homeassistant" and table_name in ("events","event_data","event_types")
GROUP BY table_schema;
States:
SELECT table_schema "database", "states" as "part", Round(Sum(data_length + index_length) / 1048576, 3) "value" FROM information_schema.tables
WHERE table_schema="homeassistant" and table_name in ("states","states_meta")
GROUP BY table_schema;
Statistics:
SELECT table_schema "database", "statistics" as "part", Round(Sum(data_length + index_length) / 1048576, 3) "value" FROM information_schema.tables
WHERE table_schema="homeassistant" and table_name in ("statistics","statistics_meta","statistics_runs","statistics_short_term")
GROUP BY table_schema;
Total DB-size:
SELECT table_schema "database", Round(Sum(data_length + index_length) / 1048576, 3) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;
Thank you. You are a gentleman and a scholar.
Moved to a new topic to prevent it getting off topic in the DB size issue topic.
One minor thing I noted, your individual sensors should be MB, not Mb (bytes not bits).
Also the second query titled “Attributes” should be “Statistics”.
Finally, how did you stack the area charts?
Looking at the ApexCharts docs this only seems possible for column types.
Here are the ApexCharts settings:
type: custom:apexcharts-card
graph_span: 20d
show:
last_updated: true
stacked: true
header:
standard_format: false
show: true
show_states: true
colorize_states: true
title: test apex graph
all_series_config:
stroke_width: 1
type: area
curve: stepline
opacity: 1
statistics:
type: mean
series:
- entity: sensor.hass_db_attributes
- entity: sensor.hass_db_statistics
- entity: sensor.hass_db_events
- entity: sensor.hass_db_states
- entity: sensor.hass_db_size
type: line
stroke_width: 2
yaxis:
- min: 0
max: 3000
That did it. Thanks.
Could one of you please copy and paste the template code for the sql-templates, so other people could befit from your experience and wisdom too? That would be great…
Update
Resolved it by removing a space before the “value” in SQL statement.
No idea why this would happen!
I tried using it in the SQL Integration.
I am getting an invalid Query.
No idea whats wrong.
Do you maybe have a clue?
Have the querying working sensors are giving data.
However I don’t see any on the graph.
Will it be visible after a few days or did I do something wrong?
Btw. Took the info from this thread.
Switched also to the 2.0.4 version.
Hi @bronco , the graph is using statistics data, it should be visible after the next statitics update, in an hour or so ( i think).
The DB size changed this morning at 5 AM
Still the same. Do I need to run something to update it?
Is your graph working yet @bronco ?
Mine is the same as yours. All sensors have a value but the graph just sits there “Loading”. I also rolled back to 2.0.4. I’ve got other Apex Charts that work just fine
Got exactly the same as before.
All the entities have value but none shown in graph.
I am on 2.0.4.
Go to Developer Tools → States. Find your sensors in the list. In the right hand attribute column is a state_class attribute listed for your sensors?
If not then they will not be generating statistics and you can’t use that option in ApexCharts.
None of my database sensors had a state_class attribute so I’ve changed them to state_class: total
. I’ll give it some time to generate statistics and check again later today.
Thanks
Mine are measurement
but total should be okay.
Thanks @tom_l
Done now the same as you have.
Report back after a few hrs if it worked or not.
It now works.