Stable MariaDB Size - not growing but why

Configuration
1

Hello there,

i’ve configured my HA with maria db and influx db. See the configuration.yaml below.

recorder:
  db_url: mysql://homeassistant:homeassistant@core-mariadb/homeassistant?charset=utf8mb4

#Influxdb
influxdb:
  host: a0d7b954-influxdb
  port: 8086
  database: homeassistant
  username: !secret influx_username
  password: !secret influx_password
  max_retries: 3
  default_measurement: state

Now i’ve asked myself where the data is safed. I’ve created two sensors to check the DB size:

#Sensor für InfluxDB Size    
  - platform: influxdb
    host: a0d7b954-influxdb
    port: 8086
    username: !secret influx_username
    password: !secret influx_password
    queries:
      - name: InfluxDb DB Size
        unit_of_measurement: MB
        value_template: '{{ (value | float / 1024 /1024) | round(1) }}'
        group_function: sum
        measurement: '"monitor"."shard"'
        database: _internal
        where: 'time > now() - 10s'
        field: diskBytes
        
#Sensor für MariaDB Size
  - platform: sql
    db_url: mysql://homeassistant:homeassistant@core-mariadb/homeassistant?charset=utf8
    queries:
      - name: DB size
        query: "SELECT table_schema AS 'db_name', ROUND(SUM( data_length + index_length ) / 1024 / 1024, 2) AS 'size_mb' FROM information_schema.TABLES WHERE table_schema='homeassistant'"
        column: "size_mb"
        unit_of_measurement: MB

THe measurement for influx works well, changing well etc… but for the mariadb im wondering why it dont grow. I’ve installed phpmyadmin and tried to check the database ( SELECT * FROM states) gives me a growing numer of entries. but when i fire the SQL statemant from the sensor i always get the same number:

image
image1443×468 109 KB

so the entries in the “states” table are growing but the database size is not?

Any suggestions here ?

Thank you for your participation

2

HA now uses a relation model, so if a status is now already in the database, then a relation to that status is made rather than inserting the status once more.
This means the database does not grow very much once a good portion of the possible statuses have been logged once.
You might also have hit a spot where the purging of old data is somewhat equal to the added new data.

1 Like
3

ahh okay… seems legit. So if i add a new sensor the size must definitly grow. I’ll check this on the weekend when my new temperature sensors arrive.