MariaDB; size almost 2GB... How to limit?

Configuration
1

I have excluded all ‘domains’ from ‘data-base’ but the ‘data-base’ is still growing.
Now almost 2GB size.
I also have some automation - to purge the DB, but looks like it is poorly working.
What I have wrong set-up? Any practical info…?

maria

# Set-up MariaDB for Hass.io
recorder:
  db_url: mysql://hass:password@core-mariadb/homeassistant?charset=utf8
#  include:
#    domains:
#      - sensor
  exclude:
    domains:
     - alarm_control_panel
     - automation
     - binary_sensor
     - camera
     - device_tracker
     - group
     - light
     - miele
     - persistent_notification
     - person
     - sensor
     - sun
     - switch
     - weather
     - zone
  #  entities:

#Tracking history
history:

purge
purge1270×569 72.7 KB

2

It’s not clear what your automation does…
Try to call service recorder.purge manually
If it won’t help, try to call it with repack attrubute

***Ooops, my bad… looks like repack supported by SQLite only(

3

Yes, I not attached the ‘action’ part.
It should be refreshed every 2 days.
At least that was the intention of this automation…

purge
purge1297×374 46.1 KB

4

limit the days for the recorder

5

Not clear… I understood ‘keep_days: 2’ so this is what the parameter is exactly doing…

6

I’m not sure if it is still an issue but the purge day counter used to reset every time you restarted home assistant. So if you have been restarting every day it would never purge.

7

Really…? I’m recently restarting it few times per day when making changes…

8

@dariusz How did you create the sensor for MariaDB size ?
Could you please share the code?

9

Yes, please see:

# Sensor part  
sensor:

#https: //www.home-assistant.io/components/sensor.sql/
  - platform: sql
    db_url: mysql://hass:password@core-mariadb/homeassistant?charset=utf8
    queries:
      - name: 'DataBase size'
        query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
        column: 'value'
        unit_of_measurement: MB

Then you can use it (example) to display this info:

sensor

Card configuration:

cards:
      - cards:
          - content: >-
              **domains:** {%- set unique_domains = states |
              map(attribute='domain') |list | unique | list -%} {%- for domain
              in unique_domains -%} {{"\n"}}- {{domain}} {%- endfor -%} {{"\n"}}
            type: markdown
          - content: >-
              **entities:** {%- for state in states -%} {{"\n"}} -
              {{state.entity_id}} {%- endfor -%}
            type: markdown
          - entities:
              - entity: sensor.database_size
            show_header_toggle: false
            title: Maria DataBase size
            type: entities
        type: horizontal-stack
    panel: true
    path: system-info
    title: System info

P.S. In above card config. 1st 2 are showing ‘domains’ and ‘entities’.
The 3rd one is for DB size…

3 Likes
10

Thanks for the code above to show Maria DB size as a sensor.
If anyone else tries it and is alarmed that their table apparently is 8GB or so (like I was!) note that the value returned by the code above is in KB not MB! Just divide by 1024 again:

query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema;'
6 Likes
11

Very cool. I think the card formatting may be indented off a bit:

cards:
  - cards:
      - content: >-
          **domains:** {%- set unique_domains = states |
          map(attribute='domain') |list | unique | list -%} {%- for domain
          in unique_domains -%} {{"\n"}}- {{domain}} {%- endfor -%} {{"\n"}}
        type: markdown
      - content: >-
          **entities:** {%- for state in states -%} {{"\n"}} -
          {{state.entity_id}} {%- endfor -%}
        type: markdown
      - entities:
          - entity: sensor.database_size
        show_header_toggle: false
        title: Maria DataBase size
        type: entities
    type: horizontal-stack
panel: true
path: system-info
title: System info

However either way when I paste the above in a manual card I get no card type found and in the bottom under the configuration I get: Cannot read property ‘setConfig’ of undefined

Thoughts?

12

Code you use shows entire view, not just card. Use this instead:

cards:
  - content: >-
      **domains:** {%- set unique_domains = states |
      map(attribute='domain') |list | unique | list -%} {%- for domain
      in unique_domains -%} {{"\n"}}- {{domain}} {%- endfor -%} {{"\n"}}
    type: markdown
  - content: >-
      **entities:** {%- for state in states -%} {{"\n"}} -
      {{state.entity_id}} {%- endfor -%}
    type: markdown
  - entities:
      - entity: sensor.database_size
    show_header_toggle: false
    title: Maria DataBase size
    type: entities
type: horizontal-stack
13

Thanks. Not sure if it’s working wrong or that’s what you wanted to happen with the above for your setup. For me think I’ll net out at:

image

entities:
  - entity: sensor.database_size
title: MariaDB
type: entities
show_header_toggle: false

So just show the DB size.

With the domains and entities as is you get the list of all of them

image
image443×513 12.2 KB

14

Hi, I think this is what you are looking for: Optimize Your Home Assistant Database

This article is a very good summary, what should we do in case of the huge MariaDB size. Let me know, if it helped you also.