MariaDB SQL sensors

Configuration
1

Marcel, would you mind sharing the SQL queries for these sensors, and are they for MariaDB or SQLite?

2

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;
3 Likes
3

Thank you. You are a gentleman and a scholar.

4

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.

5

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
1 Like
6

Hmm. That is not stacking them. I copied your config and just changed to my sensor entity_ids:

Untitled
Untitled1758×1240 178 KB

7

Forgot to mention: I still use ApexChart 2.04 because of: this bug

8

That did it. Thanks.

9

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…

10

They are in the second post.