Home-assistant.db size

Configuration
21

Thanks VDRainer, I just tried this on my setup (I’m still using the SQLite) and it worked great!

22

I guess in some sense you can “just delete the db” - as long as you don’t mind a s-ton of error messages in the log:

Error executing query: (sqlite3.OperationalError) no such table: events [SQL: 'INSERT INTO events (event_type, event_data, origin, time_fired, created) VALUES (?, ?, ?, ?, ?)'] [parameters: ('call_service', '{"service": "clear", "domain": "system_log", "service_data": {}, "service_call_id": "1974566160-6"}', 'LOCAL', '2018-07-22 00:08:51.169303', '2018-07-22 00:09:20.687270')] (Background on this error at: http://sqlalche.me/e/e3q8)
5:09 PM components/recorder/util.py (ERROR)
23

Rather than the nuclear approach of deleting the database, you can just install the sqllite utility

apt install sqlite3

and then, after stopping hass, open the database, with sqlite:

$sqlite3 home-assistant_v2.db

and run the following SQL commands to truncate the data in the table. You can change the ‘-30 days’ to however far you wish to go back from today, and it will delete everything that’s earlier than that.

delete from events where time_fired < datetime('now','-30 days');
delete from states where created < datetime('now', '-30 days');

SQLite does not recover the disk space after you have done this, so it is necessary to ‘vacuum’ the database. Simply issue the following command:

vacuum;

And then once this has all completed, exit the sqlite client, and start hass again.

4 Likes
24

For mysql/mariadb:

Nuke anything older than 5 days from “now”:

delete from events where time_fired < timestamp(NOW() - 5);
delete from states where created < timestamp(NOW() - 5);

1 Like
25

thank you for this, my db reached 1.43 now xD

image
image815×64 5.12 KB

Can we set this as a package or use it in the main configuration.yaml?

26

did you got this working, im in the same spot, dont need the sun.sun data, is youre example working?

27

Yes, I managed to keep my DB under 250mb, which made my logbook and history overview load very quickly. I found out that some ESP8266 sensors I programmed myself where sending data every second. Changing that to eveery minuted or every 5 minutes made a big difference.
In my configuration file I also excluded some stuff for the recorder:

recorder:
  purge_keep_days: 2
  exclude:
    domains:
      - weblink
      - updater
    entities:
      - sun.sun
      - sensor.yr_symbol
      - group.leds
1 Like
28

I came across this topic by accident but I have related question: Is there some way how to “decimate” the stored history data for specific sensor? What I mean:
I have sensor that updates let’s say every 30 seconds. I want this frequent updates so I can quickly respond with automations, have nice short-term graphs (1hour span) etc. But I also want to make long-term graphs. But for 30days, 180 days…graph I really don’t need datapoints every 30seconds.
I would love to have some settings like for data older than 24hours, keep only every 10th value or so…

Or can you guite me to some other way how to make long term graphs without having huge database? Thanks

1 Like
29

I’ve also been following this. My DB is now over 1.5G, and still growing slowly.

I noticed in a recent release notes (which I can’t find now) there was some mention of fixing a bug which caused the purges not to work.

Does anyone know more about this?

Should I expect the DB to start shrinking now? Or at least stop growing?

Is there some additional step I need to take to shrink it?

30

I think the bug you are referring to is fixed. I had the same problem with DB approaching 2GB, despite having purge setting configured. But yesterday I updated to latest (0.95.4) and today the DB is down to about 200MB. So tentatively it’s looking good.

I have the following configured:

# (Added) DB purge settings
recorder:
  purge_keep_days: 30
  purge_interval: 1

And here are the file sizes.

@ubuntuha:~/hass$ du -h home-assistant_v2.db
193M    home-assistant_v2.db

Other than upgrading I did nothing else. The recorder configs where there for a long time before upgrading seemingly doing nothing per the previous bug.

1 Like
31

Just to be sure: Recorder has nothing to do with storing to influxdb ?
What I mean, when I set recorder to purge every day, I don’t lose any data in Influxdb ?

Have a .db of 5 Gb and an old version of 11 Gb now:crazy_face:

32

It depends which database you are using. If you have a MariaDB/MySQL database or something like that, you should be able to create a SQL script to remove the superfluous data points further out.
You need to be careful with the purging of the database from within HomeAssistant, of course

1 Like