Large homeassistant database files

Configuration
341

What was the Nodon switch doing? How did you trace it back to that sensor?

342

Well, it totally spammed the log. I traced it by looking in the ozw.log and then by running the query by @seren Large homeassistant database files

It was quite obvious that my two Nodon switches were spamming.
I tried to configure them to only report what I needed, but at no avail. So I sold them.

343

Hi all,

If anyone is using postgresql and can help me with my purge issue it would be much appreciate

344

I’m having trouble now and then with the size of the HA database - I will then go to the /dev-service tab and use this (takes a while before you will see the results):

image
image767×508 26.3 KB

4 Likes
345

Hi, since events table keeps 2/3 of DB size (and it is not useful as historical data), is it possible to record only “states” excluding “events”?

346

I believe if you turn off logger, you’ll remove the events. I’ve never done it so take this with a grain of salt. Logger is what uses events inside your history.

347

Have a look at this post.

1 Like
348

I’ve compressed the tables a few months back. if I recall the event table went from 2.3G to 1.7G. this is the command ran for each table.

ALTER TABLE homeassistant.events ROW_FORMAT=Compressed

349

how dit you make this sensor?

350

Have a look into the sql sensor.

There are examples for the databases sizes when you are using postgres or mysql / mariadb.

351

Hi,

I followed https://www.home-assistant.io/integrations/sql/.

This is my sensor:

- platform: sql
  db_url: !secret db_connection_url
  queries:
    - name: 'ha_db'
      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;'
      column: 'value'
      unit_of_measurement: MB

where this is in secrets.yaml:
db_connection_url: mysql://<user in maria db>:<maria db password>@core-mariadb/homeassistant

Good luck!

3 Likes
352

hi found another solution with filesize integration buth thanks!

353

If your system on HA is slowing down on an RPi3, don’t start to fiddle around with your database.
The reason is very likely the limited RAM of your Pi.

So the real solution for this problem is to upgrade to an RPi4 with 4 or 8 Gb of RAM. I have done that and my system flies! You can use my extensive installation guide to get HA running on an RPi4:

1 Like
354

Is it possible to move the SQLite database to a external drive (away from the SD card) using a symlink on HA OS?
It has very limited options on terminal so this is what I was wondering. And I guess even if so this would result in an unsupported state, wouldn’t it?

355

You can specify the location of the database so you wouldn’t even have to use a symlink.

Example:

recorder:
  db_url: sqlite:////home/user/.homeassistant/test
1 Like
356

What´s the default path for SQLite on Home Assistant OS?

357

The SQL in this old post to get info on what devices are causing the most number of events didn’t work for me. Possibly because the post is so old or possibl because I’m using MariaDB.
In any case, this worked for me:

SELECT verrijkt.entity_id, SUM(LENGTH(attributes)) size, COUNT(*) count, SUM(LENGTH(attributes))/COUNT(*) avg

FROM (SELECT new_states.entity_id, events.event_id, new_states.attributes FROM events LEFT JOIN states as new_states ON events.event_id = new_states.event_id) as verrijkt

GROUP BY verrijkt.entity_id

ORDER BY size DESC

I hope I did it right because my SQL writing days are over a decade ago … :wink:

358

I use SQLite3.

Here you have how to connect and 2 query samples:

From the Linux command prompt:

sqlite3 home-assistant_v2.db

.header on

.databases

SELECT entity_id, COUNT(*) as count FROM states GROUP BY entity_id ORDER BY count DESC LIMIT 90;

SELECT entity_id, COUNT(*) as count FROM events GROUP BY entity_id ORDER BY count DESC LIMIT 30;

359

SELECT entity_id, COUNT(*) as count FROM states GROUP BY entity_id ORDER BY count DESC LIMIT 90;
works great but

SELECT entity_id, COUNT(*) as count FROM events GROUP BY entity_id ORDER BY count DESC LIMIT 30;

gives error cause there is no such column in the events table (no such column: entity_id).

1 Like
360

For anyone struggling with database size - you can try using addon which shows what’s using most space: Dbstats - Addon for database usage analytics

2 Likes