How to decrease MariaDB add-on disk IO

As I see in the add-on source, mariadb/rootfs/etc/services.d/mariadb/run, the storage is hardcoded to be on disk.

I would like to limit how often it writes data to disk, to eg. once an hour. I’m new to HA and everything around it.

I was thinking to use MEMORY storage engine, but it has limitations, it can’t store TEXT columns, but events and states tables has multiple TEXT columns (schema).

My goal is to set up a HA database that doesn’t wear down the SD card (actually I’m using an eMMC memory with SD card adapter, but it also has limits).

Currently I’m using recorder with in-memory SQLite: db_url: 'sqlite:///:memory:', but it logs several errors like “there is no transaction to commit” if it has more than a few hours of data in it, so even the in-memory version of SQLite is unable to handle the “load” (I have 4 thermostats and some basic automation around them, nothing extreme).

Take a look at recorder, history, stats, and probably history stats. The first 2 will define how often and what HA records. The other 2 can condense other sensor readings.

An example if your temp sensor takes a reading every minute, you create another sensor to condense that to an average for x amount of readings. Then exclude the first sensor from the database but it’s still available to trigger the heating system in a minutes notice.

Thank you for the ideas!

  • The history_stats is new for me.
  • I’ve tried recorder/commit_interval=600 previously, but in this case I can’t see the statistics for 10 minutes.
  • The filter (and also statistics?) integration sensors change state/emit new value only if their input sensor changes, ie. they are continuously off by 1 precision (see). I’m using the custom average integration, that is accurate.

Finally I’ve created a custom add-on, an in-memory MariaDB, based on the official add-on, plus excluded call_service event_types from the recorder, it filled up the database. This is in experimental state, but seems to be the best solution currently if I want to avoid destroying the SD card / eMMC disk with non-critical history data.

1 Like