mariaDB is it worth it now Version 2 of the database is ruining

Tags: #<Tag:0x00007f739a6340d8>

Hi all I’ve just start my journey with Hassio running with a Pi 3b booting and running from a 5300rpm hard drive
My question is it worth installing mariaDB now version 2 for the database is released

I started with a 7 day set of data collecting a lot of my sensors that levelled out at about 1.5GB. I had a lot of exclusions already but after being methodical I got this size down to 850MB. I still have pages and pages of 24hr and 7 day graphs in my config but am only recording what I need to.

After the recent optimisations in 0.112, my old states data are still being purged but I’m down from 850MB to 350MB and still falling nightly (I suspect it will end up at about 250 to 300MB). So the recent optimisation has been very significant.

So much so that you are right to question the need for MAriaDB, however, the same optimisations that were applied to the core database structure were also applied to MariaDB. So any advantage MariaDB had still remains (speed, reliability, etc…).

Thanks Tom Time for a play then

I expanded my comment a bit to give you an idea of how significant these recent changes have been for me. When it does finally level off in a couple of days I’m going to thank bdraco in the 0.112 release notes topic. This has been a massive improvement.

1 Like

Thanks for the info Tom I think Ive installed and have it running but I cant find it on the Pi

It’s an addon, so it exists in it’s own docker container. You wont see a database in /config.

ah thanks for that

Here’s a sensor to keep an eye on its size, if you want it:

- platform: sql
  db_url: !secret mariadb_url
  - name: Database Size
    query: 'SELECT table_schema "database", Round(Sum(data_length + index_length) / 1048576, 2) "value" FROM information_schema.tables WHERE table_schema="homeassistant" GROUP BY table_schema'
    column: 'value'
    unit_of_measurement: MB

Thanks Tom I will give it ago tonight how do you manage the size of the data base if you can get to it how do you delete it ?

To delete it, reinstall the addon.

To manage the size I did this: How to reduce your database size and extend the life of your SD card

Just a note - my main reason to go with MariaDB in the host system (not addon) was the fact that the internal database or a MariaDB database from the addon is included in the system snapshot making them massive and take a long time to take… Historical data is nice, but not essential, so I don’t mind losing it. Wish I could exclude data from the snapshot.

Yeah that is an issue