Home Assistant DB question

Configuration
1

My first question here in HA community :slight_smile:

My default DB (SQLite) is getting bigger and corrupted. Tried to setup InfluxDB but wondering why home-assistant_v2.db still growing. I checked from InfluxDB GUI and see the data is still coming to them. What’s mechanism they store data in database?. A few days in default and later in Influx?. I am confused here.

Below is my confing on Influx from configruation.yaml

"
influxdb:
host: localhost
port: 8086
database: !secret hadatabase
username: !secret hausername
password: !secret hapassword
max_retries: 3
default_measurement: state
"

2

You can not use influxDB as the home assistant recorder database. It can only be used as a parallel storage database, generally used for long term storage of history data, while the recorder DB is used for short term storage.

Try MariaDB instead. There’s an addon for it if you are running a supervised version of Home assistant.

3

Thanks!. I see. The SQLite is still there. My understanding is, we should limit the size of recorder database and let data grow in Influx DB instead. How should we do this?.

For Maria DB, can we use it to replace SQLite in this case?

4

Yes you can. It uses a bit more memory but is far less prone to corruption and is more responsive on lower spec’d hardware. Definitely worth doing. Read the documentation on how to do this.

Like this:

As well as only recording entities that I’m interested in short term data for I also only include the entities that I’m interested in long term data for in InfluxDB (it uses the same include/exclude system as the recorder).

5

Thanks Tom for quick reply. Really help!.

I just installed it. However I have no idea how to check it’s up and running. There is side tab like Influx DB that I can check. Nothing related to MariaDB in configruation.yaml too. Basically, I need to know

  • How big of DB file?
  • How to check our data flowing in to Maria DB

Do I need to load in Portainer to check as I understand that it’s run on different container?

6

If you have history graphs in your more info pop-ups (and have deleted home-assistant_v2.db) it’s working.

Easiest way to check the size is with this sensor:

- platform: sql
  db_url: !secret mariadb_url
  queries:
  - 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

URL secret:

mariadb_url: mysql://USERNAME:PASSWORD@core-mariadb/homeassistant?charset=utf8

Where the username and password are the ones you used to set up MariaDB.

Portainer is optional. There’s a better addon for MariaDB investigation:

2 Likes
7

Apologise for my knowledge. with quote above (about platform sql), how could I reference it from YAML file?.

8

Reference what?

9

My bad. It’s not configuration.yaml. I mean how to display this in lovelace from code above?.

10

Put the created sensor in an entities card or a sensor card or a history graph card.

11

In addition to the sensor Tom linked above, you can install the myphpadmin addon to view the database contents.

12

Way ahead of you David :slight_smile:

1 Like
13

Thank you all. My bad. Typo in configuration. Now working great!.

1 Like